SQL queries involving ' for all'

Question

I could not get a hint on how to write SQL queries for A and B for the following schema.

Programme (Pid:int, Department:string...)
Employee (Eid:int, Department:string..)
Participation (Pid:int, Eid:int, ..)

A. Names of programmes participated by all employees

B. Names of employees participating in all his department's programmes.

Any guidelines would be helpful.

[See this article](http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) — Martin Smith, Oct 18 '11 at 08:38
And this recent question on optimizing such queries (in Postgres and MySQL). You'll find various ways to do this: http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation/ — ypercubeᵀᴹ, Oct 18 '11 at 09:07

onedaywhen · Answer 1 · 2011-10-19T09:35:51.183

3

The relational operator you require is division, popularly known as "the supplier who supplies all parts".

Things to consider are whether exact division or division with remainder and how to handle an empty divisor.

UPDATE: just to clarify, SQL lacks an explicit division operator or keyword**. However, relational division can be achieved in SQL using other operators. I'll refrain from posting a working example because of the 'homework' tag. But the one I usually use is similar to the "Division with Set Operators" example at this link.

Note that @Dylan Smith's is what is commonly known as "Celko's division" and @tobyodavies's answer uses a variation on what is commonly known as "Date's division" (Date wouldn't use an outer join but instead a second NOT EXISTS). But maybe they really did reinvent these well-established approaches themselves, who knows? ;)

** the same applies to many other relational operators e.g. SQL has no semi-difference operator but can be performed using other SQL operators e.g. NOT IN, NOT EXISTS, EXCEPT, etc.

edited Oct 19 '11 at 09:35

answered Oct 18 '11 at 08:57

onedaywhen

55,269
12
100
138

Unfortunately SQL does not have division... – tobyodavies Oct 19 '11 at 00:14
@tobyodavies: SQL lacks a division operator or keyword but division can be achieved using other operators. I'll refrain from posting a working example because of the 'homework' tag. But the one I usually use is similar to the "Division with Set Operators" example at [this link](http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/). p.s. the same applies to many other relational operators e.g. SQL has no semi-difference operator but can be performed using other SQL operators e.g. `NOT IN`, `NOT EXISTS`, `EXCEPT`, outer join with null filter, etc. – onedaywhen Oct 19 '11 at 08:02
@tobyodavies: your answer is also a variation on what is commonly called "Date's division", except Date wouldn't use an outer join but a second `NOT EXISTS`. – onedaywhen Oct 19 '11 at 08:05
if that were in your answer it would be an answer to this question... – tobyodavies Oct 19 '11 at 09:09

Dylan Smith · Accepted Answer · 2011-10-18T16:33:22.657

1

Haven't tried these, but this is what I would be thinking:

SELECT pg.Name 
FROM Participation AS p INNER JOIN Programme AS pg ON p.Pid = pg.Pid
GROUP BY p.Pid 
HAVING COUNT(*) = (SELECT COUNT(*) FROM Employeee)



SELECT e.Name 
FROM Participation AS p INNER JOIN Employee AS e ON p.Eid = e.Eid
                        INNER JOIN Programme AS pg ON pg.Pid = p.Pid
WHERE pg.Department = e.Department
GROUP BY p.Eid, e.Department, e.Name
HAVING COUNT(*) = (SELECT COUNT(*) 
                   FROM Programme AS pg2 
                   WHERE pg2.Department = e.Department)

edited Oct 18 '11 at 16:33

answered Oct 18 '11 at 07:32

Dylan Smith

22,069
2
47
62

Doesn't the group by attributes be the the select for the second query? – Nemo Oct 18 '11 at 07:48
Good catch, just added name to the group by, shouldn't affect the results – Dylan Smith Oct 18 '11 at 16:33

tobyodavies · Answer 3 · 2011-10-19T00:24:32.313

Use WHERE NOT EXISTS and Outer Joins

For all universal Programmes:

SELECT * FROM Programme
WHERE NOT EXISTS (SELECT * FROM 
                  (Participation NATURAL JOIN Programme) LEFT JOIN Employee
                      USING (Eid,Department) 
                  WHERE Employee.Eid IS NULL)

This should be fairly easy to explain - selects all programs where the is no employee not participating

For all enthusiatic employees:

SELECT * FROM Employee
WHERE NOT EXISTS (SELECT * FROM 
                  Employee LEFT JOIN Participation 
                      USING (Eid,department) 
                  WHERE Participation.Eid IS NULL)

Again - selects all employyes where there are no programs in the same department in which that employee is not participating.

This may look familiar if you are familiar with formal logic at all - universal quantification is generally defined in terms of negated existential qualification

SQL queries involving ' for all'

3 Answers3

Linked