CS457 Syllabus & Progress

### Examples of Correlated nested queries

• Examples on how to use correlated nested queries

• Query 1:

 Find the fname, lname of the employee that earn the highest salary in his/her department

Note:

• I like to show you the "natural" way to solve a query:

 Formulate a condition that the qualifying tuples must satisfy Then formulate the condition using SQL tuple conditions

Solution:

 ``` Formulate the query in a "meta set language": select fname, lname from employee A // We test 1 tuple of the cartesian product at a time // Let's call the current tuple that we test // the tuple employee "A" where A's salary = max { salary of employees in employee A's department } select fname, lname from employee A where salary >= ALL { salary of employees in employee A's department } The sub-query to find { salary of employees in employee A's department } is: select salary from employee where dno = A.dno Complete solution: select fname, lname from employee A where salary >= ALL (select salary from employee where dno = A.dno) ```

• Query 2:

 Find the fname, lname of the female employee that earn the highest salary among the females in her (own) department

Solution:

 ``` Formulate the query in a "meta set language": select fname, lname from employee A where sex = 'F' and salary = max { salary of female employees in employee A's department } select fname, lname from employee A where sex = 'F' and salary >= ALL { salary of female employees in employee A's department } The sub-query to find the set: { salary of female employees in employee A's department } is: ( select salary from employee where sex = 'F' and dno = A.dno ) Complete solution: select fname, lname from employee A where sex = 'F' and salary >= ALL (select salary from employee where sex = 'F' and dno = A.dno) ```

• Query 3: when to use (NOT) EXISTS... (test if something (does not) exist)

 Find name of departments that do not have any female employee        In other words:   female employees do not exist in the department...

Solution:

 ``` select dname from department D // we test 1 tuple at a time - call this tuple "D" !!! where { set of female employee of in department D } = empty set select dname from department D // we test 1 tuple at a time - call this tuple "D" !!! where NOT EXISTS { set of female employee of in department D } select dname from department D where NOT EXISTS ( SELECT * FROM employee E WHERE E.sex = 'F' AND E.dno = D.dnumber ) ```

• Query 4:

 Find fname, lname of employees that do not work on any project controlled by the Research department

Solution:

 ``` select fname, lname from employee A where { (set of) projects w.o. by employee A and controlled by Research dept } = empty set select fname, lname from employee A where not exists { project (1) is w.o. by employee A and (2) is controlled by Research dept } select fname, lname from employee A where not exists ( select * from project // This will check every possible project where (1) project w.o. by employee A and (2) project controlled by Research dept ) select fname, lname from employee A where not exists ( select * from project where project ∈ {pno of project w.o. by employee A} and project ∈ {pno of project controlled by Research dept} ) select fname, lname from employee A where not exists ( select * from projects where pnumber in (pno of project w.o. by employee A ) and pnumber in (pno of project controlled by Research dept ) ) select fname, lname from employee A where not exists ( select * from projects where pnumber in ( select pno from works_on where essn = A.ssn ) and pnumber in (pno of project controlled by Research dept ) ) select fname, lname from employee A where not exists ( select * from project where pnumber in ( select pno from works_on where essn = A.ssn ) and pnumber in ( select pnumber from project, department where dnum = dnumber and dname = 'Research' ) ) ```