1 / 34

Advanced SQL Database Systems: Design and Implementation

Learn about relational set operators, advanced SQL JOIN operator syntax, subqueries and correlated queries, views, triggers, stored procedures, embedded SQL, and date and string manipulation functions.

hiltone
Download Presentation

Advanced SQL Database Systems: Design and Implementation

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 • How to use SQL functions to manipulate dates, strings, and other data Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

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

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

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

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

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

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

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

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

  14. Cross Join • Syntax: • SELECT column-list FROM table1 CROSS JOIN table2 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

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

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

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

  19. Outer Joins (continued) 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. Subqueries and Correlated Queries Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

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

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

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

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

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

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

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

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

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

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

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

More Related