Schulung - IBM CL732G - Db2 11.5 for LUW: SQL Workshop for Experienced Users

Course Outline

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

Objective

After completing this course, learners should be able to:

• Discuss basic relational database concepts
• Use some of the OLAP features of DB2, such as GROUP and RANK functions
• Code outer joins and join tables to themselves
• Use CASE expressions and the CAST function
• Write complex subqueries
• Code advanced SQL constructs, such as recursive SQL and table expressions