Class Overview

This DB2 training class uses a hands-on approach to teach application programmers and designers the essential concepts of DB2 SQL. Upon completion of this DB2 class, students will understand DB2 relational databases, be able to execute both simple and complex queries using DB2 SQL, and be able to create and manipulate table data.

Class Goals

* Understand the basic concepts and facilities of DB2

* Be able to create and execute simple SQL statements

* Be able to create the desired results table using an SQL select statement

* Be able to restrict rows using WHERE clause

* Sort output data into desired sequence

* Learn basic CASE Expressions

* Learn the Data and Time formats

* Learn to format data using Scalar functions

* Be able to obtain aggregate data using GROUP Functions

* Learn the limitations and capabilities of SQL Group Functions

* Know how to use subqueries

* Create both simple and complex subqueries

* Be able to identify and use likely join column relationships

* Be able to show ownership of table columns

* Know how to use the WHERE clause conditions to limit the impact of

* Be able to combine the results from multiple queries using UNION

* Know how to execute a true Outer Join

* Be able to create a table and its columns

* Use both methods to Insert data

* Know the importance of the WHERE clause in Updates, and Deletes

* Be able to Insert, Update, and Delete rows

* Be able to use ROLLBACK / COMMIT when needed

Class Outline

1. DB2 Overview

1. DB2 Concepts & Facilities

2. Relational Concepts

3. Introduction to SQL

4. Basic SQL SELECT Statement

2. Using the Select Statement

1. Full Syntax of SELECT Statement

2. Result Tables

3. ALL vs. DISTINCT

4. Column Arithmetic

5. WHERE Clause

6. ORDER BY Clause

3. Case Expressions

1. Using CASE Expressions

4. Scalar Functions

1. DATE and TIME Formats

2. Scalar Functions

3. Special Registers

5. SQL Group Functions, and Group By

1. Group Functions

2. GROUP BY

3. HAVING

6. Subqueries

1. Simple Subqueries

2. Subquery with IN, ANY/SOME, ALL, EXISTS

3. Correlated Subqueries

7. Joins

1. Join Concepts and Keys

2. Joins vs Subqueries

3. Planning a Query

8. Union

1. Combining Result Tables

2. UNION

3. Outer Join using Subqueries

9. Creating Tables

1. CREATE TABLE

2. Primary / Foreign Keys

3. DROP TABLE

4. ALTER TABLE

10. SQL Data Manipulation

1. INSERT Single Rows into a Table

2. INSERT Multiple Rows into Tables with Queries

3. UPDATE One or More Rows

4. DELETE One or More Rows

5. ROLLBACK / COMMIT