Beginner to T-SQL [Full Course]

Mitchell Pearson, the Training Manager at Pragmatic Works, welcomes viewers to a T-SQL learning event. He introduces himself, his experience, and his role, which includes creating classes and teaching. Mitchell is also an author and blogs at mitchellpearson.com. He outlines the agenda for the three-hour event, which includes an overview of SQL, SELECT and FROM clauses, WHERE for filtering, JOINs for combining tables, and aggregate queries. The logistics are explained, including the Eastern Time schedule and a 15-minute break. Viewers are encouraged to download provided materials for the event, which include SQL scripts and instructions for connecting to SQL Server Management Studio (SSMS) and an Azure SQL database.

The script covers the setup process for SSMS, a free tool required for the event. Participants are guided to download the latest version to avoid connectivity issues with Azure. Mitchell demonstrates how to connect to an Azure SQL database using SSMS, detailing the authentication process with a provided server name and credentials. He also explains how to navigate SSMS to find tables and start querying data. The session aims to familiarize participants with the basics of working with SQL databases and SSMS.

Mitchell delves into the basics of writing SQL queries, starting with simple SELECT statements to retrieve data from a database. He discusses how to specify columns and rows, use aliases for column names, and order data. The importance of understanding databases as organized structures for organizational data is emphasized. Mitchell also introduces the concept of concatenation for combining first and last names and warns about the potential confusion of using reserved words as aliases.

The session progresses to more advanced SQL techniques, including string functions like LEFT, RIGHT, UPPER, and LOWER, which are used to manipulate text data. Mitchell demonstrates how to use these functions in combination to perform complex data transformations. He also introduces the REPLACE function for finding and replacing characters in a string and the LENGTH function to determine the number of characters in a string.

Date functions are introduced, starting with GETDATE to retrieve the current system date and time. Mitchell shows how to extract specific parts of a date, such as the year and month, using DATEPART and the more straightforward YEAR and MONTH functions. He also covers the DATEADD function to perform date arithmetic, like subtracting a year from an order date, and the DATEDIFF function to calculate the difference between two dates.

The focus shifts to aggregate queries, which allow for the summarization of data. Mitchell explains how to use functions like COUNT, SUM, MIN, MAX, and AVG to perform calculations across multiple rows. He also discusses the importance of the GROUP BY clause in aggregate queries, which is necessary for grouping data based on specific columns before performing aggregations.

This section explores various SQL functions and clauses in depth, including string functions for manipulating text, date functions for working with dates, and aggregate functions for summarizing data. Mitchell demonstrates the use of the LIKE operator for pattern matching and the IN operator for filtering data based on a list of values. He also discusses the importance of handling NULL values using functions like IS NULL and COALESCE.

The session continues with a deeper look at aggregate functions, which are used to perform calculations on groups of rows. Mitchell shows how to use the GROUP BY clause to organize data into groups and then apply aggregate functions like SUM and COUNT to these groups. He also explains how to filter these groups using the HAVING clause, which is similar to the WHERE clause but applies to grouped data.

Joins are introduced as a way to combine data from multiple tables based on a related column between them. Mitchell explains the concept of INNER JOIN and demonstrates how to use it to bring together data from the Sales Order Header table and the Customer table. He emphasizes the importance of specifying the join condition accurately to ensure the correct data is combined.

The UNION and UNION ALL operations are explained, which allow for the combination of result sets from multiple SELECT statements. Mitchell illustrates the difference between the two, showing that UNION automatically removes duplicate rows, while UNION ALL includes all rows, duplicates included. He also points out the requirements for using UNION, such as having the same number of columns in each SELECT statement.

The importance of data type conversion in SQL is highlighted, particularly when combining different data types in a query. Mitchell demonstrates the use of the CAST function to convert data types, ensuring that values can be combined in a query without causing errors. He shows an example where an integer value is cast to a varchar to allow it to be included in the same column as text values.

The session concludes with advanced aggregate queries, which involve grouping data and applying aggregate functions within the context of the group. Mitchell shows how to use the GROUP BY clause to create groups and then use aggregate functions like SUM, COUNT, MIN, and MAX to perform calculations on each group. He also discusses the use of the HAVING clause to filter groups based on aggregate values.