Introduction To Structured Query Language (SQL) – Part 1

Topics

• Structured Query Language (SQL) - general concept

• SQL commands:

– Data definition language (DDL)

– Data manipulation language (DML)

– Basic SELECT queries

– Queries using DDL and DML commands

References:

• Online tutorials:

– A Gentle Introduction to SQL: http://sqlzoo.net (Interactive tutorials, across-database comparisons)

– SQL Tutorial: http://www.w3schools.com/sql/default.asp (No exercises but includes a quiz; quite a few ads and animated text.)

– Interactive Online SQL Training: http://www.sqlcourse.com (Interactive tutorials, Includes standalone SQL interpreter for trying out statements; some pictures, ads, scrolling text.)

• General, online:

– Learning Guide: SQL: http://searchdatabase.techtarget.com/orginalContent/0,289142,sid13_gci950920,00.html (Eight chapters, from basic to advanced; may have to register (free) to use - and receive e-mail newsletters :( )

• General, books:

– The Practical SQL Handbook, 4th Ed., Judith Bowman, Sandra Emerson, and Marcy Darnovsky; Addison Wesley

– SQL in a Nutshell, Kevin Kline; O’Reilly

– SQL for Smarties, 2nd Ed., Joe Celko; Morgan Kaufmann

• Database-specific:

– Oracle: http://otn.oracle.com

– MySQL: http://www.mysql.com/documentation/mysql/bychapter/

Tutorial: http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#Tutorial

– SQL Server: http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/portal_7ap1.htm

– MS Access: Help (Contents) / MS Jet SQL

• Online glossary

:

– http://www.prenhall.com/divisions/bp/app/mcfadden/student/glossaryfull.html

Relational databases

Practical definition: Organized collection of data stored in logically related tables.

Example:

1. Open the Northwind training database (C:\TEMP\NorthwindTraining.mdb in 116 PHTC).

(NOTE: For practice on your own computer, make a copy of the Northwind database before starting; or start in a blank new database and import the Northwind tables you need.)

2. Close the splash screen and the Main Switchboard form.

3. Open the Products table in Datasheet view.

4. Change to Design view (View button) and find the primary key.

5. Check for any other indexed columns (fields): View / Indexes.

6. Close the table

Referential integrity constraints: Primary / foreign key relationships or rules defined using SQL or vendor extensions that ensure data integrity and consistency throughout all tables in the database.

Example: Relationships diagram in the Northwind database

1. Click the Relationships button or choose Tools / Relationships.

2. Find the Products table and its related table or tables.

3. Note the primary key and two foreign keys in the Products table.

Structured Query Language (SQL)

Non-procedural (declarative) language common to most relational database systems.

– Used by the database system internally and by users to manipulate and query the data.

– Uses set theory (as opposed to row processing) to process requests.

– Limited but flexible set of commands.

– Free-form, uses semi-colon as statement terminator in most databases.

Knowledge of SQL should enable

– moving easily from one database system to another

– writing queries that cannot be represented in the graphical user interface

– troubleshooting

– analyzing queries for performance (tuning)

Commands classified by function:

– Data definition language (DDL) - define or change database structure(s)

CREATE

ALTER

DROP

– Data manipulation language (DML) - select or change data

INSERT

UPDATE

DELETE

SELECT

– Data control language (DCL) - control user access (e.g., GRANT, REVOKE)

– Transactions (e.g., COMMIT)

Data retrieval - Introduction to the SELECT statement.

One of the DML commands, used for retrieving specific data from one or more tables, reporting calculated results based on the data retrieved, or displaying the results of “what if?” type calculations.

Example:

1. Run the query Current Product List.

2. Change to SQL view to see the SELECT statement

3. Close the query.

Exercise: Create a list of all product categories

1. Type the following statement in the SQL window:

SELECT *

FROM Categories

2. View the results without saving by clicking the View button (switches to Datasheet view).

3. Change back to SQL view and run the statement by clicking the Run ( ! ) button.

4. Save the query (e.g., AllCategories) and close it.

Exercise: Create a phone list of customer contacts (just company and contact names with phone and FAX numbers) sorted by company name.

1. Type the following statement in the SQL window:

SELECT CompanyName, ContactName, Phone, Fax

FROM Customers

ORDER BY CompanyName

2. View the results; then run and save the query as AllCustomers. You should see 91 customers.

3. Modify the list to include only customers from the United Kingdom and at the same time, change the first two column headers to Company and Customer:

SELECT CompanyName AS Company, ContactName AS Customer, Phone, Fax

FROM Customers

WHERE Country = “UK”

ORDER BY CompanyName

4. View the results; then run and save the query as UKCustomers (7 customers)

5. Close the query.

SELECT: Syntax

SELECT column list

FROM tablename

WHERE criteria

ORDER BY column list

Operators and predicates for the WHERE clause:

Comparison operators: = > < <= >= <> (or !=)

WHERE Country <> “UK”

Between ... and ....

WHERE Price BETWEEN 10 and 20

Like (with wildcard: * ? in Access; and % _ in most other databases)

WHERE ContactTitle LIKE “Sales%”

In (list)

WHERE State IN (“OR”, “WA”, “CA”)

AND, NOT, OR with any of the above

Is [not] null

Exercise: Create alphabetically sorted product lists showing product name (ProductName), supplier (SupplierID), and unit price (UnitPrice) for the following:

- All products (result: 77 products)

- Products that cost no more than $10 (14)

- Products in the $10 to $20 price range (29)

- Chocolates (2 or 3)

Exercise: Modify the AllCustomers phone list to make separate lists for customers with and without an assigned region (two separate queries). Use is null or is not null to make the distinction.

To find customers assigned to a region (should give 31 customers):

SELECT CompanyName, ContactName, Phone, Fax, Region

FROM Customers

WHERE Region is not null

ORDER BY CompanyName;

To find customers not assigned to a region (60 customers):

SELECT CompanyName, ContactName, Phone, Fax

FROM Customers

WHERE Region is null

ORDER BY CompanyName;

NOTE: No need to include region in the select list – we know it is blank.

SELECT: Syntax

SELECT column list

FROM tablename

WHERE criteria

ORDER BY column list

Operators and predicates for the WHERE clause:

Comparison operators: = > < <= >= <> (or !=)

WHERE Country <> “UK”

Between ... and ....

WHERE Price BETWEEN 10 and 20

Like (with wildcard: * ? in Access; and % _ in most other databases)

WHERE ContactTitle LIKE “Sales%”

In (list)

WHERE State IN (“OR”, “WA”, “CA”)

AND, NOT, OR with any of the above

Is [not] null

Exercise: Create alphabetically sorted product lists showing product name (ProductName), supplier (SupplierID), and unit price (UnitPrice) for the following:

- All products (result: 77 products)

- Products that cost no more than $10 (14)

- Products in the $10 to $20 price range (29)

- Chocolates (2 or 3)

Exercise: Modify the AllCustomers phone list to make separate lists for customers with and without an assigned region (two separate queries). Use is null or is not null to make the distinction.

To find customers assigned to a region (should give 31 customers):

SELECT CompanyName, ContactName, Phone, Fax, Region

FROM Customers

WHERE Region is not null

ORDER BY CompanyName;

To find customers not assigned to a region (60 customers):

SELECT CompanyName, ContactName, Phone, Fax

FROM Customers

WHERE Region is null

ORDER BY CompanyName;

NOTE: No need to include region in the select list – we know it is blank.

Data Definition Language – DDL

CREATE

database (not in all database systems; can be complex when available)

table (varying degrees of complexity)

index

Also available in other database systems: CREATE trigger, procedure, function, role, user)

CREATE TABLE

Used to create the tables where data will be stored.

=========

Example:

1. Open the Orders table in design view.

2. Data type specified for each field (column)

Autonumber (Other databases: identity)

Text (char(n), varchar(n))

Number

Date/Time

Currency (money)

Additional data types not used in this table:

