Microsoft Transact-SQL: Querying and Modifying Data

Module 1: Understanding Transact-SQL

• Transact-SQL fundamentals: concepts, writing and executing queries, basic properties and terminology.
• Using the SQL SELECT statement.
• Basic data types and their uses.
• Key rules for NULL values.

Hands-on lab

• Explore basic SQL Server query tools.
• Write and execute your first T-SQL queries.

Module 2: Processing and filtering data

• Sort query results: control returned data, sort order and use the ORDER BY clause.
• Filter data: filter types, use the WHERE clause and handle duplicate data.

Hands-on lab

• Sort T-SQL SELECT results with SQL ORDER BY.
• Restrict the number of ordered rows returned with SQL TOP.
• Page through sorted data using SQL OFFSET-FETCH.
• Filter returned rows with WHERE clauses.
• Remove duplicate rows from results with SQL DISTINCT.

Module 3: Using joins and subqueries for analysis

• JOIN operations.
  Basic subquery operations.

Hands-on lab

• Write queries that access data from several tables using JOIN operations.
• Distinguish INNER JOIN, OUTER JOIN and CROSS JOIN operations.
• Explain how to join a table to itself using a self-join.
• Write subqueries within a SELECT statement.
• Distinguish scalar and multivalued subqueries.
• Distinguish correlated and self-contained subqueries.

Module 4: Understanding Transact-SQL built-in functions

• Scalar function fundamentals.
• Aggregate results.

Hands-on lab

• Write queries using scalar functions.
• Write queries using aggregate functions.
• Group data by a shared column value with GROUP BY.
• Understand how HAVING filters groups of rows.

Module 5: Modifying table data

• Add and update table data in T-SQL using INSERT and UPDATE.
• Modify and delete data in T-SQL using UPDATE, DELETE and MERGE.

Hands-on lab

• Add and update data in an existing table using INSERT and UPDATE.
• Use IDENTITY or SEQUENCE values to populate a data column automatically.
• Modify and delete data using UPDATE and DELETE.
• Modify data using MERGE.