sql202 sql server sql manual

102
Accelerated Computer Training for Working Professionals Bookstore Case Orange Coast Database Associates Course (800)355-9855 or http://ocdatabases.itgo.com Orange Coast Database Associates Specializing in Microsoft Office, Access, SQL, and related technologies Classes custom designed forWorking Professionals http://ocdatabases.itgo.com San Juan Capistrano, CA (800)355-9855 SQL202 Accelerated Introduction to SQL Using Microsoft SQL Server

Upload: dan-durso

Post on 21-May-2015

676 views

Category:

Technology


12 download

DESCRIPTION

SQL202 Accelerated Introduction to SQL Server.

TRANSCRIPT

Accelerated Computer Training for Working Professionals Bookstore Case

Orange Coast Database Associates Course (800)355-9855 or http://ocdatabases.itgo.com

Orange CoastDatabase Associates

Specializing in Microsoft Office,

Access, SQL, and related technologiesClasses custom designed forWorking Professionals

http://ocdatabases.itgo.com

San Juan Capistrano, CA

(800)355-9855

SQL202 Accelerated Introduction toSQL Using Microsoft SQL Server

1

Bookstore SQL202 1

An accelerated introduction to SQL for non-programmers

P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com

Welcome to SQL202 –

Accelerated Introduction toSQL Using MS SQL Server

Bookstore SQL202 2

Accelerated Introduction toSQL

• Introduction (s)

• Facilities

• Course Packet (contents may vary)– Student questionnaire

– Collaterals (Maps, Catalogs, Etc.)

– PowerPoint handouts for all sessions

– Evaluation form

– Training certificate

2

Bookstore SQL202 3

SQL Curriculum

SQL200*

SQL200S*

SQL212Oracle

SQL202Transact-SQL

SQL201WMySQL

* = included inabove courses

Bookstore SQL202 4

Accelerated Introduction to SQL

• Assumes no prior knowledge of SQL

• Quick pace for experienced computer users

• End-user, not programmer, oriented

• Focus is on SQL, not Management Studio

• SQL200s is first two modules of SQL200,otherwise identical.

3

Bookstore SQL202 5

Accelerated Introduction toSQL

• Select– Basic– Filters– Special Operators– Multi-table retrieval

• Joins• Subqueries• Unions

– Calculations andAggregates

SQL Covered (Day 1: All Day):

Bookstore SQL202 6

Accelerated Introduction toSQL

• Data Updates– Insert– Update– Delete

• Data structures– Create– Drop– Alter

SQL Covered (Day 2: AM Only):

4

Bookstore SQL202 7

Accelerated Introduction toSQL

• 3 Sessions

• Lecture

• Demo

• Student “hands-on”

• Many exercises are cumulative –later examples build on queriescreated earlier

Course Format:

Bookstore SQL202 8

Accelerated Introduction toSQL

• Session 1 – Basic SQL

• Session 2 – Multi-tableRetrieval

• Session 3 – Modifying Data

Course Schedule (“1/2” day sessions):

1

Bookstore SQL200 Module 1 1

SQL200Based on SQL Clearly Explained by Jan Harrington

SQL Programming

Module 1 – Relational Database Background,Basic Single Table Retrieval Operations

Note on SQL200 Slides

• These slides were originally designed to support thesingle SQL200 course which was used for any ofMS Access, MySQL, Oracle and SQL Server.

• As such you may see here slides developed in anyone of the above products.

• We are in the process of migrating the Oracle slidesand the MS Access slides out into their own slidesets. The SQL200 slides will cover MySQL andSQL Server which are virtually identical forpurposes of this course.

Bookstore2 SQL200 Module 2 2

2

Bookstore2 SQL200 Module 2 3

Warning!

• Below are some table name changes to beaware of in doing queries. We have createdsynonyms so either name should work.

New Name Old Name

Orders Order_filled

Order_Lines Orderlines

Bookstore2 SQL200 Module 2 4

SQL200 Contact Information

P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://[email protected]

Copyright 2001-2011. All rights reserved.

3

SQL200 Resources

• Bookstore database scripts found onbox.net at

http://tinyurl.com/SQLScripts

• Slides can be viewed on SlideShare…

http://www.slideshare.net/OCDatabases

• Follow up questions?

[email protected]

Bookstore SQL212 Module 1 5

Bookstore SQL200 Module 1 6

SQL Programming

• Course focus is SQL language

• Widely used for:– Database administration

– Enterprise application development

– Data driven web sites

