Chapter 8 Advanced SQL

Chapter Objectives

This chapter continues what was covered in Chapter 7. While Chapter 7 dealt with single table queries, Chapter 8 discusses joins. Other topics are also included, such as triggers, stored procedures, functions, Embedded SQL, Dynamic SQL, and Persistent Stored Modules. This chapter also contains a detailed discussion of transaction integrity as well as the SQL-99 enhancements and extensions to SQL. An overview of data dictionaries is also included. Chapter 7 is obviously a prerequisite for this chapter. Specific student learning objectives are included at the beginning of the chapter. From an instructor's point of view, the objectives of this chapter are:
1. To provide many examples of relational queries from SQL, which show such capabilities as multiple-table data retrieval (join and other operators such as difference, union, and intersection), explicit and implicit joining, and built-in functions.
2. To illustrate the differences between the joining and subquery approaches to manipulating multiple tables in SQL.
3. To introduce the transaction and concurrency control features of relational DBMSs.
4. To discuss the SQL-99 enhancements to SQL.
5. To briefly discuss the data dictionary facilities available in Oracle.
6. To discuss triggers and stored procedures and provide examples of how these might be used.
7. To briefly discuss dynamic and embedded SQL.

Classroom Ideas

1. Have students program in some 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 8i, have them take a look at the