Advanced SQL Server Database Querying

Duration: 2 Days

Objectives

This course is aimed at SQL Server database developers, administrators and analysts who need to take the basics of SQL Server SQL Data Manipulation Language (DML) statements to the next level. The aim of the course is to show the more advanced use of SELECT, UPDATE and DELETE statements providing delegates with the necessary knowledge and hands on experience to construct complex queries to satisfy complex information extraction requirements. The course focuses on real world business needs and shows how to make the extensive SQL Server library of functions work together to manipulate and extract data values. It also explores the many different ways of retrieving information from multiple tables from simple multi-table SELECT statements through to complex correlated sub-queries as well as introducing the many ways of analysing and processing data through methods such as grouping of data and implementing computed columns. The course will do more than just teach each topic as an isolated subject; it will show delegates how to integrate all these query methods and more to help satisfy their business information extraction needs. This course, along with the Creating Databases in SQL Server and SQL Server Database Querying Courses, helps to prepare delegates for the Microsoft 70-461 exam: Querying Microsoft SQL Server 2012/2014/2016, exam 70-761 Querying Data with Transact-SQL (M20761), and exam 70-762: Developing SQL Databases (M20762), which credit towards MCSA and MCSE certifications. It should be noted, however, that all course attendances should be complemented by reference to the skills measured by the exam, a period of self-study and test exams before sitting the actual exam. Covers SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, and SQL Server 2016.

Pre-requisites

Delegates would benefit from a basic understanding of the concept of relational databases and terms such as table, column, row and relationship, although these concepts will be recapped at the start of the course.

Course includes

A comprehensive set of reference notes covering course topics and follow-up telephone support.

Course outline

Review of SELECT Statement Basics

Nesting Functions

CASE Statement

Multiple Table Selects

  • CROSS Joins
  • INNER Join
  • OUTER Joins
  • FULL OUTER Join
  • Table Aliases
  • Self Join
  • Joining on non-key columns
  • Advanced Joins
  • Mixing INNER and OUTER Joins

Temporary Tables

  • Local Temporary Tables
  • Global Temporary Tables

Views

  • Single Table Views
  • Multi-table Views
  • Updating Through Views

Advanced Data Organisation

  • The GROUP BY Clause
  • The HAVING Clause
  • Ranking Grouped Data
  • ROW_NUMBER
  • RANK and DENSE_RANK
  • NTILE
  • The CUBE and ROLLUP options
  • GROUPING SETS
  • PIVOT

Sub Queries

  • Simple Sub Queries
  • Using Aggregate Functions in Sub Queries
  • Correlated Sub Queries
  • Avoiding Sub Queries
  • Derived Table Sub Queries
  • Sub Queries in SELECT Lists

Advanced Update and Delete Statements

  • Simple Update and Delete Statements
  • UPDATES and DELETES With Sub Queries
  • The FROM Clause

Merging Records

  • MERGE Statement

Common Table Expressions

  • Simple CTEs
  • Recursive Data Sets
  • Hierarchical Data Sets

Simple Execution Plans

  • Graphical Execution Plans
  • Estimated Plans
  • Actual Plans
  • Query Plan Costs
  • Physical Operations
  • Order Of Execution

Course Dates

London CentralLondon GreenwichBristol

Dates Price Book Now
26 - 27 Oct £745 Book Now
23 - 24 Nov £745 Book Now
21 - 22 Dec £745 Book Now
See more dates...
All our courses run in London and Bristol, or can be tailored to requirements at your own offices. For alternative dates, please email or call us on 0208 269 4260
All prices are per person and are subject to VAT
15% discount for groups of four delegates.
10% discount for groups of three delegates.
(This offer applies for delegates on the same public scheduled course on the same date.)

Related Courses


"Very thorough course and enthusiastic trainer who was very helpful and responsive to individual needs."

NVIDIA

"I have nearly thirty years experience as an information systems manager and I cannot recall meeting a trainer with the combination of teaching skill and subject knowledge that you displayed over the last two days. I am self employed and so time spent on a course is time spent not earning. This was worth the money and my time!"

Trinitas Consulting

"Staff were helpful and the course was relevant to my needs. A great course!"

VCCP



 

 

© Transmedia . 277 Greenwich High Road London SE10 8NB  UK . 0208 269 4260
Designed by Transmedia's Web Team