In this week's class, we go through sample solutions for the GROUP BY and CASE homework, then dig into different ways to nest/combine queries in your queries: Common Table Expressions, Subqueries, and Derived Tables.

Video timeline:

• 00:20 Accidental demo of a bug in Azure Data Studio where a connection "goes bad"
• 07:08 Solution to Q1 from 9_GroupingAndCase_Solutions.sql with a discussion of data types and the AVG function
• 10:30 Solution to Q2 from 9_GroupingAndCase_Solutions.sql - discussion of COUNT ALL vs COUNT DISTINCT
• 14:06 Solution to Q3 from 9_GroupingAndCase_Solutions.sql - discussion showing that multiple syntaxes can result in the same query execution plan being used behind the scenes
• 18:16 Solution to Q4 from 9_GroupingAndCase_Solutions.sql - discussion of HAVING clauses
• 21:05 Solution to Q5 form 9_GroupingAndCase_Solutions.sql - working with CASE
• 24:29 Solution to Q6 from 9_GroupingAndCase_Solutions.sql - which solution do you like better? Comparing a CASE solution and a COUNT solution
• 28:28 Bonus topic - using EXCEPT and INTERSECT to compare result sets from different ways to write a query
• 31:43 Demo on CTEs, Subqueries, and Derived tables begin: setting up sample data
• 34:00 An example problem query -- why HAVING isn't enough and we need to get more complex
• 36:21 Introduction to Common Table Expressions (CTEs)
• 40:03 More with CTEs: you may use multiples, and they may refer to a prior CTE
• 41:50 Derived table example
• 43:43 Correlated subquery example
• 47:54 Subquery in the select example
• 51:40 Setup for next week's homework, 11_CTEsSubqueriesDerivedTables_Homework.sql