chapter 8
DESCRIPTION
Chapter 8. Advanced SQL Database Systems: Design, Implementation, and Management, Seventh Edition, Rob and Coronel. 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 - PowerPoint PPT PresentationTRANSCRIPT
8
1
Chapter 8
Advanced SQL
Database Systems: Design, Implementation, and Management,
Seventh Edition, Rob and Coronel
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
2
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, 7 th Edition, Rob & Coronel
8
3
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
4
Relational Set Operators
• UNION
• INTERSECT
• MINUS
• Work properly if relations are union-compatible– Names of relation attributes must be the same
and their data types must be identical
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
5
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
6
UNION• 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, 7 th Edition, Rob & Coronel
8
7
UNION (continued)
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
8
UNION ALL
• 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 CUSTOMERUNION ALLSELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE, CUS_PHONE
FROM CUSTOMER_2;
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
9
UNION ALL (continued)
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
10
INTERSECT
• 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, 7 th Edition, Rob & Coronel
8
11
MINUS• 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, 7 th Edition, Rob & Coronel
8
12
Syntax Alternatives• 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, 7 th Edition, Rob & Coronel
8
13
Syntax Alternatives (continued)• 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, 7 th Edition, Rob & Coronel
8
14
Tue 18-6 SQL Join Operators
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
15
Cross Join• 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
PlayerName DepartmentId Scores
Jason 1 3000
Irene 1 1500
Jane 2 1000
David 2 2500
Paul 3 2000
James 3 2000
DepartmentIdDepartmentName
1 IT
2 Marketing
3 HR
PlayerName
DepartmentIdScores
DepartmentIdDepartmentName
Jason 1 3000 1 IT
Irene 1 1500 1 IT
Jane 2 1000 1 IT
David 2 2500 1 IT
Paul 3 2000 1 IT
James 3 2000 1 IT
Jason 1 3000 2 Marketing
Irene 1 1500 2 Marketing
Jane 2 1000 2 Marketing
David 2 2500 2 Marketing
Paul 3 2000 2 Marketing
James 3 3000 2 Marketing
Jason 1 3000 3 HR
Irene 1 1500 3 HR
Jane 2 1000 3 HR
David 2 2500 3 HR
Paul 3 2000 3 HR
James 3 3000 3 HR
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
16
Natural Join
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
17
JOIN USING Clause
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
18
JOIN ON Clause
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
19
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, 7 th Edition, Rob & Coronel
8
20
Outer Joins (continued)
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
21
Outer Joins (continued)
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
22
Outer Joins (continued)
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
23
Subqueries and Correlated Queries
Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel
8
24
Correlated Subqueries (continued)Example (1): you want to know all customers who have placed an order lately
Example (2): you want to know the vendor code and name of vendors for products having a quantity on hand that is less than double the minimum quantity