DB2 SQL Training

In this DB2 SQL Training training class, students will learn the DB2 SQL starting at the most basic level and going to the most advanced level. This course includes many examples.

Goals
  1. Gain a deeper knowledge and understanding of the DB2 SQL and how to write it.
Outline
  1. The Basics of SQL
    1. Introduction
    2. Finding Your Current Schema
    3. Setting Your Default SCHEMA
    4. SELECT * (All Columns) in a Table
    5. SELECT Specific Columns in a Table
    6. Place your Commas in front for better Debugging Capabilities
    7. Sort the Data with the ORDER BY Keyword
    8. ORDER BY Defaults to Ascending
    9. Use the Name or the Number in your ORDER BY Statement
    10. Two Examples of ORDER BY using Different Techniques
    11. Changing the ORDER BY to Descending Order
    12. NULL Values sort First in Ascending Mode (Default)
    13. NULL Values sort First in Descending Mode (DESC)
    14. Major Sort vs. Minor Sorts
    15. Multiple Sort Keys using Names vs. Numbers
    16. Sorts are Alphabetical, NOT Logical
    17. Using A CASE Statement to Sort Logically
    18. How to ALIAS a Column Name
    19. A Missing Comma can by Mistake become an Alias
    20. Comments using Double Dashes are Single Line Comments
    21. Comments for Multi-Lines
    22. Comments for Multi-Lines as Double Dashes per Line
    23. Formatting Number Examples
    24. Formatting Date Example
  2. The WHERE Clause
    1. Double Quoted Aliases are for Reserved Words and Spaces
    2. Character Data needs Single Quotes in the WHERE Clause
    3. Character Data needs Single Quotes, but Numbers Don't
    4. Comparisons against a Null Value
    5. Using Greater Than or Equal To (>=)
    6. AND in the WHERE Clause
    7. Troubleshooting AND OR in the WHERE Clause
    8. Troubleshooting Or
    9. Troubleshooting Character Data
    10. Using Different Columns in an AND Statement
    11. What is the Order of Precedence?
    12. Using Parentheses to change the Order of Precedence
    13. Using an IN List in place of OR
    14. The IN List is an Excellent Technique
    15. IN List vs. OR brings the same Results
    16. The IN List Can Use Character Data
    17. Using a NOT IN List
    18. Null Values in a NOT IN List Bring Back No Rows
    19. A Technique for Handling Nulls with a NOT IN List
    20. BETWEEN is Inclusive
    21. NOT BETWEEN is Also Inclusive
    22. LIKE uses Wildcards Percent ‘%' and Underscore ‘_'
    23. LIKE command Underscore is Wildcard for one Character
    24. LIKE Command Works Differently on Char Vs Varchar
    25. LIKE Command on Character Data Auto Trims
    26. Numbers are Right Justified and Character Data is Left
    27. An Example of Data with Left and Right Justification
    28. A Visual of CHARACTER Data vs. VARCHAR Data
    29. Use the TRIM command to remove spaces on CHAR Data
    30. Escape Character in the LIKE Command changes Wildcards
    31. Escape Characters Turn off Wildcards in the LIKE Command
    32. The Distinct Command
    33. Distinct vs. GROUP BY
    34. The FETCH Clause
    35. The FETCH Clause with an ORDER BY Clause
  3. Aggregation
    1. The 3 Rules of Aggregation
    2. There are Five Aggregates
    3. Troubleshooting Aggregates
    4. GROUP BY delivers one row per Group
    5. GROUP BY Dept_No Works GROUP BY 1 Fails
    6. Limiting Rows and Improving Performance with WHERE
    7. WHERE Clause in Aggregation limits unneeded Calculations
    8. Keyword HAVING tests Aggregates after they are totaled
    9. Keyword HAVING is like an Extra WHERE Clause for Totals
    10. Keyword HAVING tests Aggregates after they are totaled
    11. Getting the Average Values per Column
    12. Average Values per Column for all Columns in a Table
    13. GROUP BY GROUPING SETS Command
    14. GROUP BY Grouping Sets
    15. GROUP BY ROLLUP Command
    16. GROUP BY Rollup Result Set
    17. GROUP BY CUBE Command
    18. GROUP BY CUBE Result Set
  4. Join Functions
    1. A Two-Table Join Using Traditional Syntax
    2. A two-table join using Non-ANSI Syntax with Table Alias
    3. A two-table join using ANSI Syntax
    4. LEFT OUTER JOIN
    5. LEFT OUTER JOIN Results
    6. RIGHT OUTER JOIN
    7. RIGHT OUTER JOIN Example and Results
    8. FULL OUTER JOIN
    9. FULL OUTER JOIN Results
    10. INNER JOIN with Additional AND Clause
    11. ANSI INNER JOIN with Additional AND Clause
    12. ANSI INNER JOIN with Additional WHERE Clause
    13. OUTER JOIN with Additional WHERE Clause
    14. OUTER JOIN with Additional AND Clause
    15. OUTER JOIN with Additional AND Clause Results
    16. Evaluation Order for Outer Queries
    17. The DREADED Product Join
    18. The DREADED Product Join Results
    19. The Horrifying Cartesian product join
    20. The ANSI Cartesian Join will ERROR
    21. The CROSS JOIN
    22. The CROSS JOIN Answer Set
    23. The Self Join
    24. The Self Join with ANSI Syntax
    25. The 5-Table Join - Logical Insurance Model
    26. The Nexus Query Chameleon Writes the SQL for Users
  5. Using Nexus for DB2
    1. How to Use Nexus
    2. Why is Nexus Special?
    3. The Garden of Analysis
    4. Getting to the Super Join Builder
    5. The Super Join Builder is the First Entry in the Menu
    6. The Super Join Builder Shows Tables Visually
    7. Using the Add Join Button
    8. What to Do When No Tables are Joinable?
    9. Drag a Joinable Object into the Super Join Builder
    10. You will see the Add Custom Join Window
    11. Defining the Join Columns
    12. Your Tables Will Appear Together
    13. Select the Columns You Want on the Report
    14. Check out the SQL Tab to See the SQL that has been built
    15. SQL Tab
    16. Hit Execute to get the Report inside the Super Join Builder
    17. The Report is delivered inside the Super Join Builder
    18. The Tabs of the Super Join Builder
    19. Getting a Simple CSUM in the Analytics Tab - OLAP
    20. Getting a Simple CSUM - The SQL Automatically Generated
    21. The Answer Set of the CSUM
    22. Getting all of the OLAP functions in the Analytics Tab
    23. A Five Table Join Using the Menu
    24. The First Table is placed in the Super Join Builder
    25. Using the Add Join Cascading Menu
    26. All Five Tables Are In the Super Join Builder
    27. A Five Table Join Two Steps (Cube)
    28. Choose Cube with Columns from the Left Top of the Table
    29. All Tables are Cubed (Joined Together Instantly)
    30. Choose Cube and then Choose Your Columns
    31. Create Cube - Tables Are Joined Without Columns Selected
    32. Create Cube - Select the Columns You Want on the Report
    33. How to join DB2, Oracle and SQL Server Tables
    34. The DB2 Table is now in the Super Join Builder
    35. Drag the Joining Oracle Table to the Super Join Builder
    36. Defining the Join Columns
    37. Choose the Columns You Want on Your Report
    38. Let's Add a SQL Server Table to our DB2 and Oracle Join
    39. Defining the Join Columns
    40. All Three Tables are now in the Super Join Builder
    41. Change the Hub and Run the Join on Oracle
    42. Change the Hub and Run the Join on SQL Server
    43. Simply Amazing - Change the Hub to the Garden of Analysis
    44. Have the Answer Set Saved Automatically to Any System
    45. Saving the Answer
  6. Date Functions
    1. Getting the System Date
    2. Extracting From a Timestamp
    3. The EXTRACT Command
    4. Using the EXTRACT Command to Extract Month, Day, Year
    5. Extracting From a Date Column
    6. Extracting the Date and Time from the Timestamp
    7. Formatting Dates Example
    8. Formatting Date Standards
    9. Adding and Subtracting Days from a Date
    10. Adding Years, Months, Days, Hours and Seconds
    11. Using the Add_Months Command
    12. Adding Years to a Date
    13. Add Five Years to a Date
    14. Converting Character Data to a Date or Time
    15. Timestamp DAYOFWEEK, DAYNAME and MONTHNAME
    16. Finding Orders That Happened on a Friday
    17. NEXT_DAY Command Finds a Future Day of the Week
    18. Finding the Last Day of a Month
    19. Finding the Last Day of the Previous Month
    20. Getting the First Day of the Month
    21. Finding the Number of Days between Two Dates
    22. Resetting the Microseconds Back to Zero
    23. Turning Date and Time into Characters
    24. Converting Character Data to a Timestamp
    25. Finding Differences between Timestamps
    26. Differences between Timestamps Fractions of a Second
    27. Find Differences between Timestamp Seconds and Minutes
    28. Find Differences between Timestamp Hours and Days
    29. Find Differences between Timestamp Weeks and Months
    30. Find Differences between Timestamp Quarters and Years
    31. Formatting Dates
    32. Formatting Dates Example
    33. Formatting Timestamp Example
  7. OLAP Functions
    1. The Row_Number Command
    2. Using a Derived Table and Row_Number
    3. Ordered Analytics OVER
    4. RANK and DENSE RANK
    5. RANK Defaults to Ascending Order
    6. Getting RANK to Sort in DESC Order
    7. RANK OVER and PARTITION BY
    8. Finding Gaps between Dates
    9. CSUM - Rows Unbounded Preceding Explained
    10. CSUM - Making Sense of the Data
    11. CSUM - Making Even More Sense of the Data
    12. CSUM - The Major and Minor Sort Key(s)
    13. The ANSI CSUM - Getting a Sequential Number
    14. Reset with a PARTITION BY Statement
    15. PARTITION BY only Resets a Single OLAP not ALL of them
    16. CURRENT ROW AND UNBOUNDED FOLLOWING
    17. Different Windowing Options
    18. Moving Sum has a Moving Window
    19. How ANSI Moving SUM Handles the Sort
    20. Moving SUM every 3-rows Vs a Continuous Average
    21. PARTITION BY Resets an ANSI OLAP
    22. The Moving Window is Current Row and Preceding
    23. Moving Average
    24. Moving Average Using a CAST Statement
    25. Moving Average every 3-rows Vs a Continuous Average
    26. PARTITION BY Resets an ANSI OLAP
    27. Moving Difference
    28. Moving Difference using ANSI Syntax with Partition By
    29. COUNT OVER for a Sequential Number
    30. COUNT OVER without Rows Unbounded Preceding
    31. The MAX OVER Command
    32. MAX OVER with PARTITION BY Reset
    33. MAX OVER without Rows Unbounded Preceding
    34. The MIN OVER Command
    35. MIN OVER without Rows Unbounded Preceding
    36. MIN OVER Using PARTITION BY to Reset
    37. Finding a Value of a Column in the Next Row with MIN
    38. The CSUM for Each Product_Id and the Next Start Date
    39. Using FIRST_VALUE
    40. FIRST_VALUE
    41. FIRST_VALUE after Sorting by the Highest Value
    42. FIRST_VALUE with Partitioning
    43. FIRST_VALUE Combined with Row_Number
    44. FIRST_VALUE and Row_Number with Different Sort
    45. Using LAST_VALUE
    46. LAST_VALUE
    47. Using LAG and LEAD
    48. LEAD
    49. LEAD
    50. LEAD With Partitioning
    51. LEAD to Find the First Occurrence
    52. Using LEAD
    53. Using LEAD with an Offset of 2
    54. Using LAG
    55. Using LAG with an Offset of 2
    56. LAG
    57. LAG with Partitioning
    58. SUM (SUM (n))
  8. Temporary Tables
    1. There are two types of Temporary Tables
    2. CREATING A Derived Table
    3. Creating Multiple Derived Tables in the WITH Command
    4. Creating Multiple Derived Tables in the WITH Command
    5. The Same Derived Query shown Three Different Ways
    6. Most Derived Tables Are Used To Join To Other Tables
    7. The Three Components of a Derived Table
    8. Visualize This Derived Table
    9. Our Join Example with A Different Column Aliasing Style
    10. Column Aliasing Can Default For Normal Columns
    11. Our Join Example With the WITH Syntax
    12. Clever Tricks on Aliasing Columns in a Derived Table
    13. An Example of Two Derived Tables in a Single Query
    14. Example of Two Derived Tables in a Single WITH Statement
    15. WITH RECURSIVE Derived Table
    16. WITH RECURSIVE Final Answer Set
    17. Creating and Populating a Global Temporary Table
    18. Global Temporary Table Definitions Persist
    19. ON COMMIT DELETE ROWS Example
    20. Creating and Populating a Global Temporary Table
    21. Creating a Global Temporary Table Using a CTAS
    22. Creating a Global Temporary Table Using a CTAS Join
    23. A Global Temp Table That Populates Some of the Rows
    24. A Temporary Table with Some of the Columns
  9. Sub-query Functions
    1. An IN List is much like a Subquery
    2. An IN List Never has Duplicates - Just like a Subquery
    3. The Subquery
    4. The Three Steps of How a Basic Subquery Works
    5. These are Equivalent Queries
    6. The Final Answer Set from the Subquery
    7. Should you use a Subquery or a Join?
    8. The Basics of a Correlated Subquery
    9. The Top Query always runs first in a Correlated Subquery
    10. Correlated Subquery Example vs. a Join with a Derived Table
    11. How the Double Parameter Subquery Works
    12. More on how the Double Parameter Subquery Works
    13. How to handle a NOT IN with potential NULL Values
    14. Using a Correlated Exists
    15. How a Correlated Exists matches up
    16. The Correlated NOT Exists
  10. Strings
    1. The LENGTH Command
    2. The TRIM Command trims both Leading and Trailing Spaces
    3. A Visual of the TRIM Command Using Concatenation
    4. Trim and Trailing is Case Sensitive
    5. How to TRIM Trailing Letters
    6. The SUBSTRING Command
    7. How SUBSTRING Works with NO ENDING POSITION
    8. An Example using SUBSTRING, TRIM and CHAR Together
    9. Concatenation
    10. Concatenation and SUBSTRING
    11. Four Concatenations Together
    12. UPPER and LOWER Commands
    13. LPAD and RPAD
    14. SOUNDEX
  11. Interrogating the Data
    1. Using the LOWER Command
    2. Using the UPPER Command
    3. Non-Letters are Unaffected by UPPER and LOWER
    4. The COALESCE
    5. The Basics of CAST (Convert and Store)
    6. Some Great CAST (Convert and Store) Examples
    7. A Rounding Example
    8. Using an ELSE in the Case Statement
    9. Using an ELSE as a Safety Net
    10. Rules for a Valued Case Statement
    11. Rules for a Searched Case Statement
    12. Valued Case Vs. A Searched Case
    13. The CASE Challenge
    14. The CASE Challenge Answer
    15. Combining Searched Case and Valued Case
    16. A Trick for getting a Horizontal Case
    17. Nested Case
    18. Put a CASE in the ORDER BY
  12. View Functions
    1. The Fundamentals of Views
    2. Creating a Simple View to Restrict Sensitive Columns
    3. You SELECT From a View
    4. Creating a Simple View to Restrict Rows
    5. A View Provides Security for Columns and Rows
    6. Basic Rules for Views
    7. How to Modify a View
    8. An Exception to the ORDER BY Rule inside a View
    9. Views Are Sometimes CREATED for Formatting
    10. Creating a View to Join Tables Together
    11. How to Alias Columns in a View CREATE
    12. The Standard Way Most Aliasing is done
    13. What Happens When Both Aliasing Options Are Present
    14. Resolving Aliasing Problems in a View CREATE
    15. Answer to Resolving Aliasing Problems in a View CREATE
    16. Creating a View with a Local Check
    17. Aggregates on View Aggregates
    18. Altering a Table after a View Has Been Created
    19. A View that Errors after an ALTER
  13. Set Operators Functions
    1. Rules of Set Operators
    2. INTERSECT Explained Logically
    3. INTERSECT Explained Logically
    4. UNION Explained Logically
    5. UNION Explained Logically
    6. UNION ALL Explained Logically
    7. UNION ALL Explained Logically
    8. EXCEPT Explained Logically
    9. EXCEPT Explained Logically
    10. Minus Explained Logically
    11. Minus Explained Logically
    12. An Equal Amount of Columns in both SELECT List
    13. Columns in the SELECT list should be from the same Domain
    14. The Top Query handles all Aliases
    15. The Bottom Query does the ORDER BY
    16. Great Trick:Place your Set Operator in a Derived Table
    17. UNION Vs UNION ALL
    18. A Great Example of how MINUS works
    19. USING Multiple SET Operators in a Single Request
    20. Changing the Order of Precedence with Parentheses
    21. Using UNION ALL for speed in Merging Data Sets
  14. Table Create and Data Types
    1. The Basics of Creating a Table
    2. Creating a Table with NOT NULL Constraints
    3. Creating a Table with a UNIQUE Constraint
    4. Creating a Unique Index
    5. Creating a Clustered Index
    6. Creating a Partitioned Table for a Range on a Date Column
    7. Creating a Partitioned Table for a Number
    8. Declaring a Global Temporary Table
    9. Declaring a Global Temporary Table that Persists Using Like
    10. Creating a Table with an XML Data Type
    11. Creating a Table with a CHECK Constraint
    12. Creating a Table with Default Values
    13. Creating a Table with Multiple Constraints
    14. Defining Primary Keys
    15. Defining a Primary Key after the Table Has Been Created
    16. Defining a Foreign Key after the Table Has Been Created
    17. Creating a Table with an Identity Column that is Unique
    18. Creating a Table with an Identity Column that is Non-Unique
    19. Creating a Sequence
    20. Altering a Table to Add a Column
    21. Altering a Table to Drop a Column
    22. Renaming a Table
    23. Dropping a Table
    24. Creating a Table Using a CTAS or a LIKE
    25. Creating a Table Using a CTAS Join
    26. Data Types
  15. Data Manipulation Language (DML)
    1. INSERT Syntax # 1
    2. INSERT example with Syntax 1
    3. INSERT Syntax # 2
    4. INSERT example with Syntax 2
    5. INSERT/SELECT Command
    6. INSERT/SELECT example using All Columns (*)
    7. INSERT/SELECT example with Less Columns
    8. Two UPDATE Examples
    9. Subquery UPDATE Command Syntax
    10. Example of Subquery UPDATE Command
    11. The DELETE Command Basic Syntax
    12. Example of Subquery DELETE Command
    13. Example of Subquery DELETE That Gets Rid of Null Values
  16. Statistical Aggregate Functions
    1. Numeric Manipulation Functions
    2. The Stats Table
    3. The VARIANCE Function
    4. A VARIANCE Example
    5. The CORR Function
    6. A CORR Example
    7. The REGR_INTERCEPT Function
    8. A REGR_INTERCEPT Example
    9. The REGR_SLOPE Function
    10. A REGR_SLOPE Example
    11. The REGR_AVGX Function
    12. A REGR_AVGX Example
    13. The REGR_AVGY Function
    14. A REGR_AVGY Example
    15. The REGR_COUNT Function
    16. A REGR_COUNT Example
    17. The REGR_R2 Function
    18. A REGR_R2 Example
    19. The REGR_SXX Function
    20. A REGR_SXX Example
    21. The REGR_SXY Function
    22. A REGR_SXY Example
    23. The REGR_SYY Function
    24. A REGR_SYY Example
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Preparing for Class

Training for your Team

Length: 3 Days
  • Private Class for your Team
  • Online or On-location
  • Customizable
  • Expert Instructors

What people say about our training

Great class! Cleared all my previous doubts related to the application and gave me a very good sense of how information flows in the application!
Neeraj Jain
Iridium Satellite
My Instructor made the experience of learning programming enjoyable and easy. Kudos!
Kyle Towers
Bureau of Land Management
The trainer assigned to our class was truly an expert.
Tito Diaz
Crown World Mobility
Webucator provided me with the training I needed just as well as any brick-and-mortar class I've been to. My instructor was amazing, and I was able to stay in the comfort of my own home and I didn't have to deal with traffic!!
Danelle Lipke
US Navy

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

61,008

Students who have taken Instructor-led Training

11,712

Organizations who trust Webucator for their Instructor-led training needs

100%

Satisfaction guarantee and retake option

9.29

Students rated our trainers 9.29 out of 10 based on 28,955 reviews

Contact Us or call 1-877-932-8228