Intermediate Querying SQL Databases Using T-SQL

About this course

This course will provide more advanced T-SQL knowledge for those who have attended the QATSQL or M20761A courses. This is not a beginner course. For those requiring a basic level of T-SQL for reporting then please review the QATSQL course first.

Audience profile

IT Professionals who will be writing queries for use with Microsoft SQL Server from SQL 2005 to 2016. Non-technical ‘power users’ will also benefit from the course if they are comfortable using sophisticated software tools like SQL Server Management Studio.

The course is aimed at both non-technical ‘power users’ and IT Professionals who will be writing queries for use with Microsoft SQL Server.

Module 1: Using Table Expressions

This module introduces T-SQL expressions which return a valid relational table, typically for further use in the query. The module discusses views, derived tables, common table expressions and inline table-valued functions.

Lessons

  • Using Derived Tables
  • Using Common Table Expressions
  • Using Views
  • Using Inline Table-Valued Functions

Module 2: Using Set Operators

This module introduces the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets.

Lessons

  • Writing Queries with the UNION Operator
  • Using EXCEPT and INTERSECT
  • Using APPLY

Module 3: Using Window Ranking, Offset, and Aggregate Functions

This module introduces window functions including ranking, aggregate and offset functions. Much of this functionality is new to SQL Server 2012. It will cover the use of 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.

Lessons

  • Creating Windows with OVER
  • Exploring Window Functions

Module 4: Pivoting and Grouping Sets

This module discusses techniques for pivoting data in T-SQL as well to introduce the fundamentals of the GROUPING SETS clause. It will also cover the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server.

Lessons

  • Writing Queries with PIVOT and UNPIVOT
  • Working with Grouping Sets

Module 5: 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 conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.

Lessons

  • T-SQL Programming Elements
  • Controlling Program Flow

Module 6: Implementing Error Handling

This module introduces the use of error handlers in T-SQL code. It will introduce the difference between compile errors and run-time errors, and will cover how errors affect batches. The module will also cover 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.

Lessons

  • Using TRY / CATCH Blocks
  • Working with Error Information

Module 7: Implementing Transactions

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

Lessons

  • Transactions and the Database Engine
  • Controlling Transactions
  • Isolation Levels

Delegates should have knowledge to the level of QA’s QATSQL or M20761A courses. This is not a beginner course. For those requiring a basic level of T-SQL for reporting then please review the QATSQL course first.

Pradžios data Trukmė, d. Kurso pavadinimas Kaina, € Statusas
2020-09-07 5 20761: Querying Data with Transact-SQL 1500
2020-09-28 5 20764: Administering a SQL Database Infrastructure 1500
2020-10-12 5 20767: Implementing a SQL Data Warehouse 1500
2020-09-14 4 10987: Performance Tuning and Optimizing SQL Databases 1400
2020-10-26 5 20762: Developing SQL Databases 1500
2020-11-02 5 20765: Provisioning SQL Databases 1500
2020-10-21 3 10988: Managing SQL Business Intelligence Operations 1200
2020-11-30 3 20768: Developing SQL Data Models 1200
2020-11-16 3 10989: Analyzing Data with Power BI 1200
2020-11-23 5 10990: Analyzing Data with SQL Server Reporting Services 1500
Užklausti 5 Administering a SQL Database Infrastructure (nuotolinė klasė - ENG) 1500 Organizuojamas
Užklausti 5 Querying Data with Transact-SQL (nuotolinė klasė - ENG) 1500 Organizuojamas
Užklausti 3 Introduction to SQL Databases (nuotolinė klasė - ENG) 1200 Organizuojamas
Užklausti 5 Provisioning SQL Databases (nuotolinė klasė - ENG) 1500 Organizuojamas
Užklausti 5 Querying Microsoft SQL Server (nuotolinė klasė - ENG) 1500 Organizuojamas
Užklausti 5 Administering Microsoft SQL Server Databases (nuotolinė klasė - ENG) 1500 Organizuojamas
Užklausti 2 Querying SQL Databases Using T-SQL (nuotolinė klasė - ENG) 1100 Organizuojamas
Užklausti 2 Intermediate Querying SQL Databases Using T-SQL (nuotolinė klasė - ENG) 1100 Organizuojamas
Užklausti 5 Developing SQL Databases (nuotolinė klasė - ENG) 1500 Organizuojamas
Užklausti 5 Implementing a SQL Data Models and Reports with Microsoft SQL Server (nuotolinė klasė - ENG) 1500 Organizuojamas
Užklausti 5 Analyzing Data with SQL Server Reporting Services (nuotolinė klasė - ENG) 1500 Organizuojamas
Užklausti 5 Implementing a SQL Data Warehouse (nuotolinė klasė - ENG) 1500 Organizuojamas
Užklausti 5 Microsoft Business Intelligence end to end with SQL Server 2016 (nuotolinė klasė - ENG) 1500 Organizuojamas