Overview

This comprehensive two-day course introduces Microsoft's implementation of the SQL standard language, known as Transact-SQL. You will learn both data retrieval and data manipulation techniques for relational databases running on Microsoft SQL Server and related platforms, including Microsoft SQL Server, Azure SQL Database, and Azure Synapse Analytics.

Intended Audience:

This course is valuable for anyone who needs to write SQL or Transact-SQL queries for work. Database professionals, including data analysts, data engineers, data scientists, database administrators, and database developers, will benefit greatly from this training. It is also useful for solution architects, students, technology managers, and others who work with data systems and want to deepen their understanding of data management and retrieval.

Target Job Role:

Database Administrator

Key Skills You Will Gain:

Use SQL Server query tools and interfaces
Write SELECT statements to retrieve specific columns from one or multiple tables
Sort and filter selected data using various clauses and conditions

Detailed Course Outline:

Module 1: Getting Started with Transact-SQL

This module covers the fundamentals of the Transact-SQL language, including essential properties and terminology of relational databases. You will learn the basic SELECT statement for retrieving data from tables and understand how to construct simple queries.

Topics

Introduction to Transact-SQL and T-SQL fundamentals
Using the SELECT statement for data retrieval
Lab: Get started with SQL Server query tools and writing queries in T-SQL

Module 2: Sorting and Filtering Query Results

In this module, you will learn to control what data is returned from queries and in what order it appears. You will use the ORDER BY clause with and without paging techniques. You will explore different filter options available through the WHERE clause to control which rows are returned. You will also learn to manage results by removing duplicate entries using the DISTINCT keyword.

Topics

Sorting query results using ORDER BY
Filtering the data with WHERE clauses
Lab: Sort and filter data returned by SELECT queries

Module 3: Using Joins and Subqueries

This module explores advanced T-SQL queries that access data from multiple tables using various JOIN operations and incorporates simple subqueries for more complex data retrieval.

Topics

Using JOIN operations for combining tables
Using subqueries for nested queries
Lab: Write queries with JOIN operations
Lab: Write SELECT statements using subqueries

Module 4: Using Built-in Functions

You will explore the built-in functions available in T-SQL for returning computed or special values in SELECT statements and WHERE clauses. This includes mathematical functions, string manipulation functions, and system functions. You will also learn how to combine rows of data into groups and provide summary information such as SUM, MIN, and MAX calculations.

Topics

Getting started with scalar functions and their applications
Grouping aggregated results for summary analysis
Lab: Built-in functions and aggregate operations

Module 5: Modifying Data

In this module, you will learn the T-SQL statements for modifying table data, including UPDATE, DELETE, and MERGE operations. You will explore various options for INSERT statements, including creating new tables from existing data. You will also learn how the system can automatically supply values for columns during data insertion.

Topics