Summary/Example of the "canonical" SQL queries

Canonical queries

I have gather the different kinds of queries that we have discussed in SQL in this webpage.

I can discern the following 5 main kinds of "canonical" queries:

Find tuples that has a certain property
Find tuples that has (at least, at most or exactly) n occurences of a certain property
Find tuples that has a certain property for all occurences of a certain set
Find tuples that has a certain property for only occurences of a certain set.
Find tuples that has a certain property for no occurences of a certain set.

I will present an example of these 5 canonical queries using the employees in Research department as the set:

Find names of projects that are worked on by an employee in the 'Research' department
Similar query: click here
Find names of projects that are worked on by at least 2 employee in the 'Research' department
Similar query: click here
Find names of projects that are worked on by all employees in the 'Research' department
Similar query: click here
Find names of projects that are worked on only by employees in the 'Research' department
Similar query: click here
Find names of projects that are not worked on by any employees in the 'Research' department
Similar query: click here

Query 1

Query:

Solution:

select pname from project where pnumber in { pnumber of project w.o. by an employee in 'R' department }
select pname from project where pnumber in ( select pnum from works_on, employee, department where works_on.essn = employee.ssn and employee.dno = department.dnumber and dname = 'Research' )

Query 2

Query:

Find names of projects worked on by at least 2 employees in the 'Research' department
If we strip away the "at least 2" condition, the query becomes:
This query will helps us solve the original "at least 2" query...

Solution 1: using group by

Find projects worked on by employees in the Research department select pname, ssn, fname, lname, dname from works_on, project, employee, department where essn=ssn and pno = pnumber and dno = dnumber and dname = 'Research' order by pname
Add the at least 2 condition: >= 2 employees working on the project: select pname from works_on, project, employee, department where essn=ssn and pno = pnumber and dno = dnumber and dname = 'Research' group by pname having count(*) >= 2

Solution 2: using psuedo grouping technique in a correlated sub-query

select pname from project P where count({ employees in Research dept who work on P}) >= 2
select pname from project P where (select count(ssn) from employee E where E works in the Research dept and E works on project P) >= 2
select pname from project P where ( select count(ssn) from employee E where E.ssn IN ( select ssn from employee, department where dno = dnumber and dname = 'Research') and E.ssn IN ( select essn from works_on where pno = P.pnumber ) ) >= 2

Query 3

Query:

Find names of projects worked on by all employees in the 'Research' department

Solution Method: establish the relationship between 2 relevant sets of employees

The 2 relevant sets of employees are:
Desired relationship:

Example:

Suppose: set of employees that work on project p₁ = { e1, e2, e3 }
Suppose: set of employee in the Research department = { e1, e2 }
Then: project p₁ is worked on by all employees in the Research department

Solution in SQL:

select pname from project p where { ssn of employee working on project p.pnumber } CONTAINS { ssn of employee working in 'Research' dept }
select pname from project p where NOT EXISTS { ssn of employee working in 'Research' dept } - { ssn of employee working on project p.pnumber }
select pname from project p where NOT EXISTS (select * from employee where ssn in ( select ssn from department, employee where dname = 'Research' and dno = dnumber ) and ssn NOT in ( select essn from works_on where works_on.pno = p.pnumber ) )

Query 4

Query:

Find names of projects worked on only by employees in the 'Research' department

Solution technqiue: use a superset/subset relationship

In this case, a qualifying project must draw its employees only from the set employees in the 'Research' department:

Example:

Suppose: set of employee in the Research department = = { e1, e2, e3, e4 }
Suppose: set of employees that work on project p₁ = { e1, e2 }
Then: project p₁ is w.o. only by employees in the Research department

Solution:

select pname from project p where { ssn of employee working in 'Research' dept } CONTAINS { ssn of employee working on project p.pnumber }
select pname from project p where NOT EXISTS { ssn of employee working on project p.pnumber } - { ssn of employee working in 'Research' dept }
select pname from project p where NOT EXISTS (select * from employee where ssn in ( select essn from works_on where works_on.pno = p.pnumber ) and ssn NOT in ( select ssn from department, employee where dname = 'Research' and dno = dnumber ) )

Query 5

Query:

Find names of projects that are not worked on by any employee in the 'Research' department

Solution Method: use empty-set test (see: click here)

select pname from project P where no employee in Research department works on P
select pname from project P where {employees in Research department works on P} = ∅
select pname from project P where not exists (select * from employee E where E in Research department and E works on P)
select pname from project P where not exists (select * from employee E where E.dno = (select dnumber from department where dname='Research') and E.ssn IN (select essn from works_on where pno = P.pnumber)

Alternate solution:

Recall the query:
Observe that:

Alternate solution:

select pname from project P where P ∉ { project work on by a 'Research' emp }
Or: select pname from project where pnumber NOT IN (select pno from works_on, employee, department where essn = ssn and dno = dnumber and dname = 'Research')

Comment:

The first solution is Mathematically clearer
The alternate solution uses inversion logic and can be a bit confusing (i.e., "less" clear)