DB2 SQL Workshop for Experienced Users

start > courses and registration > training feature sheet

code.

CE130

length.

2.5 days

type.

Instructor-Led

partner.

IBM

price.

$1,950

This course is the classroom delivered version of Instructor Led Online Course 3E130.

This course teaches you how to make use of advanced SQL techniques to access DB2 databases in different environments.This course is appropriate for customers working in all DB2 enviroments, that is z/OS, OS/390, VM/VSE, iSeries, Linux, UNIX, and Windows.

Chicago, IL	feb.2.2009 - feb.4.2009	register
Chicago, IL	feb.2.2009 - feb.4.2009	register

who can benefit

This intermediate course is for experienced SQL end users, application programmers, database administrators, and user support staff who need more advanced knowledge of SQL.

prerequisites

You should complete:

SQL Workshop (CE12) and have experience with coding and executing basic SQL statements.

skills gained

After completing this course, you should be able to:

	Discuss basic relational database concepts
	Use some of the OLAP features of DB2, such as GROUPing and RANKing functions
	Create tables, views and indexes
	Use referential integrity, check constraints and triggers
	Use outer joins, and join tables to themselves
	Use CASE expressions, and the CAST function
	Identify the impact of Summary Tables, Materialized Query Tables, and temporary tables
	Use complex subqueries
	Use a greater number of scalar SQL functions
	Use advanced SQL constructs, such as recursive SQL and table expressions
	Define User-Defined Distinct Types and User-Defined Functions
	Avoid several of the most common causes for poorly-performing SQL

ibm education advantage program eligibility:

Yes - IBM Education Pack - online account

related courses, exams and materials

	before related training and products

	CE030 DB2 Family Fundamentals
	CE120 SQL Workshop

course content details

	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, DENSE_RANK and ROW_NUMBER functions

	Create Objects

	Code statements to: Create tables and views, Alter tables, Create indexes, Implement referential integrity (RI), and 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

	Join

	Retrieve data from more than one table via inner and outer joins
	Use outer joins

	CASE, CAST, Summary Tables, and Materialized Query Tables

	Identify when CASE expressions can be used
	Code CASE expressions in 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

	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

	Scalar Functions

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

	Table Expressions and Recursive SQL

	Identify reasons for using 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

	UDTs/UDFs and Performance

	Describe the concepts behind User-Defined Types, User-Defined Functions and Stored Procedures
	Predict when queries will use indexes to get better performance

	Agenda - Day 1

	Welcome
	Unit 1 - Introduction (SQL Basics review, and OLAP features)
	Exercise 1
	Unit 2 - Create Objects
	Exercise 2
	Unit 3 - Join
	Exercise 3

	Day 2

	Unit 4 - CASE, CAST, Summary Tables, and MQTs
	Exercise 4
	Unit 5 - Using Subqueries
	Exercise 5
	Unit 6 - Scalar Functions
	Exercise 6

	Day 3

	Unit 7 - Table Expressions and Recursive SQL
	Exercise 7
	Unit 8 - UDTs/UDFs and Performance
	Exercise 8