Cover image for Amazon Redshift: A Columnar Database SQL and Architecture

Amazon Redshift: A Columnar Database SQL and Architecture

Author David Cook , Tom Coffing
Release Date: 2014/12/01
ISBN: 9781940540290
Topic:
0%
28
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Amazon Redshift: A Columnar Database SQL and Architecture illuminates the brilliance behind Amazon’s Redshift technology. It is over 600 pages long, and it shows users how to set it up, tune it, load and go. This book also contains all of the SQL you need to query it with ease. After reading this book, you will know why more and more companies are using Redshift as part of their overall data warehouse strategy.

Table of Contents

  1. Cover
  2. The Tera-Tom Genius Series
  3. Tera-Tom- Author of over 50 Books
  4. The Best Query Tool Works on all Systems
  5. Copyright
  6. About Tom Coffing
  7. About Dave Cook
  8. Contents
  9. Chapter 1 - What is Columnar?
    1. What is Parallel Processing?
    2. The Basics of a Single Computer
    3. Data in Memory is Fast as Lightning
    4. Parallel Processing Of Data
    5. A Table has Columns and Rows
    6. Each Parallel Process Organizes the Rows inside a Data Block
    7. Moving Data Blocks is Like Checking In Luggage
    8. Facts That Are Disturbing
    9. Why Columnar?
    10. Row Based Blocks vs. Columnar Based Blocks
    11. As Row-Based Tables Get Bigger, the Blocks Split
    12. Data Blocks Are Processed One at a Time Per Unit
    13. Columnar Tables Store Each Column in Separate Blocks
    14. Visualize the Data – Rows vs. Columns
    15. The Architecture of Redshift
    16. Redshift has Linear Scalability
    17. Distribution Styles
    18. Distribution Key Where the Data is Unique
    19. Another Way to Create A Table
    20. Distribution Key Where the Data is Non-Unique
    21. Distribution Key is ALL
    22. Even Distribution Key
    23. Matching Distribution Keys for Co-Location of Joins
    24. Big Table / Small Table Joins
    25. Fact and Dimension Table Distribution Key Designs
    26. Improving Performance By Defining a Sort Key
    27. Sort Keys Help Group By, Order By and Window Functions
    28. Each Block Comes With Metadata
    29. How Data Might Look On A Slice
    30. Question – How Many Blocks Move Into Memory?
    31. Answer – How Many Blocks Move Into Memory?
    32. Quiz – Master that Query With the Metadata
    33. Answer to Quiz – Master that Query With the Metadata
    34. The ANALYZE Command Collects Statistics
    35. Redshift Automatically ANALYZES Some Create Statements
    36. What is a Vacuum?
    37. When is a Good Time to Vacuum?
    38. The VACUUM Command Grooms a Table
    39. Database Limits
    40. Creating a Database
    41. Creating a User
    42. Dropping a User
    43. Inserting Into a Table
    44. Renaming a Table or a Column
    45. Adding and Dropping a Column to a Table
  10. Chapter 2 - Best Practices For Table Design
    1. Converting Table Structures to Redshift
    2. Converting Table Structures to Redshift Finale
    3. Best Practices for Designing Tables
    4. Choose the Best Sort Key
    5. Each Block Comes With Metadata
    6. Creating a Sort Key
    7. Sort Keys Help Group By, Order By and Window Functions
    8. Choose a Great Distribution Key
    9. Distribution Key Where the Data is Unique
    10. Matching Distribution Keys for Co-Location of Joins
    11. Big Table / Small Table Joins
    12. Define Primary Key and Foreign Key Constraints
    13. Primary Key and Foreign Key Examples
    14. Use The Smallest Column Size When Creating Tables
    15. Use Date/Time Data Types for Date Columns
    16. Specify Redundant Predicates on the Sort Column
    17. Setting the Statement_Timeout to Abort Long Queries
  11. Chapter 3 – System Tables
    1. Amazon Redshift System Tables
    2. Trouble Shooting Catalog Table pg_table_def
    3. Seeing the System Tables in your Nexus Tree
    4. Catalog Table pg_table_def
    5. Checking Tables for Skew (Poor Distribution)
    6. Checking All Statements That Used the Analyze Command
    7. Checking Tables for Skew (Poor Distribution)
    8. Checking For Details About the Last Copy Operation
    9. Checking When a Table Has Last Been Analyzed
    10. Checking For Column Information on a Table
    11. System tables for troubleshooting data loads
    12. Determining Whether a Query is Writing to Disk
  12. Chapter 4 - Compression
    1. Compression Types
    2. Byte Dictionary Compression
    3. Delta Encoding
    4. LZO Encoding
    5. Mostly Encoding
    6. Runlength encoding
    7. Text255 and Text32k Encodings
    8. ANALYZE COMPRESSION
    9. Copy
  13. Chapter 5 – Temporary Tables
    1. Create Table Syntax
    2. Basic Temporary Table Examples
    3. More Advanced Temporary Table Examples
    4. Advanced Temporary Table Examples
    5. Table Limits and CTAS
    6. Performing a Deep Copy
    7. Deep Copy Using the Original DDL
    8. Deep Copy Using A CTAS
    9. Deep Copy Using A Create Table LIKE
    10. Deep Copy By Creating a Temp Table and Truncating Original
    11. CREATING A Derived Table
    12. The Three Components of a Derived Table
    13. Naming the Derived Table
    14. Aliasing the Column Names in The Derived Table
    15. Visualize This Derived Table
    16. Most Derived Tables Are Used To Join To Other Tables
    17. Multiple Ways to Alias the Columns in a Derived Table
    18. Our Join Example With A Different Column Aliasing Style
    19. Column Aliasing Can Default For Normal Columns
    20. CREATING A Derived Table using the WITH Command
    21. Our Join Example With The WITH Syntax
    22. WITH Statement That Uses a SELECT *
    23. A WITH Clause That Produces Two Tables
    24. The Same Derived Query shown Three Different Ways
    25. Quiz - Answer the Questions
    26. Answer to Quiz - Answer the Questions
    27. Clever Tricks on Aliasing Columns in a Derived Table
    28. A Derived Table lives only for the lifetime of a single query
    29. An Example of Two Derived Tables in a Single Query
    30. Connecting To Redshift Via Nexus
    31. Connecting To Redshift Via Nexus
    32. Connecting To Redshift Via Nexus
    33. Connecting To Redshift Via Nexus
  14. Chapter 6 - Explain
    1. Three Ways to Run an EXPLAIN
    2. EXPLAIN – Steps, Segments and Streams
    3. EXPLAIN Terms For Scans and Joins
    4. EXPLAIN Terms For Aggregation and Sorts
    5. EXPLAIN Terms For Set Operators and Miscellaneous Terms
    6. EXPLAIN Terms For Set Operators and Miscellaneous Terms
    7. EXPLAIN Example and the Cost
    8. EXPLAIN Example and the Rows
    9. EXPLAIN Example and the Width
    10. Simple EXPLAIN Example and the Costs
    11. EXPLAIN Join Example Using DS_BCAST_INNER
    12. EXPLAIN Join Example Using DS_DIST_NONE
    13. EXPLAIN Showing DS_DIST_NONE Visually
    14. EXPLAIN With a Warning
    15. EXPLAIN For Ordered Analytics Such as CSUM
    16. EXPLAIN For Scalar Aggregate Functions
    17. EXPLAIN For HashAggregate Functions
    18. EXPLAIN Using Limit, Merge and Sort
    19. EXPLAIN Using a WHERE Clause Filter
    20. EXPLAIN Using the Keyword Distinct
    21. EXPLAIN for Subqueries
  15. Chapter 7 – Basic SQL Functions
    1. Finding the Current Schema on the Leader Node
    2. Getting Things Setup in Your Search Path
    3. Five Details You Need To Know About The Search_Path
    4. Introduction
    5. SELECT * (All Columns) in a Table
    6. SELECT Specific Columns in a Table
    7. Commas in the Front or Back?
    8. Place your Commas in front for better Debugging Capabilities
    9. Sort the Data with the ORDER BY Keyword
    10. ORDER BY Defaults to Ascending
    11. Use the Name or the Number in your ORDER BY Statement
    12. Two Examples of ORDER BY using Different Techniques
    13. Changing the ORDER BY to Descending Order
    14. NULL Values sort First in Ascending Mode (Default)
    15. NULL Values sort Last in Descending Mode (DESC)
    16. Major Sort vs. Minor Sorts
    17. Multiple Sort Keys using Names vs. Numbers
    18. Sorts are Alphabetical, NOT Logical
    19. Using A CASE Statement to Sort Logically
    20. How to ALIAS a Column Name
    21. A Missing Comma can by Mistake become an Alias
    22. Comments using Double Dashes are Single Line Comments
    23. Comments for Multi-Lines
    24. Comments for Multi-Lines As Double Dashes Per Line
    25. A Great Technique for Comments to Look for SQL Errors
  16. Chapter 8 - The WHERE Clause
    1. Using Limit to bring back a Sample
    2. Using Limit With an Order By Statement
    3. The WHERE Clause limits Returning Rows
    4. Using a Column ALIAS throughout the SQL
    5. Double Quoted Aliases are for Reserved Words and Spaces
    6. Character Data needs Single Quotes in the WHERE Clause
    7. Character Data needs Single Quotes, but Numbers Don’t
    8. NULL means UNKNOWN DATA so Equal (=) won’t Work
    9. Use IS NULL or IS NOT NULL when dealing with NULLs
    10. NULL is UNKNOWN DATA so NOT Equal won’t Work
    11. Use IS NULL or IS NOT NULL when dealing with NULLs
    12. Using Greater Than Or Equal To (>=)
    13. AND in the WHERE Clause
    14. Troubleshooting AND
    15. OR in the WHERE Clause
    16. Troubleshooting Or
    17. Troubleshooting Character Data
    18. Using Different Columns in an AND Statement
    19. Quiz – How many rows will return?
    20. Answer to Quiz – How many rows will return?
    21. What is the Order of Precedence?
    22. Using Parentheses to change the Order of Precedence
    23. Using an IN List in place of OR
    24. The IN List is an Excellent Technique
    25. IN List vs. OR brings the same Results
    26. Using a NOT IN List
    27. A Technique for Handling Nulls with a NOT IN List
    28. Another Technique for Handling Nulls with a NOT IN List
    29. BETWEEN is Inclusive
    30. NOT BETWEEN is Also Inclusive
    31. LIKE command Underscore is Wildcard for one Character
    32. LIKE Command Works Differently on Char Vs Varchar
    33. The Ilike Command Is NOT Case Sensitive
    34. Troubleshooting LIKE Command on Character Data
    35. Introducing the TRIM Command
    36. Quiz – What Data is Left Justified and What is Right?
    37. Numbers are Right Justified and Character Data is Left
    38. Answer – What Data is Left Justified and What is Right?
    39. An Example of Data with Left and Right Justification
    40. A Visual of CHARACTER Data vs. VARCHAR Data
    41. Use the TRIM command to remove spaces on CHAR Data
    42. Like and Your Escape Character of Choice
    43. Like and the Default Escape Character
    44. Similar To Operators
    45. Similar To Operators
    46. Similar To Example With Lower Case Letters
    47. Similar To Example With Lower and Upper Case Letters
    48. Similar To Example With Multiple Occurrences
    49. Multiple Occurrences Must Be Consecutive
  17. Chapter 9 - Distinct Vs 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. What is the Difference Between TOP and LIMIT?
  18. Chapter 10 - 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. GROUP BY Dept_No or GROUP BY 1 the same thing
    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. Keyword HAVING is like an Extra WHERE Clause for Totals
  19. Chapter 11 - 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. Left Outer Joins Compatible with Oracle
    17. RIGHT OUTER JOIN
    18. RIGHT OUTER JOIN Example and Results
    19. Right Outer Joins Compatible with Oracle
    20. FULL OUTER JOIN
    21. FULL OUTER JOIN Results
    22. Which Tables are the Left and Which are the Right?
    23. Answer - Which Tables are the Left and Which are the Right?
    24. INNER JOIN with Additional AND Clause
    25. ANSI INNER JOIN with Additional AND Clause
    26. ANSI INNER JOIN with Additional WHERE Clause
    27. OUTER JOIN with Additional WHERE Clause
    28. OUTER JOIN with Additional AND Clause
    29. OUTER JOIN with Additional AND Clause Results
    30. Quiz – Why is this Considered an INNER JOIN?
    31. The DREADED Product Join
    32. The DREADED Product Join Results
    33. The Horrifying Cartesian Product Join
    34. The ANSI Cartesian Join will ERROR
    35. Quiz – Do these Joins Return the Same Answer Set?
    36. Answer – Do these Joins Return the Same Answer Set?
    37. The CROSS JOIN
    38. The CROSS JOIN Answer Set
    39. The Self Join
    40. The Self Join with ANSI Syntax
    41. Quiz – Will both queries bring back the same Answer Set?
    42. Answer – Will both queries bring back the same Answer Set?
    43. Quiz – Will both queries bring back the same Answer Set?
    44. Answer – Will both queries bring back the same Answer Set?
    45. How would you Join these two tables?
    46. An Associative Table is a Bridge that Joins Two Tables
    47. Quiz – Can you Write the 3-Table Join?
    48. Answer to Quiz – Can you Write the 3-Table Join?
    49. Quiz – Can you Write the 3-Table Join to ANSI Syntax?
    50. Answer – Can you Write the 3-Table Join to ANSI Syntax?
    51. Quiz – Can you Place the ON Clauses at the End?
    52. Answer – Can you Place the ON Clauses at the End?
    53. The 5-Table Join – Logical Insurance Model
    54. Quiz - Write a Five Table Join Using ANSI Syntax
    55. Answer - Write a Five Table Join Using ANSI Syntax
    56. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    57. Answer - Write a Five Table Join Using Non-ANSI Syntax
    58. Quiz –Re-Write this putting the ON clauses at the END
    59. Answer –Re-Write this putting the ON clauses at the END
  20. Chapter 12 - Date Functions
    1. Current_Date
    2. TIMEOFDAY()
    3. SYSDATE Returns a Timestamp With Microseconds
    4. GETDATE Returns a Timestamp Without Microseconds
    5. Add or Subtract Days from a date
    6. The ADD_MONTHS Command Returns a Timestamp
    7. The ADD_MONTHS Command With Trunc Removes Time
    8. ADD_MONTHS Command to Add 1-Year or 5-Years
    9. Dateadd Function And Add_Months Function are Different
    10. The EXTRACT Command
    11. EXTRACT from DATES and TIME
    12. EXTRACT with DATE and TIME Literals
    13. EXTRACT of the Month on Aggregate Queries
    14. The Datediff command
    15. The Datediff Function on Column Data
    16. The Date_Part Function Using a Date
    17. The Date_Part Function Using a Time
    18. Date_Part Abbreviations
    19. The to_char command
    20. Conversion Functions
    21. Conversion Function Templates
    22. Conversion Function Templates Continued
    23. Formatting A Date
    24. A Summary of Math Operations on Dates
    25. Using a Math Operation to find your Age in Years
    26. Date Related Functions
    27. A Side Title example with Reserved Words as an Alias
    28. Implied Extract of Day, Month and Year
    29. DATE_PART Function
    30. DATE_PART Function using an ALIAS
    31. DATE_TRUNC Function
    32. DATE_TRUNC Function using TIME
    33. MONTHS_BETWEEN Function
    34. MONTHS_BETWEEN Function in Action
    35. ANSI TIME
    36. ANSI TIMESTAMP
    37. Redshift TIMESTAMP Function
    38. Redshift TO_TIMESTAMP Function
    39. Redshift NOW() Function
    40. Redshift TIMEOFDAY Function
    41. Redshift AGE Function
    42. Time Zones
    43. Setting Time Zones
    44. Using Time Zones
    45. Intervals for Date, Time and Timestamp
    46. Using Intervals
    47. Troubleshooting The Basics of a Simple Interval
    48. Interval Arithmetic Results
    49. A Date Interval Example
    50. A Time Interval Example
    51. A DATE Interval Example
    52. A Complex Time Interval Example using CAST
    53. A Complex Time Interval Example using CAST
    54. The OVERLAPS Command
    55. An OVERLAPS Example that Returns No Rows
    56. The OVERLAPS Command using TIME
    57. The OVERLAPS Command using a NULL Value
  21. Chapter 13 - OLAP Functions
    1. CSUM
    2. CSUM – The Sort Explained
    3. CSUM – Rows Unbounded Preceding Explained
    4. CSUM – Making Sense of the Data
    5. CSUM – Making Even More Sense of the Data
    6. CSUM – The Major and Minor Sort Key(s)
    7. Reset with a PARTITION BY Statement
    8. PARTITION BY only Resets a Single OLAP not ALL of them
    9. ANSI Moving Window is Current Row and Preceding n Rows
    10. How ANSI Moving SUM Handles the Sort
    11. Quiz – How is that Total Calculated?
    12. Answer to Quiz – How is that Total Calculated?
    13. Moving SUM every 3-rows Vs a Continuous Average
    14. Partition By Resets an ANSI OLAP
    15. Moving Average
    16. The Moving Window is Current Row and Preceding
    17. How Moving Average Handles the Sort
    18. Quiz – How is that Total Calculated?
    19. Answer to Quiz – How is that Total Calculated?
    20. Quiz – How is that 4th Row Calculated?
    21. Answer to Quiz – How is that 4th Row Calculated?
    22. Moving Average every 3-rows Vs a Continuous Average
    23. Partition By Resets an ANSI OLAP
    24. RANK Defaults to Ascending Order
    25. Getting RANK to Sort in DESC Order
    26. RANK() OVER and PARTITION BY
    27. RANK() OVER And LIMIT
    28. PERCENT_RANK() OVER
    29. PERCENT_RANK() OVER with 14 rows in Calculation
    30. PERCENT_RANK() OVER with 21 rows in Calculation
    31. Quiz – What Causes the Product_ID to Reset?
    32. Answer to Quiz – What Cause the Product_ID to Reset?
    33. COUNT OVER for a Sequential Number
    34. Quiz – What caused the COUNT OVER to Reset?
    35. Answer to Quiz – What caused the COUNT OVER to Reset?
    36. The MAX OVER Command
    37. MAX OVER with PARTITION BY Reset
    38. The MIN OVER Command
    39. Quiz – Fill in the Blank
    40. Answer – Fill in the Blank
    41. The Row_Number Command
    42. Quiz – How did the Row_Number Reset?
    43. Quiz – How did the Row_Number Reset?
    44. Standard Deviation Functions Using STDDEV / OVER
    45. Standard Deviation Functions and STDDEV / OVER Syntax
    46. STDDEV / OVER Example
    47. VARIANCE / OVER Syntax
    48. Variance Functions Using VARIANCE / OVER
    49. Using VARIANCE with PARTITION BY Example
    50. Using FIRST_VALUE and LAST_VALUE
    51. Using FIRST_VALUE
    52. Using LAST_VALUE
    53. Using LAG and LEAD
    54. Using LEAD
    55. Using LEAD With and Offset of 2
    56. Using LAG
    57. Using LAG With an Offset of 2
  22. Chapter 14 - Temporary Tables
    1. CREATING A Derived Table
    2. The Three Components of a Derived Table
    3. Naming the Derived Table
    4. Aliasing the Column Names in The Derived Table
    5. Visualize This Derived Table
    6. Most Derived Tables Are Used To Join To Other Tables
    7. Multiple Ways to Alias the Columns in a Derived Table
    8. Our Join Example With A Different Column Aliasing Style
    9. Column Aliasing Can Default For Normal Columns
    10. CREATING A Derived Table using the WITH Command
    11. Our Join Example With The WITH Syntax
    12. WITH
    13. A WITH Clause That Produces Two Tables
    14. The Same Derived Query shown Three Different Ways
    15. Quiz - Answer the Questions
    16. Answer to Quiz - Answer the Questions
    17. Clever Tricks on Aliasing Columns in a Derived Table
    18. A Derived Table lives only for the lifetime of a single query
    19. An Example of Two Derived Tables in a Single Query
    20. Create Table Syntax
    21. Basic Temporary Table Examples
    22. More Advanced Temporary Table Examples
    23. Advanced Temporary Table Examples
    24. Performing a Deep Copy
    25. Deep Copy Using the Original DDL
    26. Deep Copy Using A CTAS
    27. Deep Copy Using A Create Table LIKE
    28. Deep Copy By Creating a Temp Table and Truncating Original
  23. Chapter 15 - 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 Subquery with an Aggregate
    16. Answer to Quiz- Write the Subquery with an Aggregate
    17. Quiz- Write the Correlated Subquery
    18. Answer to Quiz- Write the Correlated Subquery
    19. The Basics of a Correlated Subquery
    20. The Top Query always runs first in a Correlated Subquery
    21. Correlated Subquery Example vs. a Join with a Derived Table
    22. Quiz- A Second Chance To Write a Correlated Subquery
    23. Answer - A Second Chance to Write a Correlated Subquery
    24. Quiz- A Third Chance To Write a Correlated Subquery
    25. Answer - A Third Chance to Write a Correlated Subquery
    26. Quiz- Last Chance To Write a Correlated Subquery
    27. Answer – Last Chance to Write a Correlated Subquery
    28. Quiz- Write the NOT Subquery
    29. Answer to Quiz- Write the NOT Subquery
    30. Quiz- Write the Subquery using a WHERE Clause
    31. Answer - Write the Subquery using a WHERE Clause
    32. Quiz- Write the Subquery with Two Parameters
    33. Answer to Quiz- Write the Subquery with Two Parameters
    34. How the Double Parameter Subquery Works
    35. More on how the Double Parameter Subquery Works
    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?
  24. Chapter 16 - Substrings and Positioning Functions
    1. The TRIM Command trims both Leading and Trailing Spaces
    2. A Visual of the TRIM Command Using Concatenation
    3. Trim and Trailing is Case Sensitive
    4. How to TRIM Trailing Letters
    5. The SUBSTRING Command
    6. How SUBSTRING Works with NO ENDING POSITION
    7. Using SUBSTRING to move Backwards
    8. How SUBSTRING Works with a Starting Position of -1
    9. How SUBSTRING Works with an Ending Position of 0
    10. The POSITION Command finds a Letters Position
    11. Quiz – Find that SUBSTRING Starting Position
    12. Answer to Quiz – Find that SUBSTRING Starting Position
    13. Using the SUBSTRING to Find the Second Word On
    14. Quiz – Why Did only one Row Return
    15. Answer to Quiz – Why Did only one Row Return
    16. Concatenation
    17. Concatenation and SUBSTRING
    18. Four Concatenations Together
    19. Troubleshooting Concatenation
    20. Declaring a Cursor
  25. Chapter 17 – Interrogating the Data
    1. Quiz – What would the Answer be?
    2. Answer to Quiz – What would the Answer be?
    3. The NULLIFZERO Command
    4. Quiz – Fill in the Blank Values in the Answer Set
    5. Answer to Quiz – Fill in the Blank Values in the Answer Set
    6. Quiz – Fill in the Answers for the NULLIF Command
    7. Quiz – Fill in the Answers for the NULLIF Command
    8. The ZEROIFNULL Command
    9. Answer to the ZEROIFNULL Question
    10. The COALESCE Command
    11. The COALESCE Answer Set
    12. The Coalesce Quiz
    13. Answer – The Coalesce Quiz
    14. The Basics of CAST (Convert And STore)
    15. Some Great CAST (Convert And STore) Examples
    16. Some Great CAST (Convert And STore) Examples
    17. Some Great CAST (Convert And STore) Examples
    18. The Basics of the CASE Statements
    19. The Basics of the CASE Statement
    20. Valued Case Vs. A Searched Case
    21. Quiz - Valued Case Statement
    22. Answer - Valued Case Statement
    23. Quiz - Searched Case Statement
    24. Answer - Searched Case Statement
    25. Quiz - When NO ELSE is present in CASE Statement
    26. Answer - When NO ELSE is present in CASE Statement
    27. When an ELSE is present in CASE Statement
    28. Answer - When an ELSE is present in CASE Statement
    29. When an Alias is NOT used in a CASE Statement
    30. Answer - When an Alias is NOT used in a CASE Statement
    31. Combining Searched Case and Valued Case
    32. Nested Case
    33. Put a CASE in the ORDER BY
  26. Chapter 18 - View Functions
    1. Creating a Simple View to Restrict Sensitive Columns
    2. Creating a Simple View to Restrict Rows
    3. Creating a View to Join Tables Together
    4. You Select From a View
    5. Basic Rules for Views
    6. An ORDER BY Example Inside of a View
    7. An ORDER BY Inside of a View That is Queried Differently
    8. Creating a View With Ordered Analytics
    9. Creating a View With The TOP Command
    10. Creating a View With The LIMIT Command
    11. Altering A Table
    12. Altering A Table After a View has been Created
    13. A View that Errors After An ALTER
    14. Troubleshooting a View
    15. Updating Data in a Table through a View
  27. Chapter 19 - Set Operators Functions
    1. Rules of Set Operators
    2. INTERSECT Explained Logically
    3. INTERSECT Explained Logically
    4. UNION Explained Logically
    5. UNION Explained Logically
    6. UNION ALL Explained Logically
    7. UNION Explained Logically
    8. EXCEPT Explained Logically
    9. EXCEPT Explained Logically
    10. Minus Explained Logically
    11. Minus Explained Logically
    12. Testing Your Knowledge
    13. Answer - Testing Your Knowledge
    14. Testing Your Knowledge
    15. Answer - Testing Your Knowledge
    16. An Equal Amount of Columns in both SELECT List
    17. Columns in the SELECT list should be from the same Domain
    18. The Top Query handles all Aliases
    19. The Bottom Query does the ORDER BY (a Number)
    20. Great Trick: Place your Set Operator in a Derived Table
    21. UNION vs. UNION ALL
    22. A Great Example of how EXCEPT works
  28. Chapter 20 – Statistical Aggregate Functions
    1. The Stats Table
    2. STDDEV
    3. Casting STDDEV_SAMP and SQRT (VAR_SAMP)
    4. The STDDEV_POP Function
    5. A STDDEV_POP Example
    6. The STDDEV_SAMP Function
    7. A STDDEV_SAMP Example
    8. The VAR_POP Function
    9. A VAR_POP Example
    10. The VAR_SAMP Function
    11. A VAR_SAMP Example