Unit 1: SQL Introduction

• • Identify the purpose of the clauses in the SELECT statement
• • Describe the key differences among the IBM Db2 platforms
• • Describe and use some of the OLAP features of Db2 such as GROUPING functions like CUBE and ROLLUP, and the RANK, DEFENSE_RANK, and ROW_NUMBER functions

Unit 2: Create Objects

• • Code statements to:
  • • Create tables and views
  • • Alter tables
  • • Create indexes
  • • Implement referential integrity (RI)
  • • Define triggers and check constraints
• • Identify impacts and advantages of referential integrity, including impacts of delete rules
• • Identify considerations when using triggers and check constraints
• • Identify the advantages of views

Unit 3: JOIN

• • Use inner and outer joins to retrieve data from more than one table
• • Use joins of tables to themselves

Unit 4: CASE, CAST, Summary (Materialized Query) Tables, and Temporary Tables

• • Identify when CASE expressions can be useful
• • Code CASE expressions in the SELECT list and in the WHERE clause
• • Identify when CAST specifications can be used
• • Identify the advantages of using Summary (Materialized Query) Tables and Temporary tables

Unit 5: Using Subqueries

• • Code subqueries using the ALL, ANY / SOME, and EXISTS keywords
• • Code correlated subqueries
• • Choose the proper type of subquery to use in each case

Unit 6: Scalar Functions

• • Extend your knowledge of scalar functions which:
  • • Manipulate arithmetic data
  • • Manipulate date values
  • • Manipulate character data

Unit 7: Table Expressions and Recursive SQL

• • Identify reasons for table expressions and recursive SQL
• • Use nested and common table expressions
• • Identify the difference between views and table expressions
• • Code recursive SQL
• • Control the depth of recursion when coding recursive SQL

Unit 8: An Introduction to UDTs / UDFs and Stored Procedures

• • Describe the concepts behind User-Defined District Types, User-Defined Functions, and Stored Procedures

Unit 9: SQL and Db2 Performance

• • Explain some basic principles of Db2 performance
• • Code SQL statements to obtain better performance