Page 67 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes 4.1 Subqueries

The expression following WHERE can be either a simple predicate as explained above or it can
be a query itself! This part of the query following WHERE is called a Subquery.

A subquery, which in turn is a query can have its own subquery and the process of specifying
subqueries can continue ad infinitum! More practically, the process ends once the query has
been fully expressed as a SQL statement.
Subqueries can appear when using the comparison predicate, the IN predicate and when
quantifiers are used.
Subqueries are similar to SELECT chaining. While SELECT chaining combines SELECTs on the
same level in a query, however, subqueries allow SELECTs to be embedded inside other queries.
They can perform several functions:
1. They can take the place of a constant.
2. They can take the place of a constant yet vary based on the row being processed.

3. They can return a list of values for use in a comparison.
Subqueries always appear in the HAVING clause or the WHERE clause of a query. A subquery
may itself contain a WHERE clause and/or a HAVING clause, and, consequently.

Example: SELECT AVG(salary)FROM employee WHERE title = ‘Programmer’;
This statement will return the average salary for all employees whose title is equal to
‘Programmer’
The HAVING clause allows you to specify conditions on the rows for each group - in other
words, which rows should be selected will be based on the conditions you specify. The HAVING
clause should follow the GROUP BY clause if you are going to use it.
HAVING clause syntax:
SELECT column1, SUM(column2)

FROM “list-of-tables”
GROUP BY “column-list”
HAVING “condition”;

HAVING can best be described by example. Let’s say you have an employee table containing the
employee’s name, department, salary, and age. If you would like to select the average salary for
each employee in each department, you could enter:

SELECT dept, avg(salary)
FROM employee
GROUP BY dept;

But, let’s say that you want to ONLY calculate & display the average if their salary is over 20000:
SELECT dept, avg(salary)
FROM employee
GROUP BY dept
HAVING avg(salary) > 20000;

60 LOVELY PROFESSIONAL UNIVERSITY