CS457 Syllabus & Progress

### Techniques to find the intersection and difference of 2 sets

• Computing the Intersection

• Fact:

 Some Dayabase Systems (e.g. MySQL version 5.5.24) do not support the INTERSECT operation.

• How to compute the intersection of 2 sets:

• You can compute the intersection of two sets as follows:

 ``` x IN ( set1 INTERSECT set2 ) is the same as: (x IN set1) AND (x IN set2 ) ```

• Example:

 Find fname and lname of employees who work on some project controlled by the "Research" department and also on some project controlled by the "Administration" department

• Solution formulated using intersection:

 ``` SELECT fname, lname FROM employee WHERE ssn ∈ { ssn of employee working on a proj controlled by 'Research' dept } INTERSECT { ssn of employee working on a proj controlled by 'Administration' dept } ```

• Solution without uisng INTERSECT:

 ``` SELECT fname, lname FROM employee WHERE ssn ∈ { ssn of employee working on a proj controlled by 'Research' dept } AND ssn ∈ { ssn of employee working on a proj controlled by 'Administration' dept } Written on using SQL syntax: SELECT fname, lname FROM employee WHERE ssn IN (SELECT essn FROM works_on, project, department WHERE pno = pnumber AND dnum = dnumber AND dname = 'Research') AND ssn IN (SELECT essn FROM works_on, project, department WHERE pno = pnumber AND dnum = dnumber AND dname = 'Administration'); ```

• Computing the Difference of 2 sets

• Fact:

 SQL does not provide a set diffrence operation

• How to compute the difference between 2 sets:

• You can use the following technique to compute the (set-) difference between two sets:

 ``` x IN ( set1 - set2 ) is the same as: (x IN set1) AND (x NOT IN set2 ) ```

• Example:

 Find SSN of employees in the "Research" department who has no dependents

Solution using set difference:

 ``` SELECT ssn FROM employee E /* We test 1 employee at a time, call him "E" */ WHERE E is in the `Research' department AND E has no dependent Written using set difference: SELECT ssn FROM employee E /* We test 1 employee at a time, call him "E" */ WHERE E ∈ { employees of the `Research' department } - { employee WITH dependent } ```

• SQL solution:

 ``` We has this partial solution: SELECT ssn FROM employee E /* We test 1 employee at a time, call him "E" */ WHERE E ∈ { employees of the `Research' department } AND E ∉ { employee WITH dependent } Replace ∈ and ∉ with IN and NOT IN: SELECT ssn FROM employee WHERE ssn IN {ssn of employees in 'Research' department} AND ssn NOT IN {ssn of emp's with dependent} SQL: SELECT ssn FROM employee WHERE ssn IN (SELECT ssn /* emp's in 'Research' */ FROM employee, department WHERE dno = dnumber AND dname = 'Research') AND ssn NOT IN (SELECT essn /* emp's with dependent */ FROM dependent); ```