I need help with this Database Assignment. Thanks This is the link for the datab
ID: 3729757 • Letter: I
Question
I need help with this Database Assignment. Thanks
This is the link for the database
https://www.dropbox.com/sh/5qfsk2j6vz0ehta/AAAV72JZMw2ZMa6-a30VXFzsa?dl=0
(Q Answer the following questions a) Explain the following query in English (based on the company database) SELECT FNAME, LNAME, SSN FROM EMPLOYEE WHERE NOT EXISTS (SELECT FROM WORKS_ONB WHERE (B.PNO IN (SELECT PNUMBER FROM PROJECT WHERE DNUM 5) AND NOT EXISTS (SELECT* FROM WORKS ON C WHERE C.ESSN = SSN AND C.PNO-B.PNO)) (b) Modify the query to list employees who work on any of the projects controlled by department number 5.Explanation / Answer
a) Find an employee such that there does not exist a project controlled by department 5 that the employee does not work on.
b) SELECT FNAME, LNAME, SSN
FROM EMPLOYEE
WHERE EXISTS (
SELECT *
FROM WORKS_ON B
WHERE (B.PNO IN (
SELECT PNUMBER
FROM PROJECT
WHERE DNUM = 5)
AND
EXISTS (SELECT *
FROM WORKS_ON C
WHERE C.ESSN = SSN AND C.PNO = B.PNO
)
)
);
Note: UNION only works in MySQL. INTERSECTION and EXCEPT Not Work in MySQL. This is as per theory subject. Alternatives for INTESECTION is EXISTS and for EXCEPT is NOT EXISTS.
C)
SELECT PNAME
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM = DNUMBER AND MGRSSN =SSN AND LNAME='Wong'
INSERSECTION (SELECT PNAME
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith');
SELECT PNAME
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM = DNUMBER AND MGRSSN =SSN AND LNAME='Wong'
EXCEPT(
SELECT PNAME
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith');
SELECT PNAME
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM = DNUMBER AND MGRSSN =SSN AND LNAME='Wong'
UNION (SELECT PNAME
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith');