Transcript in factory direct

Chapter 3
3
Structured Query Language (SQL)
Database Systems: Design, Implementation, and Management
4th Edition
Peter Rob & Carlos Coronel
1
Introduction to SQL
 SQL meets ideal database language
requirements:
3

SQL coverage fits into two categories:

Data definition

Data manipulation

SQL is relatively easy to learn.

ANSI prescribes a standard SQL.
2
Data Definition Commands
 The Database Model

3
Simple Database -- PRODUCT and VENDOR tables


Each product is supplied by only a single vendor.
A vendor may supply many products.
Figure 3.1
3
Data Definition Commands
 The Tables and Their Components
3

The VENDOR table contains vendors who are not
referenced in the PRODUCT table. PRODUCT is
optional to VENDOR.

Existing V_CODE values in the PRODUCT table must
have a match in the VENDOR table.

A few products are supplied factory-direct, a few are
made in-house, and a few may have been bought in a
special warehouse sale. That is, a product is not
necessarily supplied by a vendor. VENDOR is
optional to PRODUCT.
4
3
5
Data Definition Commands
 Creating the Database Structure
3
CREATE SCHEMA AUTHORIZATION <creator>;

Example:
CREATE SCHEMA AUTHORIZATION JONES;
CREATE DATABASE <database name>;

Example:
CREATE DATABASE CH3;
6
A Data Dictionary for the CH3 Database
3
Table 3.1
7
Some Common SQL Data Types
3
Data Type
Format
Numeric
NUMBER(L,D)
INTEGER
SMALLINT
DECIMAL(L,D)
Character
CHAR(L)
VARCHAR(L)
Date
DATE
8
Data Definition Commands
 Creating Table Structures
3
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
requirements>);
9
Data Definition Commands
3
CREATE TABLE VENDOR
(V_CODE
FCHAR(5)
V_NAME
VCHAR(35)
V_CONTACT
VCHAR(15)
V_AREACODE
FCHAR(3)
V_PHONE
FCHAR(3)
V_STATE
FCHAR(2)
V_ORDER
FCHAR(1)
PRIMARY KEY (V_CODE));
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NULL UNIQUE,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
10
Data Definition Commands
3
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);
11
Data Definition Commands
 SQL Integrity Constraints
3

Entity Integrity



PRIMARY KEY
NOT NULL and UNIQUE
Referential Integrity



FOREIGN KEY
ON DELETE
ON UPDATE
12
SQL Command Coverage
3
Table 3.3
13
Basic Data Management
 Data Entry
3
INSERT INTO <table name> VALUES (attribute 1
value, attribute 2 value, … etc.);
INSERT INTO VENDOR
VALUES(‘21225, ’Bryson, Inc.’, ’Smithson’,
’615’,’223-3234’, ’TN’, ’Y’);
INSERT INTO PRODUCT
VALUES(‘11 QER/31’, ’Power painter, 15 psi.,
3-nozzle’, ’07/02/1999’, 8.5, 109.99, 0.00,
25595);
14
Figure 3.3
A Data View and Entry Screen
3
15
Basic Data Management
 Saving the Table Contents
COMMIT <table names>;
3
COMMIT PRODUCT;
 Listing the Table Contents
SELECT * FROM PRODUCT;
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND,
P_MIN, P-PRICE, P_DISCOUNT, V_CODE
FROM PRODUCT;
16
Figure 3.4
3
The Contents of
the PRODUCT
Table
17
Basic Data Management
 Making a Correction
3
UPDATE PRODUCT
SET P_INDATE = ‘12/11/96’
WHERE P_CODE = ‘13-Q2/P2’;
UPDATE PRODUCT
SET P_INDATE = ‘12/11/96’, P_PRICE = 15.99,
P_MIN=10
WHERE P_CODE = ‘13-Q2/P2’;
 Restoring the Table Contents
ROLLBACK
18
Basic Data Management
 Deleting Table Rows
3
DELETE FROM PRODUCT
WHERE P_CODE = ‘2238/QPD’;
DELETE FROM PRODUCT
WHERE P_MIN = 5;
19
Queries
 Partial Listing of Table Contents
3
SELECT <column(s)>
FROM <table name>
WHERE <conditions>;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;
Figure 3.5
20
Figure 3.6
3
The Microsoft
Access QBE and
Its SQL
21
Queries
Mathematical Operators
3
Table 3.4
22
Queries
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344;
3
Figure 3.7
23
Queries
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_PRICE <= 10;
3
Figure 3.8
24
Queries
 Using Mathematical Operators on
Character Attributes
3
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE < ‘1558-QWI’;
Figure 3.9
25
Queries
 Using Mathematical Operators on Dates
3
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE,
P_INDATE
FROM PRODUCT
WHERE P_INDATE >= ‘08/15/1999’;
Figure 3.10
26
Queries
 Logical Operators: AND, OR, and NOT
