Nested Queries in SQL, continued:
In this episode, we do a final, complicated case:
Implementing DIVIDE in SQL
Here’s an example problem:
Find all the entities of type A that are associated with all memebrs of as set of type B.
To apply this, lets use our employee database schema and rephrase the problem to be:
EX: Find the first names of all employees who work in ALL projects in Brooklyn.
We imagine two sets... the divisor and the dividend, which here will be B and A respectively.
A: The set of all Employees
B: The set of all Brooklyn projects
What we want to say is “Select any employee who works on all the projects in Brooklyn” but this isn’t
necessarily possible in SQL. There’s no way to really say “works on all”. Yet, it is easy to find all the projects
that A works on. The way you do it in SQL, you compare the projects A works on with those in B, like this:
SELECT A WHERE NOT EXISTS ( B - A.projects )
Here, A.projects represents all the projects A works on. This means that while you can’t just see if all projects in
A are all in B, you CAN subtract the projects from B and see if any are left over.
This may still be confusing, so it might be better to take a simpler database, illustrate it there, and then take it
further to the employee table. Let’s start with a table about a VIP who wants to select his assets according to a
specific set of parameters:
( please turn the page in your PDF )