Memo (long (Oracle, up to 2 GB text)

Yes/No (boolean)

OLE Object (blob, clob, raw)

Some additional data types in other database systems:

Timestamp

Interval

Enum

Set

3. Select each field and notice its properties in the Field Properties grid.

To view table structure and column properties in other databases:

describe tablename

and select [properties] from [system tables]

4. Close the Orders table.

Exercise: Create a table to store personnel data, with a StaffID column as primary key

1. Type this SQL statement in the SQL query design window:

CREATE TABLE Personnel (

StaffID text(9) CONSTRAINT StaffPK PRIMARY KEY,

LastName text(15) not null,

FirstName text(15) not null,

Birthday date,

Department text(12) null);

2. Execute the statement. If Access reports syntax errors, find and correct them.

3. Save the query as DefinePersonnel and close it.

4. Run a query to select all records from the new table:

SELECT * FROM Personnel;

The query returns one blank record (in other databases: 0 rows). Close the query.

4. Open the new table in datasheet view – it is empty and ready for data entry.

5. Change to design view and compare with the SQL statement.

6. Choose View / Indexes and compare with the constraint created on StaffID.

Another Method For Creating Tables

Select data from an existing table into a new table.

Useful for the following:

– Creating tables and entering data at the same time

– Making backups or partial backups

– Creating archive tables

Exercise: Create a copy of the Suppliers table and call it MySuppliers.

1. Type the following SQL statement in the SQL design window:

SELECT * INTO MySuppliers

FROM Suppliers;

2. Click the View button (Datasheet View) to see the results without actually executing the statement. The query header should be "Query1: Make Table Query" and the results should show 29 rows.

3. Change to SQL view (not Design view) and execute the statement. Click Yes when asked whether you want to continue (only Access gives this kind of warning!).

4. Save the query as "DefineMySuppliers" and close it.

5. Compare the new table with the original in Datasheet and Design view. What, if any, differences do you find?

Exercise: Create an extract of the Categories table without pictures and call it CategoriesNoPix.

(1. Optional: Open the Categories table in design view and note the field names. Close the table before running the SELECT ... INTO ... FROM query.)

2. Type the following SQL statement in the SQL design window:

SELECT CategoryID, CategoryName, Description INTO CategoriesNoPix

FROM Categories;

3. Switch to Datasheet View to see the results without executing the statement.

4. Switch to SQL view (not Design view) and execute the statement. Click Yes when asked about continuing.

5. Save the statement as "DefineCategoriesNoPix" and close the SQL window.

6. Compare the new table with the original.

CREATE TABLE Syntax:

– Defining from scratch

CREATE TABLE tablename (

column1name datatype(size) [NOT NULL] [index1],

column2name datatype(size)[NOT NULL] [index2],

...

[,]CONSTRAINT multifieldindex [, ...]])

Single-field constraint:

CONSTRAINT constraintname {

PRIMARY KEY |

UNIQUE |

NOT NULL |

REFERENCES foreigntablename [(foreigncol1, foreigncol2), ...]

}

Multifield constraint:

CONSTRAINT constraintname (

PRIMARY KEY (primary1, primary2 , ...) |

UNIQUE (unique1, unique2 , ...) |

NOT NULL (notnull1, notnull2 , ...) |

FOREIGN KEY (ref1, ref2 , ...) REFERENCES foreigntable (foreigncol1, foreigncol2, ...)

}

– Selecting from an existing table

SELECT column1, column2, ...

INTO newtable

FROM existingtable

DDL - CREATE INDEX

Used to create an index on an existing table. The ALTER TABLE statement can also be used to create (or drop) an index on a table.

Uses (apart from speeding up searches in large tables and in multitable queries):

– PRIMARY uniquely identifies the row (UNIQUE and NOT NULL by definition)

– UNIQUE prevents entry of duplicate values

– DISALLOW NULLS prevents null values in the indexed field

Exercise: Create the missing primary key on MySuppliers.

1. Type the following SQL statement in the SQL design window:

CREATE INDEX MySuppPK

ON MySuppliers (SupplierID)

WITH PRIMARY;

2. Execute the statement.

3. Save the query as DefineSupplierPK and close it.

4. Open the table MySuppliers in Design view to check the result.

Syntax

CREATE [ UNIQUE ] INDEX indexname

ON tablename (column1 [ASC|DESC], column2 [ASC|DESC], ...)

[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

DDL - ALTER TABLE

Used to add or remove columns or constraints.

Exercise: Add a column to CategoriesNoPix for a short description and then remove it.

1. Type the following SQL statement in the SQL design window:

ALTER TABLE CategoriesNoPix

ADD COLUMN ShortDesc Text(25);

2. Execute the statement.. Open the table in Datasheet or Design view to check the results; close the table.

4. Save as "AddColCategoriesNoPix".

4. Without closing the SQL design window, change the statement to read

ALTER TABLE CategoriesNoPix

DROP COLUMN ShortDesc;

5. Execute the statement. Open the table to check the result.

6. Save as "DropColCategoriesNoPix".

Exercise: Drop the referential integrity constraint on the Products table that does not allow a product to be added if it is not in an existing category; then add the constraint back again.

1. Open the Relationships window and note the relationship between Categories and Products. Close the Relationships window.

2. Type the following statement in the SQL window:

ALTER TABLE Products

DROP CONSTRAINT CategoriesProducts;

3. Execute the statement. Open the Relationships window and check the result.

4. Save the statement as “DropProductConstraint”.

5. Modify the statement to add the constraint back again:

ALTER TABLE Products

ADD CONSTRAINT CategoriesProducts

FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID);

