Introduction to Structured Query Language (SQL)

1. CH 7 Introduction to Structured Query Language (SQL) • The basic commands and functions of SQL • How to use SQL for data administration (to create tables, indexes, and views) • How to use SQL for data manipulation (to add, modify, delete, and retrieve data) • How to use SQL to query a database to extract useful information

2. Introduction to SQL • SQL functions fit into two broad categories: • Data definition language (Table 7.1) • SQL includes commands to create • Database objects such as tables, indexes, and views • Commands to define access rights to those database objects • Data manipulation language (Table 7.2) • Includes commands to insert, update, delete, and retrieve data within the database tables

3. SQL Data Definition Commands

4. SQL Data Manipulation Commands

5. SQL Data Manipulation Commands (continued)

6. Data Types • Data type selection is usually dictated by the nature of the data and by the intended use • Pay close attention to the expected use of attributes for sorting and data retrieval purposes • Sample of data dictionary refer to Table 7.3

7. Data Types (Table 7.4)

8. Creating Table Structures • Use one line per column (attribute) definition • Use spaces to line up the attribute characteristics and constraints • Table and attribute names are capitalized • NOT NULL specification • UNIQUE specification • Primary key attributes contain both a NOT NULL and a UNIQUE specification • RDBMS will automatically enforce referential integrity for foreign keys • Command sequence ends with a semicolon

9. Create Table Syntax CREATE TABLE tablename ( column1 data type [constraint], column2 data type [constraint], PRIMARY KEY (column1), FOREIGN KEY (column2) REFERENCES tablename CONSTRAINT constraint ); 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(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL, PRIMARY KEY (V_CODE) ); CREATE TABLE PRODUCT ( P_CODE VARCHAR(10) NOT NULL UNIQUE, P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE DATE NOT NULL, P_ONHAND SMALLINT NOT NULL, P_MIN SMALLINT NOT NULL, P_PRICE NUMBERS(8,2) NOT NULL, P_DISCOUNT NUMBERS(4,2) NOT NULL, V_CODE INTEGER, PRIMARY KEY (P_CODE), FOREIGN KEY (V_CODE) REFERENCES VENDOR ON UPDATE CASCADE );

10. Other SQL Constraints • NOT NULL constraint • Ensures that a column does not accept nulls • UNIQUE constraint • Ensures that all values in a column are unique • DEFAULT constraint • Assigns a value to an attribute when a new row is added to a table • CHECK constraint • Validates data when an attribute value is entered

11. SQL Indexes (pg. 229-230) • Indexes are used to improve efficiency of searches and to avoid duplicate column values • When a primary key is declared, DBMS automatically creates a unique index • Normally, often need additional indexes • Using the CREATE INDEX command, SQL indexes can be created on the basis of any selected attribute • Composite index • Index based on two or more attributes • Often used to prevent data duplication

12. Creating Index • Create Index Syntax CREATE [UNIQUE] INDEX indexname ON tablename(column1 [, column2]) • Examples: CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE); CREATE UNIQUE INDEX P_CODEX ON PRODUCT(P_CODE); CREATE UNIQUE INDEX EMP_TESTDEX ON TEST(EMP_NUM, TEST_CODE, TEST_DATE)

13. SQL Data Manipulation Commands Adding table rows Saving table changes Listing table rows Updating table rows Restoring table contents Deleting table rows Inserting table rows with a select subquery

14. Adding/Inserting Table Rows • INSERT • Insert data into table, one row at a time. • INSERT Syntax INSERT INTO tablename VALUES (value1, value2, … value n) • Example: INSERT INTO VENDOR VALUES (21225, ‘Bryson, Inc.’,’Smithson’,’615’,’223-3234’,’TN’,’Y’ ); INSERT INTO PRODUCT VALUES (‘BRT-345’, ‘Titanium drill bit’, ’18-Oct-05’, 75, 10, 4.50, 0.06, NULL);

15. Saving Table Changes • Changes made to table contents are not physically saved on disk until • Database is closed • Program is closed • COMMIT command is used • Syntax COMMIT; • Will permanently save any changes made to any table in the database

16. Listing Table Rows • SELECT • Used to list contents of table • Syntax SELECT columnlistFROM tablename • Columnlist represents one or more attributes, separated by commas • Asterisk can be used as wildcard character to list all attributes • Example: SELECT * FROM PRODUCT; SELECT P_CODE, P_DESCRIPT FROM PRODUCT;

