-1

enter image description here

Project(pid,name,startYear)
Developer(did,name,hireYear)
WorksOn(pid,did,year)

Originally when I examined this query, I thought the answer was D. However, I would like to walk through my logic for as to why D is correct, and I would appreciate any correction/elaboration that could confirm if I am valid in my justification for D.

The inner most sub query has combinations of all projects, along with specific developers that worked on them in 2015.

The sub query outside of that one is the inverse of that: there exists no projects with developers associated with them in 2015.

The outermost query selects developers that worked on all because the where clause has NOT EXISTS of no projects in 2015. Or you can think that the opposite of no project is all the projects.

Trebond
  • 57
  • 1
  • 1
  • 7

1 Answers1

1

Considering the query in words, it is:

  1. Find a developer
    SELECT X.did FROM Developer X
  2. Where there is no project
    WHERE NOT EXISTS (SELECT * FROM Projects Z ...
  3. Which they did not work on in 2015
    WHERE NOT EXISTS (SELECT * FROM WorksOn Y WHERE X.did = Y.did AND Y.pid = Z.pid AND Y.year = 2015)

Hence the result is developers who worked on all projects in 2015.

Nick
  • 138,499
  • 22
  • 57
  • 95
  • Thank you for the clear and concise explanation! Just a general question, do you typically look inside to outside when evaluating subqueries, or does it depend on the scenario? – Trebond May 02 '19 at 05:22
  • @Trebond it really does depend on the scenario. Personally I try to avoid subqueries as they do nothing for readability and are generally less efficient that a similarly constructed `JOIN`. For example, this query can be written more simply in the style of the answer to [this question](https://stackoverflow.com/questions/27722404/why-are-double-nested-not-exists-statements-unavoidable-in-sql) – Nick May 02 '19 at 05:53