CS457 Syllabus & Progress

Examples of simple (= non-correlated) nested queries

Examples of nested queries

Query 1:

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:

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:

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 )

Note:

If a sub-query returns one (single) value, then:

WHERE ... >= ALL ( sub-query that returns 1 value ) can be written as: WHERE ... >= ( sub-query that returns 1 value )