The minimum requirement for active attendance: 4 tasks done
The purpose of this exercise session is simply to repeat some basic database issues and especially the SQL query language. For SQL, see the textbook, Oracle manual etc. The SQL exercises concern the database described in E&N, page 146. 1. Explain the meaning of the following SQL query. select x.LNAME, y.LNAME from EMPLOYEE x, EMPLOYEE y where x.SUPERSSN = y.SSN union select x.LNAME, z.LNAME from EMPLOYEE x, EMPLOYEE y, EMPLOYEE z where x.SUPERSSN = y.SSN and y.SUPERSSN = z.SSN. 2. The SUPERSSN attribute of an employee tuple in the Company database holds a null value if the employee in question has no supervisor or if a supervisor has not yet been assigned. Change the schema of the database in such a way that null values are no longer needed in this case. How will this change affect the queries issued against the database? 3. Two queries Q1 and Q2 are equivalent if their answers are always the same, that is, for each allowed state (instance) D of the database, the answer to Q1 on D is the same as the answer to Q2 on D. a) Are the following queries equivalent? select dp.DEPENDENT\_NAME from DEPENDENT dp where dp.SEX = 'F'. select dp.DEPENDENT\_NAME from DEPENDENT dp, EMPLOYEE e where dp.SEX = 'F' and e.SSN = dp.ESSN. b) Are the following queries equivalent? select d.DNAME from DEPARTMENT d. select d.DNAME from DEPARTMENT d, EMPLOYEE e where d.DNUMBER = e.DNO. 4. Write SQL queries for printing a) the names of employees working at the Administration department; b) the names and addresses of those working for projects of Administration department; c) employees with no children. 5. Write SQL queries for printing a) the employees that work on some projects; b) the employees that work on no project. 6. Write SQL queries for printing a) the employees that work on exactly two projects; b) the departments that have several locations. 7. Write an SQL query for printing the weekly labor cost of the projects. Suppose: salary is for one month (4 weeks), 1 week = 40 hours. 8. The system catalog (metadata) of an Oracle database contains tables ALL_CONSTRAINTS and ALL_CONS_COLUMNS that are used to represent the integrity constraints defined on the tables accessible to the user and the columns associated with those constraints. Examine the structure of the tables, that is, what columns there are. Also write an SQL query in order to find out what columns belong to the primary key of the table OPS$SIPPU.DEPENDENT. The constraint type of a primary key is 'P'.