Netezza 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 Netezza 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 Netezza 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 Netezza SQL book by Tom Coffing and Mike Larkins is a 600-page masterpiece of SQL that will be the backbone of the class and provide an excellent reference guide for all your development on a Netezza 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 Netezza system.

Audience, prerequisites, and duration

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

Netezza SQL Course Outline

Chapter 1 – Introduction to SQL

Introduction
SELECT * (All Columns) in a Table
Fully Qualifying a Database, Schema and 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 First in Ascending Mode (Default)
Using the Nulls Last Command
NULL Values Sort Last in Descending Mode (DESC)
Using the Nulls First Command
Major Sort vs. Minor Sort
Multiple Sort Keys using Names vs. Numbers
An Order By That Uses an Expression
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?
Order By Decode
Quiz – Can you Add Two Minor Sorts Using Decode?
Answer – Can you Add Two Minor Sorts Using Decode?
How to ALIAS a Column Name
Using an Alias in the WHERE and ORDER BY Clause
A Missing Comma Can Become an Alias by Mistake
Using Limit to bring back a Sample
Using Limit With an ORDER BY Statement
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

Chapter 2 – The WHERE Clause

The WHERE Clause limits Returning Rows
Numbers Don’t Need Single Quotes
Not Equal
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
The Like Command Wildcards are Percent and Underscore
LIKE command Underscore is Wildcard for one Character
CASE Matters with the LIKE Command
Using LIKE for all Cases with Lower and Upper
Another Example of UPPER and LOWER
LIKE Command to Find Multiple Characters
LIKE Command to Find Either Character
Finding Anyone Whose Name Ends in ‘Y’
Escape Character in the LIKE Command changes Wildcards
Escape Characters Turn off Wildcards in the LIKE Command

Chapter 3 – Distinct, Group By, and Pivot

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?
Pivoting Data

Chapter 4 – Aggregation

Quiz – Calculate the Answer Set in your Mind
Answer – Calculate the Answer Set in your Mind
Quiz 2  – Calculate the Answer Set in your Mind
There are Five Aggregates
Quiz – How many Rows Return?
Answer – How many Rows Return?
Casting a Data Type
Troubleshooting Aggregates
GROUP BY Delivers One Row Per Group
GROUP BY DEPT_NO or GROUP BY 1 are Equivalent
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
GROUP BY GROUPING SETS
GROUP BY ROLLUP
GROUP BY CUBE
GROUP BY CUBE Answer Set

Chapter 5 – Joining Tables

Nexus Builds Your Join SQL Automatically
A Two-Table Join Using Traditional Syntax
A Join using Traditional Syntax with Table Alias
You Can Fully Qualify All Columns
A 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– 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 Right?
Answer – Which Tables are the Left and Which are 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
The DREADED Product Join Results
Cartesian Product Join with Traditional Syntax
Cartesian Product Join with ANSI Syntax
The CROSS JOIN
The CROSS JOIN Answer Set
The SELF JOIN
The SELF  JOIN with ANSI Syntax
An Associative Table is a Bridge that Joins Two Tables
Quiz – Can you Write the Three-Table Join?
Answer to Quiz – Can you Write the Three-Table Join?
Quiz –Write the Three-Table Join Using ANSI Syntax?
Answer –Write the Three-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 Five-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 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

Chapter 6 – Temporary Tables

Nexus Joins Snowflake Tables with Excel Worksheets
There are Three Types of Temporary Tables
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
Quiz – Derived Table Challenge
Answer to Quiz – Derived Table Challenge
An Example of Two Derived Tables Using WITH
Creating a Temporary Table
Creating a Temporary Table using a CTAS
CREATE Temporary Table AS (CTAS) with Specific Columns
CREATE a Temporary Table AS (CTAS) Using a Join
Dropping a Temporary Table
Alternative CREATE TEMPORARY TABLE Option
A CTAS Temp Table to Improve Zone Map Selectivity
Creating a Temp Table as a Cluster Based Table (CBT)
What Are External Tables?
External Tables Data Loading Formats
External Table Log Files
External Table Syntax
Importing Data Into Netezza Using an External Table

