---

"Not any" and "only" type of queries

---

• 2 special type of queries
  • There are 2 "special" types of queries that need a bit of logic to solve.

    I call them "not any" and "only" type of queries:

    Find things that do not have any properties from a given set (of properties) Find things that only have any properties from a given set (of properties)

  ---

  ---

  ---

  ---

• Find fname and lname of all employees that do not work on any projects controlled by the "Research" department.

  Solution:

  Fact: If an employee works on a project controlled by the Research department, then this employee must not be selected !!! First, find this set of disqualified employees: Disqualified = {employees (SSN) who work on a project controlled by the "Research" department} Solution set (employees) = All employees − Disqualified set       (i.e.: set difference !!)

  Solution procedure:

  Relations needed: Employee (fname, lname, ssn) Works_on ((essn, pno) --- projects worked on by employee) Project (which department controls the project)            Department (dname) Query plan: Find projects controlled by the Research department Find employees who work on a project controlled by the Research department Remove these employees and obtain the qualifying employees Find the fname and lname of the qualifying employees

  Solution:

  ---

  ---

  ---

  ---

• Find fname and lname of all employees that only work on projects controlled by the "Research" department.

  Hint:

  Fact: If an employee works on a project that is not controlled by the Research department, then this employee does not only work on projects controlled by the Research department First, find this set of disqualified employees: Disqualified = {employees (SSN) who work on a project that is not controlled by the "Research" department} These employees do not only work on Research projects !!! Solution set (employees) = All employees − Disqualified set       (i.e.: set difference !!)

  Solution procedure:

  Relations needed: Employee (fname, lname, ssn) Works_on ((essn, pno) --- projects worked on by employee) Project (which department controls the project)            Department (dname) Query plan: Find projects that ate not controlled by the Research department Find employees who work on a project that is not controlled by the Research department Remove these employees (the remaining employees will only work on projects controlled by the Research department) Find the fname and lname of the qualifying employees

  Solution: