IB-Learning: practical, hands-on training for software developers, software testers, business analysts and project managers

Spotlight On

• Curriculums
  • Business Analysis
  • Business Skills
  • Mainframe
  • Relational Database Management Systems
  • Software Testing
  • Windows Desktop
  • WebSphere MQ
  • Project Management
  • IT Service Management
  
  
• Class Schedules
• Facility Rentals
• Special Offers!

DB2 Advanced SQL Workshop - D213 ( 2 Days )

Course Outline

Abstract/Overview

This hands-on course provides a thorough discussion of advanced SQL query coding for DB2 for OS/390 for UDB. Significant emphasis is placed on practical tips and techniques as well as performance considerations. Students gain hands-on experience through several computer-based exercises.

Audience - Who Should Attend?

This course is intended for anyone who needs to code complex SQL in interactive or programmed DB2 for OS/390 UDB environments.

Prerequisite

A basic working knowledge of DB2 SQL is assumed. The student is expected to have a solid understanding of how to code basic SQL SELECT statements. Ideally, the student should have completed either Information Balance's "DB2 Application Programming Workshop" or "“Introduction to QMF".

Objective

• Know how to use the built-in DB2 scalar and column functions.
• Learn when and how to code simple and correlated subselects.
• Understand the writing of efficient implicit and explicit inner Joins between two or more tables.
• Know when and how to code left, right, and full outer joins.
• Learn how to correctly code meaningful Cartesian Products and how to avoid invalid ones.
• Understand how to code intersection and difference operations using the EXISTS predicate.
• Know when and how to efficiently code UNION statements.
• Learn to write complex SQL statements involving combined subselects, unions, inner joins, outer joins, and/or nested table expressions.
• Understand performance issues regarding complex SQL statements.

Content

Introduction

• Basic SQL Review
  • DDL, DCL Statements
  • DML Statements
  • Basic SELECT
  • Renamed Columns
  • CASE Expressions
• Dynamic vs. Static SQL

• Scalar Functions
  • Character
  • Numeric/Mathematical
  • Date/Time
  • Large Object
• Column Functions
  • COUNT, BIG_COUNT, SUM, AVG, STDDEV, VARIANCE
  • GROUP BY Clause
  • HAVING Clause
  • User Defined Functions

• Simple Subselect
  • Single Row
  • Multiple Row
• Correlated Subselect
  • With Standard Predicates
  • With EXISTS Predicate

• Union, Union All
• Cartesian Joins
  • Useful Cartesian Products
  • Avoiding Accidental Cartesian Products
• Inner Joins
  • Implicit Inner Join
  • Explicit INNER JOIN Operator
  • Joins on Composite Keys
  • Coding Local Predicates
• Outer Joins
  • Implicit OUTER JOIN
  • Explicit OUTER JOIN
  • Left, Right, and Full Outer Joins
  • Coding Local Predicates with Table Expressions

• Multi-Table Joins
• Table Expressions with Summary Tables
• Nested Subselects
• Subselects in the HAVING Clause

• Performance and Tuning
• Query Considerations
  • General SQL Considerations
  • Specific SQL Considerations
  • Predicates
  • Multi-Table Selects
• Avoiding Sorts