Querying Microsoft SQL Server 2022

Introduction

• Turn data into information with queries
• What you should know
• Download the sample database

1. Introduction to T-SQL Queries

• What is Transact SQL?
• The SQL querying environment
• Return records with SELECT and FROM
• Filter records with WHERE
• Sort records with ORDER BY
• Column aliases
• Create values with constants and math
• Challenge: Write a SELECT query
• Solution: Write a SELECT query

2. Additional Filter Techniques

• Limit results with TOP
• Remove duplicates with DISTINCT
• Comparison operators
• Understand NULL values
• Match text with LIKE and wildcards
• Challenge: Retrieve matching text strings
• Solution: Retrieve matching text strings

3. Retrieve Records from Multiple Tables

• Table joins and relationships
• Inner joins
• Left, right, and full outer joins
• Cross joins
• Challenge: Return data from multiple tables
• Solution: Return data from multiple tables

4. Group Records

• Understand record grouping
• Use GROUP BY and COUNT
• Aggregate functions
• Explore data with SUM
• Filter groups with HAVING
• Challenge: Obtain order summaries
• Solution: Obtain order summaries

5. Manipulate Data with Functions

• Use functions in T-SQL
• String functions
• Text concatenation
• Round with mathematical functions
• Find the GREATEST or LEAST value
• Work with date functions
• FORMAT date and times
• Group dates into buckets
• Return random records with NEWID
• Create a series of values
• The IIF logical function
• Write a CASE statement
• Challenge: Use functions in a SQL query
• Solution: Use functions in a SQL query

6. Writing SQL Scripts

• Change database context with USE
• Add comments to a script
• When to use square brackets

7. SQL Subqueries

• What is a subquery?
• Use a subquery in a HAVING clause
• Correlated subqueries
• PIVOT the result set
• Challenge: Write a query with a subquery
• Solution: Write a query with a subquery

8. Work with Variables

• Create and use variables in a query
• Create a counter for a looping statement
• Challenge: Use variables in a query
• Solution: Use variables in a query

9. Result Set Operators

• Combine results with UNION
• Return distinct rows with EXCEPT
• Return common rows with INTERSECT

Conclusion

• Next steps