• A foundation skill for eBusiness andalmost all major business applications thatuse relational databases

4

Bookstore SQL200 Module 1 7

SQL Programming

• A basic knowledge of query systems,perhaps via MS Access, or someprogramming knowledge, is desirable

• We will use GUI tools or SQL Plus almostexclusively

Bookstore SQL200 Module 1 8

Relational Database Evolution

• Based on Codd’s paper

• Early commercial efforts focused on Unix

• First mainframe implementation by IBM -precursor to today’s DB2

• First PC implementation in early 80’s byOracle

5

Bookstore SQL200 Module 1 9

Relational Database Basics

• Storage

• Databases

• Tables

• Rows

• Columns

• Indexes

• Views

• Cursors

• Application interfaces

Bookstore SQL200 Module 1 10

Relational Database Table

6

Bookstore SQL200 Module 1 11

Constraints

• Database

– Domain

– Uniqueness

– RelationshipCardinality

• 1 to 1

• 1 to N

• Other Business Rule

– Triggers

– Stored Procedures

Bookstore SQL200 Module 1 12

Relational Database with constraints

7

Bookstore SQL200 Module 1 13

Database Management Systems

Positioning Chart

VLDB

Enterprise

Workgroup

Single user

Spreadsheet

# Users

Cost

Bookstore SQL200 Module 1 14

System Architecture

AccessMDB

File ServerArchitecture

Access

8

Bookstore SQL200 Module 1 15

System Architecture

OracleDB

VisualBasic App

Client/ServerArchitecture

Access

SQL

Bookstore SQL200 Module 1 16

System Architecture

OracleDB

Browser

WebArchitecture

WebServer

SQL

9

Bookstore SQL200 Module 1 17

Approaching SQL

• Relatively simple

• Two main environments

– Interactive (This course)

– Embedded

• Static (Compiled)

• Dynamic

Bookstore SQL200 Module 1 18

SQL Standardization

ANSI standardization– First standard in 1986

– SQL 89

– SQL 92

– SQL 99

• Various vendor extensions– Microsoft/Sybase: T-SQL

– Oracle: PL/SQL

10

Bookstore SQL200 Module 1 19

SQL Conformance

• Entry

• Intermediate

• Advanced

• Most are at least entry level

Bookstore SQL200 Module 1 20

SQL Statements

• Data Manipulation Language (DML)

• Data Control Language (DCL)

• Data Definition Language (DDL)

• Note: SQL 99 changes these to seven types

11

Bookstore SQL200 Module 1 21

SQL DDL

• Data definition language (DDL)

– Create, alter, drop, etc.

– Frequently implemented via various CASEtools: Visio, Embarcadero, ERWin, etc.

– But very useful for database administration

Bookstore SQL200 Module 1 22

SQL DCL

• Data Control Language (DDL)

– Grant

– Revoke

– Deny

– Constraints

12

Bookstore SQL200 Module 1 23

SQL DML

• Data Manipulation Language (DML)

– Select

– Insert

– Update

– Delete

Bookstore SQL200 Module 1 24

SQL Statement Processing

Parse

Validate

Optimize

Access Plan

Execute

13

Bookstore SQL200 Module 1 25

Bookstore Sample Database

• Before we continue (note: instructor may havealready done this)…

• Load the sample database if you haven’t already– Use Access import table feature, or– Run SQL script, or– Use Access upsizing wizard

Bookstore SQL200 Module 1 26

Text Conventions

• In Access character strings are normallysurrounded by double quotes

– “Jones”

• In an enterprise database such as Oracle orSQL Sever enclose text strings in singlequotes

– ‘Jones’

14

Bookstore SQL200 Module 1 27

Date Conventions

• In an enterprise database such as Oracle orSQL Sever, enclose dates in single quotes

– ‘2004-12-23’ MySQL

– ’12-23-2004’ SQL Server

– ’23-DEC-04’ Oracle

Bookstore SQL200 Module 1 28

SELECT

Basic Syntax (Projection):

Select <column-list> or <*>

From <table-list>

15

Bookstore SQL200 Module 1 29

SELECT

Basic Example (Projection):

selectcustomer_last_name,customer_street

from customers

Bookstore SQL200 Module 1 30

MS Access SQL Query

16

Bookstore SQL200 Module 1 31

Bookstore SQL200 Module 1 32

SQL Server Query

17

Bookstore SQL200 Module 1 33

SELECT with Where Clause

Example (Restriction plus Projection):