Chapter 7 – 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
The Three Steps of How a Basic Subquery Works
These are Equivalent Queries
The 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
NOT IN Subquery Returns Nothing when NULLs are Present
Fixing a NOT IN Subquery with Null Values
Quiz- Write the NOT IN 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 – Write the Subquery with Two Parameters
Quiz – Write the Two Parameter Subquery With an Aggregate
Answer – Two Parameter Subquery and an Aggregate
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 8 – Date Functions

CURRENT_DATE
CURRENT_TIME and CURRENT_TIMESTAMP
Adding or Subtracting Seconds From The Time
Netezza NOW() Function
Netezza TIMEOFDAY Function
A Summary of Math Operations on Dates
Using a Math Operation to find your Age in Years
Netezza TIMESTAMP Function
Add or Subtract Days from a Date
The ADD_MONTHS Command
ADD_MONTHS to Add a Year to a Date
ADD_MONTHS to Add Five Years to a Date
Formatting a Date
The TO_CHAR Command to Format Dollar Signs
The TO_CHAR Command for Formatting Numbers
TO_CHAR and TO_DATE Functions
The EXTRACT Command
EXTRACT from DATES and TIME
EXTRACT with DATE and TIME Literals
The DATE_PART Function
Implied Extract of Day, Month, and Year using TO_CHAR
Implied Extract Using Literals
A Side Title example with Reserved Words as an Alias
EXTRACT of the Month on Aggregate Queries
Using CASE and Extract to Reformat Dates
Using CAST and SUBSTRING to Reformat Dates
The Date_Trunc Function
DATE_TRUNC Command With Time
DATE_TRUNC Command With Dates
LAST_DAY
Next Day
First Day of Month and Last Day of the Previous Month
MONTHS_BETWEEN
Finding the Number of Days Between Two Timestamps
Netezza AGE Function
Using Day and Month Intervals
Interval Example with Leap Year
Converting a Timestamp into Time
Netezza TO_TIMESTAMP Function
Find What Day of the Week You Were Born
Time Zones
Setting Time Zones
Using Time Zones
The OVERLAPS Command
An OVERLAPS Example that Returns No Rows
The OVERLAPS Command using TIME
Conversion Functions
Conversion Function Templates
Conversion Function Templates Continued

Chapter 9 – Ordered Analytic Window Functions

Nexus Gives You Netezza Analytics for Free
ROW_NUMBER
Quiz – How did the Row_Number Reset?
Quiz – Return Best Two Students In Each CLASS_CODE
Answer– Return Best Two Students In Each Class Code
RANK
DENSE_RANK
RANK vs. DENSE_RANK
Getting RANK to Sort in DESC Order
Retrieving Only the Last Three Sales Per Product_ID
DENSE_RANK() OVER and PARTITION BY
PERCENT_RANK() OVER with 14 rows in Calculation
PERCENT_RANK() OVER with 21 rows in Calculation
PERCENT_RANK and PARTITION BY
Cumulative Sum
Cumulative Sum – The Sort Explained
Cumulative Sum – Rows Unbounded Preceding Explained
Cumulative Sum – Making Sense of the Data
Cumulative Sum – Major and Minor Sort Keys
Reset with a PARTITION BY Statement
Totals and Subtotals through Partition By
Moving SUM every 3-rows vs. a Continuous Average
Partition By Resets the Calculations
Moving Average
The Moving Window is Current Row and Preceding n
How Moving Average Handles the Order By
Quiz – How is that Total Calculated?
Answer to Quiz – How is that Total Calculated?
Quiz – How is that 4th Row Calculated?
Answer – How is that 4th Row Calculated?
Moving Average every 3-rows Vs. a Continuous Average
The Partition By Statement
Partition By Resets an Analytic
Moving Difference
Moving Difference with Partition By
Moving Difference With Lag
Finding a Value of a Column in the Next Row with MIN
Finding a Next Row Value with MIN and 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
Finding Gaps Between Dates
FIRST_VALUE
Using FIRST_VALUE
FIRST_VALUE With Partitioning
Daily_Sales Minus FIRST_VALUE With Partitioning
FIRST_VALUE After Sorting by the Highest Value
FIRST_VALUE and a Derived Table
Last_Value Can Be Confusing
Last_Value Working Properly
Last_Value With Partitioning
Last_Value And First_Value with Partitioning
First and Last Value Difference
Using LEAD
Using LEAD with a PARTITION Statement
Using LEAD With an Offset of 2
Using LEAD With an Offset of 2 and a PARTITION
Using LAG
Using LAG with a PARTITION Statement
Using LAG With an Offset of 2
Using Two LAG Statements
Using LAG With an Offset of 2 and a PARTITION
CUME_DIST
CUME_DIST With a Tie Value
CUME_DIST and a Derived Table
CUME_DIST and a Partition By Statement
CUME_DIST with 14 Rows
CUME_DIST With a Partition on 7 Rows
How Ntile Works
Ntile Example
Ntile Continued
Using Quantiles (Partitions of Four)
Using Deciles (Partitions of Ten)
NTILE With a Partition
Width_Bucket

