Transcript DB2_Ch08

8
Chapter 8
Advanced SQL
Database Systems:
Design, Implementation, and Management,
Seventh Edition, Rob and Coronel
1
8
In this chapter, you will learn:
• About the relational set operators UNION,
UNION ALL, INTERSECT, and MINUS
• How to use the advanced SQL JOIN operator
syntax
• About the different types of subqueries and
correlated queries
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
2
8
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
3
8
Relational Set Operators
• UNION
• INTERSECT
• MINUS
• Work properly if relations are unioncompatible
– Names of relation attributes must be the same
and their data types must be identical
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
4
8
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
5
UNION
8
• Example -1:
– SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
CUS_AREACODE, CUS_PHONE
FROM CUSTOMER
UNION
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
CUS_AREACODE, CUS_PHONE
FROM CUSTOMER_2;
– This example generates a combined listing of customers—one
that excludes duplicate records
• Example -2
– SELECT column-list FROM T1
UNION
SELECT column-list FROM T2
UNION
SELECT column-list FROM T3;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
6
8
UNION (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
7
UNION ALL
8
• UNION ALL query can be used to produce a
relation that retains the duplicate rows
• UNION ALL statement can be used to unite more
than just two queries
• Example query:
– SELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE,
CUS_PHONE
FROM CUSTOMER
UNION ALL
SELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE,
CUS_PHONE
FROM CUSTOMER_2;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
8
8
UNION ALL (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
9
INTERSECT
8
• The NTERSECT statement can be used to combine rows from
two queries, returning only the rows that appear in both sets
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
10
MINUS
8
• The MINUS statement in SQL combines rows from two queries
and returns only the rows that appear in the first set but not in
the second
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
11
Syntax Alternatives
8
•
For example, the following query returns the customer codes for all customers who are
located in area code 615 and who have made purchases. (If a customer has made a purchase,
there must be an invoice record for that customer.)
•
SELECT CUS_CODE FROM CUSTOMER WHERE CUS_AREACODE = '615'
INTERSECT
SELECT DISTINCT CUS_CODE FROM INVOICE;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
12
Syntax Alternatives (continued)
8
•
For example, the following query returns the customer codes for all customers located in area code 615
minus the ones who have made purchases, leaving the customers in area code 615 who have not made
purchases.
•
SELECT CUS_CODE FROM CUSTOMER WHERE CUS_AREACODE = '615'
MINUS
SELECT DISTINCT CUS_CODE FROM INVOICE;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
13
8
Tue 18-6 SQL Join Operators
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
14
Cross Join
8
• Syntax:
– SELECT column-list FROM table1 CROSS JOIN table2
– Returns the Cartesian product of table1 and table2(old style).
EXAMPLE :
SELECT* FROM GameScores
CROSS JOIN Departments
PlayerNam
e
DepartmentI
d
Score
s
DepartmentI
d
DepartmentNam
e
Jason
1
3000
1
IT
Irene
1
1500
1
IT
Jane
2
1000
1
IT
PlayerName
DepartmentId
Scores
David
2
2500
1
IT
Jason
1
3000
Paul
3
2000
1
IT
Irene
1
1500
James
3
2000
1
IT
Jane
2
1000
Jason
1
3000
2
Marketing
David
2
2500
Irene
1
1500
2
Marketing
Paul
3
2000
Jane
2
1000
2
Marketing
James
3
2000
David
2
2500
2
Marketing
Paul
3
2000
2
Marketing
DepartmentI
d
DepartmentNam
e
James
3
3000
2
Marketing
Jason
1
3000
3
HR
1
IT
Irene
1
1500
3
HR
2
Marketing
Jane
2
1000
3
HR
3
HR
David
2
2500
3
HR
Paul
3
2000
3
HR
James
3
3000
3
HR
Database Systems: Design, Implementation, & Management,
7th
Edition, Rob & Coronel
15
8
Natural Join
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
16
8
JOIN USING Clause
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
17
8
JOIN ON Clause
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
18
8
Outer Joins
• Returns not only matching rows, but also
rows with unmatched attribute values for one
table or both tables to be joined
• Three types
– Left
– Right
– Full
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
19
8
Outer Joins (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
20
8
Outer Joins (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
21
8
Outer Joins (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
22
8
Subqueries and Correlated Queries
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
23
8
Correlated Subqueries (continued)
Example (2): you want to know the vendor
Example (1): you want to know all
code and name of vendors for products
customers who have placed an order lately
having a quantity on hand that is less than
double the minimum quantity
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
24