13/03/2012 1 CSI 2132 Tutorial 6 The Structured Query Language (SQL) The Structured Query Language • De facto standard used to interact with relational DB management systems • Two major branches DDL (Data Definition Language) • Contains statements that define the structure of the database (create, alter or delete tables, relationships, constraints, etc) DML (Data Manipulation Language) • Retrieve and modify data 213/03/2012 2 “Basic SQL” Consider the schema for the AIRLINE database in Fig. 3.8 What are the referential integrity constraints that should hold on the schema? Write appropriate SQL DDL statements to define the database. 3 413/03/2012 3 5 613/03/2012 4 “Basic SQL” 7 “Basic SQL” 813/03/2012 5 “Basic SQL” 9 “Basic SQL” 1013/03/2012 6 “Basic SQL” 11 “Basic SQL” 1213/03/2012 7 “Basic SQL” 13 “Basic SQL” 1413/03/2012 8 “Basic SQL” 15 “Basic SQL” 16 We use the following notation to describe the foreign key constraints. FLIGHT_LEG.(FLIGHT_NUMBER) --> FLIGHT.(NUMBER) SEAT_RESERVATION.(FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE) --> LEG_INSTANCE.(FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE)13/03/2012 9 “Basic SQL” Consider the schema for the LIBRARY database in Fig. 4.6 Choose the appropriate action (reject, cascade, set to null, set to default) for each referential integrity constraint, both for a deletion of a referenced tuple and for the update of a primary key attribute value in a referenced tuple. Justify your choices. 17 1813/03/2012 10 “Basic SQL” BOOK_AUTHORS.(BookId) --> BOOK.(BookId) ON DELETE CASCADE ON UPDATE CASCADE Automatically propagate the deletion or change of a BOOK to the referencing BOOK_AUTHORS. 19 “Basic SQL” BOOK.(PublisherName) --> PUBLISHER.(Name) ON DELETE REJECT ON UPDATE CASCADE Do not delete a PUBLISHER tuple which has linked BOOK tuples. Update the PUBLISHER’s name on all BOOK tuples which refer to it. 2013/03/2012 11 “Basic SQL” BOOK_LOANS.(BookID) --> BOOK.(BookID) ON DELETE CASCADE ON UPDATE CASCADE If a BOOK record is deleted, then delete all its associated BOOK_LOAN records. Idem with updates. REJECT on DELETE also possible! 21 “Basic SQL” BOOK_COPIES.(BookID) --> BOOK.(BookID) ON DELETE CASCADE ON UPDATE CASCADE If a BOOK record is deleted, then delete all its associated BOOK_COPIES tuples. Do likewise with updates. 2213/03/2012 12 “Basic SQL” BOOK_LOANS.(CardNo) --> BORROWER.(CardNo) ON DELETE CASCADE ON UPDATE CASCADE If a BORROWER record is deleted, then delete all its associated BOOK_LOANS tuples. Do likewise with updates. REJECT on DELETE also possible! 23 “Basic SQL” BOOK_COPIES.(BranchID) --> LIBRARY_BRANCH.(BranchID) ON DELETE CASCADE ON UPDATE CASCADE If a LIBRARY_BRANCH record is deleted, then delete all its linked BOOK_COPIES tuples. Do likewise with updates. REJECT on DELETE also possible! 2413/03/2012 13 “Basic SQL” BOOK_LOANS.(BranchID) --> LIBRARY_BRANCH.(BranchID) ON DELETE CASCADE ON UPDATE CASCADE If a LIBRARY_BRANCH record is deleted, then delete all its linked BOOK_LOANS tuples. Do likewise with updates. REJECT on DELETE also possible! 25 “Basic SQL” Consider the EMPLOYEE table’s constraint EMPSUPERFK as in Fig 4.2 2613/03/2012 14 Chapter 4: “Basic SQL” 27 “Basic SQL” Exercise If the constraint is changed to read as follows: 2813/03/2012 15 “Basic SQL” What happens when the following command is run on the COMPANY database state shown in Fig. 3.6? 29 Chapter 4: “Basic SQL” 3013/03/2012 16 “Basic SQL” Answer: The table gets emptied. Triggers a deletion of all subordinate records in James Borg’s supervision hierarchy. 31 “Basic SQL” Is it better to CASCADE or SET NULL in case of EMPSUPERFK constraint ON DELETE? 3213/03/2012 17 “Basic SQL” Answer: SET NULL is preferred, since an EMPLOYEE is not fired (deleted) when his/her supervisor is deleted. Instead, the SUPERSSN field should be SET NULL so a new supervisor could be assigned later on. 33 “More SQL” Specify the following additional SQL queries on the COMPANY database of Fig. 3.5 3413/03/2012 18 Chapter 5: “More SQL” 35 “More SQL” a) For each department whose average employee salary is over 30K, retrieve the department name and the number of employees working for it. 3613/03/2012 19 “More SQL” b) Suppose we want the number of male employees in each department rather than all employees. Can we specify this in SQL? Why or why not? 37 “More SQL” Yes, via a nested query 3813/03/2012 20 “More SQL” Specify the following SQL queries on the UNIVERSITY database schema of Fig. 1.2 39 4013/03/2012 21 41 “More SQL” a) Retrieve the names and major departments of all straight-A students (i.e. those who got ‘A’ in all their courses) 4213/03/2012 22 “More SQL” a) Retrieve the names and major departments of all straight-A students (i.e. those who got ‘A’ in all their courses) 43 “More SQL” b) Retrieve the names and major departments of all students who do not have any grade of A in any of their courses. 4413/03/2012 23 “More SQL” Another way 45 “More SQL” In SQL, specify the following queries on the COMPANY database in Fig. 3.5 using the concept of nested queries a) Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. 4613/03/2012 24 Chapter 5: “More SQL” 47 “More SQL” 48 a) Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.13/03/2012 25 “More SQL” b) Retrieve the names of all employees whose supervisor’s supervisor has ‘888665555’ for SSN. 49 “More SQL” b) Retrieve the names of all employees whose supervisor’s supervisor has ‘888665555’ for SSN. 5013/03/2012 26 “More SQL” c) Retrieve the names of employees who make at least 10K more than the employee who is paid the least in the company. 51 “More SQL” c) Retrieve the names of employees who make at least 10K more than the employee who is paid the least in the company. 5213/03/2012 27 CSI 2132 Tutorial 6 The Structured Query Language
View Full Document
Unlocking...