Introduction To Structured Query Language (SQL) – Part 1
Topics
·
Data
definition language (DDL)
·
Data
manipulation language (DML)
·
Basic
SELECT queries
·
Queries
using DDL and DML commands
References:
(Interactive tutorials,
across-database comparisons)
– 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
–
Oracle: http://otn.oracle.com
–
SQL
Server: http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/portal_7ap1.htm
–
MySQL: http://www.mysql.com/documentation/mysql/bychapter/
–
Tutorial: http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#Tutorial
– MS Access:
Help (Contents) / MS Jet SQL
–
http://www.prenhall.com/divisions/bp/app/mcfadden/student/glossaryfull.html
Relational databases
Relational
database: (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, but helpful
to adopt a consistent style.
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)
=====================================================================
For practice using SQL
commands in MS Access:
Unless
otherwise indicated:
1. Under Objects, select Queries.
2. Select "Create query in Design
view" (double-click to open).
3. Close the Show Table dialogue box.
4. Click the SQL view button (leftmost button
under the menu bar) or choose View / SQL View from the menu bar.
5. 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.
6. 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.)
7. Save the query and close the SQL design
window.
====================================================================
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.
(For my notes only: E.g.:
Which products are available from a particular supplier and at what
price? How many orders has each employee
processed this year and what is the total $ amount for each? At what price would we have to sell
particular products to make a 10% profit?)
=======
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
SELECT CompanyName AS Company,
FROM Customers
WHERE Country = “
ORDER BY CompanyName
4. View the results; then run and save the query
as UKCustomers (7 customers)
5. Close the query.
=============
Basic
syntax for the SELECT command:
SELECT column list
FROM tablename
WHERE criteria
ORDER BY column list
Criteria
for the WHERE clause:
Comparison operators: = >
< <=
>= <>
(or !=)
WHERE Country <> “
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)
SQL for each of the above:
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;
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: Not showing 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.
=====================================================================
Syntax:
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,
...)
}
=====================================================================
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.
=======================================================================
Syntax:
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
=====================================================================
MS Access
denotes these as "action queries" and warns the user any time data
will be modified.
| Exercise steps abbreviated from here
on. In general:
|
| 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.
=====================================================================
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 = "
=====================================================================
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
UPDATE MySuppliers
SET Region = "
WHERE City IN ("
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 ...