Question

1. Specify the following queries in SQL on the COMPANY database. Show the result of each query.
   1. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project.
   2. List the names of all employees who have a dependent with the same first name as themselves.
   3. Find the names of all employees who are directly supervised by ‘Franklin Wong’.

2. Specify the following updates using the SQL update commands. Show the state of each relation after the update.

1. Insert <‘Robert’,‘F’,‘Scott’,‘943775543’,‘1972-06-21’,‘2365 Main St, Bellaire, TX’,M,58000,‘888665555’, 1> into EMPLOYEE.
2. Insert <‘453453453’,‘John’,‘M’,‘1990-12-12’,‘spouse’> into DEPENDENT.
3. Delete the WORKS_ON tuples with Essn = ‘333445555’.
4. Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to ‘123456789’and ‘2007-10-01’, respectively.
5. Modify the Hours attribute of the WORKS_ON tuple with Essn = ‘999887777’and Pno = 10 to ‘5.0’.

Answer 1

a)

b)

c)