Chapter 3 3 Structured Query Language SQL Database

Chapter 3 3 Structured Query Language (SQL) Database Systems: Design, Implementation, and Management 4 th Edition Peter Rob & Carlos Coronel 1

Introduction to SQL 4 3 SQL meets ideal database language requirements: u SQL coverage fits into two categories: l Data definition l Data manipulation u SQL is relatively easy to learn. u ANSI prescribes a standard SQL. 2

Data Definition Commands 4 The Database Model 3 u Simple Database -- PRODUCT and VENDOR tables l l Each product is supplied by only a single vendor. A vendor may supply many products. Figure 3. 1 3

Data Definition Commands 4 3 The Tables and Their Components u The VENDOR table contains vendors who are not referenced in the PRODUCT table. PRODUCT is optional to VENDOR. u Existing V_CODE values in the PRODUCT table must have a match in the VENDOR table. u 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 4 3 Creating the Database Structure CREATE SCHEMA AUTHORIZATION <creator>; u Example: CREATE SCHEMA AUTHORIZATION JONES; CREATE DATABASE <database name>; u Example: CREATE DATABASE CH 3; 6

A Data Dictionary for the CH 3 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 4 3 Creating Table Structures CREATE TABLE <table name>( <attribute 1 name and attribute 1 characteristics, attribute 2 name and attribute 2 characteristics, attribute 3 name and attribute 3 characteristics, primary key designation, foreign key designation and foreign key requirements>); 9

Data Definition Commands 3 CREATE TABLE VENDOR (V_CODE FCHAR(5) NOT V_NAME VCHAR(35) NOT V_CONTACT VCHAR(15) V_AREACODE FCHAR(3) V_PHONE FCHAR(3) NOT V_STATE FCHAR(2) NOT V_ORDER FCHAR(1) NOT PRIMARY KEY (V_CODE)); NULL UNIQUE, NULL, NOT NULL, 10

Data Definition Commands 3 CREATE TABLE PRODUCT( P_CODE VCHAR(10) NOT NULL UNIQUE, P_DESCRIPT VCHAR(35) NOT NULL, P_INDATE 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 4 SQL Integrity Constraints 3 u Entity Integrity l l u PRIMARY KEY NOT NULL and UNIQUE Referential Integrity l l l FOREIGN KEY ON DELETE ON UPDATE 12

SQL Command Coverage 3 Table 3. 3 13

Basic Data Management 4 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 4 Saving the Table Contents 3 COMMIT <table names>; COMMIT PRODUCT; 4 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 4 Making a Correction 3 UPDATE PRODUCT SET P_INDATE = ‘ 12/11/96’ WHERE P_CODE = ‘ 13 -Q 2/P 2’; UPDATE PRODUCT SET P_INDATE = ‘ 12/11/96’, P_PRICE = 15. 99, P_MIN=10 WHERE P_CODE = ‘ 13 -Q 2/P 2’; 4 Restoring the Table Contents ROLLBACK 18

Basic Data Management 4 Deleting Table Rows 3 DELETE FROM PRODUCT WHERE P_CODE = ‘ 2238/QPD’; DELETE FROM PRODUCT WHERE P_MIN = 5; 19

Queries 4 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 3 SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; Figure 3. 7 23

Queries 3 SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_PRICE <= 10; Figure 3. 8 24

Queries 3 4 Using Mathematical Operators on Character Attributes SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE < ‘ 1558 -QWI’; Figure 3. 9 25

Queries 4 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 4 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 4 Special Operators 3 u BETWEEN - used to define range limits. u IS NULL - used to check whether an attribute value is null u LIKE - used to check for similar character strings. u IN - used to check whether an attribute value matches a value contained within a (sub)set of listed values. u EXISTS - used to check whether an attribute has a value. In effect, EXISTS is the opposite of IS NULL. 30

Queries 3 4 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 4 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 4 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 4 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 4 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 3 4 Changing a Column’s Data Type ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5)); 4 Changing Attribute Characteristics ALTER TABLE PRODUCT MODIFY (P_PRICE DECIMAL(9, 2)); 4 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 -QQ 2’; 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

3 The Arithmetic Operators Table 3. 5 41

Advanced Data Management Commands 4 Copying Parts of Tables 3 CREATE TABLE 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 3 4 Deleting a Table from the Database u DROP TABLE <table name>; DROP TABLE PART; 44

Advanced Data Management Commands 3 4 Primary and Foreign Key Designation 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