Advanced SQL

1
Chapter 8
Advanced SQL Database Systems Design,
Implementation, and Management, Seventh Edition,
Rob and Coronel
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
• How to use SQL functions to manipulate dates,
  strings, and other data

3
In this chapter, you will learn (continued)

• How to create and use updatable views
• How to create and use triggers and stored
  procedures
• How to create embedded SQL

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

5
UNION

• Example query
• SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
  CUS_AREACODE, CUS_PHONEFROM
  CUSTOMERUNIONSELECT CUS_LNAME, CUS_FNAME,
  CUS_INITIAL, CUS_AREACODE, CUS_PHONEFROM
  CUSTOMER_2

6
UNION (continued)
7
UNION ALL

• Example query
• SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
  CUS_AREACODE, CUS_PHONEFROM CUSTOMERUNION
  ALLSELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
  CUS_AREACODE, CUS_PHONEFROM CUSTOMER_2

8
UNION ALL (continued)
9
INTERSECT
10
MINUS
11
Syntax Alternatives
12
Syntax Alternatives (continued)
13
SQL Join Operators
14
Cross Join

• Syntax
• SELECT column-list FROM table1 CROSS JOIN table2

15
Natural Join
16
JOIN USING Clause
17
JOIN ON Clause
18
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

19
Outer Joins (continued)
20
Outer Joins (continued)
21
Outer Joins (continued)
22
Subqueries and Correlated Queries
23
WHERE Subqueries
24
IN Subqueries
25
HAVING Subqueries
26
Multirow Subquery Operators ANY and ALL
27
FROM Subqueries
28
Attribute List Subqueries
29
Attribute List Subqueries (continued)
30
Correlated Subqueries
31
Correlated Subqueries (continued)
32
Date and Time Functions
33
Date and Time Functions (continued)
34
Date and Time Functions (continued)