Netezza SQL Training

Netezza SQL Training

Course Length: 3 days
Delivery Methods: Available as private class only
Course Benefits
  • Gain a deeper knowledge and understanding of the Netezza SQL and how to write it.
Course Overview

In this training class, students will learn the Netezza SQL starting at the most basic level and going to the most advanced level with many examples.

Course Outline
  1. Basic SQL Functions
    1. Introduction
    2. SELECT * (All Columns) in a Table
    3. Fully Qualifying a Database, Schema and Table
    4. SELECT Specific Columns in a Table
    5. Commas in the Front or Back?
    6. Using Good Form
    7. Using the Best Form for Writing SQL
    8. Place your Commas in front for better Debugging Capabilities
    9. Sort the Data with the ORDER BY Keyword
    10. ORDER BY Defaults to Ascending
    11. Use the Name or the Number in your ORDER BY Statement
    12. Two Examples of ORDER BY using Different Techniques
    13. Changing the ORDER BY to Descending Order
    14. NULL Values sort First in Ascending Mode (Default)
    15. NULL Values sort Last in Descending Mode (DESC)
    16. Major Sort vs. Minor Sorts
    17. Multiple Sort Keys using Names vs. Numbers
    18. Sorts are Alphabetical, NOT Logical
    19. Using A CASE Statement to Sort Logically
    20. How to ALIAS a Column Name
    21. A Missing Comma can by Mistake become an Alias
    22. Using Limit to bring back a Sample
    23. Comments using Double Dashes are Single Line Comments
    24. Comments for Multi-Lines
    25. Comments for Multi-Lines as Double Dashes Per Line
    26. A Great Technique for Comments to Look for SQL Errors
  2. The WHERE Clause
    1. The WHERE Clause limits Returning Rows
    2. Using a Column ALIAS throughout the SQL
    3. Double Quoted Aliases are for Reserved Words and Spaces
    4. Character Data needs Single Quotes in the WHERE Clause
    5. Character Data needs Single Quotes, but Numbers Don't
    6. NULL means UNKNOWN DATA so Equal (=) won't Work
    7. Use IS NULL or IS NOT NULL when dealing with NULLs
    8. NULL is UNKNOWN DATA so NOT Equal won't Work
    9. Use IS NULL or IS NOT NULL when dealing with NULLs
    10. Using Greater Than or Equal To (>=)
    11. AND in the WHERE Clause
    12. Troubleshooting AND
    13. OR in the WHERE Clause
    14. Troubleshooting OR
    15. OR must utilize the Column Name Each Time
    16. Troubleshooting Character Data
    17. Using Different Columns in an AND Statement
    18. What is the Order of Precedence?
    19. Using Parentheses to change the Order of Precedence
    20. Using an IN List in place of OR
    21. The IN List is an Excellent Technique
    22. IN List vs. OR brings the same Results
    23. Using a NOT IN List
    24. A Technique for Handling Nulls with a NOT IN List
    25. A Better Technique for Handling Nulls with a NOT IN List
    26. BETWEEN is Inclusive
    27. BETWEEN Works for Character Data
    28. LIKE uses Wildcards Percent '%' and Underscore '_'
    29. LIKE command Underscore is Wildcard for one Character
    30. LIKE Command Works Differently on Char Vs Varchar
    31. Troubleshooting LIKE Command on Character Data
    32. Introducing the TRIM Command
    33. Numbers are Right Justified and Character Data is Left
    34. An Example of Data with Left and Right Justification
    35. A Visual of CHARACTER Data vs. VARCHAR Data
    36. Use the TRIM command to remove spaces on CHAR Data
    37. TRIM Eliminates Leading and Trailing Spaces
    38. Escape Character in the LIKE Command changes Wildcards
    39. Escape Characters Turn off Wildcards in the LIKE Command
  3. Distinct Vs Group By
    1. The Distinct Command
    2. Distinct vs. GROUP BY
    3. Rules of Thumb for DISTINCT Vs GROUP BY
  4. Review
    1. Testing Your Knowledge 1-7
  5. Aggregation Function
    1. The 3 Rules of Aggregation
    2. There are Five Aggregates
    3. Troubleshooting Aggregates
    4. GROUP BY when Aggregates and Normal Columns Mix
    5. GROUP BY delivers one row per Group
    6. GROUP BY Dept_No or GROUP BY 1 the same thing
    7. Aggregates and Derived Data
    8. Limiting Rows and Improving Performance with WHERE
    9. WHERE Clause in Aggregation limits unneeded Calculations
    10. Keyword HAVING tests Aggregates after they are totaled
    11. Keyword HAVING is like an Extra WHERE Clause for Totals
    12. Getting the Average Values per Column
    13. Average Values per Column For all Columns in a Table
    14. Three types of Advanced Grouping
    15. GROUP BY Grouping Sets
    16. GROUP BY Rollup
    17. GROUP BY Rollup Result Set
    18. GROUP BY Cube
    19. GROUP BY CUBE Result Set
    20. GROUP BY CUBE Result Set
    21. Testing Your Knowledge
    22. Final Answer to Test Your Knowledge on Aggregates
  6. Join Functions
    1. A two-table join using Non-ANSI Syntax
    2. A two-table join using Non-ANSI Syntax with Table Alias
    3. Aliases and Fully Qualifying Columns
    4. A two-table join using Non-ANSI Syntax
    5. Both Queries have the same Results and Performance
    7. LEFT OUTER JOIN Example and Results
    9. RIGHT OUTER JOIN Example and Results
    11. FULL OUTER JOIN Example and Results
    12. Which Tables are the Left and which are the Right?
    13. INNER JOIN with Additional AND Clause
    14. ANSI INNER JOIN with Additional AND Clause
    15. ANSI INNER JOIN with Additional WHERE Clause
    16. OUTER JOIN with Additional WHERE Clause
    17. OUTER JOIN with Additional AND Clause
    18. OUTER JOIN with Additional AND Clause Example
    19. The DREADED Product Join
    20. The DREADED Product Join
    21. The Horrifying Cartesian product Join
    22. The ANSI Cartesian Join will ERROR
    23. The CROSS JOIN
    24. The CROSS JOIN Answer Set
    25. The Self Join
    26. The Self Join with ANSI Syntax
    27. How would you join these two tables?
    28. How would you join these two tables? You can't….Yet!
    29. An Associative Table is a Bridge that Joins Two Table
    30. The 5-Table Join - Logical Insurance Model
    31. The Nexus Query Chameleon Writes the SQL for Users
  7. Date Functions
    1. Date, Time, and Timestamp Keywords
    2. Add or Subtract Days from a date
    3. The to_char command
    4. Conversion Functions
    5. Conversion Function Templates
    6. Conversion Function Templates Continued
    7. Formatting a Date
    8. A Summary of Math Operations on Dates
    9. Using a Math Operation to find your Age in Years
    10. Find What Day of the week you were born
    11. The ADD_MONTHS Command
    12. Using the ADD_MONTHS Command to Add 1-Year
    13. Using the ADD_MONTHS Command to Add 5-Years
    14. Date Related Functions
    15. The EXTRACT Command
    16. EXTRACT from DATES and TIME
    17. EXTRACT with DATE and TIME Literals
    18. EXTRACT of the Month on Aggregate Queries
    19. A Side Title example with Reserved Words as an Alias
    20. Implied Extract of Day, Month, and Year
    21. DATE_PART Function
    22. DATE_PART Function using an ALIAS
    23. DATE_TRUNC Function
    24. DATE_TRUNC Function using TIME
    25. MONTHS_BETWEEN Function
    26. MONTHS_BETWEEN Function in Action
    27. ANSI TIME
    29. Netezza TIMESTAMP Function
    30. Netezza TO_TIMESTAMP Function
    31. Netezza NOW () Function
    32. Netezza TIMEOFDAY Function
    33. Netezza AGE Function
    34. Time Zones
    35. Setting Time Zones
    36. Using Time Zones
    37. Intervals for Date, Time, and Timestamp
    38. Using Intervals
    39. Troubleshooting the Basics of a Simple Interval
    40. Interval Arithmetic Results
    41. A Date Interval Example
    42. A Time Interval Example
    43. A - DATE Interval Example
    44. A Complex Time Interval Example using CAST
    45. A Complex Time Interval Example using CAST
    46. The OVERLAPS Command
    47. An OVERLAPS Example that Returns No Rows
    48. The OVERLAPS Command using TIME
    49. The OVERLAPS Command using a NULL Value
  8. OLAP Functions
    1. How ANSI Moving SUM Handles the Sort
    2. Moving SUM every 3-rows Vs a Continuous Average
    3. Partition by Resets an ANSI OLAP
    4. The ANSI Moving Window is Current Row and Preceding
    5. How ANSI Moving Average Handles the Sort
    6. Moving Average every 3-rows Vs a Continuous Average
    7. Partition by Resets an ANSI OLAP
    8. Moving Difference using ANSI Syntax
    9. Moving Difference using ANSI Syntax with Partition By
    10. RANK using ANSI Syntax Defaults to Ascending Order
    11. Getting RANK using ANSI Syntax to Sort in DESC Order
    13. RANK () OVER and LIMIT
    15. PERCENT_RANK () OVER with 14 rows in Calculation
    16. PERCENT_RANK () OVER with 21 rows in Calculation
    17. COUNT OVER for a Sequential Number
    18. Troubleshooting COUNT OVER
    19. The MAX OVER Command
    20. MAX OVER with PARTITION BY Reset
    21. Troubleshooting MAX OVER
    22. The MIN OVER Command
    23. Troubleshooting MIN OVER
    24. The Row_Number Command
    25. Standard Deviation Functions Using STDDEV / OVER
    26. Standard Deviation Functions and STDDEV / OVER Syntax
    27. STDDEV / OVER Example
    28. Variance Functions Using VARIANCE / OVER
    29. VARIANCE / OVER Syntax
    30. Using VARIANCE with PARTITION BY Example
    31. Using FIRST_VALUE and LAST_VALUE
    32. Using FIRST_VALUE
    33. Using LAST_VALUE
    34. Using LAG and LEAD
    35. Using LEAD
    36. Using LEAD With and Offset of 2
    37. Using LAG
    38. Using LAG with an Offset of 2
  9. Temporary Tables
    1. There are Three Types of Temporary Tables
    2. CREATING A Derived Table
    3. Naming the Derived Table
    4. Aliasing the Column Names in the Derived Table
    5. Multiple Ways to Alias the Columns in a Derived Table
    6. CREATING a Derived Table using the WITH Command
    7. Naming the Derived Table Columns using WITH
    8. The Same Derived Query shown Three Different Ways
    9. Most Derived Tables Are Used To Join To Other Tables
    10. Our Join Example with a Different Column Aliasing Style
    11. Column Aliasing Can Default For Normal Columns
    12. Our Join Example With the WITH Syntax
    13. Clever Tricks on Aliasing Columns in a Derived Table
    14. An Example of Two Derived Tables in a Single Query
    15. Syntax for Creating A Temporary Table
    16. Creating and Populating a Temporary Table
    17. A Temporary Table in Action
    18. A Temporary Table Can Be Used Again and Again
    19. Alternative CREATE TEMPORARY TABLE Option
    20. A CTAS Temp Table to Improve Zone Map Selectivity
    21. Creating a Temp Table as a Cluster Based Table (CBT)
    22. What Are External Tables?
    23. External Tables Data Loading Formats
    24. External Table Log Files
    25. External Table Syntax
    26. Exporting Data Off of Netezza into an External Table
    27. Importing Data into Netezza Using an External Table
    28. What is the Problem Here?
  10. Sub-query Functions
    1. An IN List is much like a Subquery
    2. An IN List Never has Duplicates - Just like a Subquery
    3. An IN List Ignores Duplicates
    4. The Subquery
    5. How a Basic Subquery Works
    6. The Final Answer Set from the Subquery
    7. The Basics of a Correlated Subquery
    8. The Top Query always runs first in a Correlated Subquery
    9. The Bottom Query runs last in a Correlated Subquery
    10. Correlated Subquery Example vs. a Join with a Derived Table
    11. Correlated Subquery that Finds Duplicates
    12. How the Double Parameter Subquery Works
    13. More on how the Double Parameter Subquery Works
    14. How to handle a NOT IN with Potential NULL Values
    15. IN is equivalent to =ANY
    16. Using a Correlated Exists
    17. How a Correlated Exists matches up
    18. The Correlated NOT Exists
    19. The Correlated NOT Exists Answer Set
  11. Substrings and Positioning Functions
    1. The LOWER Function
    2. The UPPER Function
    5. TRIM for Troubleshooting the CHARACTERS Command
    6. The TRIM Command trims both Leading and Trailing Spaces
    7. Trim and Trailing is Case Sensitive
    8. Trim and Trailing works if Case right
    9. Trim Combined with the CHARACTERS Command
    10. How to TRIM only the Trailing Spaces
    11. How to TRIM Trailing Letters
    12. How to TRIM Trailing Letters and use CHARACTER_Length
    13. LTRIM Function
    14. RTRIM Function
    15. BTRIM Function
    16. The SUBSTRING Command
    18. Using SUBSTRING to move backwards
    19. How SUBSTRING Works with a Starting Position of -1
    20. How SUBSTRING Works with an Ending Position of 0
    21. An Example using SUBSTRING, TRIM, and CHAR Together
    22. SUBSTRING and SUBSTR are equal, but use different syntax
    23. The POSITION Command finds a Letters Position
    24. STRPOS Function
    25. The POSITION and STRPOS Do the Same Thing
    26. SUBSTRING and POSITION Used Together In an UPDATE
    27. The POSITION Command is brilliant with SUBSTRING
    28. Using the SUBSTRING to Find the Second Word On
    29. Concatenation
    30. Concatenation and SUBSTRING
    31. Four Concatenations Together
    32. Troubleshooting Concatenation
    33. Miscellaneous Character Functions - ASCII
    34. Miscellaneous Character Functions - CHR
    35. Miscellaneous Character Functions - INITCAP
    36. Miscellaneous Character Functions - REPEAT
    37. Miscellaneous Character Functions - TRANSLATE
    38. Character Padding Functions - LPAD Function
    39. Character Padding Functions - RPAD Function
  12. Interrogating the Data
    1. NVL Syntax
    2. NVL Example
    3. NVL Is Often Used With Calculations
    4. Comparisons of NVL
    5. A Real-World NVL Example
    6. NVL2 Syntax
    7. NVL2 Example
    8. NVL2 Syntax
    9. A Real-World NVL2 Example
    10. DECODE Syntax
    11. DECODE Example
    12. A Real-World DECODE Example
    13. The COALESCE Command
    14. The COALESCE Answer Set
    15. The Basics of CAST (Convert and Store)
    16. Some Great CAST (Convert and Store) Examples
    17. Round Function
    18. Round Function Continued
    19. The Basics of the CASE Statements
    20. The Basics of the CASE Statement shown visually
    21. Valued Case Vs. A Searched Case
    22. When an ELSE is present in CASE Statement
    23. When NO ELSE is present in CASE Statement
    24. When an Alias is NOT used in a CASE Statement
    25. When an Alias is NOT used in a CASE Statement
    26. Combining Searched Case and Valued Case
    27. A Trick for getting a Horizontal Case
    28. Nested Case
    29. Put a CASE in the ORDER BY
  13. View Functions
    1. Creating a Simple View
    2. Basic Rules for Views
    3. Views sometimes CREATED for Formatting or Row Security
    4. Another Way to Alias Columns in a View CREATE
    5. Resolving Aliasing Problems in a View CREATE
    6. Resolving Aliasing Problems in a View CREATE
    7. Resolving Aliasing Problems in a View CREATE
    8. CREATING Views for Complex SQL such as Joins
    9. WHY certain columns need Aliasing in a View
    10. Using a WHERE Clause When Selecting From a View
    11. Altering a Table
    12. Altering a Table after a View has been created
    13. A View that Errors after an ALTER
    14. Troubleshooting a View
  14. 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 Explained Logically
    8. EXCEPT Explained Logically
    9. EXCEPT Explained Logically
    10. Minus Explained Logically
    11. Minus Explained Logically
    12. Testing Your Knowledge
    13. Testing Your Knowledge
    14. An Equal Amount of Columns in both SELECT List
    15. Columns in the SELECT list should be from the same Domain
    16. The Top Query handles all Aliases
    17. The Bottom Query does the ORDER BY (a Number)
    18. Great Trick:Place your Set Operator in a Derived Table
    20. Using UNION ALL and Literals
    21. A Great Example of how EXCEPT works
    22. USING Multiple SET Operators in a Single Request
    23. Changing the Order of Precedence with Parentheses
    24. Using UNION to be same as GROUP BY GROUPING SETS
    25. Using UNION to be same as GROUP BY ROLLUP
    26. Using UNION to be the same as GROUP BY Cube
    27. Using UNION to be same as GROUP BY Cube
  15. Data Manipulations
    1. Netezza Transactions
    2. BEGIN Command
    3. COMMIT Command
    4. What Happens on a Transaction Error?
    5. Can I See My Uncommitted Changes?
    6. Until the Commit Others Can't See Your Changes?
    7. ROLLBACK Command
    8. ROLLBACK Command in ACTION
    9. INSERT Command
    10. INSERT With Keyword Null
    11. A Different Syntax for the INSERT Statements
    12. These Three Statements are the same
    13. A Third Way of Doing an INSERT
    14. Netezza Has Implemented the Default Values Clause
    16. INSERT/SELECT Examples
    17. Another Syntax for the INSERT/SELECT
    18. INSERT/SELECT Used To CREATE A Data Mart
    19. UPDATE
    20. An UPDATE in Action
    21. An UPDATE with Multiple WHERE and AND Clauses
    22. An UPDATE with Multiple WHERE and AND Clauses
    23. UPDATE Using a Subquery
    24. UPDATE Using A Join
    25. DELETE
    26. Two DELETE Examples
    27. DELETE Through a Subquery or Join
    28. DELETE Through a Subquery and A Join Examples
    29. Multi-Statement Example
    30. How to Undo a Delete
    31. A Delete Example Query
    32. How to Undo a Delete
    33. How to Undo a Delete in Action
  16. Tables, DDL, and Data Types
    1. CREATE TABLE Syntax
    2. Viewing the DDL
    3. Netezza Tables - Distribution Key or Random Distribution
    4. Table CREATE Examples with 4 different Distribution Keys
    5. The Worst Mistake You Can Make For a Distribution Key
    6. Good things to know about Table and Object Names
    7. Netezza Data Types
    8. Netezza Data Types in More Detail
    9. Netezza Data Type Extensions
    10. Reserved Names within a Table
    11. How to Query and See Non-Active Rows
    12. Column Attributes
    13. Constraints
    14. Column Level Constraint Example
    15. Defining Constraints at the Table Level
    16. Utilizing Default Values for a Table
    17. CTAS (Create Table AS)
    18. CTAS Facts
    19. Using the CTAS (Create Table AS) Table for Co-Location
    20. Altering a CTAS Table to Rename It
    21. FPGA Card and Zone Maps - The Netezza Secret Weapon
    22. How A CTAS with ORDER BY Improves Queries
    23. A CTAS Major Sort Benefits over the Minor Sort
    24. Altering a Table
    25. Altering a Table Examples
    26. Drop Table, Truncate, and Delete Compared
    27. Creating and Dropping a Netezza Database
    28. How to Determine the Database you are in?
    29. Netezza Users
    30. Altering a Netezza User
    31. Reserved Words to find out about a User
  17. Statistical Aggregate Functions
    1. The Stats Table
    2. The STDDEV_POP Function
    3. A STDDEV_POP Example
    4. The STDDEV_SAMP Function
    5. A STDDEV_SAMP Example
    6. The VAR_POP Function
    7. A VAR_POP Example
    8. The VAR_SAMP Function
    9. A VAR_SAMP Example
    10. Using GROUP BY
    11. A Great Query Example
  18. Stored Procedure Functions
    1. Netezza Stored Procedures
    2. Creating and Executing a Stored Procedure
    3. Creating a Stored Procedure
    4. Netezza Provides Multiple Ways to Run the Stored Procedure
    5. You Can Have Multiple BEGIN and END statements
    6. How to Declare and Set a Variable
    7. Declaring a Variable with a Value
    8. Input Parameters
    9. Input Parameters Using Character Data
    10. Calling a Procedure with Multiple Input Parameters
    11. CREATE OR REPLACE Procedure
    12. IF THEN ELSE IF Techniques
    13. An Easier Way for IF THEN ELSE is ELSIF or ELSEIF
    14. Using Loops in Stored Procedures
    15. Using Loops with Different EXIT strategies
    16. Looping With the WHILE Statement
    17. Stored Procedure Workshop
    18. Stored Procedure Workshop
    19. Using a FOR Loop
  19. Nexus Query Chameleon
    1. The Old Nexus Logo
    2. The New Nexus Logo
    3. Watch the Video on the new Nexus Super Join Builder
    4. How to Customize your System Tree View
    5. Introducing the new Nexus Super Join Builder
    6. Define your Joins and tell Nexus to Add and Remember Me"
    7. Nexus knows what Tables Join together
    8. Nexus Presents Tables and their columns in Color
    9. Nexus Builds your SQL Automagically
    10. Nexus can Cube a Table and Join to Everything Possible
    11. Nexus can Cube a Table and Join to Everything Possible
    12. The Cube SQL created Automagically
    13. Manipulate the Columns with the Columns Tab
    14. Single Click and ORDER BY using the Sort Tab
    15. Using the Joins Tab of Nexus
    16. The SQL Tab reflects the changes we make in all other Tabs
    17. WHERE Tab shows Tables Indexes
    18. The Answer Set Tab shows the Results
    19. The Metadata Tab shows Metadata
    20. Nexus Makes a View look like a Table
    21. Nexus Joins Views to other Views in seconds
    22. Nexus can Cube a View and Join to all other related Views
    23. Nexus Cubes Views in Seconds
    24. The Cube SQL created on Views is done Automagically
    25. Views with the Underlying Indexes of the Base Tables
    26. WHERE Tab shows Views Underlying Base Table Indexes
    27. After an Answer Set Returns, you can do many things
    28. After an Answer Set Returns, Perform OLAP Calculations
    29. After an Answer Set Returns, you can Graph and Chart
    30. Custom Joins With Nexus
    31. Users Who Want to Load the Model
    32. Users Who Want to Load the Model (Continued)
    33. How Custom Joins Will Look in the Super Join Builder
    34. Loading an ERwin Mode
    35. Loading an ERwin Model (Continued)
    36. Attaching the ERwin Model
    37. Attaching the ERwin Model (Continued)
    38. Managing the ERwin Model (Continued)
    39. Saving an Answer Set in another Format
    40. Sandbox - How to Create a Sandbox (1 of 5)
    41. Convert Netezza DDL to another Database Vendor
    42. Replicate Data from One Netezza System to Another
Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Request a Private Class
  • Private Class for your Team
  • Online or On-location
  • Customizable
  • Expert Instructors
Request Pricing