Postgres 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 Postgres 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, understand performance tuning techniques, run thousands of queries, and join many tables combined with subqueries, derived tables, date functions and formats, and advanced analytics. We can also customize the course based on your needs. Welcome to one of the best classes you will ever attend.

Reference Guide

The Postgres SQL book by Tom Coffing is a 390-page masterpiece of clear explanations into every Postgres SQL statement. The PDF delivered to each student will be the backbone of the class and provide an excellent reference guide for all work done on any Postgres 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 Postgres system.

Audience, prerequisites, and duration

This course is for everyone who has the opportunity to work on a Postgres 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

SELECT FROM WHERE and ORDER BY
Advanced ORDER BY techniques
IN, Between, and LIKE
Distinct Vs. Group By
Aggregation
Group by Grouping Sets, Rollup, and Cube
Inner and Outer Joins
Date Functions and Formatting
Ordered Analytics
Window Functions
Subqueries
Correlated Subqueries
Exists and NOT Exists
Strings
Views
Interrogating Data
Create Table
Set Operators
Statistical Aggregates

Postgres SQL Course Outline

Chapter 1 – Basic SQL Functions

Nexus Desktop – Combining all tools to all databases
Find Your Current Schema
Seeing Your Current Search Path
Adding a Schema to Your Search Path
Introduction
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
Use a Column Name or Number in an ORDER BY Statement
Two Examples of ORDER BY using Different Techniques
Changing the ORDER BY to Descending Order
NULL Values sort Last 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 Valued CASE Statement to Sort Logically
Using A Searched CASE Statement to Sort Logically
Quiz – Can you Add a Minor Sort?
Answer – Can you Add a Minor Sort?
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
Comments are a Great Technique for Finding SQL Errors
Move Data to the Cloud Effortlessly

Chapter 2 – The WHERE Clause

NexusCore Servers – Control Network and Data Movement
The WHERE Clause limits Returning Rows
Using a Column ALIAS in the WHERE Clause Errors
Numbers Don’t Need Single Quotes
Searching for NULL Values Using Equality Returns Nothing
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
WHY OR must utilize the Column Name Each Time
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 Return No Rows
A Technique for Handling Nulls with a NOT IN List
The BETWEEN Statement is Inclusive
The NOT BETWEEN Statement is also Inclusive
The BETWEEN Statement Works for Character Data
LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
LIKE command Underscore is Wildcard for one Character
Finding Anyone Who Name End in ‘Y’
Escape Character in the LIKE Command changes Wildcards
Escape Characters Turn off Wildcards in the LIKE Command

Chapter 3 – Distinct Vs. Group By

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?

Chapter 4 – Aggregation

Quiz – You calculate the Answer Set in your Mind
Answer – You calculate the Answer Set in your Mind
Quiz 2 – You calculate the Answer Set in your Mind
Answer Quiz 2  – You calculate the Answer Set in your Mind
There are Five Aggregates
Quiz – How many rows come back?
Answer – How many rows come back?
Casting a Data Type
Troubleshooting Aggregates
GROUP BY when Aggregates and Normal Columns Mix
GROUP BY Delivers one row per Group
GROUP BY Dept_No or GROUP BY 1 the same thing
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
Three types of Advanced Grouping
GROUP BY Grouping Sets
GROUP BY Rollup
GROUP BY Rollup Result Set
GROUP BY Cube
GROUP BY Cube Result Set
Quiz – GROUP BY GROUPING SETS Challenge
Answer – GROUP BY GROUPING SETS Challenge
Group By Grouping Sets Challenge – The Final Answer Set

Chapter 5 – Join Functions

A two-table join using Non-ANSI 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 are the 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
The DREADED Product Join
Result Set of the DREADED Product Join
Cartesian Product Join with Traditional Syntax
Cartesian Product Join with ANSI Syntax
The CROSS JOIN
The CROSS JOIN Answer Set
The Self Join
An Associative Table is a Bridge that Joins Two Tables
Quiz – Can you Write the 3-Table Join with Traditional Join Syntax?
Answer to Quiz – Can you Write the 3-Table Join with Traditional Join Syntax?
Quiz – Can you Write the 3-Table Join Using 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 Traditional Syntax
Answer – Write a Five Table Join Using ANSI Syntax
Quiz –Re-Write this putting the ON clauses at the END
Answer –Re-Write this putting the ON clauses at the END

Chapter 6 – Date Functions

Current Date
Current_Date, Current_Time, and Current_Timestamp
Current_Time vs. LocalTime With Precision
Local_Time and Local_Timestamp With Precision
Now() Function
Add or Subtract Days from a date
The EXTRACT Command
EXTRACT from DATES and TIME
Using CAST to Convert Dates and Derived Datas
Using Day, Month, and Year Intervals
The Basics of a Simple Interval

Chapter 7 – Analytic and Window Functions

The Row_Number Command
Quiz – How did the Row_Number Reset?
Answer – How did the Row_Number Reset?
Find the Top Two Students Per Class_Code
The RANK Command
Getting RANK to Sort in DESC Order
RANK () OVER and PARTITION BY
RANK() OVER and a Derived Table
RANK() OVER and a WITH Derived Table
PERCENT_RANK () OVER with 14 rows in Calculation
PERCENT_RANK () OVER with 21 rows in Calculation
PERCENT_RANK() OVER and PARTITION BY
CSUM
CSUM – The Sort Explained
CSUM – Rows Unbounded Preceding Explained
The CSUM – Making Sense of the Data
CSUM – The Major and Minor Sort Key(s)
The ANSI OLAP – Reset with a PARTITION BY Statement
Totals and Subtotals through Partition BY
Moving SUM
Moving SUM every 3-rows Vs. a Continuous Average
Partition By Resets the Calculations
Moving Average
How the Moving Average is calculated
How the Sort works for Moving Average (MAVG)
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Quiz – How is that 4^th Row Calculated?
Answer to Quiz – How is that 4^th Row Calculated?
Moving Average every 3-rows Vs. a Continuous Average
Partition BY Resets an ANSI OLAP
Moving Difference using ANSI Syntax
Moving Difference using ANSI Syntax with Partition By
Finding a Value of a Column in the Next Row with MIN
Finding a Value of a Column in the Next Row with PARTITION BY
Finding Multiple Values of a Column in Upcoming Rows
Finding The Next Date using MAX
COUNT OVER for a Sequential Number
COUNT OVER using ROWS UNBOUNDED PRECEDING
The MAX OVER Command
MAX OVER with PARTITION BY Reset
The MIN OVER Command
The MIN OVER Command with PARTITION BY

