chapter 8

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