Select <column-list>

From <table-list>

Where <selection-criteria>;

Bookstore SQL200 Module 1 34

Comparison Operators

• < less than

• > greater than

• <= less than or equal to

• >= greater than or equal to

• <> or != two forms for not equal

18

Bookstore SQL200 Module 1 35

SELECT with Where

Basic Example (Restriction plusProjection):

select customer_last_name,customer_street

from customers

where customer_last_name =‘Jones’

Bookstore SQL200 Module 1 36

Select with Where

19

Bookstore SQL200 Module 1 37

On Your Own

• Find books written by Mark Twain

• Show title, publisher, year

Bookstore SQL200 Module 1 38

Complex Predicates

Follow normal boolean logic

Select customer_last_name,customer_street

From customers

Where (customer_last_name =‘Jones’ or customer_last_name =‘Smith’)and customer_state=‘NY’

20

Bookstore SQL200 Module 1 39

Select with Complex Where

Bookstore SQL200 Module 1 40

Complex Where Result

21

Bookstore SQL200 Module 1 41

Special Operators

• Can be used in where clause

• LIKE

• IN

• BETWEEN

• IS NULL

Bookstore SQL200 Module 1 42

Like (“Wild Card Matches”)

• ANSI

• Wherecustomer_last_namelike ‘Jo%’

• Like ‘Jo_’

• Access

• Wherecustomer_last_namelike “Jo*”

• Like “Jo?”

22

Bookstore SQL200 Module 1 43

IN

Select *From customersWhere customer_last_name in(‘Rizzo’, ‘Jones’, ‘Garcia’)

The list in parentheses can be replaced by asubquery. We will study this later.

Bookstore SQL200 Module 1 44

SQL Where Clause with IN

23

Bookstore SQL200 Module 1 45

IS NULL

Select *From customersWhere customer_street IS NULL

SQL uses three valued logic. Must use IS NULLto test for unknowns. A null is NOT the same asblank or empty.

Bookstore SQL200 Module 1 46

On Your Own

• Find all customers with an address not equal to4592 Maple Lane

• Was Peter Johnson selected?

• Why or why not?

24

Bookstore SQL200 Module 1 47

BETWEEN

Select *From ordersWhere order_date BETWEEN ‘1-Jan-99’ and ’31-Dec-99’

Note: date formats vary from product to product.

Bookstore SQL200 Module 1 48

Where with Between

25

Bookstore SQL200 Module 1 49

Removing Duplicates

Select DISTINCTcustomer_cityFrom customers

List once each city in which there arecustomers

Removes duplicate rows from result set

Bookstore SQL200 Module 1 50

Removing Duplicates

26

Bookstore SQL200 Module 1 51

Sorting – ORDER BY

DESC will sort in descending order

Basic syntax:

Select <column list>From <table list>Where <selection criteria>Order by <column list> [DESC]

Bookstore SQL200 Module 1 52

Sorting – ORDER BY

Select *From customersOrder by customer_state,customer_city

Example:List all records sorted by state, city

27

Bookstore SQL200 Module 1 53

Sorting Results with Order By

Bookstore SQL200 Module 1 54

Selecting Top Records

Select Top 5 (or top 25percent) Customer_last_name

, contact_zipFrom customersOrder by customer_zip desc;

List largest 5 zips or top 25 % ofthem…

28

Bookstore SQL200 Module 1 55

SQL Exercises

• List all books whose publisher name beginswith “H” or “T”; sort by title [hint: use LIKE]

• List all customers whose last name ends with“S”; sort by state, city, last name

• Find the order numbers of orders with orderdates in 1999; sort by order #. [Hint: useBETWEEN]

• Find the order numbers and order dates of allorders with a “2” in column 2 of the credit card#; sort by order date descending

[end module]

Notes

Bookstore SQL200 Module 1 56

1

Bookstore2 SQL200 Module 2 1

SQL200

SQL Programming

Workshop 2 – Joins, Subqueries, Unions,Calculations and Grouping

Note on SQL200 Slides

• These slides were originally designed to support thesingle SQL200 course which was used for any ofMS Access, MySQL, Oracle and SQL Server.

• As such you may see here slides developed in anyone of the above products.

• We are in the process of migrating the Oracle slidesand the MS Access slides out into their own slidesets. The SQL200 slides will cover MySQL andSQL Server which are virtually identical forpurposes of this course.

Bookstore2 SQL200 Module 2 2

2

Bookstore2 SQL200 Module 2 3

