DP-080: Querying Data with Microsoft Transact-SQL

DP-080 Course: Querying Data with Microsoft Transact-SQL

This course describes the fundamentals 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.

Microsoft Transact-SQL - DP-080 SQL Server - SQL query training - Microsoft SQL - SQL Server data - SQL for developers - SQL with Azure - relational databases - standard SQL language

Addressed to

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 may also be useful for people who work indirectly with data or who want to learn more about working with data, such as solution architects, learners, and technology administrators.

Training objectives

This course aims to teach the fundamentals of the Transact-SQL (T-SQL) language, used in environments such as Microsoft SQL Server, Azure SQL Database, and Azure Synapse Analytics. Upon completion, participants will be able to:

• Use SQL Server query tools.
  
• Write SELECT statements to retrieve data from one or more tables.
  
• Sort and filter results with ORDER BY and WHERE.
  
• Use built-in functions and group data with GROUP BY.
  
• Write subqueries and combine tables with JOIN.
  
• Modify data with INSERT, UPDATE, DELETE, and MERGE statements.

Elements of the DP-080 training

• Introduction to Transact-SQL (9 Units)
• Sorting and Filtering Results in T-SQL (9 Units)
• Joining multiple tables with a 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)
• Data Modification with T-SQL (9 Units)

Course content DP-080 Querying Data with Microsoft Transact-SQL

Module 1: Introduction to Transact-SQL

In this module, you'll learn the basics of the Transact-SQL (T-SQL) language, as well as 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 T-SQL Queries

After completing this module, students will be able to:

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

• Understand the basics of relational databases and the SQL language

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

• Understand basic data types and how they are used

• Understanding the basics of NULL values

Module 2: Sorting and filtering query results

In this module, you'll learn how to control what data is returned and in what order. You'll use the ORDER BY clause, with and without pagination. You'll 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 leak

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 the returned rows

• Use DISTINCT to remove duplicate rows from the results

Module 3: Using Joins and Subqueries

In this module, you will explore T-SQL queries that access data from multiple tables using 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

• Understanding the differences between JOIN operations: INNER JOIN, OUTER JOIN, and CROSS JOIN

• Understanding how to join a table with itself using a self-join

• Writing subqueries within a SELECT statement

• Understanding the difference between scalar and multivalued subqueries

• Understanding the difference between correlated and independent subqueries

Module 4: Using Built-in Functions

In this module, you'll explore how to use built-in functions to return calculated or special values ​​in the SELECT list or WHERE clause. These functions include mathematical functions, string functions, and system functions. Other types of functions will be mentioned, but not covered in detail. You'll also learn how to combine rows of data into a single group and how to provide summary information for the group, such as SUM, MIN, or MAX.

Lessons

• Introduction to scalar functions

• Grouping of aggregate results

Lab: Built-in Functions

After completing this module, students will be able to:

• Writing queries using scalar functions

• Writing queries using aggregate functions

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

• Understand how to use HAVING to filter groups of rows

Module 5: Data Modification

In this module, you'll learn T-SQL statements for modifying table data, such as UPDATE, DELETE, and MERGE, as well as several INSERT options, including creating a table with data from an existing table. You'll also learn 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

• Deleting data using the DELETE statement

• Modify data using MERGE to synchronize two tables

Prerequisites

This course is aimed at people who work with data or want to learn how to query it. Recommended:

• Basic knowledge of relational databases.
  
• Familiarity with concepts such as tables, columns, rows, and data types.
  
• No prior programming experience is required, although it may be helpful.

Language

• Course: English

• Labs: English