Page 69 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes Solution:
SELECT E.ename
FROM Employee E
WHERE E.eid IN (SELECT D.Dept_managerid
FROM Department D
WHERE D.PNo IN (SELECT P.PNo
FROM Project P
WHERE P.Pname = ‘C’)

This query is solved in a bottom-up approach. First, the second sub query is solved and all the
project numbers are selected whose name is C. This output is fed as input to first sub query
where in the department manager id is selected. Finally the names of employees are listed
whose id is present in the relation.

Step by Step Procedure

1. PNo. is selected where pname is ‘C’ i.e. 33.
2. The first sub query checks whether this PNo is present in department or not. If it is present
then its corresponding dept_managerid is selected i.e., 120.
3. The main query checks whether this ‘id’ is present in employee or not. If it is present then
corresponding ename is retrieved i.e., Smith.

Task Discuss the purpose of HAVING clause.

4.3 Complex Queries

In addition to the simple queries shown in the previous section, you can create complex queries,
which may contain more than one SELECT statement. At the highest level, a query is a SELECT
statement, which consists of a query expression followed by an optional ORDER BY clause. At
the next lower level, you can combine different query blocks into a single query expression with
the UNION operator. Lower still, inside each query block is an optional search condition, which
can contain predicates that incorporate subqueries. A subquery is always a single query block
(SELECT) that can contain other subqueries but cannot contain a UNION. A query expression can
contain a maximum of 16 query blocks from all sources, including UNION, subqueries, and the
outer query block.
You can create a complex query by using the following:
1. UNION operator, which allows you to take the union of all rows returned by several
query blocks in one SELECT statement.
2. Subqueries (also known as nested queries), which allow you to embed a query block
within the search condition of an outer SELECT statement.

3. Special predicates, such as ANY, ALL, SOME, EXISTS, and IN, which allow you to compare
the value of an expression with the value of special structures and subqueries.

62 LOVELY PROFESSIONAL UNIVERSITY