View on GitHub

TSQLBeginners

A TSQL Course for Beginners

TSQL for Beginners

Jump to Section

Course Summary
Audience
Videos
Course Slack Discussion
Pre-requisites
Session Overview

Course Summary

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

Audience

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.

Videos

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

Course Slack Discussion

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

Pre-requisites

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.

Session Overview

Introducing SELECTs and Aliasing

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

Solutions for SELECT Homework and Introduction to JOINs

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

Solutions for JOINs Homework and Learn GROUP BY and CASE

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

Solutions for GROUP BY and CASE; then CTEs, Subqueries, Derived Tables, Oh My!

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

Solutions for CTEs and Friends, then Oh No, it’s Scalar User Defined Functions

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

Solutions for Scalar UDFs, then Table Valued Functions and a little CROSS APPLY

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

Solutions for TVFs, then Ranking, Numbering, and Running Totals with Windowing Functions

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

Solutions for Windowing Functions, then Stored Procedures

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