LECTURE 6:

STRUCTURED QUERY LANGUAGE (SQL) III

ISOM3260
1
Overview of ISOM3260
• Database fundamentals
• database environment and development
• Database analysis
• entity-relationship (ER) diagram
• enhanced ER diagram
• Logical database design
• transforming ER diagram into relations; normalization
• Physical database design
• technical specifications of the database
• Database implementation
• structured query language (SQL) I, II & III
• Advanced topics
• data and database administration
• data warehousing

2
Database Development Activities During SDLC

3
Outline
• Joins
• Equi-Join / Natural Join
• Outer Join
• Subqueries
• Noncorrelated Subqueries
• Correlated Subqueries

4
SELECT Statement
• Used for queries on single or multiple tables
• Clauses of the SELECT statement
• SELECT
• List the columns (and expressions) that should be returned from the
query
• FROM
• Indicate the table(s) from which data will be obtained
• WHERE
• Indicate the conditions under which a row will be included in the
result
• GROUP BY
• Indicate categorization of results
• HAVING
• Indicate the conditions under which a category (group) will be
included
• ORDER BY
• Sorts the result according to specified criteria
5
Figure 6-10:
SQL statement processing
order

6
 Processing Multiple Tables – Joins
• Join
• a relational operation that temporarily combines two or more
tables with common columns
• the common columns in joined tables are usually the primary
key of the dominant table and the foreign key of the
dependent table

• Equi-join
• a join in which the joining condition is based on equality
between values in the common columns; common columns
appear redundantly in the result table

• Natural join
• an equi-join in which one of the duplicate common columns is
eliminated in the result table

8
 Processing Multiple Tables – Joins
• Outer Join
• a join in which rows that do not have matching values in
common columns are nevertheless included in the result
table

• as opposed to equi-join / natural join, in which rows must

have matching values in order to appear in the result table

9
Design of a Relational Database for Pine Valley Furniture

10
Figure 7-1: Sample Pine Valley Furniture data

Customer_T Order_T

Product_T
Order_Line_T
11
Example: Equi-join
• Based on equality between values in the common columns

If WHERE clause is omitted, the query will return all combinations

of customers and orders (10 orders * 15 customers=150 rows).12
Figure 7-1 Customer_T and Order_T Tables

13
Example: Natural Join
• same as equi-join except that one of the duplicate common columns
is eliminated in the result table
• Most commonly used form of join operation

• For each customer who has placed an order, what is the customer’s
name and order number?

It must be specified from which table Join involves multiple

the DBMS should pick Customer_ID tables in FROM clause

SELECT CUSTOMER_T.Customer_ID, Customer_Name, Order_ID

FROM CUSTOMER_T, ORDER_T
WHERE CUSTOMER_T.Customer_ID = ORDER_T.Customer_ID;

WHERE clause performs the equality check

for common columns of the two tables 14
Example: Outer Join
• row in one table does not have a matching row in the other table
• null values appear in columns where there is no match between tables
• List customer name, id number, and order number for all customers.
Include customer information even for customers that do not have an
order.

SELECT CUSTOMER_T.Customer_ID,Customer_Name, Order_ID

FROM CUSTOMER_T LEFT OUTER JOIN ORDER_T
ON CUSTOMER_T.Customer_ID = ORDER_T.Customer_ID;

LEFT OUTER JOIN syntax will cause customer data to

appear even if there is no corresponding order data

15
Example: Outer Join

No corresponding Order_ID for these Customer_ID.

16
 Example: Multiple Join of Four Tables
• Assemble all information necessary to create an invoice for order
number 1006.
Four tables involved in this join

SELECT CUSTOMER_T.Customer_ID, Customer_Name, Customer_Address,

City, State, Postal_Code, ORDER_T.Order_ID, Order_Date,
Ordered_Quantity, Product_Description, Standard_Price,
(Ordered_Quantity * Standard_Price)
FROM CUSTOMER_T, ORDER_T, ORDER_LINE_T, PRODUCT_T
WHERE CUSTOMER_T.Customer_ID = ORDER_T.Customer_ID
AND ORDER_T.Order_ID = ORDER_LINE_T.Order_ID
AND ORDER_LINE_T.Product_ID = PRODUCT_T.Product_ID
AND ORDER_T.Order_ID = 1006;