17. Updating Table Rows • UPDATE • Modify data in a table • Syntax UPDATE tablenameSET columnname = expression [, columname = expression][WHERE conditionlist]; • If more than one attribute is to be updated in the row, separate corrections with commas • Example: UPDATE PRODUCT SET P_INDATE = ’18-JAN-2004’ , P_PRICE=16.99 WHERE P_CODE = ’13-Q2/P2’;

18. Restoring Table Contents • ROLLBACK • Used to restore the database to its previous condition • Only applicable if COMMIT command has not been used to permanently store the changes in the database • Syntax ROLLBACK; • COMMIT and ROLLBACK only work with data manipulation commands that are used to add, modify, or delete table rows

19. Deleting Table Rows • DELETE • Deletes a table row • Syntax DELETE FROM tablename[WHERE conditionlist ]; • Example: DELETE FROM PRODUCT WHERE P_CODE = ‘BRT-345’; • Note: Deletion not only limited to a primary key match.

20. Inserting Table Rows with a Select Subquery • INSERT • Inserts multiple rows from another table (source) • Uses SELECT subquery • Subquery is a query that is embedded (or nested query) inside another query • Executed first • Syntax INSERT INTO tablename SELECT columnlist FROM tablename

21. Selecting Rows with Conditional Restrictions • Select partial table contents by placing restrictions on rows to be included in output • Add conditional restrictions to the SELECT statement, using WHERE clause • Syntax SELECT columnlistFROM tablelist[ WHERE conditionlist ] ; • Example: SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 ;

22. Comparison Operators

23. Selected PRODUCT Table Attributes for VENDOR Codes Other than 21344 SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344 ; Selected PRODUCT Table Attributes with a P_PRICE Restriction SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_PRICE <= 10 ; Selected PRODUCT Table Attributes: Date Restriction SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= ’20-Jan-2004’ ;

24. SELECT Statement with a Computed Column and an Alias SELECT Statement with a Computed Column SELECT P_DESCRIPT, P_ONHAND, P_PRICE, P_ONHAND*P_PRICE FROM PRODUCT ; SELECT P_DESCRIPT, P_ONHAND, P_PRICE, P_ONHAND*P_PRICE AS TOTVALUE FROM PRODUCT ;

25. Arithmetic Operators: The Rule of Precedence • Perform operations within parentheses • Perform power operations • Perform multiplications and divisions • Perform additions and subtractions

26. Selected PRODUCT Table Attributes: The Logical OR SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288; Selected PRODUCT Table Attributes: The Logical AND SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE < 50 AND P_INDATE > ’15-jan-2004’ ; Selected PRODUCT Table Attributes: The Logical AND and OR SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE (P_PRICE <50 AND P_INDATE > ’15-jan-2004’) OR V_CODE = 24288 ;

27. Special Operators • BETWEEN • Used to check whether attribute value is within a range SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN 50.00 AND 100.00; • Alternatively, you can use SELECT * FROM PRODUCT WHERE P_PRICE >= 50.00 AND P_PRICE <= 100.00; • IS NULL • Used to check whether attribute value is null SELECT * FROM PRODUCT WHERE V_CODE IS NULL;

28. Special Operators • LIKE • Used to check whether attribute value matches a given string pattern (case sensitive) SELECT * FROM VENDOR WHERE V_CONTACT LIKE ‘Smith%’ ; SELECT * FROM VENDOR WHERE V_CONTACT LIKE ‘Johns_n’ ; • IN • Used to check whether attribute value matches any value within a value list SELECT * FROM PRODUCT WHERE V_CODE IN (21344, 24288) ; same as SELECT * FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288); • To list only vendor (V_CODE and V_NAME) that provide products. SELECT V_CODE, V_NAME FROM VENDOR WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT) ;

29. Special Operators • EXISTS • Used to check if a subquery returns any rows • List only vendors for products to order SELECT * FROM VENDOR WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_ONHAND <= P_MIN);

30. Advanced Data Definition Commands • All changes in the table structure are made by using the ALTER command • Followed by a keyword that produces specific change • Three options of keywords are available • ADD • MODIFY • DROP

