Writing SQL Queries for Db2

Creating reports, analytics, or applications? And need to get data out of Db2? Learn to write SQL queries like a pro !

Course Summary

Do you need to learn how to write SQL queries for Db2?

You know that the information that you need is stored in a Db2 database
You need to find some data to extract it
You need to create reports with reporting tools
You are using analytic tools like Power BI, Tableau, QlikView, Excel, or Access and need to get data from Db2
You want to learn to write queries properly, using commercial coding standards
You are new to writing queries, or you are self-taught and want to make sure you are doing it correctly

If so, this course is for you! And as well as detailed instruction, the course also offers optional practical exercises and quizzes to reinforce your learning.

We encourage you to complete the practical exercises. We have tried to make this as easy as possible.

Enroll now for $95 USD !

Modules and Lessons

Module 0: Getting started

Who is this course for?
Who is Greg?
What will I learn in this course?
Overview of lab setup

Module 1: Introduction to querying Db2

Module introduction
What is Db2 ?
What SQL does Db2 use ?
Types of SQL Statements
What are databases ?
Db2 instances and databases
Connecting to Db2
Connecting to PopkornKraze with DBeaver
What is the Db2 CLP ?
Db2-specific help sources
Lab 1: Introduction to querying Db2
Quiz 1: Introduction to querying Db2

Module 2: Querying a single table

Module introduction
What are tables ?
Viewing the contents of a table
What are schemas ?
What is Popkorn Kraze ?
SELECT statements
Using aliases for columns and tables
Quoted identifiers
Using 2-part names
ORDER BY clause
Restricting output with FETCH
Paginating with OFFSET
Removing duplicates with DISTINCT
Querying literals, expressions, and functions
Lab 2: Querying a single table
Lab 2: Answers
Quiz 2: Querying a single table

Module 3: Filtering the rows to return

Module introduction
Filtering output with the WHERE clause
Finding patterns with LIKE
Logical operations with AND, OR, and NOT
Logical values with the boolean data type
Ranges of values with the BETWEEN operator
Checking lists of values with IN
Working with NULL values
What are user-defined functions ?
Lab 3: Filtering the rows to return
Lab 3: Answers
Quiz 3: Filtering the rows to return

Module 4: Working with queries

Module introduction
Using statement terminators
Working with scripts in DBeaver
Using query history
Copying and saving query results
Commenting code
Formatting your scripts for readability
Quiz 4: Working with queries

Module 5: Querying numbers

Module introduction
What are data types ?
Exact numbers
Approximate numbers
Numeric operators
Common numeric functions
Lab 5: Querying numbers
Lab 5: Answers
Quiz 5: Querying numbers

Module 6: Querying strings

Module introduction
String data types
Literal string values
String operators
Trimming strings with LTRIM, RTRIM, and TRIM
Changing case with UPPER, LOWER
Extracting parts of strings with LEFT, RIGHT, SUBSTRING
Determining the length of strings
Replacing substrings with REPLACE Replacing substrings with OVERLAY
Duplicating strings with LPAD and RPAD
Finding substrings with POSITION
Replacing characters with TRANSLATE
Lab 6: Querying strings
Lab 6: Answers
Quiz 6: Querying strings

Module 7: Querying dates and times

Module introduction
Date and time data types
Literal date and time values
Current date and time
Extracting components from date and time values
Adding and subtracting date and time values
Calculating date and time differences
Calculating ages
Get string names for date and time parts
Calculating beginning and end of month
Lab 7: Querying dates and times
Lab 7: Answers
Quiz 7: Querying dates and times

Module 8: Converting between data types

Module introduction
Replacing NULL values with COALESCE
Returning NULL When Equal with NULLIF
Comparing NULL values with IS DISTINCT FROM
Implicit vs explicit conversions
Converting data types with CAST
Other built-in data types
Lab 8: Converting between data types
Lab 8: Answers
Quiz 8: Converting between data types

Module 9: Aggregating data

Module introduction
Counting rows and columns with COUNT
Summarizing data with SUM, AVG, MIN, MAX
Summarizing data in sections with GROUP BY
Filtering returned groups with HAVING
Understanding logical query execution order
Numbering rows with window functions
Partitioning ranking and numbering with PARTITION BY
Other aggregates
Lab 9: Aggregating data
Lab 9: Answers
Quiz 9: Aggregating data

Module 10: Implementing logic

Module introduction
Using conditional expressions with CASE
Simplifying conditionals with GREATEST, LEAST
Querying system functions
Defining Variables and Assigning Values
Adding conditional logic with IF, ELSE
Creating loops with WHILE
Creating loops with FOR
Advanced loop options
Lab 10: Implementing logic Lab 10: Answers
Quiz 10: Implementing logic

Module 11: Querying multiple tables

Module introduction
Using CROSS JOIN
What is a primary key ?
What is a foreign key ?
Using INNER JOIN
Using modern join syntax
Using LEFT OUTER JOIN
Other OUTER join types
Joining more than two tables
Joining a table to itself (self join)
Joins without equality (non-equi joins)
Lab 11: Querying multiple tables
Lab 11: Answers
Quiz 11: Querying multiple tables

Module 12: Applying set operations to tables

Module introduction
Using UNION and UNION ALL
Excluding data with EXCEPT
Finding common data with INTERSECT
Selecting from set-returning functions
Repetitively selecting with lateral joins
Lab 12: Applying set operations to tables
Lab 12: Answers
Quiz 12: Applying set operations to tables

Module 13: Using subqueries

Module introduction
Using subqueries
Using scalar subqueries
Using lists from subqueries
Checking for existence with EXISTS subqueries
Using set-returning subqueries
Using VALUES row constructors
Simplifying queries with common table expressions (CTEs)
Using multiple CTEs in a single query
Lab 13: Using subqueries
Lab 13: Answers
Quiz 13: Using subqueries

Module 14: Working with stored procedures and system catalogs

Module introduction
What is a stored procedure ?
Executing stored procedures
Executing dynamic SQL statements
Querying the system catalog
Lab 14: Working with stored procedures and system catalogs
Lab 14: Answers
Quiz 14: Working with stored procedures and system catalogs

Module 15: Modifying data

Module introduction
Inserting data into a table
Inserting multiple rows at once with VALUES
What are default constraints ?
What are identity columns ?
Identifying the last value inserted
What is a check constraint ?
What is a unique constraint ?
Deleting rows from a table
Inserting rows with data from another table
Selecting rows into a new table
Truncating a table vs deleting all rows
Updating data in a table
Merging data into a table
Lab 15: Modifying data
Lab 15: Answers
Quiz 15: Modifying data

Module 16: Next steps

Summary and further steps

Enroll now for $95 USD !