1 / 62

Advanced SQL Techniques: Joins, Operators, and Functions in Database Management

In Chapter 7 of "Database Systems: Design, Implementation, and Management," 6th Edition by Rob and Coronel, you'll deepen your understanding of advanced SQL concepts. Explore relational set operators like UNION, INTERSECT, and MINUS, and master various JOIN syntaxes, including outer joins. Learn about subqueries, correlated queries, and the use of SQL functions for manipulating dates and strings. Gain insights on creating updatable views, triggers, and stored procedures, all vital for robust database management.

caleb-macias
Download Presentation

Advanced SQL Techniques: Joins, Operators, and Functions in Database Management

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

  4. UNION Query Result Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  5. UNION ALL Query Result Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  6. INTERSECT Query Result Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  7. MINUS Query Results Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  8. INTERSECT Alternative Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  9. MINUS Alternative Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  10. SQL Join Expression Styles Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  11. NATURAL JOIN Result Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  12. JOIN USING Result Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  13. JOIN ON Result Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  14. Outer Joins • Returns not only rows matching join condition but also rows with unmatched values • Three types: • Left • Right • Full Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  15. LEFT JOIN Result Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  16. RIGHT JOIN Result Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  17. FULL JOIN Result Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  18. SELECT Subquery Examples Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  19. WHERE Subquery Examples Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  20. IN Subquery Example Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  21. HAVING Subquery Example Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  22. Multirow Subquery Operator Example Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  23. FROM Subquery Example Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  24. Inline Subquery Example Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  25. Correlated Subquery Examples Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  26. EXISTS Correlated Subquery Examples Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  27. Selected MS Access/SQL Server Date/Time Functions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  28. Selected Oracle Date/Time Functions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  29. Selected Oracle Date/Time Functions (continued) Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  30. Selected Oracle Numeric Functions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  31. Selected Oracle String Functions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  32. Selected Oracle Conversion Functions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  33. Selected Oracle Conversion Functions (continued) Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  34. Oracle Sequence Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  35. Oracle Sequence Examples Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  36. The PRODMASTER and PRODSALES Tables Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  37. The Oracle UPDATE Error Message Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  38. Creating an Updatable View in Oracle Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  39. PRODMASTER Table Update, Using an Updatable View Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  40. Anonymous PL/SQL Block Examples Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  41. SHOW ERRORS • Can help diagnose errors found in PL/SQL blocks • Yields additional debugging information whenever an error is generated after an PL/SQL block is created or executed Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  42. Anonymous PL/SQL Block with Variables and Loops Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  43. PL/SQL Basic Data Types Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  44. The PRODUCT Table Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  45. Creating the TRG_PRODUCT_REORDER Trigger Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  46. Verifying the TRG_PRODUCT_REORDER Trigger Execution Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  47. The P_REORDER Value Mismatch After Update of the P_MIN Attribute Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  48. Stored Procedures: Advantages • Substantially reduce network traffic and increase performance • No transmission of individual SQL statements over network • Help reduce code duplication by means of code isolation and code sharing • Minimize chance of errors and cost of application development and maintenance Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  49. Creating the PRC_PROD_DISCOUNT Stored Procedure Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

  50. Results of the PRC_PROD_DISCOUNT Stored Procedure Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

More Related