About the course
Transact-SQL (T-SQL) is the powerful extension to the SQL language used with Microsoft SQL Server and Azure SQL Database, providing the essential capabilities to interact with and manipulate relational data. This introductory course is designed to equip you with the foundational skills to effectively query, combine, transform, and begin programming with T-SQL. It covers the core language elements and tools needed to confidently retrieve and manage data in a SQL Server environment.
The course begins with mastering fundamental SELECT statements and navigating SQL Server Management Studio (SSMS). You will then quickly move to combining query results using powerful operators like UNION, INTERSECT, EXCEPT, and APPLY, enabling you to consolidate data from various sources. You will explore advanced querying techniques, including writing analytical queries using Window functions with the OVER clause, such as RANK, aggregate functions, and OFFSET functions, to perform calculations across sets of table rows.
A key focus is placed on transforming data for reporting and analysis by implementing PIVOT, UNPIVOT, ROLLUP, and CUBE using the GROUPING SET function. The course also introduces the programming aspects of T-SQL, covering variables, control flow, and how to encapsulate logic within stored procedures and user-defined functions for reusability and collaboration. You will learn crucial skills for building robust applications by implementing error handling using RAISERROR, THROW, and @@ERROR, and ensuring data integrity by managing transactions to control data change operations with commit and rollback. Through hands-on labs integrated throughout the modules, you will gain practical experience applying each T-SQL concept directly in the database.
Instructor-led online and in-house face-to-face options are available - as part of a wider customised training programme, or as a standalone workshop, on-site at your offices or at one of many flexible meeting spaces in the UK and around the World.
-
- Use SQL Server Management Studio (SSMS) and write basic SELECT statements to retrieve data.
- Combine the results of multiple queries using UNION, INTERSECT, EXCEPT, and APPLY operators.
- Write analytical queries using Window functions with the OVER clause, including RANK, aggregate, and OFFSET functions.
- Transform data for analysis and reporting using PIVOT, UNPIVOT, ROLLUP, CUBE, and GROUPING SET functions.
- Understand the programming elements of Transact-SQL, including variables and control flow.
- Create basic stored procedures and user-defined functions to encapsulate T-SQL logic.
- Insert, update, and delete data using Transact-SQL statements.
- Implement error handling in Transact-SQL using RAISERROR, THROW, and @@ERROR.
- Implement transactions to control data change operations and ensure data integrity.
-
This course is designed for individuals new to Transact-SQL who need to learn how to query, program, and manage data in Microsoft SQL Server or Azure SQL Database environments. It is ideal for:
Database Developers
Data Analysts
Business Intelligence Professionals
Database Administrators
Report Writers
Anyone needing to gain foundational skills in Transact-SQL.
-
Participants should have:
Basic familiarity with relational database concepts.
Basic computer literacy.
No prior experience with SQL, Transact-SQL, or programming is required.
We can customise the training to match your team's experience and needs, for instance, with more time and coverage of fundamentals for those completely new to relational databases.
-
This TSQL course is available for private / custom delivery for your team - as an in-house face-to-face workshop at your location of choice, or as online instructor-led training via MS Teams (or your own preferred platform).
Get in touch to find out how we can deliver tailored training which focuses on your project requirements and learning goals.
-
Use Table Expressions
Introduction to SQL Server Management Studio, the SQL language elements and the SELECT statement.
Lab1: Writing basic SELECT statements to retrieve data from the Database
Combine Query Results
Use the UNION operator. Use the INTERSECT and EXCEPT operators. Use the APPLY operator.
Lab 2: In this lab, you'll use the Transact-SQL UNION statement to combine multiple SELECT statements in the database.
Write queries that use window functions
Use Window functions with OVER clause, including RANK, AGGREGATE and OFFSET functions.
Lab 3: In this lab, you'll use the Transact-SQL SELECT statement with Window functions in the database.
Transform data by implementing pivot, unpivot, rollup, and cube
Learn how to transform data using Transact-SQL with the PIVOT and GROUPING SET functions.
Lab 4: In this lab, you'll write queries to transform data using PIVOT, UNPIVOT, GROUPING SET, CUBE and ROLLUP functions from tables in the database.
Get started with Transact-SQL programming
Transact-SQL (T-SQL) provides a robust programming language with features that let you temporarily store values in variables, apply conditional execution of commands, pass parameters to stored procedures, and control the flow of your programs.
Lab 5: In this lab, you'll write basic stored procedures and user defined functions to encapsulate a SELECT statement in the database.
Create stored procedures and user-defined functions
Learn how to use Stored Procedures to group T-SQL statements so they can be used and reused whenever needed, saving time and allowing for team collaboration.
Lab 6: In this lab, you'll insert, update, and delete data in the database.
Implement error handling with Transact-SQL
Transact-SQL is a powerful declarative language that lets you explore and manipulate your database. As the complexity of your programs increase, so does the risk of errors occurring.
Lab 7: In this lab, you'll learn to use the RAISERROR, THROW and @@ERROR functions in the database.
Implement Transactions with Transact-SQL
Learn how to construct transactions to control the behavior of multiple Transact-SQL statements. You'll learn how to determine whether an error has occurred, and when to roll back statements.
Lab 8: In this lab, you'll learn to use transactions to control data change operations in the database.
-
Official Microsoft Transact-SQL Documentation -https://learn.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver17
SQL Server Management Studio (SSMS) Documentation - https://learn.microsoft.com/en-us/sql/ssms/
Microsoft SQL Server Documentation - https://learn.microsoft.com/en-us/sql/sql-server/
Azure SQL Database Documentation - https://learn.microsoft.com/en-us/azure/azure-sql/database/
Trusted by
