Advanced Querying Microsoft SQL Server with Transact-SQL

Course Code: TSQLA

Duration: 2 days

 
 
 
 

Advanced Querying Microsoft SQL Server with Transact-SQL Course Overview

The Advanced Querying Microsoft SQL Server with Transact-SQL course is designed to give delegates practical experience in using more complex Transact-SQL techniques.

Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Course Objectives

To provide more advanced coding skills for those already familiar with using basic Transact-SQL or as a follow-on course for those who have previously attended the Querying Microsoft SQL Server with Transact-SQL course.

Who will the Course Benefit?

IT Professionals and non-technical 'power users' who are required to implement more complex Transact-SQL queries.

Skills Gained

The delegate will learn and acquire skills as follows:

  • Creating and using Views
  • Using table expressions
  • Enhanced Grouping and Pivoting Data
  • Using Windowing ranking, offset and ranking functions
  • Creating and managing Tables
  • Creating and using Sequences and Synonyms
  • Creating and managing Indexes
  • Obtaining schema metadata from data dictionary
  • Improving Query Performance

Advanced Querying Microsoft SQL Server with Transact-SQL Training Course

Course Introduction

  • Administration and Course Materials
  • Course Structure and Agenda
  • Delegate and Trainer Introductions

Session 1: USING TABLE EXPRESSIONS

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

Session 2: PIVOTING AND ENHANCED GROUPING

  • Using PIVOT and UNPIVOT in Queries
  • Working with GROUPING SETS
  • Using ROLLUP, CUBE,GROUPING and GROUPING_ID

Session 3: USING WINDOWING FUNCTIONS

  • Using Window AGGREGATE functions in Queries
  • Using Window RANK functions in Queries
  • Using Window OFFSET functions in Queries

Advanced Querying Microsoft SQL Server with Transact-SQL Training Course

Session 4: CREATE AND MANAGE TABLES

  • Creating tables
  • Datatypes and Constraints
  • Altering and Dropping tables
  • Creating temporary tables
  • Copying tables
  • Creating and using Sequences and Synonyms
  • Obtaining metadata

Session 5: INDEXES

  • Index types
  • Creating indexes
  • Performance considerations
  • Filtered indexes
  • Indexes on View
  • Indexes with INCLUDE
  • Dropping indexes
  • Obtaining metadata

Session 6: METADATA

  • INFORMATION_SCHEMA views
  • Sys views
  • Comparison between INFORMATION_SCHEMA and sys views
  • Obtaining metadata in SSMS

Session 7: IMPROVING QUERY PERFORMANCE

  • Factors in Query Performance
  • Displaying Query Performance Data
Notes:
  • Course technical content is subject to change without notice.
  • Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

Requirements

A working knowledge of SQL is required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite Querying Microsoft SQL Server with Transact-SQL course.

Pre-Requisite Courses

Course Reviews

Very impressed with the training content and how useful I found it. John, the trainer, was also great, very accommodating and eager to share useful tips and tricks and answered all my questions. I also thought it helped that the workshop examples were usually on the same table, so that could just focus on practicing the specific content. The workshop examples were very well thought out and really helped with letting the acquired knowledge sink in.

Diana - Net Zero and Sustainable Procurement Man - NHS - March 2024

Very pleased with the content of the course and professional approach of tutor who's knowledge was overwhelming - fantastic approach which gave me a great understanding.

Tomasz - Automation Support Technician - Postal/Distribution - August 2023

John - thank you for another excellent training course.

Dan - Commercial Officer - Utilities

Brilliant course, lovely attitude from Kevin. Never felt silly for not knowing answers and felt able to keep trying without being afraid to ask questions when needed.

Chrissy - System Specialist - Charity

I really enjoyed the course and found it very helpful. My lecturer was very professional and helpful with great ability to explain complicated terms.

Anna - Qaulity Advisor

Fantastic course, clearly delivered by John - I just need to practice now!

Leonie - Assistant Contracts Manager

Public Scheduled Events

Classroom & Live Virtual Instructor-Led Training

Duration: 2 days

Price: £870.00 exc. VAT 


Start Date Options Spaces  
18 Apr 2024
StayAhead Virtual Courses available 
Spaces Book Now 
23 May 2024
StayAhead Virtual Courses available 
Spaces Book Now 
27 Jun 2024
StayAhead Virtual Courses available 
Spaces Book Now 
01 Aug 2024
StayAhead Virtual Courses available 
Spaces Book Now 
12 Sep 2024
StayAhead Virtual Courses available 
Spaces Book Now 
24 Oct 2024
StayAhead Virtual Courses available 
Spaces Book Now 
28 Nov 2024
StayAhead Virtual Courses available 
Spaces Book Now 
09 Jan 2025
StayAhead Virtual Courses available 
Spaces Book Now 
 

Live Virtual Classroom

 
Join live instructor-led classroom training from the comfort of your home or office.
All the convenience and benefits of the classroom experience without the hassle and costs of travel and accommodation.
 
 



Our Customers Include

 
EDF
Amazon
American Express
Aviva
QA
BAE
University of Cambridge
Barnardo's
Scottish Government
Bauer
Bloomberg
BP
HSBC
DVLA
GlaxoSmithKline
Government Campus
Capita
Tui
NHS
Ordnance Survey
Ministry of Defence
Zurich Insurance Group
trainline
Vodafone
 
 



Our Course Curriculum

 
 
 
+44 (0)20 7600 6116
Enquiries@StayAhead.com
Copyright © 2024 StayAhead Training Ltd
Cookies   /   Privacy Policy