|
|
|
Larry Snyder & Mel Oyler, Instructors |
|
Ariel Kemp, Isaac Kunen, Gerome Miklau &
Sean Squires, Teaching Assistants |
|
University of Washington, Autumn 1999 |
|
|
|
|
Objectives |
|
Understand basics of database languages |
|
Learn how to create a database using SQL |
|
Learn how to manipulate and manage a database
using SQL |
|
|
|
|
Introduction to SQL |
|
Data Definition Commands |
|
Basic Data Management |
|
Queries |
|
|
|
|
|
|
SQL meets ideal database language requirements: |
|
SQL coverage fits into three categories: |
|
(1) Data definition. |
|
(2) Data management. |
|
(3) Data query. |
|
SQL is relatively easy to learn. |
|
ANSI prescribes a standard SQL. |
|
|
|
|
|
Reasons for Studying SQL: |
|
The ANSI standardization effort has led to a de
facto query standard for relational databases. |
|
SQL has become the basis for present and
expected future DBMS integration efforts. |
|
SQL has become the catalyst in the development
of distributed databases and database client/server architecture. |
|
|
|
|
|
|
The Database Model |
|
Database -- PRODUCT and VENDOR tables |
|
Each product is supplied by only a single
vendor. |
|
A vendor may supply many products. |
|
|
|
|
|
Creating the Database Structure |
|
CREATE SCHEMA AUTHORIZATION <creator> |
|
Example:
CREATE SCHEMA AUTHORIZATION
JONES |
|
|
|
CREATE DATABASE <database name> |
|
|
|
|
|
|
|
|
Creating Table Structures |
|
CREATE TABLE <table name>
(<attribute1 name and attribute1 characteristics,
attribute2 name and attribute2 characteristics,
attribute3 name and attribute3 characteristics,
primary key designation,
foreign
key designation and foreign key requirement>); |
|
|
|
|
|
|
CREATE TABLE VENDOR
(V_CODE FCHAR(5) NOT NULL UNIQUE,
V_NAME VCHAR(35) NOT
NULL,
V_CONTACT VCHAR(15) NOT
NULL,
V_AREACODE FCHAR(3) NOT NULL,
V_PHONE FCHAR(3) NOT
NULL,
V_STATE FCHAR(2) NOT
NULL,
V_ORDER FCHAR(1) NOT NULL,
PRIMARY KEY (V_CODE)); |
|
|
|
|
CREATE TABLE PRODUCT
(P_CODE VCHAR(10) NOT NULL UNIQUE,
P_DESCRIPT VCHAR(35) NOT
NULL,
P_INDATE DATE NOT
NULL,
P_ONHAND SMALLINT NOT NULL,
P_MIN SMALLINT NOT
NULL,
P_PRICE DECIMAL(8,2) NOT
NULL,
P_DISCOUNT DECIMAL(4,1) NOT NULL,
V_CODE SMALLINT,
PRIMARY KEY (P_CODE),
FOREIGN KEY (V_CODE) REFERENCES
VENDOR
ON DELETE RESTRICT
ON UPDATE CASCADE); |
|
|
|
|
|
|
SQL Integrity Constraints |
|
Entity Integrity |
|
PRIMARY KEY |
|
NOT NULL and UNIQUE |
|
Referential Integrity |
|
FOREIGN KEY |
|
ON DELETE |
|
ON UPDATE |
|
|
|
|
|
|
Data Entry |
|
INSERT INTO <table name> VALUES (attribute
1 value, attribute 2 value, … etc.); |
|
Examples: |
|
INSERT INTO VENDOR
VALUES(‘21225, ’Brson, Inc.’, ’Smithson’, ’615’,’223-3234’, ’TN’, ’Y’); |
|
|
|
INSERT INTO PRODUCT
VALUES(‘11 QER/31’, ’Power painter, 15 psi., 3-nozzle’, ’12/2/96’, 8.5,
109.99, 0.00, 25595); |
|
|
|
|
|
|
Checking the Table Contents |
|
SELECT <attribute names> FROM <table
names>; |
|
Examples: |
|
SELECT * FROM PRODUCT; |
|
|
|
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND,
P_MIN, P-PRICE, P_DISCOUNT, V_CODE
FROM PRODUCT; |
|
|
|
|
|
|
Saving the Table Contents |
|
COMMIT <table names>; |
|
Example: |
|
COMMIT PRODUCT; |
|
|
|
|
|
|
Adding Data to the Table |
|
INSERT INTO <table name> VALUES(attribute
values); |
|
Example: |
|
INSERT INTO PRODUCT
VALUES(‘14-Q1/L3’, ’9.00-in. Pwr. saw lade’, ’11/12/96’, 18,12, 17.49,
0.00, 21344); |
|
|
|
|
|
|
|
Deleting Table Rows |
|
DELETE FROM <table name>
WHERE <attribute name> = <attribute value>; |
|
Example: |
|
DELETE FROM PRODUCT
WHERE P_CODE = ‘2238/QPD’; |
|
|
|
DELETE FROM PRODUCT
WHERE P_MIN = 5; |
|
|
|
|
|
|
Partial Listing of Table Contents |
|
SELECT <column(s)>
FROM <table name>
WHERE <conditions>; |
|
Examples: |
|
SELECT P_DESCRIPT, P_INDATE, P_PRICE,
V_CODE
FROM PRODUCT
WHERE V_CODE = 21344; |
|
|
|
|
|
|
SELECT P_DESCRIPT, P_INDATE, P_PRICE,
V_CODE
FROM PRODUCT
WHERE V_CODE < > 21344; |
|
|
|
|
|
|
SELECT P_DESCRIPT, P_ONHAND, P_MIN,
P_PRICE
FROM PRODUCT
WHERE P_PRICE <= 10; |
|
|
|
|
|
|
|
Logical Operators: AND, OR, and NOT |
|
Examples: |
|
SELECT P_DESCRIPT, P_INDATE, P_PRICE,
V_CODE
FROM PRODUCT
WHERE V_CODE = 21344
OR V_CODE = 24288; |
|
|
|
|
|
|
SELECT P_DESCRIPT, P_INDATE, P_PRICE,
V_CODE
FROM PRODUCT
WHERE P_PRICE < 50
AND P_INDATE > 07/15/96; |
|
|
|
|
|
|
Special Operators |
|
BETWEEN is used to define range limits. |
|
Example: |
|
SELECT *
FROM PRODUCT
WHERE P_PRICE BETWEEN 50.00 AND 100.00; |
|
|
|
|
|
|
LIKE is used to check for similar character
strings. |
|
Examples: |
|
SELECT * FROM VENDOR
WHERE V_CONTACT LIKE ‘Smith%’; |
|
|
|
|
|
|
IN is used to check whether an attribute value
matches a value contained within a (sub)set of listed values. |
|
Example: |
|
SELECT * FROM PRODUCT
WHERE V_CODE IN (21344, 24288); |
|
EXISTS is used to check whether an attribute has
value. |
|
Example: |
|
DELETE FROM PRODUCT
WHERE P_CODE EXISTS; |
|
|
|
SELECT * FROM PRODUCT
WHERE V_CODE EXISTS; |
|
|
|
|
|
Changing Table Structures |
|
ALTER TABLE <table name>
MODIFY <column name> <new column characteristics>; |
|
ALTER TABLE <table name>
ADD <column name> <new column characteristics>; |
|
|
|
|
|
|
UPDATE PRODUCT
SET P_SALECODE = ‘2’
WHERE P_INDATE < 8/15/96; |
|
|
|
|
|
UPDATE PRODUCT
SET P_SALECODE = ‘1’
WHERE P_INDATE >= ‘11/15/96’
AND P_INDATE < ‘12/1/96’; |
|
|
|
|
|
|
Copying Parts of Tables |
|
INSERT INTO <receiving table>
<receiving table’s column names>
SELECT <column names of
the columns to be copied>
FROM <contributing table name>; |
|
|
|
Example: |
|
INSERT INTO PART (PART_CODE, PART_DESCRIPT,
PART_PRICE)
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT; |
|
|
|
|
|
|
Deleting a Table from the Database |
|
DROP TABLE <table name>; |
|
Example: |
|
DROP TABLE PART; |
|
|
|
|
|
|
Primary and Foreign Key Designation |
|
Examples: |
|
ALTER TABLE PRODUCT
ADD PRIMARY KEY (P_CODE); |
|
|
|
ALTER TABLE PRODUCT
ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR; |
|
|
|
|
|
|
Ordering a Listing |
|
ORDER BY <attributes> |
|
Examples: |
|
SELECT P_CODE, P_DESCRIPT, P_INDATE,
P_PRICE
FROM PRODUCT
ORDER BY P_PRICE; |
|
|
|
|
|
Results on next slide ---> |
|
|
|
|
|
|
|
SELECT P_CODE, P_DESCRIPT, P_INDATE,
P_PRICE
FROM PRODUCT
ORDER BY P_PRICE DESC; |
|
|
|
|
|
|
|
|
SELECT P_CODE, P_DESCRIPT, P_INDATE,
P_PRICE
FROM PRODUCT
WHERE P_INDATE < 9/15/96
AND P_PRICE <= 50.00
ORDER BY V_CODE DESC, P_PRICE DESC; |
|
|
|
|
|
|
Listing Unique Values |
|
SELECT DISTINCT <attributes> ... |
|
Example: |
|
SELECT DISTINCT V_CODE
FROM PRODUCT; |
|
|
|
|
|
|
|
SQL’s Numeric Functions |
|
COUNT |
|
Examples: |
|
SELECT COUNT(DISTINCT V_CODE)
FROM PRODUCT; |
|
|
|
SELECT COUNT(DISTINCT V_CODE)
FROM PRODUCT
WHERE P_PRICE <= 10.00; |
|
|
|
|
|
|
MAX and MIN |
|
Examples: |
|
SELECT MAX(P_PRICE)
FROM PRODUCT; |
|
|
|
SELECT MIN(P_PRICE)
FROM PRODUCT; |
|
|
|
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT
WHERE P_PRICE =
(SELECT MAX(P_PRICE) FROM PRODUCT); |
|
|
|
|
|
|
SUM |
|
Example: |
|
SELECT SUM(P_ONHAND * P_PRICE)
FROM PRODUCT; |
|
|
|
|
|
|
Virtual Tables: Creating a View |
|
CREATE VIEW <view name> AS
SELECT … FROM … WHERE …; |
|
Example: |
|
CREATE VIEW PRODUCT_3 AS
SELECT P_DESCRIPT, P_ONHAND, P_PRICE
FROM PRODUCT
WHERE P_PRICE > 50.00; |
|
|
|
|
|
|
Joining Database Tables |
|
Examples: |
|
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE,
VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE; |
|
|
|
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE
ORDER BY P_PRICE; |
|
|
|
|
|
|
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE
AND P_INDATE > 11/15/96; |
|
|
|
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
V_AREACODE, V_PHONE
FROM PRODUCT A, VENDOR B
WHERE A.V_CODE = B.V_CODE
ORDER BY P_PRICE; |
|