Chapter 8 – Temporary Tables

CREATING A Derived Table
Naming the Derived Table
Aliasing the Column Names in the Derived Table
CREATING A Derived Table using the WITH Command
Derived Query Examples with Three Different Techniques
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 the WITH Syntax
An Example of Two Derived Tables in a Single Query
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 Ends the Looping
WITH RECURSIVE Derived Table Final Report
Creating a Temporary Table
Creating a Temporary Table using a CTAS
Dropping a Temporary Table

Chapter 9 – Sub-query Functions

An IN List is much like a Subquery
An IN List Never has Duplicates – Just like a Subquery
An IN List Ignores Duplicates
The Subquery
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 of 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 – Write the Extreme Correlated Subquery
NOT IN Subquery Returns Nothing when NULLs are Present
Fixing a NOT IN Subquery with Null Values
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
IN is equivalent to =ANY
Using a Correlated Exists
How a Correlated Exists matches up
The Correlated NOT Exists

Chapter 10 – Strings

UPPER and lower  Functions
The Char_Length Command Counts Characters
CHAR_LENGTH Works on Fixed Length Columns
CHAR_LENGTH and OCTET_LENGTH
The TRIM Command trims both Leading and Trailing Spaces
The RTRIM and LTRIM Command trims Spaces
TRIM can also TRIM Characters
Concat and Concat_WS for Concatenation
The SUBSTR and SUBSTRING Command
How SUBSTR Works with NO ENDING POSITION
Using SUBSTR and CHAR_LENGTH Together
The POSITION Command finds a Letters Position
The POSITION Command is brilliant with SUBSTR
The REPLACE Function
SOUNDEX Function to Find a Sound
The ASCII Function
The Reverse String Function
The RIGHT Function
The String Compare (STRCMP) Function

Chapter 11 – Interrogating the Data

Quiz – Fill in the Answers for the NULLIF Command
Quiz – Fill in the Answers for the NULLIF Command
The COALESCE Command
Quiz – COALESCE Command – Fill In the Answers
Answer – COALESCE Command – Fill In the Answers
COALESCE is Equivalent to this CASE Statement
The Basics of CAST (Convert And Store)
Some Great CAST (Convert And Store) Examples
A Rounding Example Using CAST
CAST will Round Values up or Down
Valued Case vs. Searched Case
Combining Searched Case and Valued Case
A Trick for getting a Horizontal Case
Put a CASE in the ORDER BY

Chapter 12 – View Functions

The Fundamentals of Views
Creating a Simple View to Restrict Sensitive Columns
Creating a Simple View to Restrict Rows
Creating a View to Join Tables Together
Basic Rules for Views
How to Modify a View
Exception to the ORDER BY Rule inside a View
Derived Columns in a View Should Contain a Column Alias
Another Way to Alias Columns in a View CREATE
What Happens When a View Column gets Aliased Twice?
Aggregates on View Aggregates

Chapter 13 – UNION Set Operator

UNION Explained Logically
UNION Explained Logically
UNION ALL Explained Logically
UNION Explained Logically
An Equal Amount of Columns in both SELECT List
The Top Query handles all Aliases
The Bottom Query does the ORDER BY
UNION vs. UNION ALL
Using UNION ALL and Literals
Using UNION ALL for speed in Merging Data Sets
Using UNION to be same as GROUP BY GROUPING SETS

Chapter 14 – Creating Tables

Creating a Database and a Table
Create a Table IF NOT EXISTS
A Table with a NOT NULL Constraint
Create a Table with Auto_Increment
Create a Table with a Column Default Value
Create a Table with a Primary Key
Creating a Primary Key/Foreign Key Constraint
Create Table Engine Options
Range Partitioning a Table
List Partitioning a Table
List Columns Partitioning a Table
Hash Partitioning a Table
Key Partitioning a Table
Subpartitioning a Table
CREATE TABLE LIKE
CREATE TABLE LIKE and the USE Database Command
CREATE a Temporary TABLE using LIKE
CREATE TABLE AS (CTAS) Populates the Table With Data
CREATE TABLE AS (CTAS) Can Choose Certain Columns
CREATE a Temporary Table AS (CTAS)
CREATE a Temporary Table AS (CTAS) Using a Join

Chapter 15 – Data Manipulation Language (DML)

INSERT Syntax # 1
INSERT Syntax # 2
INSERT Example with Multiple Rows
Inserting Null Values into a Table
INSERT/SELECT Command
INSERT/SELECT to Build a Data Mart
UPDATE Examples
Subquery UPDATE Command Syntax
Example of Subquery UPDATE Command
Deleting Rows in a Table

Chapter 16 – Statistical Aggregate Functions

The Stats Table
The STDDEV_POP Function
A STDDEV_POP Example
The STDDEV_SAMP Function
A STDDEV_SAMP Example
The VAR_POP Function
A VAR_POP Example
The VAR_SAMP Function
A VAR_SAMP Example