Warning!

• Below are some table name changes to beaware of in doing queries. We have createdsynonyms so either name should work.

New Name Old Name

Orders Order_filled

Order_Lines Orderlines

Bookstore2 SQL200 Module 2 4

SQL200 Contact Information

P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://[email protected]

Copyright 2001-20011 All rights reserved.

3

SQL200 Resources

• Bookstore database scripts found onbox.net at

http://tinyurl.com/SQLScripts

• Slides can be viewed on SlideShare…

http://www.slideshare.net/OCDatabases

• Follow up questions?

[email protected]

Bookstore SQL212 Module 1 5

Bookstore2 SQL200 Module 2 6

SQL200

SQL Programming

Part 1 – Joins

4

Bookstore2 SQL200 Module 2 7

Relational Database with constraints (from text)

Bookstore2 SQL200 Module 2 8

More conventions

• Names can be surrounded with “ “ or [ ] asin [order details].

• Some of the PowerPoint slides may havethis convention.

• Better practice is to use an underscore as inorder_details.

5

Bookstore2 SQL200 Module 2 9

Joins

• Inner

• Outer– Left

– Right

– Full

• Cross

• Self

• Theta

• We will cover the most important; others as timeand interest permit

Bookstore2 SQL200 Module 2 10

6

Bookstore2 SQL200 Module 2 11

Inner Join

• Pairs each row from first table withcorresponding row from second table overthe “join column”

• The result set only contains rows wherethere is a match over the join column inboth tables

• Equi-join is the common inner join

Bookstore2 SQL200 Module 2 12

Inner Join

Older Syntax:

Select <column-list>

From <tablelist>

Where <predicate>

Still very commonly used

7

Bookstore2 SQL200 Module 2 13

Inner Join

Example using older syntax:

SELECT customer_first_name,customer_street, order_numb,order_date

from customers, orders

Where customers.customer_numb =orders.customer_numb

Bookstore2 SQL200 Module 2 14

Inner Join with Result

8

Bookstore2 SQL200 Module 2 15

Inner Join (New Syntax)

Basic SQL 92 Syntax:

Select <column-list>

From <table1>

Inner join <table2>

On <join condition>

Bookstore2 SQL200 Module 2 16

Inner Join

Basic Example:

SELECT customer_first_name,customer_street, order_numb,order_date

from customers

inner join orders

on customers.customer_numb =orders.customer_numb

9

Bookstore2 SQL200 Module 2 17

Inner Join with Result

Bookstore2 SQL200 Module 2 18

Inner Join over Multiple columns

• Note that that the join condition can applyto multiple columns if desired

• Used with composite keys

10

Bookstore2 SQL200 Module 2 19

Inner Join Result in MS Access

Bookstore2 SQL200 Module 2 20

Inner Join

• In the last example…

– What was the cardinality of the relationshipbetween customers and orders?

– Which table was the parent?

– What was it’s primary key?

– In which table did we employ a foreign keyand what was it?

11

Bookstore2 SQL200 Module 2 21

Cross Join

• What happens when you omit a joinexpression?

• Get the cartesian product of the tables – allpossible combinations of the two tables

• For large tables this will run a long time!

Bookstore2 SQL200 Module 2 22

Cross Join Result Set in MS Access

12

Bookstore2 SQL200 Module 2 23

Additional SQL92 Syntax

• Table1 natural join table3 – automaticallyuses columns with same name

