- 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 Debra Aaron
- Contents
- Chapter 1 – Introduction
- Chapter 2 – The Basics of SQL
- Introduction
- Setting Your Default Database
- 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
- 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
- An Order By That Uses an Expression
- How to ALIAS a Column Name
- Aliasing a Column Name with Spaces or Reserved Words
- 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
- A Great Technique for Comments to Look for SQL Errors
- sp_help at the Database Level
- sp_help at the Object Level
- Getting System Information
- Getting Additional System Information
- Chapter 3 – The WHERE Clause
- The WHERE Clause limits Returning Rows
- Double Quoted Aliases are for Reserved Words and Spaces
- Using A Column ALIAS In A WHERE Clause
- Using A Column ALIAS In An ORDER BY Clause
- In What Order Does SQL Server Process A Query?
- Character Data needs Single Quotes in the WHERE Clause
- Character Data needs Single Quotes, but Numbers Don’t
- Declaring a Variable
- 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 (>=)
- 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
- BETWEEN is Inclusive
- NOT BETWEEN is Also Inclusive
- LIKE command Underscore is Wildcard for one Character
- LIKE command using a Range of Values
- LIKE command Using a NOT Range of Values
- LIKE Command Works Differently on Char Vs Varchar
- Troubleshooting LIKE Command on Character Data
- Introducing the RTRIM 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
- RTRIM command Removes Trailing spaces on CHAR Data
- Using Like with an AND Clause to Find Multiple Letters
- Using Like with an OR Clause to Find Either Letters
- Declaring a Variable and Using it with the LIKE Command
- 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
- Chapter 4 – 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!
- TOP Command with PERCENT
- TOP Command with PERCENT
- The TOP Command WITH TIES
- The TOP Command Using a Variable
- The TOP 1 Command for a Random Sample
- Chapter 5 – 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
- Count_Big
- Limiting Rows and Improving Performance with WHERE
- WHERE Clause in Aggregation limits unneeded Calculations
- Keyword HAVING tests Aggregates after they are Totaled
- CHECKSUM_AGG to Check If a Table Has Changed
- Using Distinct Within the Aggregate
- Group By Grouping Sets
- Group By Rollup
- Answer Set for Group By Rollup Query
- Creating a Cube
- Answer Set for Cube Query
- An Easy Example of Creating a Cube
- Quiz - GROUP BY GROUPING SETS Challenge
- Answer To Quiz - GROUP BY GROUPING SETS Challenge
- Getting the Average Values Per Column
- Average Values per Column for all Columns in a Table
- Chapter 6 - Join Functions
- 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 Tables are 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
- Chapter 7 – Date Function
- Current_Timestamp
- Getdate
- Date and Time Keywords
- SYSDATETIMEOFFSET Provides the Timezone Offset
- SYSDATETIMEOFFSET Provides the Timezone Offset
- Using both CAST and CONVERT in Literal Values
- Using Both CAST and CONVERT in Literal Values
- Using both CAST and CONVERT in Literal Values
- The DATEADD Function
- The DATEDIFF Function
- DATEADD Function
- A Real World Example for DateAdd Using the Order Table
- DATEPART Function
- DATEPART Function Examples
- YEAR, MONTH, and DAY Functions
- A Better Technique for YEAR, MONTH, and DAY Functions
- DATENAME Function
- Date Formatting
- Time Formatting
- ISDATE Function
- Chapter 8 - Temporary Tables
- There are three types of Temporary Tables in TEMPDB
- Tables in TEMPDB are not your only Temporary Storage
- What is TEMPDB?
- Creating a Private Temporary Table
- You Populate a Private Temporary Table with an INSERT/SELECT
- The Three Steps to Use a Private Temporary Table
- Creating a Global Temporary Table
- You Populate a Global Temporary Table with an INSERT/SELECT
- The Three Steps to Use a Global Temporary Table
- Creating a Direct Temporary Table
- You Populate a Direct Temporary Table with an INSERT/SELECT
- The Three Steps to Use a Direct Temporary Table
- 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
- The Same Derived Query shown Three Different Ways
- MULTIPLE Derived Tables using the WITH Command
- Column Alias Can Default For Normal Columns
- Most Derived Tables Are Used To Join To Other Tables
- A Join Example Showing Different Column Alias Styles
- The Three Components of a Derived Table
- Visualize This Derived Table
- 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
- RECURSIVE Derived Table Hierarchy
- RECURSIVE Derived Table Query
- RECURSIVE Derived Table Definition
- WITH RECURSIVE Derived Table Seeding
- WITH RECURSIVE Derived Table Looping
- RECURSIVE Derived Table Looping in Slow Motion
- RECURSIVE Derived Table Looping Continued
- RECURSIVE Derived Table Looping Continued
- RECURSIVE Derived Table Ends the Looping
- RECURSIVE Derived Table Ends the Looping
- RECURSIVE Derived Table Definition
- RECURSIVE Derived Table Answer Set
- Using a Table Variable
- 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
- 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 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
- 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 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
- 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 10 – Window Functions OLAP
- The Row_Number Command
- Quiz – How did the Row_Number Reset?
- Quiz – How did the Row_Number Reset?
- Using a Derived Table and Row_Number
- Ordered Analytics OVER
- RANK and DENSE RANK
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK() OVER and PARTITION BY
- 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
- CURRENT ROW AND UNBOUNDED FOLLOWING
- Different Windowing Options
- Moving Sum has a Moving Window
- 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 Window is Current Row and Preceding
- How Moving Average Handles the Sort
- Moving Average
- Moving Average
- 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
- Moving Difference using ANSI Syntax
- Moving Difference using ANSI Syntax with Partition By
- COUNT OVER for a Sequential Number
- COUNT OVER without Rows Unbounded Preceding
- 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
- MAX OVER Without Rows Unbounded Preceding
- The MIN OVER Command
- Troubleshooting MIN OVER
- Finding a Value of a Column in the Next Row with MIN
- The CSUM For Each Product_Id and the Next Start Date
- Quiz – Fill in the Blank
- Answer – Fill in the Blank
- How Ntile Works
- Ntile
- Ntile Continued
- Ntile Percentile
- Another Ntile Example
- Using Tertiles (Partitions of Four)
- NTILE
- NTILE Using a Value of 10
- NTILE With a Partition
- Using FIRST_VALUE
- FIRST_VALUE
- FIRST_VALUE After Sorting by the Highest Value
- FIRST_VALUE with Partitioning
- Using LAST_VALUE
- LAST_VALUE
- Using LAG and LEAD
- Using LEAD
- Using LEAD With and Offset of 2
- LEAD
- LEAD With Partitioning
- Using LAG
- Using LAG With an Offset of 2
- LAG
- LAG with Partitioning
- CUME_DIST
- CUME_DIST With a Partition
- SUM(SUM(n))
- Chapter 11 - Working with Strings
- The ASCII Function
- The CHAR Function
- The UNICODE Function
- The NCHAR Function
- The LEN Function
- The DATALENGTH Function
- Concatenation
- The RTRIM and LTRIM Command trims Spaces
- The SUBSTRING Command
- Using SUBSTRING to move Backwards
- How SUBSTRING Works with a Starting Position of -1
- How SUBSTRING Works with an Ending Position of 0
- Concatenation and SUBSTRING
- SUBSTRING and Different Aliasing
- The LEFT and RIGHT Functions
- Four Concatenations Together
- The DATALENGTH Function and RTRIM
- A Visual of the TRIM Command Using Concatenation
- CHARINDEX Function Finds a Letter(s) Position in a String
- The CHARINDEX Command is brilliant with SUBSTRING
- The CHARINDEX Command Using a Literal
- PATINDEX Function
- PATINDEX Function to Find a Character Pattern
- SOUNDEX Function to Find a Sound
- DIFFERENCE Function to Quantile a Sound
- The REPLACE Function
- LEN and REPLACE Functions for Number of Occurrences
- REPLICATE Function
- STUFF Function
- STUFF without Deleting Function
- UPPER and lower Functions
- Chapter 12 - Interrogating the Data
- Quiz – What would the Answer be?
- Answer to Quiz – What would the Answer be?
- The NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- Answer– Fill in the Answers for the NULLIF Command
- The COALESCE Command – Fill In the Answers
- The COALESCE Answer Set
- COALESCE is Equivalent to This CASE Statement
- The Basics of CAST (Convert and Store)
- Some Great CAST (Convert and Store) Examples
- Some Great CAST (Convert and Store) Examples
- A Rounding Example
- Quiz - CAST Examples
- Answer to Quiz - CAST Examples
- Quiz - The Basics of the CASE Statements
- Answer to Quiz - The Basics of the CASE Statements
- Using an ELSE in the Case Statement
- Using an ELSE as a Safety Net
- Rules For a Valued Case Statement
- Rules for a Searched Case Statement
- 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
- Quiz -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 13 – Table Create and Data Types
- Creating a Table that is a Heap
- Heap Page
- Extents
- Creating a Table That Has a Clustered Index
- Clustered Index Page
- When Do I Create a Clustered Index?
- B-Trees
- The Building of a B-Tree For a Clustered Index (1 of 3)
- The Building of a B-Tree For a Clustered Index (2 of 3)
- The Building of a B-Tree For a Clustered Index (3 of 3)
- The Row Offset Array is the Guidance System For Every Row
- The Row Offset Array Provides Two Search Options (1 of 2)
- The Row Offset Array Provides Two Search Options (2 of 2)
- The Row Offset Array Helps With Inserts
- Adding an Index
- When Do I Create a Non Clustered Index?
- B-Tree For Non Clustered Index on a Clustered Table (1 of 2)
- B-Tree For Non Clustered Index on a Clustered Table (2 of 2)
- Adding A Non Clustered Index To A Heap
- B-Tree For Non Clustered Index on a Heap Table (1 of 2)
- B-Tree for a Non Clustered Index on a Heap Table (2 of 2)
- SELECT INTO
- SELECT INTO
- A Primary Key Constraint
- The Difference between a Primary Key vs. Unique Constraint
- Primary Key Foreign Key Constraints
- More Information about Foreign Key Constraints
- Check Constraint
- Default Values
- Identity Columns
- Computed Columns
- Compression
- ROWVERSION
- 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
- Why Bother To ALTER A View
- Two Exceptions to the ORDER BY Rule inside a View
- How to Get HELP with 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
- 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 – 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
- INSERT/SELECT Command
- INSERT/SELECT Example using All Columns (*)
- INSERT/SELECT Example with Less Columns
- 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 UPDATE
- The DELETE Command Basic Syntax
- Two DELETE Examples to DELETE ALL Rows in a Table
- To DELETE or to TRUNCATE
- TRUNCATE is Different from DELETE
- A DELETE Example Deleting only Some of the Rows
- Want to know How Many Rows were just changed
- Subquery and Join DELETE Command Syntax
- Example of Subquery DELETE Command
- Example of Join DELETE Command
- MERGE INTO
- MERGE INTO
- MERGE INTO
- MERGE INTO
- MERGE INTO
- MERGE INTO Example that Matches
- MERGE INTO Example that does NOT Match
- User Defined Functions (UDFs)
- User Defined Functions (UDFs)
- User Defined Function Example
- Replace
- Replace
- User Defined Types (UDTs)
- Chapter 16 – Set Operators Functions
- Rules of Set Operators
- INTERSECT Explained Logically
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- UNION ALL Explained Logically
- EXCEPT Explained Logically
- EXCEPT Explained Logically
- Another EXCEPT Example
- EXCEPT Explained Logically in Reverse Order
- 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
- 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
- Building Grouping Sets Using UNION
- Three Grouping Sets Using a UNION
- Chapter 17 – Stored Procedures
- Creating a Stored Procedure
- Executing a Stored Procedure
- There are Three Ways to Execute a Stored Procedure
- Dropping a Stored Procedure
- Passing an Input Parameter to a Stored Procedure
- Executing With Positional Parameters vs. Named Parameters
- Passing an Output Parameter to a Stored Procedure
- Changing a Stored Procedure with an ALTER
- Answer Set for the Altered Stored Procedure
- Displaying a Stored Procedure’s Definition
- Encrypting a Stored Procedure’s Definition
- Using a Stored Procedure to Delete a Row
- A Different Method to Delete a Row
- Deleting a Row Using an Input Parameter
- Using Loops in Stored Procedures
- Stored Procedure Workshop
- Looping with a WHILE Statement
- Chapter 18 – Statistical Aggregate Functions
- Chapter 19 – Nexus
- Nexus is Now Available on the Microsoft Azure Cloud
- Nexus Queries Every Major System
- Setup of Nexus is as easy as pie
- Setup of Nexus is a Easy as 1, 2, 3
- Nexus Data Visualization
- Nexus Data Visualization
- Nexus Data Visualization Shows What Tables Can Be Joined
- Nexus is doing a Five-Table Join
- Nexus Generates the SQL Automatically
- Nexus Delivers the Report
- Cross-System Joins from Teradata, Oracle and SQL Server
- The Tab of the Super Join Builder
- The 9 Tabs of the Super Join Builder – Objects Tab 1
- Selecting Columns in the Objects Tab
- The 9 Tabs of the Super Join Builder – Columns Tab 2
- Removing Columns from the Report in the Columns Tab
- The 9 Tabs of the Super Join Builder – Sorting Tab 3
- The 9 Tabs of the Super Join Builder – Joins Tab 4
- The 9 Tabs of the Super Join Builder – Where Tab 5
- Using the WHERE Tab For Additional WHERE or AND
- The 9 Tabs of the Super Join Builder – SQL Tab 6
- The 9 Tabs of the Super Join Builder – Answer Set Tab 7
- The 9 Tabs of the Super Join Builder – Analytics Tab 9
- Analytics Tab
- Analytics Tab – OLAP Example
- Analytics Tab – OLAP Example of SQL Generated
- Analytics Tab – Grouping Sets Example
- Analytics Tab – Grouping Sets Answer Set
- Nexus Data Movement
- Moving a Single Table To a Different System
- The Single Table Data Movement Screen
- Moving an Entire Database To a Different System
- The Database Mover Screen
- The Database Mover Options Tab
- Converting DDL Table Structures
- Converting DDL Table Structures
- Converting DDL Table Structures
- Hound Dog Compression
- Hound Dog Compression on Teradata
- Hound Dog Compression on Teradata
Chapter 4 – 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!
TOP Command with PERCENT
In the above example, we brought back 2 rows only. This is because of the TOP 20 percent statement. We brought back 20 percent of the rows.
TOP Command with PERCENT
In the above example, we brought back 5 rows only. This is because of the TOP 50 percent statement. We brought back 50 percent of the rows, but also notice the ORDER BY statement. We brought back the top 50 percent of students with the highest grade_pt averages.
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 Using a Variable
“You miss 100 percent of the shots you never take.”
– Wayne Gretzky
You can use the TOP command in conjunction with a variable. Above, we declared a variable called @TOPVAR. We set the variable to 5. If we highlight the DECLARE and the Query we get the answer set providing the TOP 5 salaried employees.
The TOP 1 Command for a Random Sample
SELECT TOP(1) *
FROM Employee_Table
ORDER BY NewID() ;
For those times that you need a single random row, you can use the TOP 1 technique and sorting by newid(). When you combine the TOP 1 with a sort by newid(), they return a random row each time the query executes. There is a performance cost to using TOP(1) and newid() because the system has to add a uniqueidentifier to every row and then sort by the uniqueidentifier. A better solution is to add a tablesample option to the table when randomly selecting a single row from a large table. Be careful though, because if the table is smaller sometimes no rows come back.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.