Chapter 10 – Strings

UPPER and lower  Functions
The Length Command Counts Characters
LENGTH Does Not Work on Fixed Length Columns
OCTET_LENGTH
The TRIM Command Trims Leading and Trailing Spaces
The RTRIM and LTRIM Command Trims Spaces
Trim and Trailing is Case Sensitive
Concatenation
The SUBSTR and SUBSTRING Commands
How SUBSTR Works with NO ENDING POSITION
Using SUBSTR and LENGTH Together
The POSITION Command finds a Letters Position
The POSITION Command is brilliant with SUBSTR
STRPOS Function
The POSITION And STRPOS Do The Same Thing
SUBSTRING and POSITION Used Together In An UPDATE
LPAD and RPAD
The ASCII Function
Miscellaneous Character Functions – CHR
Miscellaneous Character Functions – INITCAP
Miscellaneous Character Functions – REPEAT
Miscellaneous Character Functions – TRANSLATE

Chapter 11 – Interrogating the Data

Quiz – Fill in the Answers for the NULLIF Command
Answer – Fill in the Answers for the NULLIF Command
The COALESCE Command
COALESCE is Equivalent to this CASE Statement
COALESCE in a Real-World Example
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
The DECODE Command
DECODE
A Trick for getting a Horizontal Case
Nested Case
Extreme CASE Challenge
Answer – Extreme CASE Challenge
Put a CASE in the ORDER BY
Using A Searched CASE Statement to Sort Logically
Quiz – Can you Add a Minor Sort?
Answer – Can you Add a Minor Sort?
Order By DECODE
Quiz – Can you Add Two Minor Sorts Using Decode?
CASE Challenge
Answer – CASE Challenge
Quiz – Advanced Case Challenge
Answer – Advanced Case Challenge
NVL Syntax
NVL Example Explained
NVL Is Often Used With Calculations
Comparisons of NVL
A Real-World NVL Example
NVL2 Syntax
NVL2 Example Explained
The NULLIFZERO Command
The ZEROIFNULL Quiz
Answer to the ZEROIFNULL Quiz

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
Sometimes We Create Views for Formatting
Basic Rules for Views
How to Modify a View
The Exception to the ORDER BY Rule inside a View
Derived Columns in a View Should Have a Column Alias
The Standard Way Most Aliasing is Done
Another Way to Alias Columns in a View CREATE
What Happens When a View Column gets Aliased Twice?
Altering A Table
Altering A Table After a View has been Created
A View that Errors After An ALTER

Chapter 13 – Set Operators

