Who is this training for?
Data analysts, data engineers, data scientists, database administrators, database developers, solution architects, students, and CTO.
Training objectives
- Use SQL tools to write and execute queries
- Learn the SELECT command to extract columns from one or more tables
- Sort and filter selected data
- Use built-in functions to return data values
- Create datasets and aggregate results
- Modify data with Transact-SQL using INSERT, UPDATE, DELETE and MERGE
Summary
This course covers querying and modifying data in databases hosted in Microsoft SQL Server, Azure SQL Database, and Azure Synapse Analytics. It provides the essential skills to work with SQL and Transact-SQL in an efficient and structured way.
Course outline
Module 1: Getting Started with Transact-SQL
- Introduction to Transact-SQL
- Using SELECT
- Understanding Databases and T-SQL
- Writing SELECT Statements to Extract Data from a Relational Database Table
- Understanding Basic Data Types and How They Are Used
- Understanding the Basics of NULL Values
Module 2: Sort and Filter the Results of a Query
- Sort the Query Results
- Filter the Data
- Use ORDER BY to Sort the Results of a SELECT T-SQL statement
- Add a TOP Command to Limit Sorted Rows Returned
- Paginate Sorted Data with OFFSET-FETCH
- Write WHERE Clauses to Filter Returned Rows
- Use DISTINCT to Eliminate Duplicate Rows in Results
Module 3: Using Joins and Subqueries
- Using JOIN Operations
- Using Subqueries
- Writing Queries to Access Data from Multiple Tables Using JOIN Operations
- Understanding the Differences Between Types of JOIN Operations: INNER JOIN, OUTER JOIN, CROSS JOIN
- Understanding how to join a table to itself with a SELF-JOIN
- Writing subqueries in a SELECT statement
- Understanding the difference between scalar and
- Understand the difference between correlated and stand-alone subqueries
Module 4: Using built-in functions
- Scalar functions
- Grouping aggregated results
- Writing queries using scalar functions
- Writing queries using aggregate functions
- Using GROUP BY to combine data into groups based on the value of a common column
- Understanding how HAVING is used to filter groups of rows
Module 5: Editing Data
- Inserting Data into Tables
- Editing and Deleting Data
- Inserting Data into a Table
- Specifying that a Column Should Be Automatically Populated with an IDENTITY or SEQUENCE Value
- Editing Data Using UPDATE
- Deleting Data Using DELETE
- Editing Data Using MERGE to Synchronize Two Tables
Approach and methodology
Practical and structured approach combining focused theory and guided workshops. Participants progressively interrogate the data with Microsoft Transact-SQL through concrete exercises inspired by professional scenarios, promoting an immediate application of the learning. They learn how to write and optimize queries, manipulate data, and extract relevant information from relational databases. Led by a Microsoft certified trainer, the training focuses on interactivity and the development of directly transferable technical skills to effectively exploit data in a professional context.
Prerequisites
- Azure Basics (AZ-900 or equivalent content).
- Basic knowledge of Python and data visualization with matplotlib/seaborn.
Recommendations
- Basic knowledge of relational databases Notions of SQL query (SELECT, filtering, joins)
- Understanding of the concepts of tables, relationships and keys
- Ease with a computer environment and data manipulation
