2. (30 points) This problem is based on Elmasri\'s company database. For each of

Question

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.

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

Explanation / Answer

Please find the queries:

(a)

mysql> SELECT pname
    -> FROM project,works_on
    -> WHERE pnumber=pno
    -> GROUP BY pnumber
    -> HAVING SUM(hours) > 50;
+-----------------+
| pname           |
+-----------------+
| ProductX        |
| Computerization |
| Newbenefits     |
+-----------------+
3 rows in set (0.46 sec)

-----------------------------------------------------------------

(b)
CREATE VIEW department_locations AS
SELECT dname, COUNT(dl.dlocation) as Number_of_locations
FROM department d,dept_locations dl
WHERE d.dnumber=dl.dnumber
GROUP BY dname;

mysql> CREATE VIEW department_locations AS
    -> SELECT dname, COUNT(dl.dlocation) as Number_of_locations
    -> FROM department d,dept_locations dl
    -> WHERE d.dnumber=dl.dnumber
    -> GROUP BY dname;
Query OK, 0 rows affected (0.23 sec)

mysql> select * from department_locations ;
+----------------+---------------------+
| dname          | Number_of_locations |
+----------------+---------------------+
| Administration |                   1 |
| Headquarters   |                   1 |
| Research       |                   3 |
+----------------+---------------------+
3 rows in set (0.08 sec)

---------------------------------------------------------------------------

(c)

SELECT PNAME
FROM PROJECT
WHERE NOT EXISTS ( SELECT SSN
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM WORKS_ON
WHERE PNUMBER=PNO AND ESSN=SSN ) )

Empty

----------------------------------------------------------------------

(d)

SELECT fname,lname
FROM employee, department
WHERE ( SELECT COUNT(*) FROM dependent
WHERE ssn=essn ) >= 3 AND
dno = dnumber
AND dname='Administration'

Empty

If its Research, we get 2 employees having at-least three dependents.
-----------------------------------------------------------------------

(e)

SELECT dname, AVG(employee.salary)
FROM
department, employee
WHERE dno = dnumber
GROUP BY dname
ORDER BY 1 LIMIT 1;

+----------------+----------------------+
| dname          | AVG(employee.salary) |
+----------------+----------------------+
| Administration |           31000.0000 |
+----------------+----------------------+

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.