DB2 SQL Training
For pricing and availability
Contact: Tom Coffing
Email: Tom.Coffing@CoffingDW.com
Phone: 513 300-0341
Description
You can expect three things from a Tom Coffing DB2 SQL class: hands-on, hands-on, and hands-on training. Students will begin each chapter covering the fundamentals and continue to step up to the most advanced techniques in a building block approach. Each student can create tables on a DB2 system, run thousands of queries, and join many tables combined with subqueries, derived tables, date functions and formats, and advanced analytics. Welcome to one of the best classes you will ever attend.
Reference Guide
The IBM DB2 SQL book by Tom Coffing and Leslie Nolander is a 623-page masterpiece of SQL that will be the backbone of the class and provide an excellent reference guide for all your development on a DB2 system.
Objectives
Whether you are new to SQL or already have enormous experience, Tom Coffing will enrich your knowledge immensely. One thing is for sure: you will be extremely confident and proficient at writing SQL on a DB2 system.
Audience, prerequisites, and duration
This course is for everyone who has the opportunity to work on a DB2 system, from beginners to experts. There are never any prerequisites in a Tom Coffing classroom because Tom has mastered mixing new and experienced students within the same classroom. The course ranges from 2-5 days, depending on your needs, but the more days you choose, the more incredible the outcome will be, as we will customize the classes based on your needs.
Topics
Basic SQL Functions
The WHERE Clause
Distinct Vs. Group By
Aggregation
Group by Grouping Sets, Rollup, and Cube
Joins
Date and Time Functions
Format Functions
Analytics and Window Functions
Temporary Tables
Subqueries
Strings
Interrogating the Data
Views
Set Operators
Creating Tables
Data Manipulation Language (DML)
Math Functions
Statistical Aggregate Functions
DB2 SQL Course Outline
Chapter 1 – The Basics of SQL
Introduction
Finding Your Current Schema
Setting Your Default SCHEMA
SELECT * (All Columns) in a Table
SELECT Specific Columns in a Table
Commas in the Front or Back?
Place your Commas in front for better Debugging Capabilities
Sort the Data with the ORDER BY Keyword
ORDER BY Defaults to Ascending
Use the Name or the Number in your ORDER BY Statement
Two Examples of ORDER BY using Different Techniques
Changing the ORDER BY to Descending Order
NULL Values sort First in Ascending Mode (Default)
NULL Values sort First in Descending Mode (DESC)
Major Sort vs. Minor Sorts
Multiple Sort Keys using Names vs. Numbers
Sorts are Alphabetical, NOT Logical
Using A CASE Statement to Sort Logically
How to ALIAS a Column Name
A Missing Comma can by Mistake become an Alias
Comments using Double Dashes are Single Line Comments
Comments for Multi-Lines
Comments for Multi-Lines As Double Dashes Per Line
Formatting Number Examples
Formatting Date Example
Chapter 2 – The WHERE Clause
The WHERE Clause limits Returning Rows
Double Quoted Aliases are for Reserved Words and Spaces
Character Data needs Single Quotes in the WHERE Clause
Character Data needs Single Quotes, but Numbers Don’t
Comparisons against a Null Value
NULL means UNKNOWN DATA so Equal (=) won’t Work
Use IS NULL or IS NOT NULL when dealing with NULLs
NULL is UNKNOWN DATA so NOT Equal won’t Work
Use IS NULL or IS NOT NULL when dealing with NULLs
Using Greater Than or Equal To (>=)
AND in the WHERE Clause
Troubleshooting AND
OR in the WHERE Clause
Troubleshooting Or
Troubleshooting Character Data
Using Different Columns in an AND Statement
Quiz – How many rows will return?
Answer to Quiz – How many rows will return?
What is the Order of Precedence?
Using Parentheses to change the Order of Precedence
Using an IN List in place of OR
The IN List is an Excellent Technique
IN List vs. OR brings the same Results
The IN List Can Use Character Data
Using a NOT IN List
Null Values in a NOT IN List Bring Back No Rows
A Technique for Handling Nulls with a NOT IN List
BETWEEN is Inclusive
NOT BETWEEN is Also Inclusive
LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
LIKE command Underscore is Wildcard for one Character
LIKE Command Works Differently on Char Vs Varchar
LIKE Command on Character Data Auto Trims
Quiz – What Data is Left Justified and What is Right?
Numbers are Right Justified and Character Data is Left
Answer – What Data is Left Justified and What is Right?
An Example of Data with Left and Right Justification
A Visual of CHARACTER Data vs. VARCHAR Data
Use the TRIM command to remove spaces on CHAR Data
Escape Character in the LIKE Command changes Wildcards
Escape Characters Turn off Wildcards in the LIKE Command
Quiz – Turn off that Wildcard
ANSWER – To Find that Wildcard
The Distinct Command
Distinct vs. GROUP BY
Quiz – How many rows come back from the Distinct?
Answer – How many rows come back from the Distinct?
The FETCH Clause
The FETCH Clause with an ORDER BY Clause
Chapter 3 – Aggregation
Quiz – You calculate the Answer Set in your own Mind
Answer – You calculate the Answer Set in your own Mind
Quiz – You calculate the Answer Set in your own Mind
Answer – You calculate the Answer Set in your own Mind
The 3 Rules of Aggregation
There are Five Aggregates
Quiz – How many rows come back?
Answer – How many rows come back?
Troubleshooting Aggregates
GROUP BY Delivers one row per Group
GROUP BY Dept_No Works GROUP BY 1 Fails
Limiting Rows and Improving Performance with WHERE
WHERE Clause in Aggregation limits unneeded Calculations
Keyword HAVING tests Aggregates after they are Totaled
Keyword HAVING is like an Extra WHERE Clause for Totals
Keyword HAVING tests Aggregates after they are Totaled
Getting the Average Values Per Column
Average Values Per Column For all Columns in a Table
GROUP BY GROUPING SETS Command
GROUP BY Grouping Sets
GROUP BY ROLLUP Command
GROUP BY Rollup Result Set
GROUP BY CUBE Command
GROUP BY CUBE Result Set
Quiz – GROUP BY GROUPING SETS Challenge
Answer To Quiz – GROUP BY GROUPING SETS Challenge
Chapter 4 – Joins
A Two-Table Join Using Traditional Syntax
A two-table join using Non-ANSI Syntax with Table Alias
You Can Fully Qualify All Columns
A two-table join using ANSI Syntax
Both Queries have the same Results and Performance
Quiz – Can You Finish the Join Syntax?
Answer to Quiz – Can You Finish the Join Syntax?
Quiz – Can You Find the Error?
Answer to Quiz – Can You Find the Error?
Super Quiz – Can You Find the Difficult Error?
Answer to Super Quiz – Can You Find the Difficult Error?
Quiz – Which rows from both tables won’t Return?
Answer to Quiz – Which rows from both tables Won’t Return?
LEFT OUTER JOIN
LEFT OUTER JOIN Results
RIGHT OUTER JOIN
RIGHT OUTER JOIN Example and Results
FULL OUTER JOIN
FULL OUTER JOIN Results
Which Tables are the Left and which Tables are Right?
Answer – Which Tables are the Left and Which are the Right?
INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional AND Clause
ANSI INNER JOIN with Additional WHERE Clause
OUTER JOIN with Additional WHERE Clause
OUTER JOIN with Additional AND Clause
OUTER JOIN with Additional AND Clause Results
Quiz – Why is this considered an INNER JOIN?
Evaluation Order for Outer Queries
The DREADED Product Join
The DREADED Product Join Results
The Horrifying Cartesian Product Join
The ANSI Cartesian Join will ERROR
Quiz – Do these Joins Return the Same Answer Set?
Answer – Do these Joins Return the Same Answer Set?
The CROSS JOIN
The CROSS JOIN Answer Set
The Self Join
The Self Join with ANSI Syntax
Quiz – Will both queries bring back the same Answer Set?
Answer – Will both queries bring back the same Answer Set?
Quiz – Will both queries bring back the same Answer Set?
Answer – Will both queries bring back the same Answer Set?
How would you Join these two tables?
An Associative Table is a Bridge that Joins Two Tables
Quiz – Can you write the 3-Table Join?
Answer to Quiz – Can you Write the 3-Table Join?
Quiz – Can you write the 3-Table Join to ANSI Syntax?
Answer – Can you Write the 3-Table Join to ANSI Syntax?
Quiz – Can you Place the ON Clauses at the End?
Answer – Can you Place the ON Clauses at the End?
The 5-Table Join – Logical Insurance Model
Quiz – Write a Five Table Join Using ANSI Syntax
Answer – Write a Five Table Join Using ANSI Syntax
Quiz – Write a Five Table Join Using Non-ANSI Syntax
Answer – Write a Five Table Join Using Non-ANSI Syntax
Quiz –Re-Write this putting the ON clauses at the END
Answer –Re-Write this putting the ON clauses at the END
The Nexus Query Chameleon Writes the SQL for Users.
Chapter 5 – Date Functions
Getting the System Date
Extracting From a Timestamp
The EXTRACT Command
Using the EXTRACT Command to Extract Month, Day, Year
Extracting From a Date Column
Extracting the Date and Time From the Timestamp
Formatting Dates Example
Formatting Date Standards
Adding and Subtracting Days from a Date
Adding Years, Months, Days, Hours and Seconds
Using the Add_Months Command
Adding Years to a Date
Add Five Years to a Date
Converting Character Data to a Date or Time
Timestamp DAYOFWEEK, DAYNAME and MONTHNAME
Finding Orders That Happened on a Friday
NEXT_DAY Command Finds a Future Day of the Week
Finding The Last Day of a Month
Finding The Last Day of the Previous Month
Getting The First Day of the Month
Finding the Number of Days Between Two Dates
Resetting the Microseconds Back to Zero
Turning Date and Time into Characters
Converting Character Data to a Timestamp
Finding Differences Between Timestamps
Differences Between Timestamps Fractions of a Second
Find Differences Between Timestamp Seconds and Minutes
Find Differences Between Timestamp Hours and Days
Find Differences Between Timestamp Weeks and Months
Find Differences Between Timestamp Quarters and Years
Formatting Dates
Formatting Dates Example
Formatting Dates Example
Formatting Dates Example
Formatting Timestamp Example
Formatting Timestamp Example
Chapter 6 – Analytic and Window Functions
The Row_Number Command
Quiz – How did the Row_Number Reset?
Quiz – How did the Row_Number Reset?
Using a Derived Table and Row_Number
Ordered Analytics OVER
RANK and DENSE RANK
RANK Defaults to Ascending Order
Getting RANK to Sort in DESC Order
RANK() OVER and PARTITION BY
Finding Gaps Between Dates
CSUM – Rows Unbounded Preceding Explained
CSUM – Making Sense of the Data
CSUM – Making Even More Sense of the Data
CSUM – The Major and Minor Sort Key(s)
The ANSI CSUM – Getting a Sequential Number
Reset with a PARTITION BY Statement
PARTITION BY only Resets a Single OLAP not ALL of them
CURRENT ROW AND UNBOUNDED FOLLOWING
Different Windowing Options
Moving Sum has a Moving Window
How ANSI Moving SUM Handles the Sort
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Moving SUM every 3-rows Vs a Continuous Average
Partition By Resets an ANSI OLAP
The Moving Window is Current Row and Preceding
Moving Average
Moving Average Using a CAST Statement
Moving Average every 3-rows Vs a Continuous Average
Partition By Resets an ANSI OLAP
Moving Difference
Moving Difference using ANSI Syntax with Partition By
COUNT OVER for a Sequential Number
COUNT OVER Without Rows Unbounded Preceding
Quiz – What caused the COUNT OVER to Reset?
Answer to Quiz – What caused the COUNT OVER to Reset?
The MAX OVER Command
MAX OVER with PARTITION BY Reset
MAX OVER Without Rows Unbounded Preceding
The MIN OVER Command
MIN OVER Without Rows Unbounded Preceding
MIN OVER Without Rows Unbounded Preceding
Finding a Value of a Column in the Next Row with MIN
The CSUM For Each Product_Id and the Next Start Date
Quiz – Fill in the Blank
Answer – Fill in the Blank
Using FIRST_VALUE
FIRST_VALUE
FIRST_VALUE After Sorting by the Highest Value
FIRST_VALUE with Partitioning
FIRST_VALUE Combined with Row_Number
FIRST_VALUE And Row_Number with Different Sort
Using LAST_VALUE
LAST_VALUE
Using LAG and LEAD
LEAD
LEAD
LEAD With Partitioning
LEAD to Find the First Occurrence
Using LEAD
Using LEAD With an Offset of 2
Using LAG
Using LAG With an Offset of 2
LAG
LAG with Partitioning
SUM(SUM(n))
Chapter 7 – Temporary Tables
There are Two types of Temporary Tables
CREATING A Derived Table
Creating Multiple Derived Tables in the WITH Command
Creating Multiple Derived Tables in the WITH Command
The Same Derived Query shown Three Different Ways
Most Derived Tables Are Used To Join To Other Tables
The Three Components of a Derived Table
Visualize This Derived Table
Our Join Example With A Different Column Aliasing Style
Column Aliasing Can Default For Normal Columns
Our Join Example With The WITH Syntax
Quiz – Answer the Questions
Answer to Quiz – Answer the Questions
Clever Tricks on Aliasing Columns in a Derived Table
An Example of Two Derived Tables in a Single Query
Example of Two Derived Tables in a Single WITH Statement
WITH RECURSIVE Derived Table Hierarchy
WITH RECURSIVE Derived Table Query
WITH RECURSIVE Derived Table Definition
WITH RECURSIVE Derived Table Seeding
WITH RECURSIVE Derived Table Looping
WITH RECURSIVE Derived Table Looping in Slow Motion
WITH RECURSIVE Derived Table Looping Continued
WITH RECURSIVE Derived Table Looping Continued
WITH RECURSIVE Derived Table Ends the Looping
WITH RECURSIVE Derived Table Definition
WITH RECURSIVE Final Answer Set
Creating and Populating a Global Temporary Table
Global Temporary Table Definitions Persist
ON COMMIT DELETE ROWS Example
Creating and Populating a Global Temporary Table
Creating a Global Temporary Table Using a CTAS
Creating a Global Temporary Table Using a CTAS Join
A Global Temp Table That Populates Some of the Rows
A Temporary Table With Some of the Columns
Chapter 8 – Subqueries
An IN List is much like a Subquery
An IN List Never has Duplicates – Just like a Subquery
The Subquery
The Three Steps of How a Basic Subquery Works
These are Equivalent Queries
The Final Answer Set from the Subquery
Quiz- Answer the Difficult Question
Answer to Quiz- Answer the Difficult Question
Should you use a Subquery or a Join?
Quiz- Write the Subquery
Answer to Quiz- Write the Subquery
Quiz- Write the More Difficult Subquery
Answer to Quiz- Write the More Difficult Subquery
Quiz – Write the Extreme Subquery
Answer to Quiz- Write the Extreme Subquery
Quiz- Write the Subquery with an Aggregate
Answer to Quiz- Write the Subquery with an Aggregate
Quiz- Write the Correlated Subquery
Answer to Quiz- Write the Correlated Subquery
The Basics of a Correlated Subquery
The Top Query always runs first in a Correlated Subquery
Correlated Subquery Example vs. a Join with a Derived Table
Quiz- A Second Chance To Write a Correlated Subquery
Answer – A Second Chance to Write a Correlated Subquery
Quiz- A Third Chance To Write a Correlated Subquery
Answer – A Third Chance to Write a Correlated Subquery
Quiz- Last Chance To Write a Correlated Subquery
Answer – Last Chance to Write a Correlated Subquery
Quiz – Write the Extreme Correlated Subquery
Answer To Quiz – Write the Extreme Correlated Subquery
Quiz- Write the NOT Subquery
Answer to Quiz- Write the NOT Subquery
Quiz- Write the Subquery using a WHERE Clause
Answer – Write the Subquery using a WHERE Clause
Quiz- Write the Subquery with Two Parameters
Answer to Quiz- Write the Subquery with Two Parameters
How the Double Parameter Subquery Works
More on how the Double Parameter Subquery Works
Quiz – Write the Triple Subquery
Answer to Quiz – Write the Triple Subquery
Quiz – How many rows return on a NOT IN with a NULL?
Answer – How many rows return on a NOT IN with a NULL?
How to handle a NOT IN with Potential NULL Values
IN is equivalent to =ANY
Using a Correlated Exists
How a Correlated Exists matches up
The Correlated NOT Exists
Chapter 9 – Strings
The LENGTH Command Counts Characters
The LENGTH Command – Spaces can Count too
The LENGTH Command and Char(20) Data
The LENGTH Needs a TRIM
The TRIM Command trims both Leading and Trailing Spaces
A Visual of the TRIM Command Using Concatenation
Trim and Trailing is Case Sensitive
How to TRIM Trailing Letters
The SUBSTRING Command
How SUBSTRING Works with NO ENDING POSITION
An Example using SUBSTRING, TRIM and CHAR Together
Concatenation
Concatenation and SUBSTRING
Four Concatenations Together
UPPER and LOWER Commands
LPAD and RPAD
SOUNDEX
Chapter 10 – Interrogating the Data
Using the LOWER Command
Using the UPPER Command
Non-Letters are Unaffected by UPPER and LOWER
Quiz – Fill in the Answers for the NULLIF Command
Quiz – Fill in the Answers for the NULLIF Command
The COALESCE Command
The COALESCE Answer Set
The COALESCE Command – Fill In the Answers
The COALESCE Answer Set
The COALESCE Command – Fill In the Answers
The COALESCE Answer Set
COALESCE is Equivalent to This CASE Statement
The Basics of CAST (Convert And STore)
Some Great CAST (Convert And Store) Examples
A Rounding Example
Quiz – The Basics of the CASE Statements
Answer to Quiz – The Basics of the CASE Statements
Using an ELSE in the Case Statement
Using an ELSE as a Safety Net
Rules For a Valued Case Statement
Rules For a Searched Case Statement
Valued Case Vs. A Searched Case
Quiz – Valued Case Statement
Answer – Valued Case Statement
Quiz – Searched Case Statement
Answer – Searched Case Statement
The CASE Challenge
The CASE Challenge Answer
Combining Searched Case and Valued Case
A Trick for getting a Horizontal Case
Nested Case
Put a CASE in the ORDER BY
Chapter 11 – Views
The Fundamentals of Views
Creating a Simple View to Restrict Sensitive Columns
You SELECT From a View
Creating a Simple View to Restrict Rows
A View Provides Security for Columns and Rows
Basic Rules for Views
How to Modify a View
An Exception to the ORDER BY Rule inside a View
Views Are Sometimes CREATED for Formatting
Creating a View to Join Tables Together
How to Alias Columns in a View CREATE
The Standard Way Most Aliasing is Done
What Happens When Both Aliasing Options Are Present
Resolving Aliasing Problems in a View CREATE
Answer to Resolving Aliasing Problems in a View CREATE
Creating a View with a Local Check
Aggregates on View Aggregates
Altering A Table After a View Has Been Created
A View that Errors After An ALTER
Chapter 12 – Set Operators
Rules of Set Operators
INTERSECT Explained Logically
INTERSECT Explained Logically
UNION Explained Logically
UNION Explained Logically
UNION ALL Explained Logically
UNION ALL Explained Logically
EXCEPT Explained Logically
EXCEPT Explained Logically
Minus Explained Logically
Minus Explained Logically
An Equal Amount of Columns in both SELECT List
Columns in the SELECT list should be from the same Domain
The Top Query handles all Aliases
The Bottom Query does the ORDER BY
Great Trick: Place your Set Operator in a Derived Table
UNION Vs UNION ALL
A Great Example of how MINUS works
USING Multiple SET Operators in a Single Request
Changing the Order of Precedence with Parentheses
Using UNION ALL for speed in Merging Data Sets
Chapter 13 – Table Create and Data Types
The Basics of Creating a Table
Creating a Table with NOT NULL Constraints
Creating a Table with a UNIQUE Constraint
Creating a Unique Index
Creating a Clustered Index
Creating a Partitioned Table for a Range on a Date Column
Creating a Partitioned Table for a Number
Declaring a Global Temporary Table
Declaring a Global Temporary Table that Persists Using Like
Creating a Table with an XML Data Type
The Eight Restrictions on XML Columns in a Table
Creating a Table with a CHECK Constraint
Creating a Table With Default Values
Creating a Table With Multiple Constraints
Defining Primary Keys
Defining a Primary Key After the Table Has Been Created
Defining a Foreign Key After the Table Has Been Created
Creating a Table with an Identity Column that is Unique
Creating a Table with an Identity Column that is Non-Unique
Creating a Sequence
Altering a Table to Add a Column
Altering a Table to Drop a Column
Renaming a Table
Dropping a Table
Creating a Table Using a CTAS or a LIKE
Creating a Table Using a CTAS Join
Data Types
Data Types Continued
Data Types Continued
Data Types Continued
Data Types Continued
Chapter 14 – Data Manipulation Language (DML)
INSERT Syntax # 1
INSERT example with Syntax 1
INSERT Syntax # 2
INSERT example with Syntax 2
INSERT/SELECT Command
INSERT/SELECT example using All Columns (*)
INSERT/SELECT example with Less Columns
Two UPDATE Examples
Subquery UPDATE Command Syntax
example of Subquery UPDATE Command
The DELETE Command Basic Syntax
Example of Subquery DELETE Command
Example of Subquery DELETE That Gets Rid of Null Values
Chapter 15 – Statistical Aggregate Functions
Numeric Manipulation Functions
The Stats Table
The VARIANCE Function
A VARIANCE Example
The CORR Function
A CORR Example
Another CORR Example so you can Compare
The REGR_INTERCEPT Function
A REGR_INTERCEPT Example
Another REGR_INTERCEPT Example so you can Compare
The REGR_SLOPE Function
A REGR_SLOPE Example
Another REGR_SLOPE Example so you can Compare
The REGR_AVGX Function
A REGR_AVGX Example
Another REGR_AVGX Example so you can Compare
The REGR_AVGY Function
A REGR_AVGY Example
Another REGR_AVGY Example so you can Compare
The REGR_COUNT Function
A REGR_COUNT Example
The REGR_R2 Function
A REGR_R2 Example
The REGR_SXX Function
A REGR_SXX Example
The REGR_SXY Function
A REGR_SXY Example
The REGR_SYY Function
A REGR_SYY Example