ITPEC FE Morning April 2020 Question 29

Topic: Databases

There are three tables, EMPLOYEE, PROJECT, and WORK_PROJ for recording employees, projects, and working information of employees on projects respectively. When the SQL statement shown below is executed for these tables, which of the following is generated as the output?

EMPLOYEE

EIDENAME
1Rahbar
2Karthik
3Abir

PROJECT

PIDPNAME
1Construction
2Land Purchase

WORK_PROJ

EIDPIDHOURS
1120
1210
2140
3120
3210
[SQL Statement]
SELECT ENAME FROM EMPLOYEE
WHERE NOT EXISTS
((SELECT PID FROM PROJECT)
EXCEPT
(SELECT PID FROM WORK_PROJ
WHERE WORK_PROJ.EID = EMPLOYEE.EID))

Explanation

Rahbar and Abir — the query lists the employees who work on every project.

For each employee, the inner query takes all project ids and removes (EXCEPT) the ones that employee works on. What remains are the projects the employee misses. NOT EXISTS is true only when nothing remains.

  • •Rahbar (EID 1) works on projects 1 and 2: 1, 2 minus 1, 2 leaves nothing, so Rahbar is listed
  • •Karthik (EID 2) works on project 1 only: project 2 remains, so Karthik is not listed
  • •Abir (EID 3) works on projects 1 and 2: nothing remains, so Abir is listed

Answer (d)

Why not others:

  • •(a) NULL — reads the query as finding no rows, but two employees cover both projects
  • •(b) Rahbar, Karthik and (c) Karthik, Abir — include Karthik, who never works on Land Purchase (PID 2)

Key rule: NOT EXISTS (all EXCEPT mine) expresses "for all" in SQL: the row qualifies when nothing in the full set is missing from its own set.

Databases · AI-written, may contain errors

Practice this exam

This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official FE past-paper collection or Report an issue.