Intermediate Querying SQL Databases using T-SQL

Overview

This course provides more advanced T-SQL knowledge for those who’ve already attended QATSQL. This is not a beginner’s course, and a foundational knowledge of writing T-SQL queries is required. For those without a basic level of T-SQL for reporting, please complete the QATSQL course first.

Prerequisites

Participants should have:

• Experience of writing SQL queries using SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and INNER and OUTER JOIN statements.

Target audience

This course is designed for IT professionals writing queries for use with Microsoft SQL Server from SQL 2005 onwards. Non-technical 'power users' will also benefit from the course if they’re comfortable using sophisticated software tools like SQL Server Management Studio.

Objectives

By the end of this course, learners will be able to:

• Use various table expressions to write queries in a modular way.
• Use advanced forms of GROUP BY, including PIVOT, UNPIVOT, GROUPING SETS, and GROUPING_ID.
• Use window functions to perform ranking operations and to perform comparisons between rows within result sets.
• Use batches, variables, control of flow, loops, conditional operations, and dynamic SQL appropriately.
• Use error handling statements.
• Use transactions and isolation levels in code.

Outline

Using Table Expressions

• This module introduces T-SQL expressions which return a valid relational table, typically for further use in the query. You’ll discuss views, inline table-valued functions, multi-statement table-valued functions, derived tables, common table expressions, and temporary tables.

Pivoting and Advanced Grouping

• This module discusses techniques for pivoting data in T-SQL and introduces fundamentals of the GROUPING SETS clause. It also covers the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server.

Window Functions

• This module introduces window functions, including ranking, aggregate, and offset functions. It also covers using T-SQL functions, such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, and LAST_VALUE, to perform calculations against a set, or window, of rows.

Programming with T-SQL

• This module provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditional operations, and how to create and execute dynamic SQL statements.

Implementing Error Handling

• This module introduces the use of error handlers in T-SQL code, including the difference between compile errors and run-time errors and how errors affect batches. It also examines how to control error handling using TRY/CATCH blocks, the use of the ERROR class of functions, and the use of the new THROW statement.

Transactions and Isolation Levels

• This module introduces the concepts of transaction management in SQL Server. It provides a high-level overview of transaction properties, covering the basics of marking transactions with BEGIN, COMMIT, and ROLLBACK.

Appendix: Subqueries

• This module is provided for additional post-course reading and gives a brief introduction to the concept of subqueries in SQL.

Appendix: Using Set Operators

• This module is provided for additional post-course reading, and introduces the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets, along with the APPLY operator, which can be used to execute a user-defined function against every row returned by a query.

Exams and assessments

There are no formal exams included in this course. Participants will complete interactive labs and knowledge checks to reinforce learning outcomes.

Hands-on learning

This course includes:

• Hands-on labs to apply key concepts in a real-world environment.