CS457 Syllabus & Progress

### Formulating "only" queries in SQL

• The "Only" Query

• The subset condition condition can be used to solve a type of query that I call "ONLY query"

• Example:

 Find names of projects that is works on by only employees in the 'Research' department

• Steps towards the solution:

1. Formulate the solution using a subset condition

Consider 2 projects:

 project 1 and           project 2

Consider the set of employees that work on project 1 and project 2 in relation to the set of employees in the Research

The yellow circle are the employees in the Reseacrh department

The green circle are employees that work on project 1

 Project 1 is worked on by only employees in the Research department !!!

The reddish circle are employees that work on project 2

 Project 2 is worked on by employees other than the Research department !!!

Conclusion:

• A project P that is works on by only employees in the 'Research' department if and only if:

 ``` { employees working on project P } ⊆ { employees in the Research dept } ```

In Psuedo SQL code:

 ``` SELECT pname FROM project p WHERE "set of employees working on project p" ⊆ "set of employees in the 'Research' department" ```

2. Use the subset test technique to re-write the psuedo code into the following:

 ``` Re-written solution: SELECT pname FROM project p WHERE "set of employees working on project p" - "set of employees in the 'Research' department" = empty set Or: SELECT pname FROM project p WHERE NOT EXISTS ("set of employees working on project p" - "set of employees in the 'Research' department") ```

3. Apply the set difference technique to re-write the inner query:

 ``` SELECT pname FROM project p WHERE NOT EXISTS (SELECT ssn FROM employee /* Universe set of employee */ WHERE ssn IN "set of employees working on project p" and ssn NOT IN "set of employees in the 'Research' department" ) ```

4. Work out the 2 inner sets using 2 queries and we obtain the SQL query:

 ``` SELECT pname FROM project P WHERE NOT EXISTS (SELECT ssn FROM employee WHERE ssn IN (SELECT essn FROM works_on WHERE pno = P.pnumber) and ssn NOT IN (SELECT ssn FROM employee, department WHERE dno = dnumber AND dname='Research') ) ```

• Only and All queries....

• Fact:

 An "all" query uses a superset condition An "only" query uses a subset condition

So these 2 types of queries is very similar !!!

• Another example of "only" query

• Query:

 Find name of departments that only have employees who have one or more dependents

• Steps towards the solution:

1. Formulate the solution using a subset condition

 ``` We are comparing these 2 set of employees: set1 = { employees working in department "dnumber" } set2 = { employees with one or more dependents } ===> All employees in department "dnumber" are members of set2 SELECT dname FROM department d WHERE "set of employees working in d.dnumber" ⊆ "set of employees with one or more dependents" ```

2. Use the subset technique to re-write the psuedo code into the following form:

 ``` Re-written solution: SELECT dname FROM department d WHERE "set of employees working in d.dnumber" - "set of employees with one or more dependents" = empty set Or: SELECT dname FROM department d WHERE NOT EXISTS ( "set of employees working in d.dnumber" - "set of employees with one or more dependents" ) ```

3. Apply the set difference technique to re-write the inner query:

 ``` SELECT dname FROM department d WHERE NOT EXISTS (SELECT ssn FROM employee /* Universe set of SSN */ WHERE ssn IN "set of employees working in d.dnumber" and ssn NOT IN "set of employees with one or more dependents" ) ```

4. Work out the 2 inner sets using 2 queries and we obtain the SQL query:

 ``` SELECT dname FROM department d WHERE NOT EXISTS (SELECT ssn FROM employee WHERE ssn IN (SELECT ssn FROM employee WHERE dno = d.dnumber) and ssn NOT IN (SELECT essn FROM dependent) ) ```