Advanced SQL for DB2

Start SQL for DB2 Training Today!

Dashcourses has partnered with GoGoTraining to bring you this Self-paced Course
Set your own training schedule
Watch as many times as you want 24/7 for 60 days following purchase
Find out what Dashcourses' self-paced Advanced SQL for DB2 training is all about with no obligation - download Silverlight and View a Preview Module
Our self-paced training modules are viewable in Internet Explorer, Firefox and Safari

Training Description

This Advanced SQL for DB2 self-paced technical training course is designed for the seasoned SQL professional who wants to get more “bang for the buck” out of DB2, especially DB2 Database on the Linux, UNIX, and Windows platforms.

A common complaint is that so much money is sunk into relational database management systems, only to have them end up as glorified flat-file processors due to inefficient SQL and misguided application design.

Why select all the columns and all the rows only to have your application program (or third-party tool) do the filtering? Why select detail-level rows when what you want to see is summarized results?

This self-paced training course presents advanced SQL syntax in an easy-to-understand format, demystifying even the most complex of the latest features. It enables the attendee to return to work much better prepared to avail him/herself of the full power of the DB2 engine.

Training Objectives

Upon completing this self-paced Advanced SQL for DB2 technical training course, you should be able to:

Use relational database concepts and terminology fluently, and code simple to complex SELECT, INSERT, UPDATE, and DELETE statements
Differentiate between the syntax elements of subselect, fullselect, and select-statement, and the terms query and sub query, and code both correlated and noncorrelated subqueries
Identify various types of two-dimensional inputs to a query, including the two-dimensional output of a prior query, and combine and contrast multiple two-dimensional results with the UNION, INTERSECT, and EXCEPT set operators
Use advanced expressions and scalar functions, as well as concatenations and calculations, including sophisticated date-time manipulation and arithmetic
Differentiate between and code inner joins and left, right, and full outer joins, as well as Cartesian products
Perform aggregate processing using advanced column functions, grouping sets, and the super groups ROLLUP and CUBE
Code recursive SQL using common table expressions or views, and identify and avoid situations that cause infinite loops
Code SQL that utilizes the vast array of object-relational features available in DB2 for Linux, UNIX, and Windows, including data-integrity constraints, user-defined distinct types (UDT’s), user-defined functions (UDF’s), triggers, stored procedures, and compound SQL
Adhere to some simple guidelines to ensure that your SQL—whether extremely basic or impossibly complex—performs as well as possible

Prerequisites

Before taking this technical training, you should be able to code basic to intermediate SQL statements and have real-world experience coding such statements. You can develop these skills by attending SQL Fundamentals for DB2. Download the SQL Self-Test to see if you meet the course prerequisites.

Proficiency with Windows, Notepad (or another Windows-based text editor), the mouse, and the PC in general is assumed. For a Linux/UNIX audience, familiarity with equivalent tools and concepts is assumed.

Self-Paced Modules

Advanced SQL for DB2 Training Module 0: Overview

Course Description
Course Objectives
Prerequisites
Workshop Setup: If necessary, install DB2 and the SAMPLE database

Advanced SQL for DB2 Training Module 1a – Review of SQL Fundamentals

Concepts and Terminology
Simple SELECT Statements

ADVANCED SQL FOR DB2 TRAINING MODULE 1B – REVIEW OF SQL FUNDAMENTALS

Types of Joins
Aggregate Processing
Subqueries and Unions
Modification Statements

Advanced SQL for DB2 Training Module 2 – Subqueries

Syntax Elements
Noncorrelated and Correlated Subqueries
Subquery Predicates
Modification Statements with Subqueries
Workshop 2: Subqueries

Advanced SQL for DB2 Training Module 3 – Two-dimensional Matrices

Two-dimensional Input and Output
Views and Table Expressions
Literal Tables: The VALUES Clause

Advanced SQL for DB2 Training Module 4 – Set Operations

Union
Intersection
Difference
Workshop 3: VALUES, Table Expressions, and Set Operators

Advanced SQL for DB2 Training Module 5 –Advanced Types of Expressions

CASE Expression
CAST Specification
Labeled Duration
Scalar Fullselect

Advanced SQL for DB2 Training Module 6a – Advanced Functions and Calculations

Data-type Conversion with Scalar Functions
Numeric Scalar Functions and Arithmetic Operations

ADVANCED SQL FOR DB2 TRAINING MODULE 6B - ADVANCED FUNCTIONS AND CALCULATIONS

String Scalar Functions and Concatenation
Chronological Scalar Functions and Date-time Arithmetic
Miscellaneous Scalar Functions
Workshop 4: Expressions and Scalar Functions

Advanced SQL for DB2 Training Module 7 – Inner and Outer Joins

Syntax Options for Inner Joins
Left, Right, and Full Outer Joins
Filtering Rows in Outer Joins
Case Studies
Workshop 5: Inner and Outer Joins

Advanced SQL for DB2 Training Module 8 – Aggregation

Aggregate Processing
Advanced Column Functions
Grouping Sets and Super Groups
Workshop 6: Grouping Sets and Super Groups

Advanced SQL for DB2 Training Module 9 – Recursive SQL

What Is Recursion?
Recursion with Common Table Expressions
Recursion with Views
Rules for Recursive Queries
Workshop 7: Recursive SQL

Advanced SQL for DB2 Training Module 10 – Object-Relational Features

Database-managed Data Integrity
Triggers and Stored Procedures
User-defined Distinct Types (UDT’s)
User-defined Functions (UDF’s)
Topics Not (Yet) Covered
Workshop 8: User-defined Database Objects

Advanced SQL for DB2 Training Module 11 –Performance Considerations

Performance Monitoring
Database Design Guidelines
Application Design Guidelines
The Optimizer’s Three Primary Inputs
Explain: Visual and Otherwise
SQL Tweaking Techniques
Workshop 9: Visual Explain

Advanced SQL for DB2 Training Module 12 – Review and Summation

What you have learned
Where to go from here

Equipment Specification

DB2 needs to be installed and the SAMPLE database created. DB2 can be installed on Linux, UNIX, or Windows. For Linux and UNIX, your system programmer should be able to help you, and the same may be true for Windows.
If you are installing DB2 on your own machine, understand that installation is fraught with its own unique challenges, so allow plenty of time to get up and running before starting the course.
The free version of DB2 is called DB2 Express-C and can be found on ibm.com, currently at:
• http://www-01.ibm.com/software/data/db2/9/
Know that IBM changes their Web links frequently, so you may need to navigate around to find DB2 Express-C, if it is not at the above link.
I installed DB2 on Windows Vista Home Premium 32-bit and on Windows Vista Ultimate 64-bit. In both cases I had lots of setbacks. One resource that came in quite handy (and that ultimately guided me to two successful installations) was:
• http://www.clearviewinformatics.com/2008/04/23/installing-db2-express-c-on-windows/
The version I installed most recently is 9.5. DB2 9.7 is being released in June 2009, so download whatever is the current release; the workshops should not be affected. However, if you are using a version of DB2 that is significantly older than either of these (like DB2 version 8), the SAMPLE tables may have different contents and the workshops will be adversely affected. In that case, you might want to install the current version of DB2 instead of using the older version.
The SAMPLE database is used for the workshops and may be installed along with DB2. You can also install it after the fact by going to First Steps (under Set-up Tools) or by using the db2sampl line command. Again, your system programmer or online documentation should be able to help you with this.