This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: Chapter 8 69 Chapter 8 Advanced SQL Chapter Overview Chapter 8 follows from Chapter 7, from single table queries to multi-table joins, subqueries (both non-correlated and correlated), establishing referential integrity, and derived tables. Triggers, stored procedures, functions, embedded SQL, dynamic SQL, and Persistent Stored Modules are also covered. This chapter also contains a detailed discussion of transaction integrity as well as the SQL-200n enhancements and extensions to SQL and an overview of data dictionaries. Chapter 7 is a prerequisite for this chapter. Chapter Objectives Specific student learning objectives are included at the beginning of the chapter. From an instructors point of view, the objectives of this chapter are to: 1. Build the students SQL skills and an appreciation of SQL through many examples of relational queries from SQL; demonstrate capabilities such as multiple-table data retrieval (join and other operators such as difference, union, and intersection), explicit and implicit joining, and built-in functions. 2. Illustrate the differences between the joining and subquery approaches to manipulating multiple tables in SQL. 3. Introduce the transaction and concurrency control features of relational DBMSs. 4. Discuss the SQL:200n enhancements to SQL. 5. Briefly discuss the data dictionary facilities available in Oracle 10g. 6. Discuss triggers and stored procedures and provide examples of how these might be used. 7. Briefly discuss dynamic and embedded SQL. 8. Understand OLTP and OLAP and how SQL is used in writing queries for the two approaches. Key Terms Correlated subquery Natural join Persistent Stored Modules (SQL/PSM) Dynamic SQL Online analytical processing (OLAP) Embedded SQL Online transaction processing (OLTP) Procedure Equi-join Outer join Trigger Function User-defined data type (UDT) Join 70 Modern Database Management, Ninth Edition Classroom Ideas 1. Have students program in a system that supports SQL along with this chapter. The nuances of joining multiple tables, nesting subqueries, properly qualifying built-in functions, and so forth are really only learned by writing a wide variety of non-trivial queries. There are exercises at the end of the chapter that will provide such practice for students. 2. If students have access to Oracle, have them take a look at the various data dictionary views available to them as a user. You may also want to discuss the various DBA views available and show these to the students during your lecture. Remember that Teradata University supports Oracle for classroom use, and that you may set up access for yourself and your students. The databases from the text are available, as are much larger datasets that you may want to use. Teradata Universitys home page is www.teradatastudentnetwork.com ....
View Full Document
This note was uploaded on 11/02/2011 for the course IDS 410 taught by Professor Staff during the Spring '08 term at Ill. Chicago.
- Spring '08