Complex Queries

Nested Queries

• Some queries require that existing values in the database be fetched and then used in a comparison condition.

• Such queries can be conveniently formulated by using nested queries, which are complete select-from-where blocks within the WHERE clause of another query.

• That other query is called the outer query.

• A complete SELECT query, called a nested query, can be specified within the WHERE-clause of another query, called the outer query.

• Many of the previous queries can be specified in an alternative form using nesting.

Example 1: Retrieve the name and address of all employees who work for the 'Research' department.

SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
              FROM DEPARTMENT
              WHERE DNAME='Research' );

Example 2:
Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' either as a worker or as a manager of the department that controls the project.

SELECT DISTINCT Pnumber FROM PROJECT
WHERE Pnumber IN (SELECT Pnumber 
                  FROM PROJECT, DEPARTMENT, EMPLOYEE 
                  WHERE Dnum=Dnumber 
					AND Mgr_ssn=Ssn 
					AND Lname='Smith')
				OR
		SELECT DISTINCT Pnumber FROM PROJECT
			WHERE Pnumber IN (SELECT Pno
                  FROM WORKS_ON, EMPLOYEE
                  WHERE Essn=Ssn AND Lname='Smith');

Example 3:
Retrieve the Ssns of all employees who work on the same (project,hours) combination on some project that employee (whose Ssn is equal to ‘33344555’) works on.

SELECT DISTINCT Essn FROM WORKS_ON 
	WHERE (Pno,Hours) IN (SELECT (Pno,Hours) 
                      FROM WORKS_ON 
                      WHERE Essn='333445555');

Other Operators

• =ANY (or =SOME) operator returns TRUE if the value v is equal to some value in the set V and is hence equivalent to IN

• ANY and SOME have the same meaning

• Other operators that can be combined with ANY (or SOME) include >, >=, <, <=, and <>

• The keyword ALL also combined with each of these operators Eg., (v>ALL V)

• Returns TRUE if the value v is greater than all the values in the set V.

Example:
Retrieve the names of employees whose salary is greater than the salary of all the employees in department 5

SELECT Fname, Lname 
FROM EMPLOYEE 
WHERE Salary > ALL (SELECT Salary 
                    FROM EMPLOYEE 
                    WHERE Dno=5);

Ambiguity of attribute names

Example:
Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.

SELECT E.Fname, E.Lname 
FROM EMPLOYEE E 
WHERE E.Ssn IN (SELECT Essn 
                FROM DEPENDENT 
                WHERE E.Fname = Dependent_name 
                AND E.Sex = Sex);

Correlated nested queries

• If a condition in the WHERE-clause of a nested query references an attribute of a relation declared in the outer query, the two queries are said to be correlated.

• The result of a correlated nested query is different for each tuple (or combination of tuples) of the relation(s) the outer query.

Example 1:
Retrieve the name of each employee who has a dependent with the same first name as the employee.

SELECT E.FNAME, E.LNAME 
FROM EMPLOYEE AS E 
WHERE E.SSN IN (SELECT ESSN 
                FROM DEPENDENT 
                WHERE ESSN=E.SSN 
                AND E.FNAME=DEPENDENT_NAME);

Example 2:
A query written with nested SELECT... FROM... WHERE... blocks and using the = or IN comparison operators can always be expressed as a single block query.

SELECT E.FNAME, E.LNAME 
FROM EMPLOYEE E, DEPENDENT D 
	WHERE E.SSN=D.ESSN 
		AND E.FNAME=D.DEPENDENT_NAME;

Example 3:
Retrieve the name of each employee who works on all the projects controlled by department number 5.

SELECT FNAME, LNAME 
FROM EMPLOYEE 
WHERE ((SELECT PNO 
        FROM WORKS_ON 
        WHERE SSN=ESSN) 
        CONTAINS (SELECT PNUMBER 
                  FROM PROJECT 
                  WHERE DNUM=5));

EXISTS Function

• EXISTS is used to check whether the result of a correlated nested query is empty (contains no tuples) or not

• The result of EXISTS is a boolean value TRUE or FALSE

Example 4:
Retrieve the name of each employee who has a dependent with the same first name as the employee.

SELECT FNAME, LNAME 
FROM EMPLOYEE 
WHERE EXISTS (SELECT * 
              FROM DEPENDENT 
              WHERE SSN=ESSN 
              AND FNAME=DEPENDENT_NAME);

Example 5:
Retrieve the names of employees who have no dependents.

SELECT FNAME, LNAME 
FROM EMPLOYEE 
WHERE NOT EXISTS (SELECT * 
                  FROM DEPENDENT 
                  WHERE SSN=ESSN);