You are previewing Microsoft SQL Server 2012 T-SQL.
O'Reilly logo
Microsoft SQL Server 2012 T-SQL

Book Description

One of the most popular databases worldwide is the 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. With easy to use sample tables, and 700 pages of real-world example’s and explanations, this book is your perfect reference guide. 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, makes this book a worldwide masterpiece where all can learn.

Table of Contents

  1. Cover
  2. The Tera-Tom Video Series
  3. The Tera-Tom Genius Series
  4. Tera-Tom- Author of over 50 Books
  5. The Best Query Tool Works on all Systems
  6. Trademarks and Copyrights
  7. About Tom Coffing
  8. About Debra Aaron
  9. Contents
  10. Chapter 1 – Introduction
    1. Introduction to the Family of SQL Server Products
    2. Introduction to the Family Continued
    3. Microsoft Azure SQL Data Warehouse
    4. Nexus is Now Available on the Microsoft Azure Cloud
    5. Symmetric Multi-Processing (SMP)
    6. Naming of Objects
  11. Chapter 2 – The Basics of SQL
    1. Introduction
    2. Setting Your Default Database
    3. SELECT * (All Columns) in a Table
    4. Fully Qualifying a Database, Schema and Table
    5. SELECT Specific Columns in a Table
    6. Commas in the Front or Back?
    7. Place your Commas in front for better Debugging Capabilities
    8. Sort the Data with the ORDER BY Keyword
    9. ORDER BY Defaults to Ascending
    10. Use the Name or the Number in your ORDER BY Statement
    11. Two Examples of ORDER BY using Different Techniques
    12. Changing the ORDER BY to Descending Order
    13. NULL Values sort First in Ascending Mode (Default)
    14. NULL Values sort Last in Descending Mode (DESC
    15. Major Sort vs. Minor Sorts
    16. Multiple Sort Keys using Names vs. Numbers
    17. Sorts are Alphabetical, NOT Logical
    18. Using A CASE Statement to Sort Logically
    19. An Order By That Uses an Expression
    20. How to ALIAS a Column Name
    21. Aliasing a Column Name with Spaces or Reserved Words
    22. A Missing Comma can by Mistake become an Alias
    23. Comments using Double Dashes are Single Line Comments
    24. Comments for Multi-Lines
    25. Comments for Multi-Lines as Double Dashes Per Line
    26. A Great Technique for Comments to Look for SQL Errors
    27. sp_help at the Database Level
    28. sp_help at the Object Level
    29. Getting System Information
    30. Getting Additional System Information
  12. Chapter 3 – The WHERE Clause
    1. The WHERE Clause limits Returning Rows
    2. Double Quoted Aliases are for Reserved Words and Spaces
    3. Using A Column ALIAS In A WHERE Clause
    4. Using A Column ALIAS In An ORDER BY Clause
    5. In What Order Does SQL Server Process A Query?
    6. Character Data needs Single Quotes in the WHERE Clause
    7. Character Data needs Single Quotes, but Numbers Don’t
    8. Declaring a Variable
    9. Comparisons against a Null Value
    10. NULL means UNKNOWN DATA so Equal (=) won’t Work
    11. Use IS NULL or IS NOT NULL when dealing with NULLs
    12. NULL is UNKNOWN DATA so NOT Equal won’t Work
    13. Use IS NULL or IS NOT NULL when dealing with NULLs
    14. Using Greater Than or Equal To (>=)
    15. AND in the WHERE Clause
    16. Troubleshooting AND
    17. OR in the WHERE Clause
    18. Troubleshooting Or
    19. Troubleshooting Character Data
    20. Using Different Columns in an AND Statement
    21. Quiz – How many rows will return?
    22. Answer to Quiz – How many rows will return?
    23. What is the Order of Precedence?
    24. Using Parentheses to change the Order of Precedence
    25. Using an IN List in place of OR
    26. The IN List is an Excellent Technique
    27. IN List vs. OR brings the same Results
    28. The IN List Can Use Character Data
    29. Using a NOT IN List
    30. Null Values in a NOT IN List Bring Back No Rows
    31. A Technique for Handling Nulls with a NOT IN List
    32. BETWEEN is Inclusive
    33. NOT BETWEEN is Also Inclusive
    34. LIKE command Underscore is Wildcard for one Character
    35. LIKE command using a Range of Values
    36. LIKE command Using a NOT Range of Values
    37. LIKE Command Works Differently on Char Vs Varchar
    38. Troubleshooting LIKE Command on Character Data
    39. Introducing the RTRIM Command
    40. Quiz – What Data is Left Justified and What is Right?
    41. Numbers are Right Justified and Character Data is Left
    42. Answer – What Data is Left Justified and What is Right?
    43. An Example of Data with Left and Right Justification
    44. A Visual of CHARACTER Data vs. VARCHAR Data
    45. RTRIM command Removes Trailing spaces on CHAR Data
    46. Using Like with an AND Clause to Find Multiple Letters
    47. Using Like with an OR Clause to Find Either Letters
    48. Declaring a Variable and Using it with the LIKE Command
    49. Escape Character in the LIKE Command changes Wildcards
    50. Escape Characters Turn off Wildcards in the LIKE Command
    51. Quiz – Turn off that Wildcard
    52. ANSWER – To Find that Wildcard
  13. Chapter 4 – Distinct, Group By and TOP
    1. The Distinct Command
    2. Distinct vs. GROUP BY
    3. Quiz – How many rows come back from the Distinct?
    4. Answer – How many rows come back from the Distinct?
    5. TOP Command
    6. TOP Command is brilliant when ORDER BY is used!
    7. TOP Command with PERCENT
    8. TOP Command with PERCENT
    9. The TOP Command WITH TIES
    10. The TOP Command Using a Variable
    11. The TOP 1 Command for a Random Sample
  14. Chapter 5 – Aggregation
    1. Quiz – You calculate the Answer Set in your own Mind
    2. Answer – You calculate the Answer Set in your own Mind
    3. The 3 Rules of Aggregation
    4. There are Five Aggregates
    5. Quiz – How many rows come back?
    6. Answer – How many rows come back?
    7. Troubleshooting Aggregates
    8. GROUP BY when Aggregates and Normal Columns Mix
    9. GROUP BY delivers one row per Group
    10. Count_Big
    11. Limiting Rows and Improving Performance with WHERE
    12. WHERE Clause in Aggregation limits unneeded Calculations
    13. Keyword HAVING tests Aggregates after they are Totaled
    14. CHECKSUM_AGG to Check If a Table Has Changed
    15. Using Distinct Within the Aggregate
    16. Group By Grouping Sets
    17. Group By Rollup
    18. Answer Set for Group By Rollup Query
    19. Creating a Cube
    20. Answer Set for Cube Query
    21. An Easy Example of Creating a Cube
    22. Quiz - GROUP BY GROUPING SETS Challenge
    23. Answer To Quiz - GROUP BY GROUPING SETS Challenge
    24. Getting the Average Values Per Column
    25. Average Values per Column for all Columns in a Table
  15. Chapter 6 - Join Functions
    1. A Two-Table Join Using Traditional Syntax
    2. A two-table join using Non-ANSI Syntax with Table Alias
    3. You Can Fully Qualify All Columns
    4. A two-table join using ANSI Syntax
    5. Both Queries have the same Results and Performance
    6. Quiz – Can You Finish the Join Syntax?
    7. Answer to Quiz – Can You Finish the Join Syntax?
    8. Quiz – Can You Find the Error?
    9. Answer to Quiz – Can You Find the Error?
    10. Super Quiz – Can You Find the Difficult Error?
    11. Answer to Super Quiz – Can You Find the Difficult Error?
    12. Quiz – Which rows from both tables won’t Return?
    13. Answer to Quiz – Which rows from both tables Won’t Return?
    14. LEFT OUTER JOIN
    15. LEFT OUTER JOIN Results
    16. RIGHT OUTER JOIN
    17. RIGHT OUTER JOIN Example and Results
    18. FULL OUTER JOIN
    19. FULL OUTER JOIN Results
    20. Which Tables are the Left and which Tables are Right?
    21. Answer - Which Tables are the Left and Which are the Right?
    22. INNER JOIN with Additional AND Clause
    23. ANSI INNER JOIN with Additional AND Clause
    24. ANSI INNER JOIN with Additional WHERE Clause
    25. OUTER JOIN with Additional WHERE Clause
    26. OUTER JOIN with Additional AND Clause
    27. OUTER JOIN with Additional AND Clause Results
    28. Quiz – Why is this considered an INNER JOIN?
    29. Evaluation Order for Outer Queries
    30. The DREADED Product Join
    31. The DREADED Product Join Results
    32. The Horrifying Cartesian Product Join
    33. The ANSI Cartesian Join will ERROR
    34. Quiz – Do these Joins Return the Same Answer Set?
    35. Answer – Do these Joins Return the Same Answer Set?
    36. The CROSS JOIN
    37. The CROSS JOIN Answer Set
    38. The Self Join
    39. The Self Join with ANSI Syntax
    40. Quiz – Will both queries bring back the same Answer Set?
    41. Answer – Will both queries bring back the same Answer Set?
    42. Quiz – Will both queries bring back the same Answer Set?
    43. Answer – Will both queries bring back the same Answer Set?
    44. How would you Join these two tables?
    45. An Associative Table is a Bridge that Joins Two Tables
    46. Quiz – Can you write the 3-Table Join?
    47. Answer to Quiz – Can you Write the 3-Table Join?
    48. Quiz – Can you write the 3-Table Join to ANSI Syntax?
    49. Answer – Can you Write the 3-Table Join to ANSI Syntax?
    50. Quiz – Can you Place the ON Clauses at the End?
    51. Answer – Can you Place the ON Clauses at the End?
    52. The 5-Table Join – Logical Insurance Model
    53. Quiz - Write a Five Table Join Using ANSI Syntax
    54. Answer - Write a Five Table Join Using ANSI Syntax
    55. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    56. Answer - Write a Five Table Join Using Non-ANSI Syntax
    57. Quiz –Re-Write this putting the ON clauses at the END
    58. Answer –Re-Write this putting the ON clauses at the END
  16. Chapter 7 – Date Function
    1. Current_Timestamp
    2. Getdate
    3. Date and Time Keywords
    4. SYSDATETIMEOFFSET Provides the Timezone Offset
    5. SYSDATETIMEOFFSET Provides the Timezone Offset
    6. Using both CAST and CONVERT in Literal Values
    7. Using Both CAST and CONVERT in Literal Values
    8. Using both CAST and CONVERT in Literal Values
    9. The DATEADD Function
    10. The DATEDIFF Function
    11. DATEADD Function
    12. A Real World Example for DateAdd Using the Order Table
    13. DATEPART Function
    14. DATEPART Function Examples
    15. YEAR, MONTH, and DAY Functions
    16. A Better Technique for YEAR, MONTH, and DAY Functions
    17. DATENAME Function
    18. Date Formatting
    19. Time Formatting
    20. ISDATE Function
  17. Chapter 8 - Temporary Tables
    1. There are three types of Temporary Tables in TEMPDB
    2. Tables in TEMPDB are not your only Temporary Storage
    3. What is TEMPDB?
    4. Creating a Private Temporary Table
    5. You Populate a Private Temporary Table with an INSERT/SELECT
    6. The Three Steps to Use a Private Temporary Table
    7. Creating a Global Temporary Table
    8. You Populate a Global Temporary Table with an INSERT/SELECT
    9. The Three Steps to Use a Global Temporary Table
    10. Creating a Direct Temporary Table
    11. You Populate a Direct Temporary Table with an INSERT/SELECT
    12. The Three Steps to Use a Direct Temporary Table
    13. CREATING A Derived Table
    14. Naming the Derived Table
    15. Aliasing the Column Names in the Derived Table
    16. Multiple Ways to Alias the Columns in a Derived Table
    17. CREATING a Derived Table using the WITH Command
    18. The Same Derived Query shown Three Different Ways
    19. MULTIPLE Derived Tables using the WITH Command
    20. Column Alias Can Default For Normal Columns
    21. Most Derived Tables Are Used To Join To Other Tables
    22. A Join Example Showing Different Column Alias Styles
    23. The Three Components of a Derived Table
    24. Visualize This Derived Table
    25. Our Join Example With The WITH Syntax
    26. Quiz - Answer the Questions
    27. Answer to Quiz - Answer the Questions
    28. Clever Tricks on Aliasing Columns in a Derived Table
    29. A Derived Table lives only for the lifetime of a single query
    30. An Example of Two Derived Tables in a Single Query
    31. RECURSIVE Derived Table Hierarchy
    32. RECURSIVE Derived Table Query
    33. RECURSIVE Derived Table Definition
    34. WITH RECURSIVE Derived Table Seeding
    35. WITH RECURSIVE Derived Table Looping
    36. RECURSIVE Derived Table Looping in Slow Motion
    37. RECURSIVE Derived Table Looping Continued
    38. RECURSIVE Derived Table Looping Continued
    39. RECURSIVE Derived Table Ends the Looping
    40. RECURSIVE Derived Table Ends the Looping
    41. RECURSIVE Derived Table Definition
    42. RECURSIVE Derived Table Answer Set
    43. Using a Table Variable
  18. Chapter 9 – Sub-query Functions
    1. An IN List is much like a Subquery
    2. An IN List Never has Duplicates – Just like a Subquery
    3. An IN List Ignores Duplicates
    4. The Subquery
    5. The Three Steps of How a Basic Subquery Works
    6. These are Equivalent Queries
    7. The Final Answer Set from the Subquery
    8. Quiz- Answer the Difficult Question
    9. Answer to Quiz- Answer the Difficult Question
    10. Should you use a Subquery of a Join?
    11. Quiz- Write the Subquery
    12. Answer to Quiz- Write the Subquery
    13. Quiz- Write the More Difficult Subquery
    14. Answer to Quiz- Write the More Difficult Subquery
    15. Quiz – Write the Extreme Subquery
    16. Answer to Quiz – Write the Extreme Subquery
    17. Quiz- Write the Subquery with an Aggregate
    18. Answer to Quiz- Write the Subquery with an Aggregate
    19. Quiz- Write the Correlated Subquery
    20. Answer to Quiz- Write the Correlated Subquery
    21. The Basics of a Correlated Subquery
    22. The Top Query always runs first in a Correlated Subquery
    23. Correlated Subquery Example vs. a Join with a Derived Table
    24. Quiz- A Second Chance to Write a Correlated Subquery
    25. Answer - A Second Chance to Write a Correlated Subquery
    26. Quiz- A Third Chance to Write a Correlated Subquery
    27. Answer - A Third Chance to Write a Correlated Subquery
    28. Quiz- Last Chance To Write a Correlated Subquery
    29. Answer – Last Chance to Write a Correlated Subquery
    30. Quiz – Write the Extreme Correlated Subquery
    31. Answer To Quiz – Write the Extreme Correlated Subquery
    32. Quiz- Write the NOT Subquery
    33. Answer to Quiz- Write the NOT Subquery
    34. Quiz- Write the Subquery using a WHERE Clause
    35. Answer - Write the Subquery using a WHERE Clause
    36. Quiz – Write the Triple Subquery
    37. Answer to Quiz – Write the Triple Subquery
    38. Quiz – How many rows return on a NOT IN with a NULL?
    39. Answer – How many rows return on a NOT IN with a NULL?
    40. How to handle a NOT IN with Potential NULL Values
    41. Using a Correlated Exists
    42. How a Correlated Exists matches up
    43. The Correlated NOT Exists
    44. The Correlated NOT Exists Answer Set
    45. Quiz – How many rows come back from this NOT Exists?
    46. Answer – How many rows come back from this NOT Exists?
  19. Chapter 10 – Window Functions OLAP
    1. The Row_Number Command
    2. Quiz – How did the Row_Number Reset?
    3. Quiz – How did the Row_Number Reset?
    4. Using a Derived Table and Row_Number
    5. Ordered Analytics OVER
    6. RANK and DENSE RANK
    7. RANK Defaults to Ascending Order
    8. Getting RANK to Sort in DESC Order
    9. RANK() OVER and PARTITION BY
    10. CSUM – Rows Unbounded Preceding Explained
    11. CSUM – Making Sense of the Data
    12. CSUM – Making Even More Sense of the Data
    13. CSUM – The Major and Minor Sort Key(s)
    14. The ANSI CSUM – Getting a Sequential Number
    15. Troubleshooting The ANSI OLAP on a GROUP BY
    16. Reset with a PARTITION BY Statement
    17. PARTITION BY only Resets a Single OLAP not ALL of them
    18. CURRENT ROW AND UNBOUNDED FOLLOWING
    19. Different Windowing Options
    20. Moving Sum has a Moving Window
    21. How ANSI Moving SUM Handles the Sort
    22. Quiz – How is that Total Calculated?
    23. Answer to Quiz – How is that Total Calculated?
    24. Moving SUM every 3-rows Vs a Continuous Average
    25. Partition By Resets an ANSI OLAP
    26. The Moving Window is Current Row and Preceding
    27. How Moving Average Handles the Sort
    28. Moving Average
    29. Moving Average
    30. Quiz – How is that Total Calculated?
    31. Answer to Quiz – How is that Total Calculated?
    32. Quiz – How is that 4th Row Calculated?
    33. Answer to Quiz – How is that 4th Row Calculated?
    34. Moving Average every 3-rows Vs a Continuous Average
    35. Partition By Resets an ANSI OLAP
    36. Moving Difference using ANSI Syntax
    37. Moving Difference using ANSI Syntax with Partition By
    38. COUNT OVER for a Sequential Number
    39. COUNT OVER without Rows Unbounded Preceding
    40. Quiz – What caused the COUNT OVER to Reset?
    41. Answer to Quiz – What caused the COUNT OVER to Reset?
    42. The MAX OVER Command
    43. MAX OVER with PARTITION BY Reset
    44. MAX OVER Without Rows Unbounded Preceding
    45. The MIN OVER Command
    46. Troubleshooting MIN OVER
    47. Finding a Value of a Column in the Next Row with MIN
    48. The CSUM For Each Product_Id and the Next Start Date
    49. Quiz – Fill in the Blank
    50. Answer – Fill in the Blank
    51. How Ntile Works
    52. Ntile
    53. Ntile Continued
    54. Ntile Percentile
    55. Another Ntile Example
    56. Using Tertiles (Partitions of Four)
    57. NTILE
    58. NTILE Using a Value of 10
    59. NTILE With a Partition
    60. Using FIRST_VALUE
    61. FIRST_VALUE
    62. FIRST_VALUE After Sorting by the Highest Value
    63. FIRST_VALUE with Partitioning
    64. Using LAST_VALUE
    65. LAST_VALUE
    66. Using LAG and LEAD
    67. Using LEAD
    68. Using LEAD With and Offset of 2
    69. LEAD
    70. LEAD With Partitioning
    71. Using LAG
    72. Using LAG With an Offset of 2
    73. LAG
    74. LAG with Partitioning
    75. CUME_DIST
    76. CUME_DIST With a Partition
    77. SUM(SUM(n))
  20. Chapter 11 - Working with Strings
    1. The ASCII Function
    2. The CHAR Function
    3. The UNICODE Function
    4. The NCHAR Function
    5. The LEN Function
    6. The DATALENGTH Function
    7. Concatenation
    8. The RTRIM and LTRIM Command trims Spaces
    9. The SUBSTRING Command
    10. Using SUBSTRING to move Backwards
    11. How SUBSTRING Works with a Starting Position of -1
    12. How SUBSTRING Works with an Ending Position of 0
    13. Concatenation and SUBSTRING
    14. SUBSTRING and Different Aliasing
    15. The LEFT and RIGHT Functions
    16. Four Concatenations Together
    17. The DATALENGTH Function and RTRIM
    18. A Visual of the TRIM Command Using Concatenation
    19. CHARINDEX Function Finds a Letter(s) Position in a String
    20. The CHARINDEX Command is brilliant with SUBSTRING
    21. The CHARINDEX Command Using a Literal
    22. PATINDEX Function
    23. PATINDEX Function to Find a Character Pattern
    24. SOUNDEX Function to Find a Sound
    25. DIFFERENCE Function to Quantile a Sound
    26. The REPLACE Function
    27. LEN and REPLACE Functions for Number of Occurrences
    28. REPLICATE Function
    29. STUFF Function
    30. STUFF without Deleting Function
    31. UPPER and lower Functions
  21. Chapter 12 - Interrogating the Data
    1. Quiz – What would the Answer be?
    2. Answer to Quiz – What would the Answer be?
    3. The NULLIF Command
    4. Quiz – Fill in the Answers for the NULLIF Command
    5. Answer– Fill in the Answers for the NULLIF Command
    6. The COALESCE Command – Fill In the Answers
    7. The COALESCE Answer Set
    8. COALESCE is Equivalent to This CASE Statement
    9. The Basics of CAST (Convert and Store)
    10. Some Great CAST (Convert and Store) Examples
    11. Some Great CAST (Convert and Store) Examples
    12. A Rounding Example
    13. Quiz - CAST Examples
    14. Answer to Quiz - CAST Examples
    15. Quiz - The Basics of the CASE Statements
    16. Answer to Quiz - The Basics of the CASE Statements
    17. Using an ELSE in the Case Statement
    18. Using an ELSE as a Safety Net
    19. Rules For a Valued Case Statement
    20. Rules for a Searched Case Statement
    21. Valued Case Vs. A Searched Case
    22. Quiz - Valued Case Statement
    23. Answer - Valued Case Statement
    24. Quiz - Searched Case Statement
    25. Answer - Searched Case Statement
    26. Quiz - When NO ELSE is present in CASE Statement
    27. Answer - When NO ELSE is present in CASE Statement
    28. Quiz -When an Alias is NOT used in a CASE Statement
    29. Answer -When an Alias is NOT used in a CASE Statement
    30. Combining Searched Case and Valued Case
    31. A Trick for getting a Horizontal Case
    32. Nested Case
    33. Put a CASE in the ORDER BY
  22. Chapter 13 – Table Create and Data Types
    1. Creating a Table that is a Heap
    2. Heap Page
    3. Extents
    4. Creating a Table That Has a Clustered Index
    5. Clustered Index Page
    6. When Do I Create a Clustered Index?
    7. B-Trees
    8. The Building of a B-Tree For a Clustered Index (1 of 3)
    9. The Building of a B-Tree For a Clustered Index (2 of 3)
    10. The Building of a B-Tree For a Clustered Index (3 of 3)
    11. The Row Offset Array is the Guidance System For Every Row
    12. The Row Offset Array Provides Two Search Options (1 of 2)
    13. The Row Offset Array Provides Two Search Options (2 of 2)
    14. The Row Offset Array Helps With Inserts
    15. Adding an Index
    16. When Do I Create a Non Clustered Index?
    17. B-Tree For Non Clustered Index on a Clustered Table (1 of 2)
    18. B-Tree For Non Clustered Index on a Clustered Table (2 of 2)
    19. Adding A Non Clustered Index To A Heap
    20. B-Tree For Non Clustered Index on a Heap Table (1 of 2)
    21. B-Tree for a Non Clustered Index on a Heap Table (2 of 2)
    22. SELECT INTO
    23. SELECT INTO
    24. A Primary Key Constraint
    25. The Difference between a Primary Key vs. Unique Constraint
    26. Primary Key Foreign Key Constraints
    27. More Information about Foreign Key Constraints
    28. Check Constraint
    29. Default Values
    30. Identity Columns
    31. Computed Columns
    32. Compression
    33. ROWVERSION
  23. Chapter 14 – View Functions
    1. The Fundamentals of Views
    2. Creating a Simple View to Restrict Sensitive Columns
    3. Creating a Simple View to Restrict Rows
    4. Basic Rules for Views
    5. How to Modify a View
    6. Why Bother To ALTER A View
    7. Two Exceptions to the ORDER BY Rule inside a View
    8. How to Get HELP with a View
    9. How to Get HELP with a View
    10. Views sometimes CREATED for Formatting or Row Security
    11. Creating a View to Join Tables Together
    12. You Select From a View
    13. Another Way to Alias Columns in a View CREATE
    14. The Standard Way Most Aliasing is done
    15. What Happens When Both Aliasing Options Are Present
    16. Resolving Aliasing Problems in a View CREATE
    17. Answer to Resolving Aliasing Problems in a View CREATE
    18. Aggregates on View Aggregates
    19. Altering A Table
    20. Altering a Table after a View has been Created
    21. A View that Errors After An ALTER
    22. Troubleshooting a View
    23. Updating Data in a Table through a View
    24. Loading Data through a View
    25. Maintenance Restrictions on a Table through a View
  24. Chapter 15 – Data Manipulation Language (DML)
    1. INSERT Syntax # 1
    2. INSERT Example with Syntax 1
    3. INSERT Syntax #2
    4. INSERT Example with Syntax 2
    5. INSERT Example with Syntax 3
    6. INSERT/SELECT Command
    7. INSERT/SELECT Example using All Columns (*)
    8. INSERT/SELECT Example with Less Columns
    9. The UPDATE Command Basic Syntax
    10. Two UPDATE Examples
    11. Subquery UPDATE Command Syntax
    12. Example of Subquery UPDATE Command
    13. Join UPDATE Command Syntax
    14. Example of an UPDATE Join Command
    15. Fast UPDATE
    16. The DELETE Command Basic Syntax
    17. Two DELETE Examples to DELETE ALL Rows in a Table
    18. To DELETE or to TRUNCATE
    19. TRUNCATE is Different from DELETE
    20. A DELETE Example Deleting only Some of the Rows
    21. Want to know How Many Rows were just changed
    22. Subquery and Join DELETE Command Syntax
    23. Example of Subquery DELETE Command
    24. Example of Join DELETE Command
    25. MERGE INTO
    26. MERGE INTO
    27. MERGE INTO
    28. MERGE INTO
    29. MERGE INTO
    30. MERGE INTO Example that Matches
    31. MERGE INTO Example that does NOT Match
    32. User Defined Functions (UDFs)
    33. User Defined Functions (UDFs)
    34. User Defined Function Example
    35. Replace
    36. Replace
    37. User Defined Types (UDTs)
  25. Chapter 16 – Set Operators Functions
    1. Rules of Set Operators
    2. INTERSECT Explained Logically
    3. INTERSECT Explained Logically
    4. UNION Explained Logically
    5. UNION Explained Logically
    6. UNION ALL Explained Logically
    7. UNION ALL Explained Logically
    8. EXCEPT Explained Logically
    9. EXCEPT Explained Logically
    10. Another EXCEPT Example
    11. EXCEPT Explained Logically in Reverse Order
    12. An Equal Amount of Columns in both SELECT List
    13. Columns in the SELECT list should be from the same Domain
    14. The Top Query handles all Aliases
    15. The Bottom Query does the ORDER BY
    16. Great Trick: Place your Set Operator in a Derived Table
    17. UNION Vs UNION ALL
    18. Using UNION ALL and Literals
    19. A Great Example of how EXCEPT works
    20. USING Multiple SET Operators in a Single Request
    21. Changing the Order of Precedence with Parentheses
    22. Building Grouping Sets Using UNION
    23. Three Grouping Sets Using a UNION
  26. Chapter 17 – Stored Procedures
    1. Creating a Stored Procedure
    2. Executing a Stored Procedure
    3. There are Three Ways to Execute a Stored Procedure
    4. Dropping a Stored Procedure
    5. Passing an Input Parameter to a Stored Procedure
    6. Executing With Positional Parameters vs. Named Parameters
    7. Passing an Output Parameter to a Stored Procedure
    8. Changing a Stored Procedure with an ALTER
    9. Answer Set for the Altered Stored Procedure
    10. Displaying a Stored Procedure’s Definition
    11. Encrypting a Stored Procedure’s Definition
    12. Using a Stored Procedure to Delete a Row
    13. A Different Method to Delete a Row
    14. Deleting a Row Using an Input Parameter
    15. Using Loops in Stored Procedures
    16. Stored Procedure Workshop
    17. Looping with a WHILE Statement
  27. Chapter 18 – Statistical Aggregate Functions
    1. The Stats Table
    2. Above is the Stats_Table data in which we will use in our statistical examples.
    3. The VAR and VARP Functions
    4. A VAR Example
    5. A VARP Example
    6. The STDEV and STDEVP Functions
    7. A STDEV Example
    8. A STDEVP Example
  28. Chapter 19 – Nexus
    1. Nexus is Now Available on the Microsoft Azure Cloud
    2. Nexus Queries Every Major System
    3. Setup of Nexus is as easy as pie
    4. Setup of Nexus is a Easy as 1, 2, 3
    5. Nexus Data Visualization
    6. Nexus Data Visualization
    7. Nexus Data Visualization Shows What Tables Can Be Joined
    8. Nexus is doing a Five-Table Join
    9. Nexus Generates the SQL Automatically
    10. Nexus Delivers the Report
    11. Cross-System Joins from Teradata, Oracle and SQL Server
    12. The Tab of the Super Join Builder
    13. The 9 Tabs of the Super Join Builder – Objects Tab 1
    14. Selecting Columns in the Objects Tab
    15. The 9 Tabs of the Super Join Builder – Columns Tab 2
    16. Removing Columns from the Report in the Columns Tab
    17. The 9 Tabs of the Super Join Builder – Sorting Tab 3
    18. The 9 Tabs of the Super Join Builder – Joins Tab 4
    19. The 9 Tabs of the Super Join Builder – Where Tab 5
    20. Using the WHERE Tab For Additional WHERE or AND
    21. The 9 Tabs of the Super Join Builder – SQL Tab 6
    22. The 9 Tabs of the Super Join Builder – Answer Set Tab 7
    23. The 9 Tabs of the Super Join Builder – Analytics Tab 9
    24. Analytics Tab
    25. Analytics Tab – OLAP Example
    26. Analytics Tab – OLAP Example of SQL Generated
    27. Analytics Tab – Grouping Sets Example
    28. Analytics Tab – Grouping Sets Answer Set
    29. Nexus Data Movement
    30. Moving a Single Table To a Different System
    31. The Single Table Data Movement Screen
    32. Moving an Entire Database To a Different System
    33. The Database Mover Screen
    34. The Database Mover Options Tab
    35. Converting DDL Table Structures
    36. Converting DDL Table Structures
    37. Converting DDL Table Structures
    38. Hound Dog Compression
    39. Hound Dog Compression on Teradata
    40. Hound Dog Compression on Teradata