DP-080: Querying Data with Microsoft Transact-SQL

Course Description

This course describes the basics of Transact-SQL, Microsoft's dialect of the standard SQL language. Topics include querying and modifying data in relational databases hosted on Microsoft SQL Server-based database systems, such as Microsoft SQL Server, Azure SQL Database, and Azure Synapse Analytics.

Public Profile

This course may be useful for people who need to write basic SQL or Transact-SQL queries. This includes anyone who works with data, for example as a data analyst, data engineer, data scientist, database administrator, or database developer. It can also be useful for people who work indirectly with data or who want to learn more about working with data, such as solutions architects, students, and technology managers.

Items in this collection

• Introduction to Transact-SQL (9 Units)
• Sorting and filtering results in T-SQL (9 Units)
• Joining multiple tables with JOIN in T-SQL (9 Units)
• Writing subqueries in T-SQL (7 Units)
• Using built-in functions and GROUP BY in Transact-SQL (10 Units)
• Modifying data with T-SQL (9 Units)

Course outline

Module 1: Introduction to Transact-SQL

In this module you will learn the basics of the Transact-SQL (T-SQL) language, as well as the general properties and terminology of relational databases. This module also introduces the basic SELECT statement for retrieving data from a table.

Lessons

• Introduction to Transact-SQL

• Using the SELECT statement

Lab: Introduction to SQL Server query tools and writing queries in T-SQL

After completing this module, students will be able to:

• Use a query tool to write and run queries in Transact-SQL

• Understand the basic concepts of relational databases and the SQL T language

• Write SELECT statements to retrieve data from a relational database table

• Understand basic data types and how they are used

• Understand the basics of NULL values

Module 2: Sorting and filtering query results

In the module you will learn how to control what data is returned and in what order. You will use the ORDER BY clause, with and without pagination. You will learn about several types of filters that can be used in the WHERE clause to control which rows of data are returned. You'll also learn how to manage results by removing duplicates with DISTINCT.

Lessons

• Sorting query results

• Data leakage

Lab: Sorting and filtering data returned by SELECT queries

After completing this module, students will be able to:

• Use ORDER BY to sort the results of a T-SQL SELECT statement

• Add a TOP clause to limit the sorted rows returned

• Paging sorted data with OFFSET-FET

• Write WHERE clauses to filter returned rows

• Use DISTINCT to remove duplicate rows from results

Module 3: Using joins and subqueries

In this module, you will explore T-SQL queries that access data from multiple tables with various types of JOIN operations and simple subqueries.

Lessons

• Using JOIN operations

• Using subqueries

Lab: Writing queries with JOIN operations

Lab: Writing SELECT Statements Using Subqueries

After completing this module, students will be able to:

• Write queries that access data from multiple tables using JOIN operations

• Understand the differences between the type of JOIN operations: INNER JOIN, OUTER JOIN and CROSS JOIN

• Understand how to join a table with itself with a self-join

• Writing subqueries inside a SELECT statement

• Understand the difference between scalar and multivalue subqueries

• Understand the difference between correlated and independent subqueries

Module 4: Using built-in functions

In the module, you will explore using built-in functions to return calculated or special values ​​in the SELECT list or WHERE clause. Functions include math functions, string functions, and system functions. There are other types of functions that will be mentioned, but they will not be covered in detail. You will also learn how to combine rows of data into a single group and provide summary information for the group, such as SUM, MIN, or MAX.

Lessons

• Introduction to scalar functions

• Grouping of aggregated results

Lab: Integrated Features

After completing this module, students will be able to:

• Write queries using scalar functions

• Write queries using aggregate functions

• Use GROUP BY to combine data into groups based on a common column value

• Understand how HAVING is used to filter groups of rows

Module 5: Data Modification

In this module, you will learn T-SQL statements for modifying table data, such as UPDATE, DELETE, and MERGE, as well as various INSERT options, including creating a table with data from an existing table. You'll also discover how to have the system automatically provide values ​​for columns as data is inserted.

Lessons

• Inserting data into tables

• Modification and deletion of data

Lab: Data Modification

After completing this module, students will be able to:

• Insert data into an existing table

• Specify that a column should be automatically populated with an IDENTITY or SEQUENCE value

• Modify data using the UPDATE statement

• Delete data using the DELETE statement

• Modify data using MERGE to synchronize two tables

Language

• English course

• Labs: English