Transcript Slide 1

7
Chapter 7
Advanced SQL
Database Systems:
Design, Implementation, and Management,
Sixth Edition, Rob and Coronel
1
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, 6th Edition, Rob & Coronel
2
7
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
3
7
UNION Query Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
4
7
UNION ALL Query Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
5
7
INTERSECT Query Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
6
7
MINUS Query Results
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
7
7
INTERSECT Alternative
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
8
7
MINUS Alternative
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
9
7
SQL Join Expression Styles
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
10
7
NATURAL JOIN Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
11
7
JOIN USING Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
12
7
JOIN ON Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
13
7
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
14
7
LEFT JOIN Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
15
7
RIGHT JOIN Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
16
7
FULL JOIN Result
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
17
7
SELECT Subquery Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
18
7
WHERE Subquery Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
19
7
IN Subquery Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
20
7
HAVING Subquery Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
21
7
Multirow Subquery Operator Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
22
7
FROM Subquery Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
23
7
Inline Subquery Example
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
24
7
Correlated Subquery Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
25
7
EXISTS Correlated Subquery Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
26
7
Selected MS Access/SQL Server
Date/Time Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
27
7
Selected Oracle Date/Time Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
28
7
Selected Oracle Date/Time Functions
(continued)
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
29
7
Selected Oracle Numeric Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
30
7
Selected Oracle String Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
31
7
Selected Oracle Conversion Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
32
7
Selected Oracle Conversion Functions
(continued)
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
33
7
Oracle Sequence
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
34
7
Oracle Sequence Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
35
7
The PRODMASTER
and PRODSALES Tables
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
36
7
The Oracle UPDATE Error Message
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
37
7
Creating an Updatable View in Oracle
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
38
7
PRODMASTER Table Update,
Using an Updatable View
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
39
7
Anonymous PL/SQL Block Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
40
7
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
41
7
Anonymous PL/SQL Block
with Variables and Loops
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
42
7
PL/SQL Basic Data Types
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
43
7
The PRODUCT Table
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
44
7
Creating the
TRG_PRODUCT_REORDER Trigger
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
45
7
Verifying the TRG_PRODUCT_REORDER
Trigger Execution
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
46
7
The P_REORDER Value Mismatch After
Update of the P_MIN Attribute
Database Systems: Design, Implementation, & Management, 6th 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, 6th Edition, Rob & Coronel
48
7
Creating the PRC_PROD_DISCOUNT
Stored Procedure
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
49
7
Results of the PRC_PROD_DISCOUNT
Stored Procedure
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
50
7
The PRC_CUS_ADD Stored Procedure
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
51
7
The PRC_INV_ADD and
PRC_LINE_ADD Stored Procedures
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
52
7
Testing the PRC_INV_ADD and
PRC_LINE_ADD Procedures
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
53
7
Cursor Processing Commands
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
54
7
Cursor Attributes
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
55
7
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, 6th 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
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
57
7
SQL Status and Error Reporting Variables
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
58
7
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
Database Systems: Design, Implementation, & Management, 6th 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, 6th 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, 6th Edition, Rob & Coronel
61
7
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, 6th Edition, Rob & Coronel
62