DP-080T00: Query and Modify Data with Transact-SQL

Module 1: Introduction to Transact-SQL

• This module provides an introduction to relational databases, the SQL language and Transact-SQL. You will learn what kinds of statements make up the SQL language and look at the SELECT statement in detail.

Learning objectives

• After completing this module, you will be able to:
  • Understand what SQL is for
  • Identify database objects in schemas
  • Identify SQL statement types
  • Use the SELECT statement
  • Work with data types
  • Handle NULLs

Prerequisites

• Before starting this module, it is helpful to have experience of working with tables with data; for example in a spreadsheet.

Module 2: Sort and Filter Results in T-SQL

• Learn how to sort and filter query results

Learning objectives

• After completing this module, you will be able to:
  • Sort your results
  • Limit the sorted results
  • Return paged results
  • Remove duplicates
  • Filter data with predicates

Prerequisites

• Before starting this module, you should be familiar with the basic syntax of the SELECT Transact-SQL statement.

Module 3: Combine Multiple Tables with JOINs in T-SQL

• Explore T-SQL queries accessing data from multiple tables with various kinds of JOIN operations.

Learning objectives

• After completing this module, you will be able to:
  • Describe join concepts and syntax
  • Write queries that use inner and outer joins
  • Write queries that use cross joins
  • Write queries that use self joins

Prerequisites

• Before starting this module, you should have experience of using Transact-SQL SELECT queries to retrieve and filter data from a table in a database.

Module 4: Write Subqueries in T-SQL

• Learn how to use subqueries to combine multiple SELECT statements and compose nested queries.

Learning objectives

• After completing this module, you will be able to:
  • Understand what subqueries are
  • Use scalar or multi-valued subqueries
  • Use self-contained or correlated subqueries

Prerequisites

• Before starting this module, you should have experience of using Transact-SQL SELECT queries to retrieve and filter data from a table in a database.

Module 5: Use Built-In Functions and GROUP BY in Transact-SQL

• Learn how to use functions in Transact-SQL, and how to group aggregated results.

Learning objectives

• After completing this module, you will be able to:
  • Categorize built-in functions
  • Use scalar functions
  • Use ranking and rowset functions
  • Use aggregate functions
  • Summarize data with GROUP BY
  • Filter groups with HAVING

Prerequisites

• Before starting this module, you should have experience of using Transact-SQL SELECT queries to retrieve data from tables in a database.

Module 6: Modify Data with T-SQL

• Learn how to use Transact-SQL to insert, update, and delete data in a database.

Learning objectives

• After completing this module, you will be able to:
  • Insert data into a table
  • Generate automatic values
  • Update data in a table
  • Delete data from a table
  • Merge data based on multiple tables

Prerequisites

• Before starting this module, you should have experience of using Transact-SQL SELECT queries to retrieve data from tables in a database.