Advanced Transact SQL Training Course

Who should attend

Anyone new to database development who needs to gain hands-on experience of writing and maintaining SQL queries, on platforms such as Access, MySQL, SQL Microsoft Server, Oracle, Sybase...

Prerequisites

Working knowledge of your Operating System (eg Windows, Linux, MacOSX)

Live, instructor-led online and on-site training

This Advanced Transact SQL course is available for private / custom delivery for your team - face-to-face, on-site at your location of choice, or remotely via MS Teams, Zoom or your own platform - get in touch to find out how we can deliver tailored training which focuses on your project requirements and learning goals.

Each module includes a presentation, followed by a lab where delegates execute various statements, before trying to write their own queries.

Module 1: 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 Adventureworks Database

Module 2: 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 Adventureworks database.

Module 3: 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 Adventureworks database.

Module 4: 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 Adventureworks database.

Module 5: 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 AdventureWorks database.

Module 6: 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. You may need to execute stored procedures that someone else has created or create your own.
Lab 6: In this lab, you'll insert, update, and delete data in the AdventureWorks database.

Module 7: 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 AdventureWorks database.

Module 8: 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 AdventureWorks database.