Chapter 8 – Advanced SQL(b)

PRINCIPLES OF DATABASE
DESIGN AND DATA
MANAGEMENT – Lecture 8
Advanced SQL (Chapter 8)

Presented By:
Likeleli Letsie
 Learning Objectives
• Types of sub-queries

Date: 2025-03-25
 Sub-queries

●
The use of a complete SELECT statement embedded
within another SELECT statement.
●
The results of this inner SELECT statement (or
subselect) are used in the outer statement to help
determine the contents of the final result.
●
A sub-select can be used in the WHERE and HAVING
clauses of an outer SELECT statement, where it is
called a subquery or nested query.
●
Subselects may also appear in INSERT, UPDATE,
and DELETE statements

Date: 2025-03-25
 Sub-queries
• A subquery is normally expressed inside parentheses.
• The first query in the SQL statement is known as the outer
query.
• The query inside the SQL statement is known as the inner
query.
– The inner query is executed first.
– The output of an inner query is used as the input for the
outer query.
• The entire SQL statement is sometimes referred to as a nested
query.
• We can think of the subquery as producing a temporary table
with results that can be accessed and used by the outer
statement.
• A subquery can be used immediately following a relational
operator (=, <, >, <=, > =,< >) in a WHERE clause, or a
HAVING clause.
Date: 2025-03-25
 Rules for Subqueries
The following rules apply to subqueries:
(1) The ORDER BY clause may not be used in a subquery (although it may
be used in the outermost SELECT statement).
(2) The subquery SELECT list must consist of a single column name or
expression, except for subqueries that use the keyword EXISTS.
(3) By default, column names in a subquery refer to the table name in the
FROM clause of the subquery. It is possible to refer to a table in a FROM
clause of an outer query by qualifying the column name (see following).
(4) When a subquery is one of the two operands involved in a comparison,
the subquery must appear on the right-hand side of the comparison. For
example, it would be incorrect to express the previous example as:
SELECT staffNo, fName, IName, position, salary
FROM Staff
WH ERE (SELECT AVG(salary) FROM Staff) < salary;

Date: 2025-03-25
 Sub-Queries
There are three types of subquery:
●
A scalar subquery returns a single column and a single row, that is, a
single value.
– In principle, a scalar subquery can be used whenever a single value is
needed.

Date: 2025-03-25
 Sub-Queries
• A row subquery returns multiple columns, but
only a single row. A row subquery can be used
whenever a row value constructor is needed,
typically in predicates.
• A table subquery returns one or more columns
and multiple rows. A table subquery can be used
whenever a table is needed, for example, as an
operand for the IN predicate.

Date: 2025-03-25
 Sub-Queries
• A subquery can be based on the use of
the SELECT statement to return one or
more values to another query
• E.g.
SELECT P_CODE, P_PRICE FROM PRODUCT
WHERE P_PRICE >= (SELECT AVG(P_PRICE)
FROM PRODUCT);
• However subqueries have a wide range
of uses.
Date: 2025-03-25
 Sub-Queries
• SELECT Subqueries can be used within a SQL data manipulation
language (DML) statement such as INSERT, UPDATE, or DELETE

Date: 2025-03-25
 Sub-Queries
• Subqueries within the SELECT statement
to retrieve data from the database
• WHERE subqueries
• IN subqueries
• HAVING subqueries

Date: 2025-03-25
 WHERE Sub-queries
• WHERE subqueries
• Most common type of subquery
• Uses an inner SELECT subquery on the right side
of a WHERE comparison expression
SELECT P_CODE, P_PRICE FROM PRODUCT
WHERE P_PRICE >=
(SELECT AVG(P_PRICE) FROM PRODUCT);

• When used in a >, <, =, >=, or <= conditional

expression, requires a subquery that returns only
one value (one column, one row)
• value generated by the subquery must be of a
comparable data type;

Date: 2025-03-25
 WHERE Sub-queries
• WHERE subqueries

lists all customers who ordered a claw

hammer
Date: 2025-03-25
 IN Sub-queries
IN subqueries
• A case where comparison is done not to only one value (a
single value), but to a list of values
• When you want to compare a single attribute to a list of
values, you use the IN operator

Date: 2025-03-25
 HAVING Sub-queries
HAVING subqueries
• HAVING clause is used to restrict the output of a GROUP BY
query, a condition on the grouped rows
• Just as in the WHERE clause, you can use a subquery with a
HAVING clause.

Date: 2025-03-25
 HAVING Sub-queries
HAVING subqueries

Date: 2025-03-25
 Muiltirow sub-queries
MULTIROW subqueries

• The IN subquery compares a value to a

list of values.
• IN subquery uses an equality operator;
that is, it selects only those rows that
are equal to at least one of the values
in the list.
• What happens if you need to make an
inequality comparison ( > or < ) of one
value to a list of values?
Date: 2025-03-25
 Muiltirow sub-queries
MULTIROW subqueries
• Which products cost more than all individual products
provided by vendors from Florida
Outer Query (line 1): compares a single value
(P_QOH * P_PRICE) with a list of values returned by
the first subquery (sq1) using a comparison
operator other than equals.

SQ B (line 5): a list of all

vendors from Florida

SQ A (in Returns the list of product costs for all

products provided by vendors from FL
line 4) (Florida). Uses the output of SQB.

Date: 2025-03-25
 FROM sub-queries
FROM subqueries
• Subqueries within WHERE, HAVING, and IN
statements, and how the ANY and ALL
operators was part of a conditional
expression
• Always appeared at the right side of the
expression
• You will learn how to use subqueries in the
FROM clause
• The FROM clause specifies the table(s) from
which the data will be drawn
• Use a SELECT subquery in the FROM clause
Date: 2025-03-25
 FROM Subqueries
FROM subqueries

subquery returns all

customers who purchased
product 13-Q2/P2

subquery returns all

customers who purchased
product 23109-HB
joining the CUSTOMER table with two virtual tables. The
join condition selects only the rows with matching
CUS_CODE values in each table (base or virtual)
Date: 2025-03-25
 Attribute list sub-queries
• Attribute list subqueries
• SELECT statement uses the attribute list to
indicate what columns to project in the
resulting set.
• Attributes of base tables, computed
attributes, or the result of an aggregate
function.
• The attribute list can also include a
subquery expression, also known as an
inline subquery.
• A subquery in the attribute list must return
one value; otherwise, an error code is
raised.

Date: 2025-03-25