8 Chapter 8 Advanced SQL Database Systems Design

8 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 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 Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 2

8 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 Database Systems: Design, Implementation, & Management, 7 th 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, 7 th Edition, Rob & Coronel 4

8 UNION • Example query: – 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; Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 5

UNION (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 6

8 UNION ALL • 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, 7 th Edition, Rob & Coronel 7

UNION ALL (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 8

INTERSECT Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 9

MINUS Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 10

Syntax Alternatives Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 11

Syntax Alternatives (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 12

SQL Join Operators Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 13

8 Cross Join • Syntax: – SELECT column-list FROM table 1 CROSS JOIN table 2 Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 14

Natural Join Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 15

JOIN USING Clause Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 16

JOIN ON Clause Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 17

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, 7 th Edition, Rob & Coronel 18

Outer Joins (continued) 8 Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 19

Outer Joins (continued) 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

8 Subqueries and Correlated Queries Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 22

WHERE Subqueries Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 23

IN Subqueries Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 24

HAVING Subqueries Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 25

Multirow Subquery Operators: ANY and ALL Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 26

FROM Subqueries Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 27

Attribute List Subqueries Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 28

Attribute List Subqueries (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 29

Correlated Subqueries Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 30

Correlated Subqueries (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 31

Date and Time Functions Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 32

Date and Time Functions (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8 33

8 Date and Time Functions (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 34

8 Date and Time Functions (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 35