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.
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
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
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
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
