Querying Microsoft SQL Server 2019

Course details

Discover how to query data from SQL Server tables. In this course, Adam Wilbert covers how to retrieve information stored in a Microsoft SQL Server database using the Transact-SQL (T-SQL) language. Learn the fundamental structure of a query and see how to manipulate data through the use of built-in functions, operators, filters, and other language features to return a dataset that's formatted to meet a wide variety of needs and perspectives. Upon wrapping up this course, you'll be equipped with the commands and keywords you need to know to effectively analyze your data.

Learning objectives
Writing SELECT queries
Removing duplicates with DISTINCT
Matching text with LIKE and wildcards
Retrieving records from multiple tables
Grouping records
Using functions in T-SQL
Writing SQL scripts
Using subqueries
Creating and using variables in a query

Concepts

Introduction

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

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

Additional Filter Techniques

Limit results with TOP
Remove duplicates with DISTINCT
Comparison operators
Understand NULL values
Match text with LIKE and wildcards

Retrieve Records from Multiple Tables

Table joins and relationships
Inner joins
Left, right, and full outer joins
Cross joins

Grouping Records

Understand record grouping
Use GROUP BY and COUNT
Aggregate functions
Explore data with SUM
Filter groups with HAVING

Manipulate Data with Functions

Use functions in T-SQL
String functions
Text concatenation
Round with mathematical functions
Work with date functions
FORMAT date and times
Return random records with NEWID
The IIF logical function

Writing SQL Scripts

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

SQL Subqueries

What is a subquery
Use a subquery in a WHERE clause
Correlated subqueries
PIVOT the result set

Work with Variables

Create and use variables in a query
Create a counter for a looping statement

Result Set Operators

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

Conclusion

Next steps