### Summary/Example of the "canonical" SQL queries

• Canonical queries

• I can discern the following 5 different 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 no (zero) occurences of a certain set Find tuples that has a certain property for only 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 Find names of projects that are worked on by at least 2 employee in the 'Research' department Find names of projects that are worked on by all employees in the 'Research' department Find names of projects that are worked on by zero employees in the 'Research' department (I.e., Find names of projects that are not worked on by any employees in the 'Research' department) Find names of projects that are worked on only by employees in the 'Research' department

• Query 1

• Query:

 Find names of projects worked on by an (some) employee in the 'Research' department

• 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

• 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 Need 2 or more 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

 ``` select pname from project P where |{ 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: use a superset/subset relationship

• In this case, the set of employees that work on a certain project (say p1) must contain the set of employees of the Research department

• Example:

• Suppose: set of employees that work on project p1 = { e1, e2, e3 }
• Suppose: set of employee in the Research department = = { e1, e2 }

• Then: project p1 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.essn = p.pnumber ) ) ```

• Query 4

• Query:

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

• Solution:

 ``` Logic of the solution: select pname from project p where { employee in 'Research' dept that work on P } = ∅ (I.e.: no employee in 'Research" can work on project P) More specifically: select pname from project p where { employee: (1) is in 'Research' dept and (2) works on P } = ∅ Re-write the and condition: select pname from project p where { employee in 'Research' dept } ∩ { employee working on project p.pnumber } = ∅ Replace the "= ∅" condition with NOT EXISTS: select pname from project p where NOT EXISTS { employee working on project p.pnumber } INTERSECT { employee in 'Research' dept } Write the set intersect in SQL (see: click here): 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 in ( select ssn from department, employee where dno = dnumber and dname = 'Research' ) ) ```

• Alternate solution:

• Recall the query:

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

Observe that:

• If a project is worked on by an employee in the Research department:

 We must exclude the project from the answer !!!

• 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

 Personally, I prefer this straight forward reasoning solution.....

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

• Query 5

• 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 p1 = { e1, e2 }

• Then: project p1 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 ) ) ```