Each pair of tables requires an equality-check condition in the

WHERE clause, matching primary keys against foreign keys 17
Figure 7-4: Results from a four-table join

From Customer_T table

Ordered_Quantity Standard_Price Ordered_Quantity * Standard_Price

From From Product_T table

Order_T table
Expression
From
Order_Line_T table
18
 Overview of Subqueries
• Outer Query vs. Inner Query (Subquery)
• Outer query: display result based on the data from the table in the
outer query
• Inner query: a SELECT statement inside the outer query
• Purposes of subquery:
• In WHERE/HAVING clause for conditional checking
• As a virtual “table” of the FROM clause
• Subqueries can be:
• Noncorrelated
• execute inner query once for the entire outer query
• Correlated
• execute inner query once for each row returned by the outer query

20
Noncorrelated Subqueries
• do not depend on data from the outer query
• execute once for the entire outer query

• Option 1: in WHERE/HAVING clause for conditional

checking

21
Example 1: Subqueries
• Two equivalent queries
• One using a join
• One using a subquery

22
 Example 2: Subquery
• Which customers have placed orders?
The IN operator will test to
see if the Customer_ID value
of a row is included in the
• SELECT Customer_Name list returned from the
FROM CUSTOMER_T subquery
WHERE Customer_ID IN
(SELECT DISTINCT Customer_ID FROM ORDER_T);

Subquery is embedded in parentheses. In this case it returns

a list that will be used in the WHERE clause of the outer query

• Use “IN” but not “=“ if the inner query can return multiple rows of results
• The inner query should return one column for comparison purpose 23
Figure 7-8a Processing a noncorrelated subquery

24
A noncorrelated subquery processes completely before the outer query.
 Example 3: Subquery
• The qualifier NOT may be used in front of IN

A join can be
used in an
inner query

Inner query returns list of customers who had ordered computer desk. Outer
25
query lists customers’ names who were not in the list returned by inner query.
Noncorrelated Subqueries
• do not depend on data from the outer query
• execute once for the entire outer query

• Option 1: in WHERE/HAVING clause for conditional

checking
• Option 2: as a virtual “table” of the FROM clause

26
Example 4: Subquery as Derived Table
• Which products have a standard price that is higher that the average
standard price? One column of the subquery is an
Subquery forms the derived aggregate function that has an
table used in the FROM alias name. That alias can then be
clause of the outer query referred to in the outer query.

SELECT Product_Description, Standard_Price, AVGPRICE

FROM
(SELECT AVG(Standard_Price) AVGPRICE FROM PRODUCT_T),
PRODUCT_T
WHERE Standard_Price > AVGPRICE;

The WHERE clause normally cannot include aggregate functions, but

because the aggregate is performed in the subquery, its result can be 27
used in the outer query’s WHERE clause.
Correlated vs. Noncorrelated
Subqueries
• Noncorrelated subqueries
• do not depend on data from the outer query
• execute once for the entire outer query

• Correlated subqueries
• make use of data from the outer query
• execute once for each row of the outer query
• can make use of the EXISTS operator

28
Example 5: Correlated Subquery
• What are the order numbers that include furniture finished in natural ash?
The EXISTS operator will return a
TRUE value if the subquery resulted
SELECT DISTINCT Order_ID in a non-empty set, otherwise it
FROM ORDER_LINE_T returns a FALSE.
WHERE EXISTS
(SELECT * FROM PRODUCT_T
WHERE Product_ID = ORDER_LINE_T.Product_ID
AND Product_Finish = ‘Natural Ash’);

The subquery is testing for a value that

comes from the outer query.

29
 Figure 7-8b
Processing a
correlated
subquery

Result:
ORDER_ID
1001
1002
1003
1006
1007
1008
1009

30
Subquery refers to outer-query data, so executes once for each row of outer query data.
Example 6: Correlated Subqueries
ANY and ALL with logical operators such as =, >, and <.

31
Review Questions
• What are the types of join?
• What are subqueries?
• What is a noncorrelated subquery?
• What is a correlated subquery?

32