Advanced SQL Query Building - Topical Outline

The Sample Data Base
Review of Relational Concepts: Select, Project, Join
Access Strategies in DB2
EXPLAIN, and the Use of Indexes
   Computer Exercise: EXPLAIN and PLAN_TABLEs

Performance Considerations
   DB2 Processing Sequence
   Predicate Characteristics
   Indexable, Stage 1, and Stage 2 Predicates
   Filter Factors

Aggregated Queries
   Built-in Column Functions
   GROUP BY
   HAVING
   ORDER BY
        Computer Exercise: Data Aggregates

Special registers
Dates, Times, Timestamps, and Durations
Buil-in Scalar Functions
Nested Table Expressions
       Computer Exercise: Special registers and scalar functions

Joins
    Name specification
    Primary Keys and Foreign Keys
   Joins on non-key columns
    Joins and aggregates
   Join Access Strategies
       Computer Exercise: Joins

Joins, Part 2
   Outer joins
   Outer joins and nested table expressions
   The "classic" join syntax
   Influencing the Join Access Path
       Computer Exercise: Outer Joins

Subqueries
      IN and NOT IN
      Arithmetic Comparison
      ANY and ALL
      EXISTS and NOT EXISTS
Correlated subqueries
   Finding mismatches between tables
Subquery access strategies
   Computer Exercise: Subqueries

UNION and UNION ALL
    Query parallellism and EXPLAIN
EXPLAINing a Plan or Package
Programming considerations
    Static and Dynamic SQL
    Cursor issues
   Computer Exercise: UNION and Plan EXPLAINS

G-555 / 2 Days                                                        These Materials © Copyright 2007 by Hunter Cobb    V6.x

Course Objectives
Course description
Home page

this page last updated: 10 February, 2007
this page copyright © 2007 by The Trainer's Friend, Inc.