CS457 Syllabus & Progress

Examples of Correlated nested queries

A bit of advice first...

How I formulate an SQL query:

Procedure:

Set up a pseudo SQL select statement that provides the correct result set
Refine the pseudo SQL statement one clasue at a time into a valid SQL clause -- until all psuedo clauses are removed

A simple example:

Find fname, lname of all employees in the Reseach department:

Initial solution: select fname, lname from employee E where E is employee in Research department
Refine: select fname, lname from employee E where E.dno = dnumber of the Research department Refine further: select fname, lname from employee E where E.dno IN (dnumber of the Research department) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ How to find this ??
Finally: select fname, lname from employee E where E.dno IN (select dnumber from department where dname = 'Research') /* A complete SQL query */

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:

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