1 / 24

Chapter 8

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

willem
Download Presentation

Chapter 8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  3. Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

  5. Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

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

  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_PHONEFROM CUSTOMERUNION ALLSELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONEFROM CUSTOMER_2; Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

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

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

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

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

  14. Tue 18-6 SQL Join Operators Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

  16. Natural Join Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

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

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

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

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

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

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

  24. Correlated Subqueries (continued) 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 Example (1): you want to know all customers who have placed an order lately Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

More Related