Amazon Redshift Architecture and SQL Training Class | Webucator Big Data Classes

Overview

In this training class, students will learn the Amazon Redshift Architecture and SQL starting at the most basic level and going to the most advanced level with many examples.

Goals

Learn to gain a deeper knowledge and understanding of the Amazon Redshift Architecture and SQL and how to write it.

Outline

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
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
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
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
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
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
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
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
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?
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
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
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
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. RANK Defaults to Ascending Order
26. Getting RANK to Sort in DESC Order
27. RANK() OVER and PARTITION BY
28. RANK() OVER And LIMIT
29. PERCENT_RANK() OVER
30. PERCENT_RANK() OVER with 14 rows in Calculation
31. PERCENT_RANK() OVER with 21 rows in Calculation
32. Quiz: What Causes the Product_ID to Reset?
33. Answer to Quiz: What Cause the Product_ID to Reset?
34. COUNT OVER for a Sequential Number
35. Quiz: What caused the COUNT OVER to Reset?
36. Answer to Quiz: What caused the COUNT OVER to Reset?
37. The MAX OVER Command
38. MAX OVER with PARTITION BY Reset
39. The MIN OVER Command
40. Quiz: Fill in the Blank
41. Answer: Fill in the Blank
42. The Row_Number Command
43. Quiz: How did the Row_Number Reset?
44. Quiz: How did the Row_Number Reset?
45. Standard Deviation Functions Using STDDEV / OVER
46. Standard Deviation Functions and STDDEV / OVER Syntax
47. STDDEV / OVER Example
48. VARIANCE / OVER Syntax
49. Variance Functions Using VARIANCE / OVER
50. Using VARIANCE with PARTITION BY Example
51. Using FIRST_VALUE and LAST_VALUE
52. Using FIRST_VALUE
53. Using LAST_VALUE
54. Using LAG and LEAD
55. Using LEAD
56. Using LEAD With and Offset of 2
57. Using LAG
58. Using LAG With an Offset of 2
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
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?
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
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. The Basics of the CASE Statements
17. The Basics of the CASE Statement
18. Valued Case Vs. A Searched Case
19. Quiz: Valued Case Statement
20. Answer: Valued Case Statement
21. Quiz: Searched Case Statement
22. Answer: Searched Case Statement
23. Quiz: When NO ELSE is present in CASE Statement
24. Answer: When NO ELSE is present in CASE Statement
25. When an ELSE is present in CASE Statement
26. Answer: When an ELSE is present in CASE Statement
27. When an Alias is NOT used in a CASE Statement
28. Answer: When an Alias is NOT used in a CASE Statement
29. Combining Searched Case and Valued Case
30. Nested Case
31. Put a CASE in the ORDER BY
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
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
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
12. Will both queries bring back the same Answer Set?
13. Answer: Will both queries bring back the same Answer Set?
14. Quiz: Will both queries bring back the same Answer Set?
15. Answer: Will both queries bring back the same Answer Set?
16. How would you Join these two tables?
17. An Associative Table is a Bridge that Joins Two Tables
18. Quiz: Can you Write the 3-Table Join?
19. Answer to Quiz: Can you Write the 3-Table Join?
20. Quiz: Can you Write the 3-Table Join to ANSI Syntax?
21. Answer: Can you Write the 3-Table Join to ANSI Syntax?
22. Quiz: Can you Place the ON Clauses at the End?
23. Answer: Can you Place the ON Clauses at the End?
24. The 5-Table Join: Logical Insurance Model
25. Quiz: Write a Five Table Join Using ANSI Syntax
26. Answer: Write a Five Table Join Using ANSI Syntax
27. Quiz: Write a Five Table Join Using Non-ANSI Syntax
28. Answer: Write a Five Table Join Using Non-ANSI Syntax
29. Quiz: Re-Write this putting the ON clauses at the END
30. Answer: Re-Write this putting the ON clauses at the END

Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Preparing for Class

Onsite: Course Setup

Customized Onsite Training