DP-080: Querying Data with Microsoft Transact-SQL

Introduction to Transact-SQL

• • Work with schemas

 

• • Explore the structure of SQL statements

 

• • Examine the SELECT statement

 

• • Work with data types

 

• • Handle NULLs

 

• • Exercise – Work with SELECT statements

 

• • Knowledge check

 

Sort and filter results in T-SQL

• • Sort your results

 

• • Limit the sorted results

 

• • Page results

 

• • Remove duplicates

 

• • Filter data with predicates

 

• • Exercise – Sort and filter query results

 

• • Knowledge check

 

Combine multiple tables with JOINs in T-SQL

• • Understand joins concepts and syntax

 

• • Use inner joins

 

• • Use outer joins

 

• • Use cross joins

 

• • Use self joins

 

• • Exercise – Query multiple tables with joins

 

• • Knowledge check

 

Write Subqueries in T-SQL

• • Understand subqueries

 

• • Use scalar or multi-valued subqueries

 

• • Use self-contained or correlated subqueries

 

• • Exercise – Use subqueries

 

• • Knowledge check

 

Use built-in functions and GROUP BY in Transact-SQL

• • Categorize built-in functions

 

• • Use scalar functions

 

• • Use ranking and rowset functions

 

• • Use aggregate functions

 

• • Summarize data with GROUP BY

 

• • Filter groups with HAVING

 

• • Exercise – Use built-in functions

 

• • Knowledge check

 

Modify data with T-SQL

• • Insert data

 

• • Generate automatic values

 

• • Update data

 

• • Delete data

 

• • Merge data based on multiple tables

 

• • Exercise – Modify data

 

• • Knowledge check