DP-080: Querying Data with Microsoft Transact-SQL

Course description

This course covers 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.
 

Audience 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 solution architects, students, and technology administrators.
 

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)
• Data modification 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 T-SQL queries

After completing this module, students will be able to:

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

• Understand the basics 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. It will use the ORDER BY clause, with and without pagination. You will learn about various types of filters that can be used in the WHERE clause to control which rows of data are returned. You will also learn how to manage results by removing duplicates with DISTINCT.

lessons

• Sorting of query results

• data filtering

Lab : Sorting and filtering the data returned by SELECT queries

After completing this module, students will be able to:

• Using ORDER BY to order the results of a T-SQL SELECT statement

• Add a TOP clause to limit the sorted rows returned

• Page sorted data with OFFSET-FET

• Write WHERE clauses to filter the returned rows

• Using DISTINCT to remove duplicate rows from results

Module 3: Using joins and subqueries

In this module, you'll 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 to itself with a self-join

• Writing subqueries inside a SELECT statement

• Understand the difference between scalar and multivalued subqueries

• Understand the difference between correlated and independent subqueries

Module 4: Using built-in functions

In the module, you'll explore using built-in functions to return special or computed 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 not covered in detail. You will 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 the aggregated 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 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 entered.

lessons

• Insertion of data in tables

• Modification and deletion of data

Lab : Modifying Data

After completing this module, students will be able to:

• Insert data into an existing table

• Specify that a column should be auto-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