CS457 Syllabus & Progress

Examples of simple (= non-correlated) nested queries

• Examples of nested queries

• Query 1:

 Find fname, lname of employees in the "Research" department

• Solution using a NON-nested query (uses a join):

 ``` SELECT fname, lname FROM employee, department WHERE dno = dnumber AND dname = 'Research' ```

• Solution using a nested query:

• Suppose we know that the 'Research' department has the department number 5, we can write the query as:

 ``` SELECT fname, lname FROM employee WHERE dno = 5 Or: SELECT fname, lname FROM employee WHERE dno IN ( 5 ) ```

• We can use a subquery to find the department number of the Research department:

 ``` ( SELECT dnumber FROM department WHERE dname='Research' ) ```

• Therefore, the solution can be expressed as a nested query as follows:

 ``` SELECT fname, lname FROM employee WHERE dno IN ( SELECT pnumber FROM department WHERE dname = 'Research' ) ```

• Query 2:

 Find fname, lname of employees that do not have any dependent

• Work out solution (one step at a time):

 ``` Conceptual solution: SELECT fname, lname FROM employee WHERE ssn IN { ssn of employees without dependent } Which is equivalent to: SELECT fname, lname FROM employee WHERE ssn NOT IN { ssn of employees with (one or more) dependent } We can write this in SQL as: SELECT fname, lname FROM employee WHERE ssn NOT IN (SELECT essn FROM dependent); ```

• Query 3:

 Find fname and lname of employees who earn the highest salary in the company

Solution:

 ``` Conceptual solution: SELECT fname, lname FROM employee WHERE salary = MAX salary of all employees Re-formulate condition using >= ALL: SELECT fname, lname FROM employee WHERE salary >= ALL ( set of salary of all employees ) Write the inner query in SQL: SELECT fname, lname FROM employee WHERE salary >= ALL ( SELECT salary FROM employee ) ```