Page 68 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 4: Advanced SQL

4.2 Nested Subqueries Notes

A query inside a query is called as nested query. Inner query is called as sub query. Sub query is
usually present in WHERE or HAVING clause.

Consider the following example,

Querys

Query (a): Find the names of employees who are working in department number 6.
Solution:
SELECT E.ename

FROM Employee E
WHERE E.eid IN (SELECT D.Dept_managerid
FROM Department D
WHERE D.DNo = 6)
This query returns “David”

These SQL statements can be read as,
Select employee name from table employee E such that E.eid is present in dept_managerid
where department number is 6. The DBMS first solves the sub query,

SELECT D.Dept_managerid
FROM Department D
WHERE D.DNo = 6

and retrieves the managerid of all the employees who are working for department number 6.
Result: D.Dept_managerid
122
Next, the DBMS checks the presence of this id in employee table. If there exists an id = 122 then
it displays the result for that id.
Result: ename
David
The main query that contains the sub queries in called as outer query.

As already mentioned IN can also be replaced with NOT IN. In this case it checks for the tuples
which are not present in the given relation. In order to find the employee who are not working
in de number 6, we just have to replace IN by NOT IN and the whole query remains the same.
SELECT Rename
FROM Employee E
WHERE E.eid NOT IN (SELECT D.Dept_managerid
FROM Department D
WHERE D.DNo = 6)
Query (b): Find the names of employees who are working on project C.

LOVELY PROFESSIONAL UNIVERSITY 61