0845 450 6120

Advanced Querying SQL Databases using T-SQL

Course Details

Name Advanced Querying SQL Databases using T-SQL
Description
URL
Location:
Glasgow
Start Date:
Days:
Price:
£1249.00 +vat
was £1295.00
Exam:
Residential:
Course ID:
354116
Offer

Overview

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.

Target Audience:

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.

Please note - Delegates attending an Extended Classroom delivery will need access to a second monitor.

Pre-Requisites

Delegates should have knowledge to the level of  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.

Course Content

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

Glasgow - Renfield Street

Description:

Venue's features:

  • Easy access from main travel routes
  • Spacious, comfortable air-conditioned classrooms
  • Drinks and snacks available
  • Internet access
  • Choice of hotels and accommodation available nearby
  • Compliance with Disability Discrimination Act (DDA) building access requirements.

Location:

Portland House
17 Renfield Street
Glasgow
G2 5AH

 

Directions:

By Road:


Follow the signs to the City Centre car parks. The nearest car park is the NCP Mitchell Street car park - use the exit to Gordon Street and Renfield Street is on your left. Alternatively, the NCP Oswald Street car park which has a pedestrian exit through Central Station (see above) is within five minutes walk from Portland House.
 


By Rail: 


Glasgow
Central Station
Leave the station via the main exit into Gordon Street. Turn right, cross the road, and turn left at the traffic lights into Renfield Street. The training centre is in Portland House, which is on the left, with a Starbucks Coffee Shop on the ground floor.  

Queen Street Station
Take the exit onto West George Street, turning left and walk to the junction with Buchanan Street (pedestrianised shopping street) and turn left into Buchanan Street. Turn right into St Vincent Street which crosses Renfield Street at the next traffic lights. Turn left, and Portland House is across the road facing you. 

 

By Bus:


Express buses run from GlasgowAirport (terminal forecourt, outside departures lobby) to near the Central railway station, to Queen Street railway station, and to the Buchanan Street bus station.
There's service every 15 minutes throughout the day. The fare is £3.50 on both Scottish Citylink and Fairline buses.

Our Customers Include