Transcript Slide 1
Chapter 7 Advanced SQL Database Systems: Design, Implementation, and Management, Sixth Edition, Rob and Coronel 1
7
7
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, 6 th Edition, Rob & Coronel 2
In this chapter, you will learn: ( continued )
7
• 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, 6 th Edition, Rob & Coronel 3
UNION Query Result
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 4
UNION ALL Query Result
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 5
INTERSECT Query Result
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 6
MINUS Query Results
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 7
INTERSECT Alternative
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 8
MINUS Alternative
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 9
SQL Join Expression Styles
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 10
NATURAL JOIN Result
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 11
JOIN USING Result
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 12
JOIN ON Result
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 13
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, 6 th Edition, Rob & Coronel 14
7
LEFT JOIN Result
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 15
RIGHT JOIN Result
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 16
FULL JOIN Result
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 17
SELECT Subquery Examples
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 18
WHERE Subquery Examples
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 19
IN Subquery Example
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 20
HAVING Subquery Example
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 21
Multirow Subquery Operator Example
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 22
FROM Subquery Example
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 23
Inline Subquery Example
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 24
Correlated Subquery Examples
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 25
EXISTS Correlated Subquery Examples
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 26
Selected MS Access/SQL Server Date/Time Functions
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 27
Selected Oracle Date/Time Functions
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 28
Selected Oracle Date/Time Functions ( continued )
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 29
Selected Oracle Numeric Functions
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 30
Selected Oracle String Functions
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 31
Selected Oracle Conversion Functions
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 32
Selected Oracle Conversion Functions ( continued )
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 33
Oracle Sequence
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 34
Oracle Sequence Examples
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 35
The PRODMASTER and PRODSALES Tables
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 36
The Oracle UPDATE Error Message
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 37
Creating an Updatable View in Oracle
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 38
PRODMASTER Table Update, Using an Updatable View
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 39
Anonymous PL/SQL Block Examples
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 40
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
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 41
Anonymous PL/SQL Block with Variables and Loops
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 42
PL/SQL Basic Data Types
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 43
The PRODUCT Table
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 44
Creating the TRG_PRODUCT_REORDER Trigger
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 45
Verifying the TRG_PRODUCT_REORDER Trigger Execution
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 46
The P_REORDER Value Mismatch After Update of the P_MIN Attribute
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 47
7
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, 6 th Edition, Rob & Coronel 48
Creating the PRC_PROD_DISCOUNT Stored Procedure
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 49
Results of the PRC_PROD_DISCOUNT Stored Procedure
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 50
The PRC_CUS_ADD Stored Procedure
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 51
The PRC_INV_ADD and PRC_LINE_ADD Stored Procedures
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 52
Testing the PRC_INV_ADD and PRC_LINE_ADD Procedures
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 53
Cursor Processing Commands
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 54
Cursor Attributes
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 55
SQL and Procedural Languages: Key Differences • Run-time mismatch: – SQL executed one instruction at a time – Host language typically runs at the client side in its own memory space • Processing mismatch: – Host language processes one data element at a time • Data type mismatch: – Data types may not match Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 56
7
Embedded SQL Framework • A standard syntax to identify embedded SQL code within host language • A standard syntax to identify host variables • A communication area used to exchange status and error information between SQL and the host language
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 57
SQL Status and Error Reporting Variables
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 58
Static SQL • Embedded SQL in which the programmer used predefined SQL statements and parameters – End users of programs are limited to actions that were specified in application programs • SQL statements will not change while application is running
7
Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 59
7
Dynamic SQL • SQL statement is not known in advance, but instead is generated at run time • Program can generate SQL statements at run time that are required to respond to ad hoc queries • Attribute list and the condition are not known until the end user specifies them • Tends to be much slower than static SQL • Requires more computer resources Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 60
7
Summary • SQL provides relational set operators to combine output of two queries to generate new relation • Operations that join tables can be classified as inner joins and outer joins • Subqueries and correlated queries are used when it is necessary to process data based on
other
processed data • SQL functions are used to extract or transform data Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 61
Summary ( continued ) • Oracle sequences may be used to generate values to be assigned to a record • PL/SQL may be used to create triggers, stored procedures, and PL/SQL functions • If SQL statements are designed to return more than one value inside the PL/SQL code, a cursor is needed • Embedded SQL refers to the use of SQL statements within an application programming language Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 62
7