Question

- Consider the following relational schema. - Write SQL statements for the following queries employee fname CHARACTER VARYING (15) minit CHARACTER VARYING(1) InameCHARACTER VARYING(15) essn CHARACTERO) bdate DATE address CHARACTER VARYING(50) dependent works on CHARACTERO dependent name CHARACTER VARYING(15) essn CHARACTER(9) pno NUMERIC hours NUMERIC CHARACTER() DATE CHARACTER VARYING(B) bdate CHARACTER(1) dept locations elationship salary NUMERIC super ssn CHARACTERO) dno dno NUMERIC dlocation CHARACTER VARYING(15) NUMERIC department CHARACTER VARYING(25) NUMERIC CHARACTERO) project dno mgssn mgstartdate DATE pname CHARACTER VARYING(25) pno plocation CHARACTER VARYING (15) dno NUMERIC NUMERIC 1) Retrieve the first names of employees who had worked on both the 'ProductX project and the 'Producty project. 2) Retrieve the first names of employees who had worked on the ProductX project or the 'ProductY" project. 3) Retrieve the first names of employees who had worked on either the 'ProductX' project or the 'ProductY" project. 4) Retrieve the first names of employees who had worked on all projects 5) Retrieve the first names of employees who had never worked on a project.

Answer 1

1) SELECT employee.fname

FROM employee,project,works_on

WHERE employee.essn=works_on.essn AND works_on.pno=project.pno AND project.pname = 'ProductX' AND project.pname='ProductY';

2) SELECT employee.fname

FROM employee,project,works_on

WHERE employee.essn=works_on.essn AND works_on.pno=project.pno AND project.pname = 'ProductX' OR project.pname='ProductY';

3) (SELECT employee.fname

FROM employee,project,works_on

WHERE employee.essn=works_on.essn AND works_on.pno=project.pno AND project.pname = 'ProductX' OR project.pname='ProductY')

EXCEPT

(SELECT employee.fname

FROM employee,project,works_on

WHERE employee.essn=works_on.essn AND works_on.pno=project.pno AND project.pname = 'ProductX' AND project.pname='ProductY');

4) SELECT employee.fname

FROM employee

WHERE NOT EXISTS ( SELECT pno

FROM project

WHERE NOT EXISTS ( SELECT *

FROM works_on

WHERE works_on.pno=project.pno AND employee.essn=works_on.essn ) );

5) (SELECT employee.essn

FROM employee)

EXCEPT

(SELECT employee.essn

FROM employee,essn

WHERE employee.essn=works_on.essn);