2. (30 points) This problem is based on Elmasri's company database. For each of the following, specify the query

using SQL : this is a paper and pencil problem, you do not need to implement these queries, but if

you want to implement them, you are free to do so.

Feel free to create views or to save queries (to help answer a more complex query) or to create other tables (eg:

using the INTO command discussed in class) if that helps you. If you are using views or subqueries, show the

SQL to create the views and subqueries.

(a) What are the names of the projects on which the combined hours (i.e. the total of all the hours of all the

employees who work on that project) are more than 50.

(b) Specify the following view in SQL: A view that has the department name and the number of locations

for that department for each department. Please note that here you have to use the \CREATE VIEW"

statement.

(c) List the names of the projects such that every employee works on that project.

(d) How many employees from the Administration department have at least three dependents.

(e) What is the name of the department whose employees have the lowest average salary.

EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO 12345678909-JAN-55 08-DEC-45 19-JUL-58 20-JUN-31 15-SEP-52 8 731 Fondren, Houston, TX M 638 Voss, Houston, TX 3321 Castle, Spring, TX 333445555 Zelaya 999887777 Wallace 987654321 Narayan666884444 333445555 888665555 987654321 888665555 333445555 333445555 987654321 40000 25000 43000 on Jennifer 975 Fire Oak, Humble, TX M 5631 Rice, Houston, TX 980 Dallas, Houston, TX 450 Stone, Houston, TX English 453453453 31-JUL-62 Jabbar 987987987 29-MAR-59 25000 25000 888665555 10-NOV-27 DEPT LOCATIONS Stafford Bellaire DEPARTMENT DNUMBERMGRSSN DNAME Research Administration Headquarters 333445555 987654321 888665555 MGRSTARTDATE 22-MAY-78 01-JAN-85 19-JUN-71 and Houstorn WORKS_ON ESSN PNQ HOURS 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 20 999887777 30 99988777710 987987987 987987987 30 987654321 987654321 888665555 2 3 PROJECT PNAME PNUMBER PLOCATION DNUM ProductX ProductY ProductZ Computerization Reorganization Newbenefits arland Houston Houston DEPENDENT ESSN DEPENDENT NAMESEX BDATE 05-APR-76 25-OCT-73 03-MAY-48 29-FEB-32 01-JAN-78 31-DEC-78 05-MAY-57 RELATIONSHIP 333445555 333445555 333445555 987654321 123456789 123456789 123456789 DAUGHTER Theodore Jo Abner SPOUSE SPOUSE SON DAUGHTER SPOUSE