Chapter 3 Structured Query Language (SQL)

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

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

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

4. Data Definition Commands • The Tables and Their Components • 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.

6. Data Definition Commands • Creating the Database Structure CREATE SCHEMA AUTHORIZATION <creator>; • Example:CREATE SCHEMA AUTHORIZATION JONES; CREATE DATABASE <database name>; • Example:CREATE DATABASE CH3;

7. A Data Dictionary for the CH3 Database Table 3.1

8. Some Common SQL Data Types Data Type Format Numeric NUMBER(L,D) INTEGER SMALLINT DECIMAL(L,D) Character CHAR(L) VARCHAR(L) Date DATE

9. Data Definition Commands • 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 requirements>);

10. Data Definition Commands 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));

11. Data Definition Commands 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);

12. Data Definition Commands • SQL Integrity Constraints • Entity Integrity • PRIMARY KEY • NOT NULL and UNIQUE • Referential Integrity • FOREIGN KEY • ON DELETE • ON UPDATE

13. SQL Command Coverage Table 3.3

14. Basic Data Management • Data Entry INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.); INSERT INTO VENDORVALUES(‘21225, ’Bryson, Inc.’, ’Smithson’, ’615’,’223-3234’, ’TN’, ’Y’); INSERT INTO PRODUCTVALUES(‘11 QER/31’, ’Power painter, 15 psi., 3-nozzle’, ’07/02/1999’, 8.5, 109.99, 0.00, 25595);

15. Figure 3.3 A Data View and Entry Screen

16. Basic Data Management • Saving the Table Contents COMMIT <table names>; 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;

17. Figure 3.4 The Contents of the PRODUCT Table

18. Basic Data Management • Making a Correction UPDATE PRODUCTSET P_INDATE = ‘12/11/96’WHERE P_CODE = ‘13-Q2/P2’; UPDATE PRODUCTSET P_INDATE = ‘12/11/96’, P_PRICE = 15.99, P_MIN=10WHERE P_CODE = ‘13-Q2/P2’; • Restoring the Table Contents ROLLBACK

19. Basic Data Management • Deleting Table Rows DELETE FROM PRODUCTWHERE P_CODE = ‘2238/QPD’; DELETE FROM PRODUCTWHERE P_MIN = 5;

20. Queries • Partial Listing of Table Contents SELECT <column(s)>FROM <table name>WHERE <conditions>; SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE V_CODE = 21344; Figure 3.5

21. Figure 3.6 The Microsoft Access QBE and Its SQL

22. Queries Mathematical Operators Table 3.4

23. Queries SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE V_CODE <> 21344; Figure 3.7

24. Queries SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROM PRODUCTWHERE P_PRICE <= 10; Figure 3.8

25. Queries • Using Mathematical Operators on Character Attributes SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROM PRODUCTWHERE P_CODE < ‘1558-QWI’; Figure 3.9

26. Queries • Using Mathematical Operators on Dates SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATEFROM PRODUCTWHERE P_INDATE >= ‘08/15/1999’; Figure 3.10

27. Queries • Logical Operators: AND, OR, and NOT SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE V_CODE = 21344OR V_CODE = 24288; Figure 3.11

28. Queries SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE P_PRICE < 50AND P_INDATE > ‘07/15/1999’; Figure 3.12

29. Queries SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE (P_PRICE < 50 AND P_INDATE > ‘07/15/1999’)OR V_CODE = 24288; Figure 3.13

30. Queries • Special Operators • 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.

31. Queries • Special Operators BETWEEN is used to define range limits. SELECT * FROM PRODUCTWHERE P_PRICE BETWEEN 50.00 AND 100.00; SELECT *FROM PRODUCTWHERE P_PRICE > 50.00AND P_PRICE < 100.00;

32. Queries • Special Operators IS NULL is used to check whether an attribute value is null. SELECT P_CODE, P_DESCRIPT FROM PRODUCTWHERE P_MIN IS NULL; SELECT P_CODE, P_DESCRIPT FROM PRODUCTWHERE P_INDATE IS NULL;

33. Queries • Special Operators LIKE is used to check for similar character strings. SELECT * FROM VENDORWHERE V_CONTACT LIKE ‘Smith%’; SELECT * FROM VENDORWHERE V_CONTACT LIKE ‘SMITH%’;

34. Queries • Special Operators IN is used to check whether an attribute value matches a value contained within a (sub)set of listed values. SELECT * FROM PRODUCTWHERE V_CODE IN (21344, 24288); EXISTS is used to check whether an attribute has value. DELETE FROM PRODUCTWHERE P_CODE EXISTS; SELECT * FROM PRODUCTWHERE V_CODE EXISTS;

35. Advanced Data Management Commands • Changing Table Structures ALTER TABLE <table name>MODIFY (<column name> <new column characteristics>); ALTER TABLE <table name>ADD (<column name> <new column characteristics>);

36. Advanced Data Management Commands • Changing a Column’s Data Type ALTER TABLE PRODUCTMODIFY (V_CODE CHAR(5)); • Changing Attribute Characteristics ALTER TABLE PRODUCTMODIFY (P_PRICE DECIMAL(9,2)); • Adding a New Column to the Table ALTER TABLE PRODUCTADD (P_SALECODE CHAR(1));

37. Advanced Data Management Commands UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_CODE = ‘1546-QQ2’; Figure 3.14 Selected PRODUCT Table Attributes: Multiple Data Entry

38. Advanced Data Management Commands UPDATE PRODUCTSET P_SALECODE = ‘1’WHERE P_CODE IN (‘2232/QWE’, ‘2232/QTY’); Figure 3.15 Selected PRODUCT Table Attributes: Multiple Data Entry

39. Advanced Data Management Commands UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_INDATE < ‘07/10/1999’; UPDATE PRODUCTSET P_SALECODE = ‘1’WHERE P_INDATE >= ‘08/15/1999’AND P_INDATE < ‘08/20/1999’;

40. Advanced Data Management Commands Selected PRODUCT Table Attributes: Multiple Update Effect Figure 3.16

41. The Arithmetic Operators Table 3.5

42. Advanced Data Management Commands • Copying Parts of Tables CREATE TABLE PARTPART_CODE CHAR(8) NOT NULL UNIQUE,PART_DESCRIPT CHAR(35),PART_PRICE DECIMAL(8,2),PRIMARY KEY(PART_CODE)); INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE)SELECT P_CODE, P_DESCRIPT, P_PRICEFROM PRODUCT;

43. The Part Attributes Copied from the PRODUCT Table Figure 3.17

44. Advanced Data Management Commands • Deleting a Table from the Database • DROP TABLE <table name>; DROP TABLE PART;

45. Advanced Data Management Commands • Primary and Foreign Key Designation ALTER TABLE PRODUCTADD PRIMARY KEY (P_CODE); ALTER TABLE PRODUCTADD FOREIGN KEY (V_CODE) REFERENCES VENDOR; ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE) ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;