Chapter 8 Advanced SQL
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 ...view middle of the document...
You may also want to discuss the various DBA views available and show these to the students during your lecture.
3. When discussing multiple table queries, always emphasize that there is more than one way to write a query. For example, show the students a query using a join and then the same query using subqueries.
4. Emphasize the cases when a subquery is needed and cannot be substituted with a join. A good example of this would be the case where one needs to find all customers who never purchased a product (using a subquery with the NOT IN qualifier).
5. Develop an exercise for the students to explore the effects of a trigger. Have them create and populate some tables, write an insert trigger for one of the tables that might impact other tables. You could then have the students insert some records and see the results. It is important to show the effects of triggers through examples that the students can try out followed by problems that they would have to solve by writing triggers. The same can be said for stored procedures.
6. The discussion on SQL/PSM might be a good place to introduce PL/SQL before introducing triggers and stored procedures.
Answers to Review Questions
1. Define each of the following key terms:
a. Dynamic SQL. The process of making an application capable of generating specific SQL code on the fly, as the application is processed.
b. Correlated subquery. This type of subquery is processed outside in, rather than inside out. That is, the inner query is executed for each row in the outer query, and the inner query depends in some way on values from the current row in the outer query.
c. Embedded SQL. The process of including hard-coded SQL statements in a program written in another language, such as C or Java.
d. Procedure. A collection of procedural and SQL statements that are assigned a unique name within the schema and stored in the database.
e. Join. The most frequently used relational operation, which brings together data from two or more related tables into one result table.
f. Equi-join. A join in which the joining condition is based on equality between values in the common columns. It produces a table of rows composed of columns from two other tables, where common columns appear (redundantly) in the result table.
g. Natural join. A join that returns all requested results, but values of the common columns are included only once. Natural joins are the same as equi-joins, except that duplicate columns are eliminated. The natural join is the most commonly used form of join operation;
h. Outer join. A join in which rows that do not have matching values in common columns are nevertheless included in the result table. Outer joins return all the values in one of the tables included in the join, regardless of whether a match exists in the other table(s) or not.
i. Function. A stored subroutine that returns one value and has only input parameters.
j. Persistent stored...