Querying Microsoft SQL Server 2022

Course details

Want to learn more about how to query data from SQL Server tables? In this course, instructor Adam Wilbert covers how to retrieve information stored in a Microsoft SQL Server database using the Transact-SQL (T-SQL) language and the updated features of SQL Server 2022.

Learn the fundamental structure of a successful query, from ideation to execution. Find out how to manipulate data through the use of built-in functions, operators, filters, and other language features to return a data set, ensuring that it’s properly formatted and applicable to a wide variety of needs and perspectives. Along the way, Adam gives you tips and pointers on how to get the most out of your queries. Upon completing this course, you'll be equipped with the commands and keywords you need to know to analyze your data more effectively.

Concepts

0. Introduction

01 - Turn data into information with queries
02 - What you should know
03 - Download the sample database
04 - Exploring the code challenge environment

1. Introduction to T-SQL Queries

05 - What is Transact SQL
06 - The SQL querying environment
07 - Return records with SELECT and FROM
08 - Filter records with WHERE
09 - Sort records with ORDER BY
10 - Column aliases
11 - Create values with constants and math
12 - Challenge - Write a SELECT query
13 - Solution - Write a SELECT query

2. Additional Filter Techniques

14 - Limit results with TOP
15 - Remove duplicates with DISTINCT
16 - Comparison operators
17 - Understand NULL values
18 - Match text with LIKE and wildcards
19 - Challenge - Retrieve matching text strings
20 - Solution - Retrieve matching text strings
21 - Solution - Filter results from a query result

3. Retrieve Records from Multiple Tables

22 - Table joins and relationships
23 - Inner joins
24 - Left, right, and full outer joins
25 - Cross joins
26 - Challenge - Return data from multiple tables
27 - Solution - Return data from multiple tables
28 - Solution - Join data in multiple tables

4. Group Records

29 - Understand record grouping
30 - Use GROUP BY and COUNT
31 - Aggregate functions
32 - Explore data with SUM
33 - Filter groups with HAVING
34 - Challenge - Obtain order summaries
35 - Solution - Obtain order summaries
36 - Solution - Group rows and aggregate data

5. Manipulate Data with Functions

37 - Use functions in T-SQL
38 - String functions
39 - Text concatenation
40 - Round with mathematical functions
41 - Find the GREATEST or LEAST value
42 - Work with date functions
43 - FORMAT date and times
44 - Group dates into buckets
45 - Return random records with NEWID
46 - Create a series of values
47 - The IIF logical function
48 - Write a CASE statement
49 - Challenge - Use functions in a SQL query
50 - Solution - Use functions in a SQL query
51 - Solution - Use functions in a query

6. Writing SQL Scripts

52 - Change database context with USE
53 - Add comments to a script
54 - When to use square brackets

7. SQL Subqueries

55 - What is a subquery
56 - Use a subquery in a HAVING clause
57 - Correlated subqueries
58 - PIVOT the result set
59 - Challenge - Write a query with a subquery
60 - Solution - Write a query with a subquery
61 - Solution - Obtain information with a subquery

8. Work with Variables

62 - Create and use variables in a query
63 - Create a counter for a looping statement
64 - Challenge - Use variables in a query
65 - Solution - Use variables in a query

9. Result Set Operators

66 - Combine results with UNION
67 - Return distinct rows with EXCEPT
68 - Return common rows with INTERSECT

Conclusion

69 - Next steps