3
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344
OR V_CODE = 24288;
Figure 3.11
27
Queries
3
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE < 50
AND P_INDATE > ‘07/15/1999’;
Figure 3.12
28
Queries
3
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE (P_PRICE < 50 AND P_INDATE >
‘07/15/1999’)
OR V_CODE = 24288;
Figure 3.13
29
Queries
 Special Operators
3

BETWEEN - used to define range limits.

IS NULL - used to check whether an attribute
value is null

LIKE - used to check for similar character strings.

IN - used to check whether an attribute value
matches a value contained within a (sub)set of
listed values.

EXISTS - used to check whether an attribute has a
value. In effect, EXISTS is the opposite of IS
NULL.
30
Queries
3
 Special Operators
BETWEEN is used to define range limits.
SELECT *
FROM PRODUCT
WHERE P_PRICE BETWEEN 50.00 AND 100.00;
SELECT *
FROM PRODUCT
WHERE P_PRICE > 50.00
AND P_PRICE < 100.00;
31
Queries
 Special Operators
3
IS NULL is used to check whether an attribute value
is null.
SELECT P_CODE, P_DESCRIPT
FROM PRODUCT
WHERE P_MIN IS NULL;
SELECT P_CODE, P_DESCRIPT
FROM PRODUCT
WHERE P_INDATE IS NULL;
32
Queries
 Special Operators
3
LIKE is used to check for similar character strings.
SELECT * FROM VENDOR
WHERE V_CONTACT LIKE ‘Smith%’;
SELECT * FROM VENDOR
WHERE V_CONTACT LIKE ‘SMITH%’;
33
Queries
 Special Operators
3
IN is used to check whether an attribute value
matches a value contained within a (sub)set of
listed values.
SELECT * FROM PRODUCT
WHERE V_CODE IN (21344, 24288);
EXISTS is used to check whether an attribute has
value.
DELETE FROM PRODUCT
WHERE P_CODE EXISTS;
SELECT * FROM PRODUCT
WHERE V_CODE EXISTS;
34
Advanced Data Management
Commands
3
 Changing Table Structures
ALTER TABLE <table name>
MODIFY (<column name> <new column
characteristics>);
ALTER TABLE <table name>
ADD (<column name> <new column characteristics>);
35
Advanced Data Management
Commands
 Changing a Column’s Data Type
3
ALTER TABLE PRODUCT
MODIFY (V_CODE CHAR(5));
 Changing Attribute Characteristics
ALTER TABLE PRODUCT
MODIFY (P_PRICE DECIMAL(9,2));
 Adding a New Column to the Table
ALTER TABLE PRODUCT
ADD (P_SALECODE CHAR(1));
36
Advanced Data Management
Commands
3
UPDATE PRODUCT
SET P_SALECODE = ‘2’
WHERE P_CODE = ‘1546-QQ2’;
Figure 3.14
Selected PRODUCT Table Attributes: Multiple Data Entry
37
Advanced Data Management
Commands
3
UPDATE PRODUCT
SET P_SALECODE = ‘1’
WHERE P_CODE IN (‘2232/QWE’, ‘2232/QTY’);
Figure 3.15 Selected PRODUCT Table Attributes:
Multiple Data Entry
38
Advanced Data Management
Commands
3
UPDATE PRODUCT
SET P_SALECODE = ‘2’
WHERE P_INDATE < ‘07/10/1999’;
UPDATE PRODUCT
SET P_SALECODE = ‘1’
WHERE P_INDATE >= ‘08/15/1999’
AND P_INDATE < ‘08/20/1999’;
39
Advanced Data Management
Commands
Selected PRODUCT Table Attributes: Multiple Update Effect
3
Figure 3.16
40
The Arithmetic Operators
3
Table 3.5
41
Advanced Data Management
Commands
 Copying Parts of Tables
3
CREATE TABLE PART
PART_CODE
CHAR(8) NOT NULL
PART_DESCRIPT CHAR(35),
PART_PRICE
DECIMAL(8,2),
PRIMARY KEY(PART_CODE));
UNIQUE,
INSERT INTO PART (PART_CODE, PART_DESCRIPT,
PART_PRICE)
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT;
42
The Part Attributes Copied from the PRODUCT Table
3
Figure 3.17
43
Advanced Data Management
Commands
 Deleting a Table from the Database
3

DROP TABLE <table name>;
DROP TABLE PART;
44
Advanced Data Management
Commands
 Primary and Foreign Key Designation
3
ALTER TABLE PRODUCT
ADD PRIMARY KEY (P_CODE);
ALTER TABLE PRODUCT
ADD FOREIGN KEY (V_CODE) REFERENCES
VENDOR;
ALTER TABLE PRODUCT
ADD PRIMARY KEY (P_CODE)
ADD FOREIGN KEY (V_CODE) REFERENCES
VENDOR;
45