Description

One of the most popular databases worldwide is Microsoft’s SQL Server. The Microsoft Azure Cloud is rapidly making T-SQL one of the standards of SQL among millions of companies. The Microsoft SQL Server 2012 T-SQL book is your key to mastering T-SQL. This book is your perfect reference guide with easy-to-use sample tables and 700 pages of real-world examples and explanations. Each chapter in this book starts with simple examples and continues to use a building block approach to teach both the new user and the advanced user. Pleasing to the eye and stimulating to the mind, this book is a worldwide masterpiece where all can learn.

Topics:

• Major Sort vs. Minor Sorts
• The Like Command Wildcards are Percent and Underscore
• Distinct Vs. Group By
• There are Five Aggregates
• Inner and Outer 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)
• Stored Procedures
• Statistical Aggregate Functions

 

Microsoft SQL Server T-SQL Course Outline

Chapter 1 – Introduction

Chapter 1 – Introduction
Introduction to the Family of SQL Server Products
Introduction to the Family Continued
Microsoft Azure SQL Data Warehouse
Nexus is Now Available on the Microsoft Azure Cloud
Symmetric Multi-Processing (SMP)
Naming of Objects

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 – 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 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

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 Definition
RECURSIVE Derived Table Definition
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 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

Why Use Stored Procedures?
Stored Procedure Rules
Stored Procedure Best Practices
Statements that are NOT Allowed in a Stored Procedure
Creating a Stored Procedure
Executing a Stored Procedure
There are Three Ways to Execute a Stored Procedure
Dropping a Stored Procedure
Changing a Stored Procedure with an ALTER
Answer Set for the Altered Stored Procedure
Three Ways to Get Output – 1) Output Parameter
Three Ways to Get Output – 2) RETURN Statement
Three Ways to Get Output – 3) SELECT Statement
Create a Stored Procedure that Sorts Using a Valued CASE
Answer – Create a Stored Procedure that Sorts Using CASE
Create a Stored Procedure that Sorts Using a Searched CASE
Answer – Create a Stored Procedure that Sorts Using CASE
Passing an Input Parameter to a Stored Procedure
Executing with Positional Parameters vs. Named Parameters
Quiz – Using IF, ELSE IF and ELSE
Answer – Using IF, ELSE IF and ELSE
Quiz – Using the PRINT Command
Answer – Using the PRINT Command
Quiz – Using the CASE Command
Answer – Using the CASE Command
Quiz – Using the CASE Command
Quiz – Get a Horizontal Case
Answer – Get a Horizontal Case
Passing an Output Parameter to a Stored Procedure
Displaying a Stored Procedure’s Definition
Another Way to Display a Stored Procedure’s Definition
Encrypting a Stored Procedure’s Definition
Quiz – Aggregation in a Stored Procedure With CAST
Answer – Aggregation in a Stored Procedure With CAST
Positional Parameters, Named Parameters and Both
Answer – GROUP BY GROUPING SETS Challenge
Quiz – GROUP BY ROLLUP Challenge
Answer To Quiz – GROUP BY ROLLUP Challenge
Quiz – Set, Rollup and Cube Challenge
Answer – Set, Rollup and Cube Challenge
Quiz – Two Stored Procedures and a Temp Table
Answer – Two Stored Procedures and a Temp Table
Answer – 2 Stored Procedures, 2 Temp Tables and a UNION
Results – 2 Stored Procedures, 2 Temp Tables and a UNION
Using PIVOT
Using PIVOT For Daily Data
Using PIVOT For Quarterly and Yearly Data
Using PIVOT For Quarterly and Yearly Data
Using UNPIVOT
Variables and Defaults
Using a Stored Procedure To Delete a Row
A Different Method To Delete a Row
Deleting a Row Using an Input Parameter
Global Variable – @@rowcount
Global Variable – @@identity
Global Variable – @@identity in a Stored Procedure
Using Loops in Stored Procedures
Stored Procedure Workshop
Looping with a WHILE Statement
Cursors
Cursor Example 1 of 4
Cursor Example 2 of 4
Cursor Example 3 of 4
Cursor Example 4 of 4
The WITH RESULTS SET Clause
Stored Procedures and User Defined Functions
Using Dynamic SQL – Method One
Using Dynamic SQL – Method Two
Special Stored Procedures and Static Queries
Special Stored Procedures and Dynamic Queries
Using a Cursor to Retrieve All Tables and Row Counts
Using sp_Msforeachtable to Retrieve Tables and Row Counts
Using TRY and CATCH for Error Trapping
Using Transactions
Quiz – Using Transactions
Answer – Using Transactions
Recursion
Recursion and Factorial Example
Factorial Example Using a WHILE Loop
Getting the Fibonacci Numbers using a Temp Table
Creating Identity Columns
Identity Columns That Have a Relationship Across Tables
Function Example to Get a Date
Loading Data to a Table with an XML Data Type
Querying a Table with an XML Data Type

Chapter 18 – Statistical Aggregate Functions

The Stats Table
Above is the Stats_Table data in which we will use in our statistical examples.
The VAR and VARP Functions
A VAR Example
A VARP Example
The STDEV and STDEVP Functions
A STDEV Example
A STDEVP Example

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