2 OBJECTIVES
Use joins to retrieve data from more than
one table
Use the IN and EXISTS operators to query
multiple tables
Use a subquery within a subquery
Use an alias
3 OBJECTIVES (CONT…)
Join a table to itself
Perform set operations (union, Union
ALL, intersection, and difference)
Use the ALL and ANY operators in a query
Perform special operations (inner join,
outer join, and product)
4
5
6 QUERYING MULTIPLE TABLES
When querying more than one table, the
tables must be joined
Join tables by finding columns with
matching data
Join tables by using a condition in the
WHERE clause
7 JOINING TWO TABLES
In the SELECT clause, list all columns you
want to display
In the FROM clause, list all tables
involved in the query
In the WHERE clause, restrict to the rows
that have common values in matching
columns
8 JOINING TWO TABLES
List the number and name of each customer, together with the number, last
name, and first name of the sales rep who represents the customer.
9
List the number and name of each customer whose credit limit is $7500,
together with the number, last name, and first name of the sales rep who
represents the customer.
10
For every part on order, list the order number, part number, number of units ordered,
quoted price, and unit price.
COMPARING JOINS, IN, AND
11
EXISTS
Tables can be joined using IN or EXISTS clause
Use IN operator with a subquery
Use the EXISTS operator to retrieve data from more
than one table
12
Find the description of each part in order number 21610.
13
Find the description of each part in order number 21610.
Find the order number and order date for each order
14
that contains part number DR93.
15
Find the order number and order date for each
order that contains part number DR93.
16
CORRELATED
SUBQUERY
Subquery involves a table listed in the outer query
You need to qualify ORDER_NUM column in subquery as
ORDERS.ORDER_NUM
17
CORRELATED
SUBQUERY
For each row in the ORDERS table
Subquery executed using the value of
ORDERS.ORDER_NUM that appears in the row
The inner query makes a list of rows in the
ORDER_LINE table
Where ORDER_LINE.ORDER_NUM matches this value
and
In which PART_NUM is equal to DR93
18 USING A SUBQUERY WITHIN
A SUBQUERY
A nested subquery is a subquery within a subquery
SQL evaluates the queries from the innermost query
to the outermost
It is possible that there is more than one approach
to formulation of the queries
Many DMBS have optimizers that analyze queries
for efficiency
FIND THE ORDER NUMBER AND ORDER
DATE FOR EACH ORDER THAT
19
INCLUDES A PART THAT IS LOCATED IN
WAREHOUSE 3.
SELECT ORDER_NUM, ORDER_DATE Outer query is
FROM ORDERS evaluated last
WHERE ORDER_NUM IN
(SELECT ORDER_NUM Intermediate query is
FROM ORDER_LINE evaluated 2 nd
WHERE PART_NUM IN
(SELECT PART_NUM
FROM PART Innermost query
WHEREWAREHOUSE =‘3’));is evaluated 1
st
A QUERY IS EVALUATED IN 3
20
STEPS:
INNERMOST SUBQUERY PRODUCES A TEMPORARY
TABLE OF PART NUMBERS FOR THOSE PARTS
LOCATED IN WAREHOUSE.
INTERMEDIATE QUERY PRODUCES A SECOND
TEMPORARY TABLE WITH A LIST OF ORDER
NUMBERS. EACH ORDER NUMBER IN THIS
COLLECTION HAS A ROW IN THE ORDER_LINE TABLE
FOR WHICH THE PART NUMBER IS IN THE
TEMPORARY TABLE PRODUCED IN STEP 1.
THE OUTER QUERY PRODUCES THE DESIRED LIST
OF ORDER NUMBERS AND ORDER DATES. ONLY
THOSE ORDERS WHOSE NUMBERS ARE IN THE
TEMPORARY TABLE PRODUCED IN STEP 2 ARE
INCLUDED IN THE RESULTS.
FIND THE ORDER NUMBER AND ORDER DATE
21 FOR EACH ORDER THAT INCLUDES A PART
THAT IS LOCATED IN WAREHOUSE 3.
SELECT ORDERS.ORDER_NUM, ORDER_DATE
FROM ORDER_LINE, ORDERS, PART
WHERE ORDER_LINE.ORDER_NUM = ORDERS.
ORDER_NUM
AND ORDER_LINE.PART_NUM = PART. PART_NUM
AND WAREHOUSE =‘3’;
22
LIST THE CUSTOMER NUMBER, ORDER NUMBER, ORDER DATE,
AND ORDER TOTAL FOR EACH ORDER WITH A TOTAL THAT
EXCEEDS $1000. ASSIGN THE COLUMN NAME ORDER_TOTAL TO
THE COLUMN THAT DISPLAYS ORDER TOTAL.
23
HAVING & GROUP BY
CLAUSE
HAVING clause restricts the output of
a GROUP BY query
Applies conditional criterion to the
grouped rows
24
USING AN ALIAS
An alias is an alternate name for a table
Used when tables are listed in the FROM
clause
Created by typing the name of the table,
hitting a space, then typing the name of
the alias
Allows for simplicity
LIST THE NUMBER, LAST NAME, AND FIRST
25
NAME FOR EACH SALES REP TOGETHER WITH
THE NUMBER AND NAME FOR EACH
CUSTOMER THE SALES REP REPRESENT.
SELECT R.REP_NUM, LAST_NAME,
FIRST_NAME, C.
CUSTOMER_NUM,CUSTOMER_NAME
FROM REP R, CUSTOMER C
WHERE R.REP_NUM = C.REP_NUM;
26
JOINING SEVERAL TABLES
Condition shows how the columns are related for each pair
of tables
28
JOINING SEVERAL TABLES STEP-BY-STEP
In the SELECT clause list all the columns
to display
Qualify the column name if needed
In the FROM clause list all tables
Include tables used in the WHERE clause,
even if they are not in the SELECT clause
29 JOINING SEVERAL TABLES
STEP-BY-STEP
Take one pair of related tables at a time
Indicate in the WHERE clause the
condition that relates the tables
Join conditions with the AND operator
Include any additional conditions in the
WHERE clause
Connect them with the AND operator
30 PRODUCT
The product (Cartesian Product) of two
tables is the combination of all rows in
the first table and all rows in the second
table
Omit the WHERE clause to form a
product
31
32
REFERENCES
Coronel, C., Morris, S. 2017. Database Systems: Design,
Implementation and Management. 12th edition. Cengage Learning
Boston.
Pratt, P.J, 2005. A Guide to SQL. 7th Edition. Thomson/Course
Technology.