Chapter 5 Structured Query Language (SQL)

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

2. In this chapter, you will learn: • The basic commands and functions of SQL • How SQL is used for data manipulation (to add, modify, delete, and retrieve data) • How to use SQL to query a database to extract useful information • How SQL is used for data administration (to create tables, indexes, and views) • About more advanced SQL features such as updatable views, stored procedures, and triggers

3. Introduction to SQL • Ideal database language • Createdatabase and table structures • Perform basic data management chores (add, delete, and modify) • Perform complex queries to transform data into useful information

4. Introduction to SQL • SQL (Structured Query Language)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. • SQL2 : SQL-92 • SQL3 : SQL-98/99 support object-oriented data management

5. 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.

6. 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. • Some vendors have never supplied a product ( 0,N )

7. Data Definition Commands • The Tables and Their Components • 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. ( 0,1 )

8. PRODUCT CH5_TEXT VENDER

9. Data Definition Commands • Create database structure • Holds all tables and is a collection of physical files stored on disk • DBMS automatically creates tables to store metadata • Database administrator creates structure or schema • Logical group of tables or logical database • Groups tables by owner • Enforces security

10. Data Definition Commands • Creating the Database Structure CREATE SCHEMA AUTHORIZATION <creator>; • Example:CREATE SCHEMA AUTHORIZATION JONES; • Schema : logical database structurea group of database objects- such as tables and indexes – that are related to each other. CREATE DATABASE <database name>; • Example:CREATE DATABASE CH5;

11. A Data Dictionary for the CH5 Database Table 5.1

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

13. 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>);

14. Data Definition Commands CREATE TABLE VENDOR(V_CODE INTEGER NOT NULL UNIQUE, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(3) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL,PRIMARY KEY(V_CODE));

15. CREATE TABLE PRODUCT(P_CODE VARCHAR(10) NOT NULLUNIQUE,P_DESCRIPT VARCHAR(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); • ON DELETE RESTRICTcannotdelete a vender as long as there is a product that references that vender • ON UPDATE CASCADEupdate V_CODE in VENDER → update V_CODE in PRODUCT

16. Data Definition Commands • Adherence to entity integrity and referential integrityrules is crucial • SQL Integrity Constraints • Entity Integrity • PRIMARY KEY • NOT NULL and UNIQUE • Referential Integrity • FOREIGN KEY • ON DELETE • ON UPDATE

17. SQL Command Coverage Table 5.3

18. Data Entry and Saving • Enters data into a table • Saves changes to disk INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.); COMMIT <table names> ;

19. 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);

20. Figure 5.3 A form-based Data View and Entry Screen

21. Basic Data Management • Saving the Table Contents COMMIT <table names>; COMMIT PRODUCT; • Any changes made to the table contentsare not physically saved on diskuntil • COMMIT • close the database • log out of SQL

22. Listing Table Contents and Other Commands • Allows table contents to be listed • UPDATE command makes data entry corrections • ROLLBACK command restores database back to previous condition if COMMIT hasn’t been used • DELETE command removes table row SELECT <attribute names> FROM <table names>;

23. Basic Data Management • 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;

24. Figure 5.4 The Contents of the PRODUCT Table

25. Basic Data Management • Making a Correction UPDATEPRODUCTSET P_INDATE = ‘12/11/96’WHERE P_CODE = ‘13-Q2/P2’; UPDATEPRODUCTSET P_INDATE = ‘12/11/96’, P_PRICE = 15.99, P_MIN = 10WHERE P_CODE = ‘13-Q2/P2’;

26. Basic Data Management • Restoring the Table Contents ROLLBACK • If COMMITnot yet • Does not require to specify the table name.SQL assumes that the database currently in memoryis the one to be restored. • Update integrity in transaction management (Ch.9) • COMMIT • ROLLBACK

27. Basic Data Management • Deleting Table Rows DELETE FROMPRODUCTWHERE P_CODE = ‘2238/QPD’; DELETEFROMPRODUCTWHERE P_MIN = 5;

28. Queries • Creating partial listings of table contents SELECT <column(s)>FROM <table name>WHERE <conditions>; Table 5.4 Mathematical Operators

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

30. Figure 5.6 The Microsoft Access QBE and Its SQL QBE (Query By Example) query generator

31. Examples • Mathematical operators • Mathematical operators on character attributes • Mathematical operators on dates SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHEREV_CODE <> 21344; SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFROM PRODUCTWHEREP_CODE < ‘1558-QWI’; SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEFROM PRODUCTWHEREP_INDATE >= ‘01/20/2002’;

32. Queries SELECTP_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROMPRODUCTWHERE V_CODE <> 21344; Figure 5.7

33. Queries SELECTP_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROMPRODUCTWHERE P_PRICE <= 10; Figure 5.8

34. Queries • Using Mathematical Operators on Character Attributes SELECTP_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROMPRODUCTWHEREP_CODE < ‘1558-QWI’; Figure 5.9

35. Queries • Using Mathematical Operators on Dates SELECTP_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATEFROMPRODUCTWHEREP_INDATE >= ‘08/15/1999’; Figure 5.10

36. Computed Columns • New columns can be created through valid computations or formulas • Formulas may contain mathematical operators • May contain attributes of any tables specified in FROM clause • Alias is alternate name given to table or column in SQL statement SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHAND*P_PRICEASTOTVALUE FROM PRODUCT;

37. Operators • Logical: AND, OR, NOT • Rules of precedence • Conditions within parenthesis executed first • Boolean algebra • Special • BETWEEN - defines limits • IS NULL - checks for nulls • LIKE - checks for similar string • IN - checks for value in a set • EXISTS - opposite of IS NULL SELECT * FROM PRODUCTWHERE V_CODE = 21344 OR V_CODE = 24288;

38. Queries • Logical Operators: AND, OR, and NOT SELECTP_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROMPRODUCTWHEREV_CODE=21344 OR V_CODE=24288; Figure 5.13

39. Queries SELECTP_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROMPRODUCTWHEREP_PRICE<50 AND P_INDATE>‘07/15/1999’; Figure 5.14

40. Queries SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROMPRODUCTWHERE(P_PRICE<50 AND P_INDATE>07/15/1999’) OR V_CODE=24288; Figure 5.15

41. 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. - the opposite of IS NULL.

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

43. Queries • Special Operators IS NULL is used to check whether an attribute value is null. SELECT P_CODE, P_DESCRIPT FROM PRODUCTWHEREP_MIN ISNULL; SELECT P_CODE, P_DESCRIPT FROM PRODUCTWHEREP_INDATE ISNULL;

44. Queries • Special Operators LIKE is used to check for similar character strings. SELECT * FROM VENDORWHEREV_CONTACT LIKE ‘Smith%’; SELECT * FROM VENDORWHEREV_CONTACT LIKE ‘SMITH%’; • % : cn , c=any character, n≧0 • _ : c1

45. 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 PRODUCTWHEREV_CODE IN (21344, 24288);

46. Queries • EXISTS is used to check whether an attribute has value. DELETEFROM PRODUCTWHEREP_CODE EXISTS; SELECT * FROM PRODUCTWHEREV_CODE EXISTS;

47. 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>);

48. 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));

49. Advanced Data Management Commands UPDATE PRODUCTSETP_SALECODE = ‘2’WHEREP_CODE = ‘1546-QQ2’;

50. Advanced Data Management Commands UPDATE PRODUCTSETP_SALECODE = ‘1’WHEREP_CODE IN (‘2232/QWE’, ‘2232/QTY’);