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  (Seven chapters, 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 (May have to register (free) for some options)

               – MySQL, general: http://dev.mysql.com/doc/mysql/en/index.html
                           SQL reference: http://dev.mysql.com/doc/mysql/en/sql-syntax.html

– SQL Server: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/getstart_4fht.asp

– MS Access: Help / MS Access Help / Table of Contents / MS Jet SQL Reference

Relational databases

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

Example:

1. Open the Northwind training database in MS Access 2003 (C:\TEMP\NorthwindTraining.mdb in 116 PHTC)  by double-clicking on the file name or by opening MS Access from the Start menu and locating the database under File/Open.  The database opens behind a "splash screen":

(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 by clicking OK.  The Main Switchboard form becomes visible in front of the database window:

3.  Close the Main Switchboard by clicking on Display Database Window.  Verify that "Tables" is selected in the Objects bar on the left.

4. Double-click on the Products table to see the data (open it in Datasheet view, in MS Access terminology).  A partial view:

5. Change to table Design view by clicking on the View button (circled above) and find the primary key.  To check for other indexed columns, select Indexes from the View menu:

6. Close the table

Referential integrity constraints: Primary / foreign key relationships or rules defined using SQL or vendor extensions that prevent loss or unwanted modification of data and maintain data consistency throughout all tables in the database.

Example: Relationships diagram in the Northwind database

1. Click the Relationships button (circled below) or choose Tools / Relationships:

The relationships window opens:

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

3. Note the primary key (ProductID) and two foreign keys (SupplierID and CategoryID) in the Products table.

Structured Query Language (SQL) Overview

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

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

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

– Has a limited but flexible set of commands.

– Free-form for the most part, 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)

SQL commands classified by function:

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

CREATE

ALTER

DROP

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

INSERT

UPDATE

DELETE

SELECT

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

– Transactional language - used to control logical units of work (e.g., COMMIT)

Data retrieval - Introduction to the SELECT statement

SELECT is one of the Data Manipulation Language (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. Click Queries on the Objects pane and run the query Current Product List (double-click or click on Open):

2. Change to SQL view to see the SELECT statement:

Notice the following in the statement above:

         The table name has been aliased (Product List).
         Columns are named as alias_name.column_name (strictly speaking, not necessary for a single-table query).

         Access uses square brackets when names contain punctuation such as spaces; other databases use quotes.   
         Access inserts many more parentheses than actually needed.

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., as 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 (e.g., as AllCustomers). You should see 91 customers.

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

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 (e.g., as UKCustomers).  There should be 7 customers

5. Close the query.

SELECT: Syntax for a single-table SELECT statement

SELECT column list

FROM tablename

WHERE criteria

ORDER BY column list

Note:  Although the language is free-form, the clauses that make up the statement must occur in the order shown.  SELECT and FROM are required.  The remaining clauses are optional.

Operators and predicates for the WHERE clause with examples:

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

WHERE Country <> “UK”

BETWEEN ... AND ...

WHERE Price BETWEEN 10 AND 20

WHERE CompanyName BETWEEN A AND F

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

                         WHERE Country = "USA" AND City = "New York"

IS NULL, IS NOT NULL

WHERE PostalCode IS NOT NULL
                         WHERE Discount IS 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.

SQL for SELECT with criteria exercises:

SELECT ProductName, SupplierID, UnitPrice

FROM Products

ORDER BY ProductName;

SELECT ProductName, SupplierID, UnitPrice

FROM Products

WHERE UnitPrice <= 10

ORDER BY ProductName;

SELECT ProductName, SupplierID, UnitPrice

FROM Products

WHERE UnitPrice BETWEEN 10 AND 20

ORDER BY ProductName;

SELECT ProductName, SupplierID, UnitPrice

FROM Products

WHERE ProductName LIKE "*Choc*"

OR ProductName LIKE "*Schok*"

ORDER BY ProductName;

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 there is no value for region in this query.

DATA DEFINITION LANGUAGE – DDL

CREATE

The CREATE statement is used to create the main data storage objects:

database (not in all database systems, in particular, not in MS Access; can be complex when available)

table (varying degrees of complexity)

index

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

Creating Tables

Most database systems allow data selection from an existing table into a new table without writing a CREATE statement.

This method is useful for the following:

– Creating tables and quickly 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.  Verify that the query appears on the Query list (note that the icon for this DDL query is different from the icon for SELECT queries) and that the new table, MySuppliers, is on the Table list.

6. 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 for this class: Open the Categories table in design view and note the column names. If you were creating such an extract in a work situation, you would need to know the column names or look them up using data dictionary or other databse tools.  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

Used to create the tables where data will be stored.

=========

Example:

1. Open the Orders table in design view.

2. Notice the data type specified for each column:

Autonumber (Other database systems: identity, sequence)

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

Number              (In all database systems, various types depending on size or type of number to be stored)

Date/Time

Currency (money)

Additional MS Access data types not used in this table:

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

Yes/No (boolean -not available in some other database systems, e.g. Oracle)

OLE Object (blob, clob, raw)

Some additional data types in other database systems:

Timestamp

Interval

Enum

Set

3. Select each column and notice its properties in the Field Properties grid, in particular:

             Field (column) size
             Caption (this is the column alias)
             Required and Allow Zero Length properties
             Indexed property

To view table structure and column properties in other databases:

describe tablename

and select [properties] from [system tables]

4. Close the Orders table.

The CREATE statement must specify the new table name, the column names, and the relevant data types and properties for each column.

========

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.  In the database window, check the Query list for this DDL query (notice that the icon for the query is different from the icon for SELECT queries) and check the Table list for the new Personnel table.

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.

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

6. Change to design view and compare with the SQL statement.  Also, choose View / Indexes and compare with the constraint created on StaffID:

7. Close the Personnel table.

CREATE TABLE Syntax:

      – Selecting from an existing table

SELECT column1, column2, ...

INTO newtable

FROM existingtable

– 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, ...)

}

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.

Exercise: Add two rows to the Personnel table, one with all the data filled in, the other with required columns only (two separate queries). Use these SQL statements or similar:

INSERT INTO Personnel

VALUES("7777777", "Smith", "John", #7/17/1950#, "Chemistry");

INSERT INTO Personnel (StaffID, LastName, FirstName)

VALUES ("5555555", "Jones", "Jane");

NOTE: The date/time data type uses a # delimiter in Access; quotes in other databases.

When you execute the statements, Access will give you a warning (other database systems do not!):

Click Yes to complete the execution step.

Try to add a third row using one of the StaffIDs already in the table. What happens?

Verify the results by selecting all data from the Personnel table.  For the statements above:

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

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.  The syntax for this command varies across systems.

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 the SupplierID column in table 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, we 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}

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 the relevant rows in the table (that is, run SELECT queries with the same criteria) 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 ...