In this week's class, we step through sample solutions for JOINs homework, then demonstrate how to use GROUP BY clauses and CASE statements with a set of diagrams and queries.

Video timeline:

• 00:00 Showing the scripts covered in the syllabus
• 01:40 Recapping the tables used in the homework in 6_Joins_Solutions.sql and how to know which join columns to use on tables
• 06:40 Solution to Question 1 in 6_Joins_Solutions.sql
• 11:16 Solution to Question 2 in 6_Joins_Solutions.sql (left and right outer joins)
• 15:20 Solution to Question 3 in 6_Joins_Solutions.sql
• 23:50 Solution to Question 4 in 6_Joins_Solutions.sql
• 15:22 Solution to Question 5 in 6_Joins_Solutions.sql
• 27:00 Solution to Question 6 in 6_Joins_Solutions.sql
• 18:18 Solution to Question 7 in 6_Joins_Solutions.sql
• 33:30 Introduction to GROUP BY and Case begins with an overview of logical processing order
• 33:50 Setting up the same table in 7_GroupingAndCase_DiagramsForHomework.ipynb (a Jupyter notebook you can open and use in Azure Data Studio)
• 35:48 GROUP BY and COUNT()
• 37:55 GROUP BY and MAX()
• 39:07 GROUP BY and MIN()
• 40:25 GROUP BY and AVG() with a discussion of rounding INTs
• 43:09 GROUP BY and HAVING
• 46:40 CASE - the "CASE colname when" syntax
• 49:16 CASE - the "CASE WHEN condition" syntax
• 50:31 CASE - the "CASE WHEN condition" syntax with compound logic in the condition, with an example of concatenation and notes on NULL's impact on combining strings
• 53:23 Combining GROUP BY and CASE
• 55:53 Brief overview of homework