31. Changing a Column’s Data Type • ALTER can be used to change data type (for example from integer to char) ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5) ); Changing a Column’s Data Characteristics • Use ALTER to change data characteristics (for example from Decimal (8,2) to Decimal (9,2) ALTER TABLE PRODUCT MODIFY (P_PRICE DECIMAL (9,2) );

32. Adding or Dropping a Column • Use ALTER to add a column • Do not include the NOT NULL clause for new column ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1) ); • Use ALTER to drop a column • Some RDBMSs impose restrictions on the deletion of an attribute ALTER TABLE VENDOR DROP COLUMN V_ORDER;

33. Advanced Data Updates • To make data entries in an existing row’s column. • It only updates data in existing rows. • Use UPDATE command UPDATE PRODUCT SET P_SALECODE = ‘2’ WHERE P_CODE = ‘1546-QQ2’ ; UPDATE PRODUCT SET P_SALECODE = ‘1’ WHERE P_CODE IN ( ‘2232/QWE’ , ‘2232/QTY’ ) ;

34. Copying Parts of Tables • SQL permits copying contents of selected table columns so that the data need not be reentered manually into newly created table(s) • First create the PART table structure Use command syntax CREATE TABLE • Next add rows to new PART table using PRODUCT table rows INSERT INTO target_tablename (target_columnlist) SELECT source_columnlist FROM source_tablename; • Adding Primary and Foreign Key ALTER TABLE PART ADD PRIMARY KEY (PART_CODE); • Delete table from database DROP TABLE PART;

35. ADVANCED SELECT QUERIES • SQL provides useful functions • Count • Find minimum and maximum values • Calculate averages • SQL allows the user to limit queries to only those entries having no duplicates or entries whose duplicates may be grouped

36. Ordering A Listing (ORDER BY) SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE; A Query Based on Multiple Restrictions SELECT P_DESCRIPT, V_CODE, P_INDATE, P_PRICE FROM PRODUCT WHERE P_INDATE <’21-Jan-2004’ AND P_PRICE <= 50 ORDER BY V_CODE, P_PRICE DESC; Listing Unique Value SELECT DISTINCT V_CODE FROM PRODUCT; (To show how many different vendors in product table.)

37. Aggregate Functions (Refer Page 256 – 262)

38. VIRTUAL TABLES: CREATING A VIEW • View is a virtual table based on a SELECT query • Can contain columns, computed columns, aliases, and aggregate functions from one or more tables • Base tables are tables on which the view is based • Create a view by using the CREATE VIEW command CREATE VIEW viewname AS SELECT query • Example on page 263

39. JOINING DATABASE TABLES(Pg. 264 – 268) • Ability to combine (join) tables on common attributes is most important distinction between a relational database and other databases • Join is performed when data are retrieved from more than one table at a time • Join is generally composed of an equality comparison between the foreign key and the primary key of related tables

40. The Results of a Join SELECT PRODUCT.P_DESCRIPTION, 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; An Ordered and Limited Listing After a JOIN SELECT PRODUCT.P_DESCRIPTION, 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 AND P_INDATE > ’15-Jan-2004’; Recursive Joins SELECT E.EMP_NUM, E.EMP_LNAME, E.EMP_MGR, M.EMP_LNAME FROM EMP E, EMP M WHERE E.EMP_MGR = M.EMP_NUM ORDER BY E.EMP_MGR;

41. The Left Outer Join Results Right outer join and left outer join used to select rows that have no matching values in other related table SELECT P_CODE, V_CODE, V_NAME FROM VENDOR LEFT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE; The Right Outer Join Results SELECT P_CODE, V_CODE, V_NAME FROM VENDOR RIGHT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;

42. Summary • SQL commands can be divided into two overall categories: • Data definition language commands • Data manipulation language commands • Basic data definition commands allow you to create tables, indexes, and views • Many SQL constraints can be used with columns • Aggregate functions • Special functions that perform arithmetic computations over a set of rows

43. Summary (continued) • ORDER BY clause • Used to sort output of a SELECT statement • Can sort by one or more columns and use either an ascending or descending order • Join output of multiple tables with SELECT statement • Natural join uses join condition to match only rows with equal values in specified columns • Right outer join and left outer join used to select rows that have no matching values in other related table