- Course Summary
- Audience
- Videos
- Course Slack Discussion
- Pre-requisites
- Session Overview
- Introducing SELECTs and Aliasing
- Solutions for SELECT Homework and Introduction to JOINs
- Solutions for JOINs Homework and Learn GROUP BY and CASE
- Solutions for Group By and CASE; then CTEs, Subqueries, Derived Tables, Oh My!
- Solutions for CTEs and Friends, then Oh No, it’s Scalar User Defined Functions
- Solutions for Scalar UDFs, then Table Valued Functions and a little CROSS APPLY
- Solutions for TVFs, then Ranking, Numbering, and Running Totals with Windowing Functions
- Solutions for Windowing Functions, then Stored Procedures
This free course introduces you to the Transact SQL language implemented in SQL Server and takes you from newbie to a master of SELECT statements. Each session of the course features a demo introducing a subject and a homework assignment to try on your own. The following session covers sample solutions for the homework previously assigned, then introduces a new subject with more homework.
The course covers:
- SELECT statements and aliasing tables
- Using WHERE clauses and JOINs
- Aggregating data using GROUP BY and CASE statements
- Common Table Expressions, Subqueries, and Derived tables
- Scalar and Table Valued User Defined Functions
- An introduction to Stored Procedures
This course is intended for anyone who wants to learn TSQL. This could be database administrators, developers, business analysts, students, IT support staff, or anyone who is interested to learn a programming language where they can work with sets of data.
Live classes will air each Wednesday on YouTube, generally at 3PM British Summer Time / 10AM Eastern.
Recordings will automagically appear in this Redgate University Community Circle Course
Need some help with your homework, or just want to chat with others in the course? You can use this Slack channel to chat while attending a course live, or afterward anytime -- don't worry if you have joined the course late, just start where you are.
- Join the SQL Community Slack group for discussion: https://t.co/w5LWUuDrqG
- Click the + next to 'Channels' and join #tsqlbeginners
To get started with the course, you will need to have a couple of applications installed. The course doesn't show how to install these tools, but jumps right in under the assumption that you have already installed them successfully.
- Microsoft SQL Server Developer Edition, which is free from Microsoft
- Windows: You probably want to click 'Download now' under where it says 'Developer'
- Mac: You probably want to click "Choose your installation setup" under "Docker", which will eventually take you to this Quickstart page on how to Run SQL Server container images with Docker
- Either Azure Data Studio or SQL Server Management Studio (both of which are free as well). Most demos will be shown in Azure Data Studio.
- Windows: Take your pick of either.
- Mac: You want Azure Data Studio (SSMS is Windows-only)
- If using Azure Data Studio, it is very helpful to add the 'SSMS Keymap' extension by Kevin Cunnane. Here is how to install that.
We cover: Getting going in SQL Server Management Studio or in Azure Data Studio. Using databases. SELECT statements and aliasing. Bonus topic: What does 'deprecated' mean?
Follow along with the introductory demo using 1_SelectAndAlias_Demo.sql
Tackle the homework for next session using 2_SelectAndAlias_Homework.sql
Video timeline:
- 00:22 Course Syllabus
- 02:10 Notes on what to install to follow along
- 07:00 Basics on using SQL Server Management Studio (SSMS)
- 08:00 Info about the RAISERROR command I use at the top of the scripts
- 09:49 Using the RESTORE DATABASE script
- 16:11 Azure Data Studio (ADS) – why you might want to use this and comparing ADS to SSMS
- 17:44 Explanation of ‘USE’ and discussion of the concept of “using a database”
- 20:24 Running our first SELECT statement
- 22:24 Brief overview of schemas
- 23:15 Discussion of GO batch separator
- 25:30 Discussion of semi-colon terminators
- 27:34 Why “SELECT *” is an anti-pattern
- 32:05 A brief return to SSMS to demo dragging object names and column names over from Object Explorer (this doesn’t work in Azure Data Studio, but it does have good object auto-completion for typing)
- 35:28 What square[brackets] do around object names, and how they can allow you to use special characters (with a “stupid pet trick” demo of how to create a database with a space as its name)
- 39:00 Example of using a column alias with ‘AS’
- 40:48 Discussion of when you want to use a column alias and why
- 41:20 Demo of aliasing without the word ‘AS’ – and a warning of how this can be done accidentally
- 43:09 Demo of aliasing with reversed order and “=” (and a little discussion of ANSI compliance)
- 47:43 A quick step through of the “quiz” questions in the demo file
- 51:17 Demo of aliasing a table, and comparing three and two part names
- 53:30 Recap of the “quiz” questions on table name aliasing
- 56:08 Brief introduction of next week’s homework
We cover sample solutions for 'SELECTS and Aliasing', then introduce inner joins outer joins (left, right, full), cross joins.
Sample solutions for 'SELECTS and Aliasing' are in 3_SelectAndAlias_Solutions.sql
Follow along with the demo using 4_Joins_DiagramsForHomework.ipynb -- this is a Jupyter notebook file which will open in Azure Data Studio
Try your hand at the homework with 5_Joins_Homework.sql
Video timeline:
- 00:52 Overview of the course files on GitHub
- 01:30 View of the Azure Data Studio Jupyter notebook as it displays in GitHub
- 03:25 Using the WideWorldImporters database
- 03:40 Solution to Question 1 in 2_SelectAndAlias_Solutions.sql
- 05:11 What execution plans are and why I'm showing you some
- 08:40 the sp_help procedure, and why it's useful
- 10:00 Computed Columns
- 12:30 Solution to Question 2 in 2_SelectAndAlias_Solutions.sql
- 15:25 Solution to Question 3 in 2_SelectAndAlias_Solutions.sql (and the controversy of NULLS, with an overview of NVARCHAR for good measure)
- 22:54 ANSI NULLS and why you shouldn't turn it off
- 24:44 Solution to Question 4 in 2_SelectAndAlias_Solutions.sql
- 28:00 Solution to Question 5 in 2_SelectAndAlias_Solutions.sql
- 30:23 Solution to Question 6 in 2_SelectAndAlias_Solutions.sql and a discussion of collation
- 37:31 Solution to Question 7 in 2_SelectAndAlias_Solutions.sql and an intro to ORDER BY
- 41:52 Solution to Question 8 in 2_SelectAndAlias_Solutions.sql with intro to OFFSET and FETCH
- 44:19 Demo that your TSQL syntax generally doesn't determine a specific execution plan
- 46:19 Solution to Question 9 in 2_SelectAndAlias_Solutions.sql
- 49:40 Review of where column aliases DON'T work
- 51:22 JOINs discussion begins here!
- 53:00 Overview of the joins sample database and an explanation of the Jupyter notebook
- 54:50 INNER join: matching rows from both tables
- 59:37 LEFT OUTER join: rows from the 'left' table with matching rows from the 'right' table when present
- 01:01:48 RIGHT OUTER join: rows from the 'right' table with matching rows from the 'left' table when present
- 01:04:44 FULL OUTER join: return all rows from each 'side', matching where possible
- 01:07:18 Simplify our data a bit, and then CROSS JOIN
- 01:09:40 Recap of the joins and info about homework for next time
We step through sample solutions for JOINs homework, then introduce GROUP BY clauses and CASE statements.
Sample solutions for JOINs homework are in 6_Joins_Solutions.sql
Follow along with the demo using 7_GroupingAndCase_DiagramsForHomework.ipynb -- this is a Jupyter notebook file which will open in Azure Data Studio
Try your hand at the homework with 8_GroupingAndCase_Homework.sql
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
We'll go through sample solutions for the GROUP BY and CASE homework, then dig into differnet ways to nest/combine queries in your queries: Common Table Expressions, Subqueries, and Derived Tables
Sample solutions are in 8_GroupingAndCase_Homework.sql
Follow along with the demo using 9_GroupingAndCase_Solutions.sql
Try your hand at the homework with 11_CTEsSubqueriesDerivedTables_Homework.sql
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
This time, we talk through sample solutions for the previous week, then give an introduction to making code reusable using functions. We'll include big-picture information on why functions are controversial in SQL Server and how to be successful with them.
Sample solutions are in 12_CTEsSubqueriesDerivedTables_Solutions.sql
Follow along with the demo using 13_ScalarUDFs_Demo.sql
Try your hand at the homework with 14_ScalarUDFs_Homework.sql
We'll chat through solutions for the Scalar User Defined Function (UDFs) challenges, then I'll show how Table Valued Functions (TVFs) put the "fun" in functions. No, really, they do! We'll also cover a bit of CROSS APPLY as we discuss TVFs.
Sample solutions are in 15_ScalarUDFs_Solutions.sql
Follow along with the demo using 16_TVFs_Demo.sql
Try your hand at the homework with 17_TVFs_Homework.sql
We'll wrap up our discussion of functions by working through the homework, then it's time for another favorite: Windowing Functions!
Sample solutions are in 18_TVFs_Solutions.sql
Follow along with the demo using 19_WindowFunctions_Demo.sql
Try your hand at the homework with 20_WindowFunctions_Homework.sql
In our last session, we'll go through our homework on window functions, then do a gentle introduction to Stored Procedures. Want to try the stored procedure homework yourself before seeing the solutions? Totally fine, you can pause the video at that point.
Sample solutions for Windowing Functions are in 21_WindowFunctions_Solutions.sql
Follow along with the demo using 22_StoredProcedures_Demo.sql
Try your hand at the homework with 23_StoredProcedures_Homework.sql
Sample solutions for Stored Procedures are in 24_StoredProcedures_Solutions.sql