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
- Slides: 35