How to use correlated queries

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

(1) Formulate the query at a high level using sets:

select fname, lname from employee A // We test 1 tuple of the outer relation 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 }
Employe A is selected if: A's salary = highest salary among all employees in A's department

How to use correlated queries

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

(2) Re-write/refine the condition using a valid tuple condition

select fname, lname from employee A // We test 1 tuple of the outer relation 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 }

How to use correlated queries

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

(3) Formulate the subquery in SQL:

select fname, lname from employee A // We test 1 tuple of the outer relation 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 }
select fname, lname from employee A where salary >= ALL (select salary from employee // Employees in A's department where dno = A.dno)

Another example on how to use correlated query: when to use NOT EXISTS

Find name of departments that do not have any female employee

(1) Formulate the query at a high level using sets:

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
Department D is selected if: The set of female employee in department D is empty I.e.: there are no female employees in department D

Another example on how to use correlated query: when to use NOT EXISTS

Find name of departments that do not have any female employee

(2) Re-write/refine the condition using a valid tuple condition

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 }

Another example on how to use correlated query: when to use NOT EXISTS

Find name of departments that do not have any female employee

(3) Formulate the subquery in SQL:

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 // Female employees in department D WHERE E.sex = 'F' AND E.dno = D.dnumber )