---

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) )