• Table 1 natural join table2 using(<column-list>

• Not yet widely available in commercialimplementations

Bookstore2 SQL200 Module 2 24

Joining More than Two Tables

• Can join several tables in one select

• Try to limit to three or four

• Join order can be important forperformance (although optimizers willusually handle this for you)

• Use parentheses to force order ofevaluation (also vendor extensions, oftencalled “hints”)

13

Bookstore2 SQL200 Module 2 25

Joining More than Two Tables

• Add orderlines detail to previous queries

SELECT customer_first_name, customer_street,orders.order_numb, orders.order_date,orderlines.isbn, orderlines.quantity

FROM customers

INNER JOIN orders ON

customers.customer_numb=orders.customer_numb

INNER JOIN orderlines

on orders.order_numb = orderlines.order_numb

Bookstore2 SQL200 Module 2 26

Multi-table Join with Results

14

Bookstore2 SQL200 Module 2 27

MS Access Multi-table Join Result Set

On Your Own

• Add the book title to the previous queryresults

• Hint: add another join to books table

15

Bookstore2 SQL200 Module 2 29

Sample Database

• Before we continue (Access classesonly)…

• Create a new employees table

Bookstore2 SQL200 Module 2 30

Correlation Names (Table Aliases)

• Can abbreviate references to tables

• For example:

Select e.name, j.payrange

From employees as e

Inner join job_information as j

On e.jobcode = j.jobcode;

16

Bookstore2 SQL200 Module 2 31

Self Joins

• Implements a recursive relationship

• Important in various applications

– Parts lists/assemblies

– HR

– Etc.

– Table joined to itself using correlation names

Bookstore2 SQL200 Module 2 32

Self Joins

SELECT e.*, m.name

FROM employees AS e, employeesAS m

WHERE e.managerid =m.employeeid;

17

Bookstore2 SQL200 Module 2 33

Bookstore2 SQL200 Module 2 34

Outer Joins

• Left – selects all rows from the left or first table,even if no match exists in the other table

– Widely used in commercial practice

– Especially useful for reporting

– Can be slower and interfere with optimizer

• Right – same idea but all rows from right table

• Full – all rows form both tables

18

Bookstore2 SQL200 Module 2 35

Left Outer Join

Basic SQL 92 Syntax:

Select <column-list>

From <table1>

Left join <table2>

On <join condition>

Bookstore2 SQL200 Module 2 36

Left-Join

Basic Example:

SELECT customer_first_name,customer_street, order_numb,order_date

from customers as c

left join orders as o

on c.customer_numb =o.customer_numb

19

Bookstore2 SQL200 Module 2 37

Bookstore2 SQL200 Module 2 38

Left Join with Results

20

Bookstore2 SQL200 Module 2 39

SQL200

SQL Programming

Part 2– Subqueries, Unions

Bookstore2 SQL200 Module 2 40

Subqueries

• One select statement embedded in another

• Can be nested multiple levels deep

• Can be used in select, from and whereclauses

• Two types:– Uncorrelated – executes inner query then outer

– Correlated – executes inner query once foreach outer query row

21

Bookstore2 SQL200 Module 2 41

Uncorrelated Subquery

select isbn, quantity

from orderlines

where order_numb in

(select order_numb fromorders where order_datebetween ‘1/1/99’ and‘12/31/99’);

Bookstore2 SQL200 Module 2 42

Uncorrelated Subquery with Results

22

Bookstore2 SQL200 Module 2 43

Negative Subquery

• A type of subquery that matches “notfound” conditions

Bookstore2 SQL200 Module 2 44

Negative Subquery

select isbn, quantity

from orderlines

where order_numb not in

(select order_numb fromorders where order_datebetween ‘1/1/99’ and‘12/31/99’);

23

Bookstore2 SQL200 Module 2 45

Negative Subquery with Results

Bookstore2 SQL200 Module 2 46

Correlated Subquery with Exists

• Inner subquery executed once for each outer row

• Exists will return true or false depending onwhether the result will have any rows or not

• Can be a quick way to test for existence ofrecords (parent records, say) as used inapplication enforcement of referential integrity

24

Bookstore2 SQL200 Module 2 47

Correlated subquery with Exists

SELECT isbn, quantity

FROM orderlines AS ol

WHERE exists

(select * from orders o whereol.order_numb = o.order_numb

and o.order_date between ‘1/1/99’and ‘12/31/99’);

This type of query covered in intermediate SQL class

Bookstore2 SQL200 Module 2 48

Unions

• Combines two or more tables

• Tables must be union compatible

25

Bookstore2 SQL200 Module 2 49

Unions

Select <column-list> from<table1>

Union [ALL]

Select <same-columns> from<table2>

Bookstore2 SQL200 Module 2 50

Unions

select *

from employees

union all

select *

from employees_copy

26

Bookstore2 SQL200 Module 2 51

Results of Union query

Bookstore2 SQL200 Module 2 52

SQL200

SQL Programming

Part 3 – Calculations, Aggregates

27

Bookstore2 SQL200 Module 2 53

Calculated Fields

• Can add a column calculated from others

SELECT order_numb, quantity,cost_each,quantity*cost_each asextension

FROM orderlines

Bookstore2 SQL200 Module 2 54

Calculated field in the Result

28

Bookstore2 SQL200 Module 2 55

Bookstore2 SQL200 Module 2 56

String Manipulation

• Concatenation

• Trim

• Substring

• Upper, Lower

• Etc. (various vendor extensions)

29

Bookstore2 SQL200 Module 2 57

Concatenation

• Used for concatenated keys

• Useful to format reports

Basic syntax:

(Access) Field1 & Field2(Oracle, std) Field1 || Field2(Sql Server) Field1 + Field2

Bookstore2 SQL200 Module 2 58

Concatenation

select customer_first_name

+ ‘ ‘ +trim(customer_last_name)

as Name

from customers

30

Bookstore2 SQL200 Module 2 59

Bookstore2 SQL200 Module 2 60

Date Functions

• Numerous date functions

• Often vendor specific

• Often used:– year

– month

– DateAdd, DateDiff, DatePart

– getdate()

• Ex: where year(order_date) = 1999

31

Bookstore2 SQL200 Module 2 61

Aggregate Functions

• Count

• Sum

• Min

• Max

• Avg

• Often used in conjunction with grouping

Bookstore2 SQL200 Module 2 62

Aggregate Functions

Basic syntax:

Select <function>(<column>)From <table>Group by <column-list>Having <predicate>

Group by all columns to left of one(s)you want to aggregate

32

Bookstore2 SQL200 Module 2 63

Aggregate Functions

SELECT order_numb, Count(*) AS[Number of Order Lines] ,Sum(quantity) AS [Total Quantity],Sum(quantity * cost_each) AS [TotalAmount]

FROM order_lines

GROUP BY order_numb

having count(*) > 1;

Bookstore2 SQL200 Module 2 64

33

Bookstore2 SQL200 Module 2 65

Having vs. Where

• Having and Where clauses are similar butnot the same

• Having removes groups after they areformed

• Where removes rows before groups areformed

Bookstore2 SQL200 Module 2 66

Exercise

• List all customers and their orders

– Name nicely formatted

– With orders in the year of 1999 (do not usebetween, etc.)

– Show total order quantities and amounts

– Only include orders with more than threeorder lines

34

Bookstore2 SQL200 Module 2 67

Exercise Result

[end module]

Notes

Bookstore2 SQL200 Module 2 68

1

Bookstore SQL200 Module 3 1

SQL/200

SQL Programming

Workshop 3 – Modifying Data, Managing theDatabase

Note on SQL200 Slides

Bookstore SQL200 Module 3 2

• These slides were originally designed to support thesingle SQL200 course which was used for any ofMS Access, Oracle and SQL Server.

• As such you may see here slides developed in anyone of the above products.

• We are in the process of migrating the Oracle slidesand the MS Access slides out into their own slidesets. These SQL200 slides (used in SQL202 as wellas SQL200) will focus on Microsoft SQL Server.

2

Bookstore2 SQL200 Module 2 3

Warning!

• Below are some table name changes to beaware of in doing queries. We have createdsynonyms so either name should work.

New Name Old Name

Orders Order_filled

Order_Lines Orderlines

Bookstore SQL200 Module 3 4

SQL200 Contact Information

P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://[email protected]

Copyright 2001-2011. All rights reserved.

3

Note on SQL200 Slides

• These slides were originally designed to support thesingle SQL200 course which was used for any ofMS Access, MySQL, Oracle and SQL Server.

• As such you may see here slides developed in anyone of the above products.

• We are in the process of migrating the Oracle slidesand the MS Access slides out into their own slidesets. The SQL200 slides will cover MySQL andSQL Server which are virtually identical forpurposes of this course.

Bookstore2 SQL200 Module 2 5

SQL200 Resources

• Bookstore database scripts found onbox.net at

http://tinyurl.com/SQLScripts

• Slides can be viewed on SlideShare…

http://www.slideshare.net/OCDatabases

• Follow up questions?

[email protected]

Bookstore SQL212 Module 1 6

4

Bookstore SQL200 Module 3 7

SQL200 Module 3

• Part 1 – Modifying Data

• Part 2 – Managing Database Structures

• Part 3 – Creating Views and Indexes

• Part 4 -- Security

Bookstore SQL200 Module 3 8

SQL/200

SQL Programming

Part 1 – Modifying Data

5

Bookstore SQL200 Module 3 9

Relational Database with constraints (from text)

Bookstore SQL200 Module 3 10

Data Modification Statements

• Insert

• Update

• Delete

6

Bookstore SQL200 Module 3 11

Data Modification Statements

• End-user rarely sees thesestatements

• Application developer preparesthese statements “behind thescenes” based on forms filled outby user

Bookstore SQL200 Module 3 12

Insert

• Adds new rows to an existing table

• Two forms:

– Single Row

– Multi-Row

7

Bookstore SQL200 Module 3 13

Single Row Insert

Basic Syntax:

Insert [into] <table-name>

Values (<value-list>)

Bookstore SQL200 Module 3 14

Single Row Insert

Basic Example:insert into sources(source_numb,source_name, source_street)values(22,'Specialty Books','Canal Street')

8

Bookstore SQL200 Module 3 15

Insert Statement

Bookstore SQL200 Module 3 16

Sources table after Insert

9

Bookstore SQL200 Module 3 17

Multi-row Insert

Basic Syntax:

Insert [into] <table-name>

Select <select-statement>

We will do this after creating a newtable later in this module

Bookstore SQL200 Module 3 18

Update

• Updates fields in an existing row

Basic Syntax:

Update <table-name>

Set <field1> = new value, <field2> = newvalue,…

Where <selection-criteria>

10

Bookstore SQL200 Module 3 19

Update

• Increase Ingram prices by 10%

Example:

Update books

Set retail_price = retail_price*1.10

Where source_numb = 1

Bookstore SQL200 Module 3 20

Ingram Book Prices before Update

11

Bookstore SQL200 Module 3 21

Ingram Book Prices after Update

Bookstore SQL200 Module 3 22

After update in MS Access

12

Bookstore SQL200 Module 3 23

Delete

• Deletes one or more rows

Basic Syntax:

Delete from <table-name>

Where <selection-criteria>

Bookstore SQL200 Module 3 24

Delete

Example:

Delete from sources

Where source_numb = 22

13

Bookstore SQL200 Module 3 25

Delete

Bookstore SQL200 Module 3 26

Sources table after Delete

14

Bookstore SQL200 Module 3 27

Delete and Referential Integrity

• Can affect referential integrity when deleting a“parent” row

• Can do following with child…– Cascade: delete the child row– Set null: set the child’s foreign key null– Set default: as above but to default value– No action: don’t allow delete of parent row

• Referential integrity can be established whencreating or modifying table structures which wewill look at later in the class

Bookstore SQL200 Module 3 28

SQL/200

SQL Programming

Part 2– Managing Database Structures

15

DDL

• Create

• Alter

• Drop

Bookstore SQL200 Module 3 29

Bookstore SQL200 Module 3 30

Schemas

• Logical view of a database; sort of a “sub-database” – we will not cover these in thismodule or…

– Catalogs

– Clusters

– Domains (somewhat like a user defined datatype)

• These topics are highly dependent upon thevendor DBMS and installation practices

16

Bookstore SQL200 Module 3 31

Tables

• Base tables

• Temporary tables

– Local (or module scope)

– Global (session scope)

Bookstore SQL200 Module 3 32

Creating Tables

• Use create statement

• Specify:

– Columns with data types and columnconstraints

– Table constraints

• Foreign key references

• Primary key designation

17

Bookstore SQL200 Module 3 33

Data Types

• Int – integers or whole numbers– Ex: how_many int

• Char – fixed length fields– Ex: state char(2)

• Varchar/Varchar2 – variable length fields– Ex: address varchar(35)

• Money – money field; same as MS Accesscurrency

• Date/Datetime – date and time

• And many others – see documentation or Help

Bookstore SQL200 Module 3 34

Create Table

Basic syntax:

Create table <table-name>

<column1> <datatype> <constraints>

,.. <column1> <datatype> <constraints>

<table constraints>

Note: often preceded by a drop

18

Bookstore SQL200 Module 3 35

Temporary Tables

Basic syntax (SQL standard):

Create [global] temporary table <table-name>

<rest of statement as for normal create>

Note: SQL Server uses a different syntax. Just puta #in front of the table name as in #mytable.

Bookstore SQL200 Module 3 36

Column Constraints

• Primary key

• Not NULL

• CHECK clause

• Default

• Unique

19

Bookstore SQL200 Module 3 37

Table Constraints

• Primary Key

• Foreign Key

• Compare fields against each other. I.e.ship_date >= order_date

Bookstore SQL200 Module 3 38

But first – the Drop Statement

• Deletes a database “object”

– Drop table <table-name>

– Drop view <view-name>

– Drop index <index-name>

– Drop domain <domain-name>

20

Bookstore SQL200 Module 3 39

Create Table

Example 1: Create a summary table

Create table summary(

isbn varchar(20) primary key,

How_many int check (how_many >= 0),

Constraint isbn_fk

Foreign key (isbn) referencesbooks(isbn)

)

Bookstore SQL200 Module 3 40

Create Summary Table

21

Bookstore SQL200 Module 3 41

Constraints on Summary Table

Bookstore SQL200 Module 3 42

Multi-row Insert

Basic Syntax:

Insert [into] <table-name>[(<column list>)]

Select <select-statement>

22

Bookstore SQL200 Module 3 43

Multi-row Insert

Basic Example: (store # times eachbook ordered)

Insert into summary

Select isbn, count(*)

From orderlines

Group by isbn;

Bookstore SQL200 Module 3 44

Multi-row Insert

23

Bookstore SQL200 Module 3 45

After multi-row insert in MS Access

Bookstore SQL200 Module 3 46

SQL/200

SQL Programming

Part 3 – Creating Views and Indexes, ModifyingStructures

24

Bookstore SQL200 Module 3 47

Views

• Think of a view as a named query whereinthe definition is stored in the database

• Can be read like a table

• Some are updateable

Bookstore SQL200 Module 3 48

Views

Basic syntax:

Create view <view-name> (<column-list>)

As

<select statement>

25

Bookstore SQL200 Module 3 49

Creating a View

Bookstore SQL200 Module 3 50

Using Views

• Can be used like a table subject to variouslimitations

– Cannot insert into grouped queries, etc.

– Etc.

• Sample syntax:

select column-list

from employee_view

26

Bookstore SQL200 Module 3 51

Using a View

Bookstore SQL200 Module 3 52

Indexes

• Used to speed searches, joins, etc.

• Placed on:

– primary and foreign keys

– Secondary keys

• In commercial practice often managed byDBA’s for large databases

27

Bookstore SQL200 Module 3 53

Indexes

Basic syntax:

Create [unique] index <index-name>

On <table-name> (field-name> [desc])

Note: can place index on a composite key; ex: state andcity

Bookstore SQL200 Module 3 54

Indexes

Basic example:

create index state_inx

on customers(customer_state)

28

Bookstore SQL200 Module 3 55

Customers table with index

Dropping an index

Basic Syntax:

– Drop index <table-name.index-name>;

Bookstore SQL200 Module 3 56

29

Bookstore SQL200 Module 3 57

Modifying a Table Design

• Applies to tables

• Use ALTER statement

– Add columns

– Delete columns

– Rename columns

– Add column constraints

– Add table constraints

Bookstore SQL200 Module 3 58

Modifying a Table Design

Basic syntax:

Alter <table-name>

Add <field-name>,

Add <table-constraint>,

Modify <field-name>

Etc.

30

Bookstore SQL200 Module 3 59

Modify a Table Design

Example: add a phone number field

alter table publishers

add phone char(12);

Bookstore SQL200 Module 3 60

After alter publishers table

31

Bookstore SQL200 Module 3 61

SQL/200

SQL Programming

Part 4 – Security

Bookstore SQL200 Module 3 62

Security

• Important DBA function

– Beyond scope of this course

– Typically controlled through EnterpriseManager or Studio GUI’s

• In commercial practice application securityfrequently controlled via own login and a“users” table or similar

32

Bookstore SQL200 Module 3 63

Security

• Specifics can vary by product

• Access: workgroup administrator

• SQL Server: users, roles

• Oracle: users, roles

Bookstore SQL200 Module 3 64

SQL Security Statements

• Grant

• Revoke

• Deny

33

Bookstore SQL200 Module 3 65

Grant

Syntax:

Grant <access-right> [with grant option]

On <object> to <user>

Note: by default only tables owners and admins can accessa table. Others must be granted the relevant rights.

Bookstore SQL200 Module 3 66

Access Rights

• Select

• Update

• Insert

• Delete

• References

• All privileges

34

Bookstore SQL200 Module 3 67

Grant

Example:

Grant update

On employees to ddurso

Bookstore SQL200 Module 3 68

Revoke

• Revokes the rights

• Syntax similar to grant

[end module]

Accelerated Computer Training for Working Professionals

Orange Coast Database Associates Course (800)355-9855 or http://ocdatabases.itgo.com

Orange CoastDatabase Associates

Specializing in Microsoft Office,

Access, SQL, and related technologiesComputer Training, Programming & Consulting

32422 Alipaz St., Suite B-4A

San Juan Capistrano, CA

(800)355-9855 (Toll Free) | (949)489-1472 (Direct) | (949)485-6284 (Fax)

http://ocdatabases.itgo.com | [email protected]

Accelerated Computer Training