6. Execute the statement. Open the Relationships window and check the result.

7. Save as “AddProductConstraint”.

Syntax:

ALTER TABLE tablename

ADD COLUMN colname datatype(size) [NOT NULL][CONSTRAINT indexname]

or

ADD CONSTRAINT multicolumnindex

or

DROP COLUMN colname

or

DROP CONSTRAINT indexname

DDL - DROP

Use DROP objectname to remove from the database any object that was CREATEd.

In MS Access, can only DROP tables and indexes.

Exercise: Remove the CategoriesNoPix table from the database - we no longer need it.

1. Type the following statement in the SQL window:

DROP TABLE CategoriesNoPix;

2. Execute the statement.

3. Check the table list - the table should be gone.

4. Save and close the DROP query (optional).

Syntax

DROP {TABLE tablename |

INDEX indexname ON tablename}

Data modification language statements (DML) INSERT, UPDATE, and DELETE

DML - INSERT

Use the INSERT command to enter data into a table. You may insert one row at a time, or select several rows from an existing table and insert them all at once.

NOTE: Be sure to save the next three queries - you may need them again!

Exercise: Add two records to the Personnel table, one with all the data, the other with required columns only (two separate queries). Try to add a third record using one of the StaffIDs already in the table. What happens? Use these SQL statements for the first two staff members:

INSERT INTO Personnel

VALUES("7777777", "Vagabond", "Johnny", #7/17/1950#, "Chemistry");

INSERT INTO Personnel (StaffID, LastName, FirstName)

VALUES ("5555555", "Miller", "Dusty");

NOTE: Date/time data type delimited with # in Access; with quotes in other databases.

Exercise: Add several employees from the Northwind database to the Personnel table. Here is an SQL statement that should add 5 staff members:

INSERT INTO Personnel (StaffID, LastName, FirstName, Birthday)

SELECT EmployeeID, LastName, FirstName, BirthDate

FROM Employees

WHERE Country = "USA";

Syntax

Single-row INSERT:

INSERT INTO tablename (column list)

VALUES (value list)

Multi-row INSERT:

INSERT INTO tablename (column list)

SELECT column list

FROM othertable

WHERE criteria

Column and value lists are comma-separated lists in both cases

DML – UPDATE

Use the UPDATE statement to change data values in one or more columns, usually based on specific criteria.

Exercise: Assign suppliers from London and Manchester in the MySuppliers table to the UK region. Check the table before and after running this update query:

UPDATE MySuppliers

SET Region = "UK"

WHERE City IN ("London", "Manchester");

Exercise: Where no region has been entered in the MySuppliers table, change the value to “Unassigned”. Check the table before and after running the query:

UPDATE MySuppliers

SET Region = "Unassigned"

WHERE Region is null;

Syntax

UPDATE tablename

SET col1 = value1, col2 = value2, ...

WHERE criteria

DML - DELETE

Used to remove whole rows from a table. Use with caution!

Check the Personnel table before and after running each of the following DELETE statements.

Exercise: Delete the Chemistry staff member from the Personnel table:

DELETE * FROM Personnel

WHERE Department = "Chemistry";

Exercise: Delete all staff members (no conditions):

DELETE * FROM Personnel;

Syntax

DELETE * FROM tablename

WHERE criteria

NOTE: MS Access requires the * to designate all columns; other databases use DELETE FROM ...

For practice using SQL commands in MS Access:

Unless otherwise indicated:

– Under Objects, select Queries.

– Select "Create query in Design view" (double-click to open).

– Close the Show Table dialogue box.

– Click the SQL view button (leftmost button under the menu bar) or choose View / SQL View from the menu bar.

– In the window called "Query1: Select Query", type the SQL statement, replacing SELECT with the appropriate SQL

command. Be sure to keep the semi-colon statement terminator.

– To execute the command, click the Run button (!) or by choosing Query / Run from the menu bar. (Note: For queries that modify data, you can usually see which records or how many records will be modified without actually executing the query by changing the "view" to Datasheet View: Use the View button or choose View / Datasheet.)

– Save the query and close the SQL design window.

==============================================================================

MS Access denotes DML statements as "action queries" and warns the user any time data will be modified.

Abbreviated list of steps for DML exercises:

1. Type the statement in the SQL window.

2. Check results without executing by switching to Datasheet view.

3. Execute the statement (click Yes when prompted about continuing).

4. Check the results in the appropriate table.

5. Save the statement and close it.

Access may change the SQL in non-standard ways when you leave the SQL window.

This does not change how the statement works.

==============================================================================