DP-080: Query and modify data with 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