Rules of Set Operators
INTERSECT Explained Logically
Answer – Intersect Explained Logically
Quiz – Union Explained Logically
Answer – Union Explained Logically
Quiz – Union ALL Explained Logically
Answer – Union ALL Explained Logically
Quiz – Except Explained Logically
Answer – Except Explained Logically
Quiz – Minus Explained Logically
Answer – MINUS Explained Logically
Quiz – Testing Your Knowledge
Answer – Testing Your Knowledge
Rule 1 – Equal Number of Columns in Both SELECT Lists
Rule 2 –  Top Query Handles all Aliases
Rule 3 – The Bottom Query does the ORDER BY
Intersect Challenge
Answer – Intersect Challenge
Quiz – Set Operator Challenge
Answer – Set Operator Challenge
Quiz – Advanced Set Operator Challenge
Answer – Advanced Set Operator Challenge
UNION Vs. UNION ALL
Using UNION ALL and Literals
Using UNION ALL for Speed in Merging Data Sets
Great Trick:  Place your Set Operator in a Derived Table
A Great Example of how EXCEPT and MINUS work
Using UNION to be same as GROUP BY GROUPING SETS
USING Multiple SET Operators in a Single Request
Changing the Order of Precedence with Parentheses

Chapter 14 – Data Manipulation

INSERT Syntax # 1
INSERT Syntax # 2
These Three Statements are the Same
A Third Way of Doing an INSERT
Netezza Has Implemented the Default Values Clause
INSERT/SELECT Command
INSERT/SELECT to Build a Data Mart
UPDATE Examples
Example of Subquery UPDATE Command
MERGE
Rename a Table
Deleting Rows in a Table
Netezza Transactions
BEGIN Command
COMMIT Command
What Happens on a Transaction Error?
Can I See My Uncommitted Changes?
Until the Commit Others Can’t See Your Changes?
ROLLBACK Command
ROLLBACK Command in ACTION
DELETE
Two DELETE Examples
How to Undo A Delete
A Delete Example Query
How to Undo a Delete
How to Undo a Delete In Action

Chapter 15 – Tables, DDL, and Data Types

CREATE TABLE Syntax
Viewing the DDL
Netezza Tables – Distribution Key or Random Distribution
Table CREATE Examples with 4 different Distribution Keys
The Worst Mistake You Can Make For A Distribution Key
Good things to know about Table and Object Names
Netezza Data Types
Netezza Data Types in More Detail
Netezza Data Type Extensions
Reserved Names Within A Table
Column Attributes
Constraints
Constraints Continued
Column Level Constraint Example
Defining Constraints at the Table Level
Utilizing Default Values for a Table
CTAS (Create Table AS)
CTAS Facts
Using the CTAS (Create Table AS) Table For Co-Location
Altering a CTAS Table to Rename It
FPGA Card and Zone Maps – The Netezza Secret Weapon
How A CTAS with ORDER BY Improves Queries
A CTAS Major Sort Benefits over the Minor Sort
Altering A Table
Altering a Table Examples
Drop Table, Truncate, and Delete Compared
Creating and Dropping a Netezza Database
How to Determine the Database you are in?
Netezza Users
Altering a Netezza User
Reserved Words to find out about a User

Chapter 16 – Statistical Aggregate Functions

The Stats Table
The STDDEV_POP Function
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
The VARIANCE Function
A VARIANCE Example
Using GROUP BY

Chapter 17  – Stored Procedure Functions

Netezza Stored Procedures
Creating and Executing a Stored Procedure
Creating a Stored Procedure
Netezza Provides Multiple Ways to Run the  Stored Procedure
You Can Have Multiple BEGIN and END statements
How to Declare and Set a Variable
Declaring a Variable With A Value
Input Parameters
Input Parameters Using Character Data
Calling a Procedure With Multiple Input Parameters
CREATE OR REPLACE Procedure
IF THEN ELSE IF Techniques
An Easier Way for IF THEN ELSE is ELSIF or ELSEIF
Using Loops in Stored Procedures
Using Loops with Different EXIT strategies
Looping With The WHILE Statement
Stored Procedure Workshop
Stored Procedure Workshop
Stored Procedure Workshop Alternative Version
Using FOR to Loop

Chapter 18 – Mathematical Functions

Numeric Manipulation Functions
ACOS
ASIN
ATAN
ATAN2
Ceil
COS
COT
DEGREES
EXP
FACTORIAL
Floor
LN
MOD
PI
POW
RADIANS
ROUND
SIGN
SIN
SQRT
TAN
TANH