- Consider the following relational schema. - Write SQL statements for the follo
ID: 3705774 • Letter: #
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.Explanation / Answer
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);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.