Chapter 3 – Distinct, Group By and TOP
by Tom Coffing, Leona Coffing
V14 Certification: Teradata SQL
- Cover
- The Tera-Tom Video Series
- The Tera-Tom Genius Series
- Tera-Tom- Author of over 50 Books
- The Best Query Tool Works on all Systems
- Trademarks and Copyrights
- About Tom Coffing
- About Leona Coffing
- Contents
- Chapter 1 – The Basics of SQL
- Passing Your Teradata Certification Tests
- 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
- ORDER BY Defaults to Ascending
- Use the Name or the Number in your 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)
- NULL Values sort Last in Descending Mode (DESC)
- Major Sort vs. Minor Sorts
- Multiple Sort Keys using Names vs. Numbers
- Sorts are Alphabetical, NOT Logical
- Using A CASE Statement to Sort Logically
- How to ALIAS a Column Name
- A Missing Comma can by Mistake become an Alias
- The Title Command and Literal Data
- Comments using Double Dashes are Single Line Comments
- Comments for Multi-Lines
- Comments for Multi-Lines As Double Dashes Per Line
- A Great Technique for Comments to Look for SQL Errors
- Chapter 2 – The Where Clause
- The WHERE Clause limits Returning Rows
- Using a Column ALIAS throughout the SQL
- Double Quoted Aliases are for Reserved Words and Spaces
- Character Data needs Single Quotes in the WHERE Clause
- Character Data needs Single Quotes, but Numbers Don’t
- Comparisons Against a Null Value
- NULL means UNKNOWN DATA so Equal (=) won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- NULL is UNKNOWN DATA so NOT Equal won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- Using Greater Than Or Equal To (>=)
- Using GE as Greater Than or Equal To (>=)
- AND in the WHERE Clause
- Troubleshooting AND
- OR in the WHERE Clause
- Troubleshooting Or
- 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 Bring Back No Rows
- A Technique for Handling Nulls with a NOT IN List
- An IN List with the Keyword ANY
- A NOT IN List with the Keywords NOT = ALL
- A NOT IN List with the Keywords NOT = ALL and NULL
- BETWEEN is Inclusive
- NOT BETWEEN is Also Inclusive
- LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
- LIKE command Underscore is Wildcard for one Character
- LIKE Command Works Differently on Char Vs Varchar
- Troubleshooting LIKE Command on Character Data
- Introducing the TRIM Command
- Quiz – What Data is Left Justified and What is Right?
- Numbers are Right Justified and Character Data is Left
- Answer – What Data is Left Justified and What is Right?
- An Example of Data with Left and Right Justification
- A Visual of CHARACTER Data vs. VARCHAR Data
- Use the TRIM command to remove spaces on CHAR Data
- LIKE ALL means ALL conditions must be Met
- These Two Queries Do Exactly The Same Thing
- LIKE ANY means ANY of the Conditions can be Met
- These Two Queries Do Exactly The Same Thing
- IN ANSI Transaction Mode Case Matters
- In Teradata Transaction Mode Case Doesn’t Matter
- Escape Character in the LIKE Command changes Wildcards
- Escape Characters Turn off Wildcards in the LIKE Command
- Quiz – Turn off that Wildcard
- ANSWER – To Find that Wildcard
- Built-In Functions
- Chapter 3 – Distinct, Group By and TOP
- 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?
- TOP Command
- TOP Command is brilliant when ORDER BY is Used!
- What is the Difference Between TOP and Sample?
- SAMPLE Does NOT Use the ORDER BY Correctly
- The TOP Command WITH TIES
- The TOP Command will NOT work with Certain Commands
- Chapter 4 - Aggregation
- Quiz – You calculate the Answer Set in your own Mind
- Answer – You calculate the Answer Set in your own Mind
- The 3 Rules of Aggregation
- There are Five Aggregates
- Quiz – How many rows come back?
- Answer – How many rows come back?
- 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
- Aggregates Return Null on Empty Tables
- Keyword HAVING is like an Extra WHERE Clause for Totals
- Getting the Average Values Per Column
- Average Values Per Column For all Columns in a Table
- 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
- GROUP BY CUBE Result Set
- Chapter 5 – Join Functions
- Teradata Join Quiz
- Teradata Join Quiz Answer
- Redistribution
- Big Table Small Table Join Strategy
- Duplication of the Smaller Table across All-AMPs
- If the Join Condition is the Distribution Key no Movement
- Matching Rows That Are On The Same AMP Naturally
- What if the Join Condition Columns are Not Primary Indexes
- Strategy 1 of 4 – The Merge Join
- Quiz – Redistribute the Employees by their Dept_No
- Quiz – Employees' Dept_No landed on AMP with Matches
- Quiz – Redistribute the Orders to the Proper AMP
- Answer to Redistribute the Employees by their Dept_No Quiz
- Strategy 2 of 4 – The Hash Join
- Strategy 3 of 4 – The Nested Join
- Strategy 4 of 4 – The Product Join
- A Two-Table Join Using Traditional 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
- OUTER JOIN with Additional AND Clause Results
- Quiz – Why is this Considered an INNER JOIN?
- Evaluation Order For Outer Queries
- The DREADED Product Join
- The DREADED Product Join Results
- The Horrifying Cartesian Product Join
- The ANSI Cartesian Join will ERROR
- Quiz – Do these Joins Return the Same Answer Set?
- Answer – Do these Joins Return the Same Answer Set?
- The CROSS JOIN
- The CROSS JOIN Answer Set
- The Self Join
- The Self Join with ANSI Syntax
- Quiz – Will both queries bring back the same Answer Set?
- Answer – Will both queries bring back the same Answer Set?
- Quiz – Will both queries bring back the same Answer Set?
- Answer – Will both queries bring back the same Answer Set?
- How would you Join these two tables?
- An Associative Table is a Bridge that Joins Two Tables
- Quiz – Can you Write the 3-Table Join?
- Answer to Quiz – Can you Write the 3-Table Join?
- Quiz – Can you Write the 3-Table Join to 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 Non-ANSI 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
- The Nexus Query Chameleon Writes the SQL for Users
- Chapter 6 – Date Functions
- Current_Date
- Date, Time, and Current_Timestamp Keywords
- Dates are stored Internally as INTEGERS from a Formula
- Displaying Dates for INTEGERDATE and ANSIDATE
- DATEFORM
- Changing the DATEFORM in Client Utilities such as BTEQ
- Date, Time, and Timestamp Recap
- Timestamp Differences
- Troubleshooting Timestamp
- Add or Subtract Days from a date
- A Summary of Math Operations on Dates
- Age in Days vs. Age in Years
- Find What Day of the week you were Born
- The ADD_MONTHS Command
- Using the ADD_MONTHS Command to Add 1 Year
- Using the ADD_MONTHS Command to Add 1 Year
- Using the ADD_MONTHS Command to Add 5 Years
- The EXTRACT Command
- Another Version of the EXTRACT Command
- EXTRACT from DATES and TIME
- Why EXTRACT is a Better Form
- EXTRACT or Math Can Accomplish the Same Thing
- EXTRACT with DATE and TIME Literals
- CAST the Date of January 1, 2011 and the Year 1800
- EXTRACT of the Month on Aggregate Queries
- The System Calendar
- Using the System Calendar In Its Simplest Form
- How to really use the Sys_Calendar.Calendar
- Storing Dates Internally
- Storing Time Internally
- Storing TIME With TIME ZONE Internally
- Storing Timestamp Internally
- Storing Timestamp with TIME ZONE Internally
- Storing Date, Time, and Timestamp with Zone Internally
- Time Zones
- Setting Time Zones at the System Level
- Setting Time Zones at the User Level
- Setting Time Zones at the Session Level
- Seeing your Time Zone
- Creating a Sample Table for Time Zone Examples
- Inserting Rows in the Sample Table for Time Zone Examples
- Inserting Rows in the Sample Table for Time Zone Examples
- Normalizing our Time Zone Table with a CAST
- Intervals for Date, Time and Timestamp
- Interval Data Types and the Bytes to Store Them
- Using Intervals
- Troubleshooting The Basics of a Simple Interval
- Interval Arithmetic Results
- A Date Interval Example
- A Time Interval Example
- A DATE Interval Example
- A Complex Time Interval Example using CAST
- A Complex Time Interval Example using CAST
- The OVERLAPS Command
- An OVERLAPS Example that Returns No Rows
- The OVERLAPS Command using TIME
- The OVERLAPS Command using a NULL Value
- Chapter 7 – Format Functions
- The FORMAT Command
- The Basics of the FORMAT Command
- Quiz – How will the Date Appear after Formatting
- Answer to Quiz – How will the Date Appear after Formatting
- Quiz – How will the Date Appear after Formatting
- Answer to Quiz – How will the Date Appear after Formatting
- Formatting with MMM for the Abbreviated Month
- Answer to Quiz – How will the Date Appear after Formatting
- Formatting with MMMM for the Full Month Name
- Formatting with MMMM for the Full Month
- Formatting with DDD for the Julian Day
- Formatting with DDD for the Julian Day
- Formatting with EEE or EEEE for the Day of the Week
- EEEE for the Abbreviated or Full Day of the Week
- Placing Spaces inside your Formatting Commands with a B
- Formatting Spaces with B or b
- Formatting with 9
- Formatting with 9 Results
- Troubleshooting when Formatted Data Overflows
- Troubleshooting when Formatted Data Overflows
- Formatting with X or x
- Formatting with X or x Results
- Formatting with Z
- Formatting with Z Visual
- Formatting with 9
- Formatting with 9 Visual
- Formatting with $
- Formatting with $ Visual
- Formatting with $ and Commas
- Formatting with $ and Commas Visual
- Formatting with $ and Commas and 9
- Formatting with $ and Commas and 9 with Zero Dollars
- A Great Formatting Example
- A Great Formatting Example for Day, Month and Year
- A Trick to get SQL Assistant to Format Data
- Using the CASESPECIFIC (CS) Command in Teradata Mode
- Using NOT CASESPECIFIC (CS) in ANSI Mode
- Using the LOWER Command
- Using the UPPER Command
- Chapter 8 - OLAP Functions
- On-Line Analytical Processing (OLAP) or Ordered Analytics
- Cumulative Sum (CSUM) Command and how OLAP Works
- After the Sort the CSUM is Calculated
- The OLAP Major Sort Key
- The OLAP Major Sort Key and the Minor Sort Key(s)
- Troubleshooting OLAP – My Data isn’t coming back Correct
- GROUP BY in Teradata OLAP Syntax Resets on the Group
- CSUM the Number 1 to get a Sequential Number
- A Single GROUP BY Resets each OLAP with Teradata Syntax
- CSUM
- CSUM – The Sort Explained
- CSUM – Rows Unbounded Preceding Explained
- CSUM – Making Sense of the Data
- CSUM – Making Even More Sense of the Data
- CSUM – The Major and Minor Sort Key(s)
- The ANSI CSUM – Getting a Sequential Number
- Troubleshooting The ANSI OLAP on a GROUP BY
- Reset with a PARTITION BY Statement
- PARTITION BY only Resets a Single OLAP not ALL of them
- The Moving SUM (MSUM) and Moving Window
- How the Moving Sum is Calculated
- How the Sort works for Moving SUM (MSUM)
- GROUP BY in the Moving SUM does a Reset
- Quiz – Can you make the Advanced Calculation in your mind?
- Answer to Quiz for the Advanced Calculation in your mind?
- Quiz – Write that Teradata Moving SUM in ANSI Syntax
- Both the Teradata Moving SUM and ANSI Version
- ANSI Moving Window is Current Row and Preceding n Rows
- How ANSI Moving SUM Handles the Sort
- Quiz – How is that Total Calculated?
- Answer to Quiz – How is that Total Calculated?
- Moving SUM every 3-rows Vs a Continuous Average
- Partition By Resets an ANSI OLAP
- The Moving Average (MAVG) and Moving Window
- How the Moving Average is Calculated
- How the Sort works for Moving Average (MAVG)
- GROUP BY in the Moving Average does a Reset
- Quiz – Can you make the Advanced Calculation in your mind?
- Answer to Quiz for the Advanced Calculation in your mind?
- Quiz – Write that Teradata Moving Average in ANSI Syntax
- Both the Teradata Moving Average and ANSI Version
- Moving Average
- The Moving Window is Current Row and Preceding
- How Moving Average Handles the Sort
- Quiz – How is that Total Calculated?
- Answer to Quiz – How is that Total Calculated?
- Quiz – How is that 4th Row Calculated?
- Answer to Quiz – How is that 4th Row Calculated?
- Moving Average every 3-rows Vs a Continuous Average
- Partition By Resets an ANSI OLAP
- The Moving Difference (MDIFF)
- Moving Difference using ANSI Syntax
- Moving Difference using ANSI Syntax with Partition By
- Trouble Shooting the Moving Difference (MDIFF)
- Using the RESET WHEN Option in Teradata V13
- How Many Months Per Product_ID Has Revenue Increased?
- The RANK Command
- How to get Rank to Sort in Ascending Order
- Two ways to get Rank to Sort in Ascending Order
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK() OVER and PARTITION BY
- RANK() OVER And QUALIFY
- RANK() OVER and PARTITION BY with a QUALIFY
- QUALIFY and WHERE
- Quiz – How can you simplify the QUALIFY Statement?
- Answer to Quiz –Can you simplify the QUALIFY Statement
- The QUALIFY Statement without Ties
- The QUALIFY Statement with Ties
- The QUALIFY Statement with Ties Brings back Extra Rows
- Mixing Sort Order for QUALIFY Statement
- Quiz – What Caused the RANK to Reset?
- Answer to Quiz – What Caused the RANK to Reset?
- Quiz – Name those Sort Orders
- Answer to Quiz – Name those Sort Orders
- PERCENT_RANK() OVER
- PERCENT_RANK() OVER with 14 rows in Calculation
- PERCENT_RANK() OVER with 21 rows in Calculation
- Quiz – What Causes the Product_ID to Reset?
- Answer to Quiz – What Cause the Product_ID to Reset?
- COUNT OVER for a Sequential Number
- Troubleshooting COUNT OVER
- Quiz – What caused the COUNT OVER to Reset?
- Answer to Quiz – What caused the COUNT OVER to Reset?
- The MAX OVER Command
- MAX OVER with PARTITION BY Reset
- Troubleshooting MAX OVER
- The MIN OVER Command
- Troubleshooting MIN OVER
- Finding a Value of a Column in the Next Row with MIN
- Finding Gaps Between Dates
- The CSUM For Each Product_Id For The First 3 Days
- Quiz – Fill in the Blank
- Answer – Fill in the Blank
- The Row_Number Command
- Quiz – How did the Row_Number Reset?
- Quiz – How did the Row_Number Reset?
- Row_Number With Qualify to get the Typical Rows Per Value
- A Second Typical Rows Per Value Query on Sale_Date
- Chapter 9 – The Quantile Function
- The Quantile Function and Syntax
- A Quantile Example
- A Quantile Example using DESC Mode
- QUALIFY to find Products in the top Partitions
- QUALIFY to find Products in the top Partitions Sorted DESC
- QUALIFY to find Products in the top Partitions Sorted ASC
- QUALIFY to find Products in top Partitions with Tiebreaker
- Using Tertiles (Partitions of Four)
- How Quantile Works
- Chapter 10 - Temporary Tables
- There are Three types of Temporary Tables
- CREATING A Derived Table
- Naming the Derived Table
- Aliasing the Column Names in The Derived Table
- Multiple Ways to Alias the Columns in a Derived Table
- CREATING A Derived Table using the WITH Command
- 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 A Different Column Aliasing Style
- Column Aliasing Can Default For Normal Columns
- Our Join Example With The WITH Syntax
- Quiz - Answer the Questions
- Answer to Quiz - Answer the Questions
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- 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 Looping Continued
- WITH RECURSIVE Derived Table Ends the Looping
- WITH RECURSIVE Derived Table Definition
- WITH RECURSIVE Derived Table Definition
- Creating a Volatile Table
- You Populate a Volatile Table with an INSERT/SELECT
- The Three Steps to Use a Volatile Table
- Why Would You Use the ON COMMIT DELETE ROWS?
- The HELP Volatile Table Command Shows your Volatiles
- A Volatile Table with a Primary Index
- The Joining of Two Tables Using a Volatile Table
- You Can Collect Statistics on Volatile Tables
- The New Teradata V14 Way to Collect Statistics
- Some Great Examples of Creating a Volatile Table Quickly
- Creating Partitioned Primary Index (PPI) Volatile Tables
- A Volatile Table That Only Populates Some of the Rows
- A Volatile Table With No Data
- A Volatile Table With Some of the Columns
- A Volatile Table With No Data and Zeroed Statistics
- A Multiset Volatile Table With Statistics Example
- Using a Volatile Table to Get Rid of Duplicate Rows
- CREATING A Global Temporary Table
- Many Users Can Populate the Same Global Temporary Table
- Global Temporary Table with a Primary Index and Compress
- Chapter 11 - 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 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 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 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
- Quiz – How many rows return on a NOT IN with a NULL?
- Answer – How many rows return on a NOT IN with a NULL?
- How to handle a NOT IN with Potential NULL Values
- IN is equivalent to =ANY
- Using a Correlated Exists
- How a Correlated Exists matches up
- The Correlated NOT Exists
- The Correlated NOT Exists Answer Set
- Quiz – How many rows come back from this NOT Exists?
- Answer – How many rows come back from this NOT Exists?
- Chapter 12 – Substring and Positioning Functions
- The CHARACTERS Command Counts Characters
- The CHARACTERS Command – Spaces can Count too
- The CHARACTERS Command and Char(20) Data
- Troubleshooting the CHARACTERS Command
- TRIM for Troubleshooting the CHARACTERS Command
- CHARACTERS and CHARACTER_LENGTH equivalent
- OCTET_LENGTH
- The TRIM Command trims both Leading and Trailing Spaces
- Trim Combined with the CHARACTERS Command
- How to TRIM only the Trailing Spaces
- A Visual of the TRIM Command Using Concatenation
- Trim and Trailing is Case Sensitive
- How to TRIM Trailing Letters
- The SUBSTRING Command
- SUBSTRING and SUBSTR are equal, but use different syntax
- How SUBSTRING Works with NO ENDING POSITION
- Using SUBSTRING to move Backwards
- How SUBSTRING Works with a Starting Position of -1
- How SUBSTRING Works with an Ending Position of 0
- An Example using SUBSTRING, TRIM and CHAR Together
- The POSITION Command finds a Letters Position
- The POSITION Command is brilliant with SUBSTRING
- Quiz – Find that SUBSTRING Starting Position
- Answer to Quiz – Find that SUBSTRING Starting Position
- Using the SUBSTRING to Find the Second Word On
- Quiz – Why Did only one Row Return
- Answer to Quiz – Why Did only one Row Return
- Concatenation
- Concatenation and SUBSTRING
- Four Concatenations Together
- Troubleshooting Concatenation
- Imbedded Services Functions
- Imbedded Services Functions and Their Descriptions
- Imbedded Services Functions Example With To_Date
- Chapter 13 – Interrogating the Data
- Quiz – What would the Answer be?
- Answer to Quiz – What would the Answer be?
- The NULLIFZERO Command
- Quiz – Fill in the Blank Values in the Answer Set
- Answer to Quiz – Fill in the Blank Values in the Answer Set
- Quiz – Fill in the Answers for the NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- The ZEROIFNULL Command
- Answer to the ZEROIFNULL Question
- The COALESCE Command
- The COALESCE Answer Set
- The Coalesce Quiz
- Answer – The Coalesce Quiz
- The Basics of CAST (Convert And STore)
- Some Great CAST (Convert And STore) Examples
- Some Great CAST (Convert And STore) Examples
- Teradata Rules About Rounding
- A Rounding Example
- Some Great CAST (Convert And STore) Examples
- A Teradata Extension – The Implied Cast
- The Basics of the CASE Statements
- The Basics of the CASE Statements
- Valued Case Vs. A Searched Case
- Quiz - Valued Case Statement
- Answer - Valued Case Statement
- Quiz - Searched Case Statement
- Answer - Searched Case Statement
- Quiz - When NO ELSE is present in CASE Statement
- Answer - When NO ELSE is present in CASE Statement
- When an ELSE is present in CASE Statement
- Answer - When an ELSE is present in CASE Statement
- When an Alias is NOT used in a CASE Statement
- Answer - When an Alias is NOT used in a CASE Statement
- Combining Searched Case and Valued Case
- A Trick for getting a Horizontal Case
- Nested Case
- Put a CASE in the ORDER BY
- Chapter 14 – View Functions
- The Fundamentals of Views
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict Rows
- Basic Rules for Views
- How to Modify a View
- Two Exceptions to the ORDER BY Rule inside a View
- How to Get HELP with a View
- Views sometimes CREATED for Formatting or Row Security
- Creating a View to Join Tables Together
- You Select From a View
- Another Way to Alias Columns in a View CREATE
- The Standard Way Most Aliasing is Done
- What Happens When Both Aliasing Options Are Present
- Resolving Aliasing Problems in a View CREATE
- Answer to Resolving Aliasing Problems in a View CREATE
- Aggregates on View Aggregates
- Locking Row for Access
- Altering A Table
- Altering A Table After a View has been Created
- A View that Errors After An ALTER
- Troubleshooting a View
- Updating Data in a Table through a View
- Loading Data through a View
- Maintenance Restrictions on a Table through a View
- Chapter 15 – Macro Functions
- The Rules of Macros
- The Rules of Macros
- CREATING and EXECUTING a Simple Macro
- Multiple SQL Statements inside a Macro
- Complex Joins inside a Macro
- Passing an INPUT Parameter to a Macro
- Troubleshooting a Macro with INPUT Parameters
- An UPDATE Macro with Two Input Parameters
- Executing a Macro with Named (Not Positional) Parameters
- Macro Parameter Restrictions
- Troubleshooting a Macro
- More Troubleshooting of a Macro
- Clever Tricks
- Chapter 16 – Set Operators Functions
- Rules of Set Operators
- Rules of Set Operators
- INTERSECT Explained Logically
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- UNION Explained Logically
- EXCEPT Explained Logically
- EXCEPT Explained Logically
- Minus Explained Logically
- Minus Explained Logically
- Testing Your Knowledge
- Answer - Testing Your Knowledge
- Testing Your Knowledge
- Answer - Testing Your Knowledge
- An Equal Amount of Columns in both SELECT List
- Columns in the SELECT list should be from the same Domain
- The Top Query handles all Aliases
- The Bottom Query does the ORDER BY (a Number)
- Great Trick: Place your Set Operator in a Derived Table
- UNION Vs UNION ALL
- Using UNION ALL and Literals
- A Great Example of how EXCEPT works
- USING Multiple SET Operators in a Single Request
- Changing the Order of Precedence with Parentheses
- Using UNION ALL for speed in Merging Data Sets
- Using UNION to be same as GROUP BY GROUPING SETS
- Chapter 17 - Table Create and Data Types
- Creating a Table With A Unique Primary Index (UPI)
- Creating a Table With A Non-Unique Primary Index (NUPI)
- Creating a Table With A Multi-Column Primary Index
- Creating a Table With No Primary Index
- Creating a Table Without Entering a Primary Index Definition
- Creating a SET Table
- Creating a MULTISET Table
- Creating a SET Table With a Unique Primary Index
- Creating a Table With Multiple Secondary Indexes
- Creating a PPI Table with Simple Partitioning
- Creating a PPI Table with RANGE_N Partitioning per Month
- Creating a PPI Table with RANGE_N Partitioning per Day
- Creating a PPI Table with RANGE_N Partitioning per Week
- A Clever Range_N Option
- Creating a PPI Table with CASE_N
- The No Case and Unknown Partition Options
- Partitioning of Older and Newer Data Separately
- Multi-Level Partitioning
- Almost All PPI Tables have a Non-Unique Primary Index
- PPI Table With a Unique Primary Index (UPI)
- Clever Trick for PPI Tables
- Another Clever Trick for PPI Tables
- Character Based PPI for RANGE_N
- Character-Based PPI for CASE_N
- Dates and Character-Based Multi-Level PPI
- TIMESTAMP Partitioning That is Deterministic
- Altering a PPI Table the Hard Way
- Altering a PPI Table the Easy Way With TO CURRENT
- Altering a PPI Table and Saving the Deleted Data
- Using the PARTITION Keyword in your SQL
- SQL for RANGE_N
- SQL for CASE_N
- Creating a Columnar Table
- Creating a Columnar Table With Multi-Column Containers
- Columnar Row Hybrid CREATE Statement
- CREATE Statement for both Row and Column Partition
- CREATING a Bi-Temporal Table
- Creating a Table With Fallback
- Creating a Table With No Fallback
- Creating a Table With a Before Journal
- Creating a Table With a Dual Before Journal
- Creating a Table With an After Journal
- Creating a Table With a Dual After Journal
- Creating a Table With a Journal
- Why Use Journaling?
- Table Customization of the Data Block Size
- Table Customization with FREESPACE Percent
- Creating a QUEUE Table
- You Can Select From a Queue Table
- Exploring the Real Purpose of a Queue Table
- Column Attributes
- An Example of a Table With Column Attributes
- An Example of a Table With Column Level Constraints
- An Example of a Table With Table Level Constraints
- Create Table AS
- Creating Partitioned Primary Index (PPI) Volatile Tables
- A Volatile Table That Only Populates Some of the Rows
- A Volatile Table With No Data
- A Volatile Table With Some of the Columns
- A Volatile Table With No Data and Zeroed Statistics
- A Multiset Volatile Table With Statistics Example
- Data Types
- Data Types Continued
- Data Types Continued
- Major Data Types and the number of Bytes they take up
- Chapter 18 - Data Manipulation Language (DML)
- INSERT Syntax # 1
- INSERT Example with Syntax 1
- INSERT Syntax # 2
- INSERT Example with Syntax 2
- INSERT Example with Syntax 3
- Using NULL for Default Values
- INSERT/SELECT Command
- INSERT/SELECT Example using All Columns (*)
- INSERT/SELECT Example with Less Columns
- INSERT/SELECT to Build a Data Mart
- Fast Path INSERT/SELECT
- NOT quite the Fast Path INSERT/SELECT
- UNION for the Fast Path INSERT/SELECT
- BTEQ for the Fast Path INSERT/SELECT
- The UPDATE Command Basic Syntax
- Two UPDATE Examples
- Subquery UPDATE Command Syntax
- Example of Subquery UPDATE Command
- Join UPDATE Command Syntax
- Example of an UPDATE Join Command
- Fast Path UPDATE
- The DELETE Command Basic Syntax
- Two DELETE Examples to DELETE ALL Rows in a Table
- A DELETE Example Deleting only Some of the Rows
- Subquery and Join DELETE Command Syntax
- Example of Subquery DELETE Command
- Example of Join DELETE Command
- Fast Path DELETE
- Fast Path DELETE Example # 2
- Fast Path DELETE Example # 3
- MERGE INTO
- MERGE INTO Example that Matches
- MERGE INTO Example that does NOT Match
- OReplace
- User Defined Functions (UDFs)
- User Defined Function Example
- User Defined Types (UDTs)
- User Defined Types Example
- Formatting for Internationalizations
- Chapter 19 – Stored Procedure Functions
- Stored Procedures Vs. Macros
- Creating a Stored Procedure
- How you CALL a Stored Procedure
- Label all BEGIN and END statements except the first ones
- How to Declare a Variable
- How to Declare a Variable and then SET the Variable
- An IN Variable is passed to the Procedure during the CALL
- The IN, OUT and INOUT Parameters
- Using IF inside a Stored Procedure
- Example of two Stored Procedures with different techniques
- Using Loops in Stored Procedures
- Using Keywords LEAVE vs. UNTIL for LEAVE vs. REPEAT
- Stored Procedure Workshop
- Stored Procedure Workshop Answer
- Stored Procedure Workshop Alternative Answer
- Chapter 20 – Trigger Functions
- Chapter 21 – The SAMPLE Function
- The SAMPLE Function and Syntax
- SAMPLE Function Examples
- A SAMPLE Example that asks for Multiple Samples
- A SAMPLE Example with the SAMPLEID
- A SAMPLE Example WITH REPLACEMENT
- A SAMPLE Example with Four 10% Samples
- A Randomized SAMPLE
- A SAMPLE with Conditional Logic
- Aggregates and A SAMPLE using a Derived Table
- Random Number Generator
- Using Random to SELECT a Percentage of Rows
- Using Random and Aggregations
- Chapter 22 – Statistical Aggregate Function
- The Stats Table
- The KURTOSIS Function
- A Kurtosis Example
- The SKEW Function
- A SKEW Example
- 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
- The CORR Function
- A CORR Example
- Another CORR Example so you can Compare
- The COVAR_POP Function
- A COVAR_POP Example
- Another COVAR_POP Example so you can Compare
- The REGR_INTERCEPT Function
- A REGR_INTERCEPT Example
- Another REGR_INTERCEPT Example so you can Compare
- The REGR_SLOPE Function
- A REGR_SLOPE Example
- Another REGR_SLOPE Example so you can Compare
- Using GROUP BY
- No Having Clause Vs Use of HAVING
- Chapter 23 – HELP and SHOW
- Determining the Release of your Teradata System
- Basic HELP Commands
- Other HELP Commands
- HELP DATABASE
- HELP USER
- HELP TABLE
- Adding a Comment to a Table
- Adding a Comment to a View
- SELECT SESSION
- USER Information Functions
- HELP SESSION
- HELP SQL
- A HELP SQL Example
- Show Commands
- SHOW Table command for Table DDL
- SHOW View command for View Create Statement
- SHOW Macro command for Macro Create Statement
- SHOW Trigger command for Trigger Create Statement
- Chapter 24 – Teradata (BTET) vs. ANSI Mode
- Chapter 25 – BTEQ – Batch Teradata Query
- BTEQ – Batch TEradata Query Tool
- How to Logon to BTEQ in Interactive Mode
- Running Queries in BTEQ in Interactive Mode
- BTEQ Commands Vs BTEQ SQL Statements
- WITH BY Command for Subtotals
- WITH Command for a Grand Total
- WITH and WITH BY Together for Subtotals and Grand Totals
- How to Logon to BTEQ in a SCRIPT
- Multi-statement Transactions Using BTEQ Semi-colons
- Chapter 26 – Locking
- The Four Major Locks of Teradata
- The Read Lock
- The Read Lock and Joins
- The Write Lock
- The Exclusive Lock
- The Three Levels of Locking
- Locking at the Row Hash Level
- Locking at the Table Level
- Locking at the Database Level
- The Ongoing Battle between Read and Write Locks
- Compatibility between Read Locks
- Why Read Locks Wait on Write Locks
- Why Write Locks Wait on Read Locks
- The Access Lock is Different from the Other Locks
- What is the Purpose of an Access Lock?
- Locking Modifiers - Locking Row, Table or Database
- All Views should consider the Locking for Access Statement
- What is a Dead Lock or a Deadly Embrace?
- Pseudo Tables are designed to minimize Dead Locks
- Pseudo Tables are referenced in the Explain Plan
- Incompatible Locks Wait on each Other
- The Checksum Lock of Teradata
- The Nowait Option for Locking
- The Automatic Locking for Access Button inside Nexus
- Viewpoint Lock Viewer
- Viewpoint Lock Viewer Lets You Configure Your View
- What is a Host Utility (HUT) Lock?
- Chapter 27 – Lessons with Tera-Tom Video Guide
- Video 1 - Teradata Basics - The Architecture
- Video 2 - Hashing the Primary Index
- Video 3 - The Cold Hard Teradata Facts
- Video 4 -Inside the Amps’ Disc
- Video 5 - PPI (Partitioned Primary Index Tables)
- Video 6 Teradata Columnar
- Video 7- Space
- Video 8 - How Teradata Joins Tables Together
- Video 9- Protection Features (1/3)
- Video 10- Protection Features (2/3)
- Video 11- Protection Features (3/3)
- Video 12- Collect Statistics (1 of 2)
- Video 13- Collect Statistics (2 of 2)
- Video 14- Locking
- Video 15- Temporal Tables
- Video 16- Join Indexes
- Teradata SQL Video 1 - The Basics of SQL
- Teradata SQL Video 2 – Building Your SQL Knowledge
- Teradata SQL Video 3 - Aggregates
- Teradata SQL Video 4 - Joins
- Teradata SQL Video 5 - Dates
- Teradata SQL Video 6 – Temporary Tables (Derived)
- Teradata SQL Video 7 – Volatile and Global Temporary Tables
- Teradata SQL Video 8 – Ordered Analytic Functions
- Teradata SQL Video 9 – Advanced Ordered Analytics
- Teradata SQL Video 10 - Subqueries
- Teradata SQL Video 11 – Substring and Positioning
- Teradata SQL Video 12 – Data Interrogation
- Teradata SQL Video 13 - Views
- Teradata SQL Video 14 - Macros
- Teradata SQL Video 15 – Stored Procedures
- Teradata SQL Video 16 – Set Operators
- Teradata SQL Video 17 – Creating Tables
Chapter 3 – Distinct, Group By and TOP
“A bird does not sing because it has the answers, it sings because it has a song.”
- Anonymous
The Distinct Command
DISTINCT eliminates duplicates from returning in the Answer Set.
Distinct vs. GROUP BY
Both examples produce the exact same result
Class_Code
?
FR
JR
SO
SR
| Rules for Distinct Vs. GROUP BY |
(1) Many Duplicates – use GROUP BY (2) Few Duplicates – use DISTINCT (3) Space Exceeded - use GROUP BY |
Distinct and GROUP BY in the two examples return the same answer set.
Quiz – How many rows come back from the Distinct?
How many rows will come back from the above SQL?
Answer – How many rows come back from the Distinct?
SELECT Distinct Class_Code, Grade_Pt
FROM Student_Table
ORDER BY Class_Code, Grade_Pt ;
How many rows will come back from the above SQL? 10. All rows came back. Why? Because there are no exact duplicates that contain a duplicate Class_Code and Duplicate Grade_Pt combined. Each row in the SELECT list is distinct.
TOP Command
In the above example, we brought back 3 rows only. This is because of the TOP 3 statement which means to get an answer set, and then bring back the first 3 rows in that answer set. Because this example does not have an ORDER BY statement, you can consider this example as merely bringing back 3 random rows.
TOP Command is brilliant when ORDER BY is Used!
In the above example, we brought back 3 rows only. This is because of the TOP 3 statement which means to get an answer set, and then bring back the first 3 rows. Because this example uses an ORDER BY statement, the data brought back is from the top 3 students with the highest Grade_Pt. This is the real power of the TOP command. Use it with an ORDER BY!
What is the Difference Between TOP and Sample?
Both queries above bring back three rows. The TOP command is designed to bring back the top n rows. TOP allows the data to be sorted first so it can bring back the top 3 students with the highest grade points. The SAMPLE command will bring back three rows, but it doesn't utilize the sort. Sample is a random sample, but TOP can be more.
SAMPLE Does NOT Use the ORDER BY Correctly
The TOP command is designed to bring back the top n rows. TOP allows the data to be sorted first so it can bring back the top 3 students with the highest grade points. The SAMPLE command will bring back three rows, but it doesn't utilize the sort. Sample is a random sample, but TOP can be more. The data above is only sorted by Grade_Pt after the random sample returns. A TOP 3 command would have sorted all the students by Grade_Pt first and then given us the top three.
The TOP Command WITH TIES
By using the TOP WITH TIES Command, this will bring in the TOP amount along with ANY ties. So while you might only ask for the top 2 with ties, you might get 4 rows back. Why did 4 rows return here? Which row came back first? Four rows returned with the first row coming back as a NULL for Class_Code. Then the next row returned was one of the Freshman. There were two other Freshman that tie. All ‘FR’ come back in a tie!
The TOP Command will NOT work with Certain Commands
Because of the location of TOP within the SELECT and the elimination of some of the rows, TOP is NOT compatible with the following SQL Constructs.
* DISTINCT and QUALIFY
* WITH and WITH BY
* SAMPLE
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.