Querying Microsoft SQL Server (NE-20461)

This 5-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014, and is the foundation for all SQL Server-related disciplines.


Certified Training Course




R 8 800 p.p. (Excl. VAT)

SATV Redeemable 


Delivery Method

Classroom-based, Instructor-led with emphasis on hands-on exercises.


5 days

Start time: 09:00 (Registration will commence at 08:30 on the first day)


The knowledge and skills that a learner must have before attending this course is as follows: 

  • Working knowledge of relational databases
  • Basic knowledge of the Microsoft Windows operating system and its core functionality    

Course Objectives

After completing this course, students will be able to:

  • Describe the basic architecture and concepts of Microsoft SQL Server 2014
  • Understand the similarities and differences between Transact-SQL and other computer languages
  • Write SELECT queries
  • Query multiple tables
  • Sort and filter data
  • Describe the use of data types in SQL Server
  • Modify data using Transact-SQL
  • Use built-in functions
  • Group and aggregate data
  • Use subqueries
  • Use table expressions
  • Use set operators
  • Use window ranking, offset and aggregate functions
  • Implement pivoting and grouping sets
  • Execute stored procedures
  • Program with T-SQL
  • Implement error handling
  • Implement transactions

Exam & Certifications

This course prepares students to write the Querying Microsoft SQL Server 2012 Exam (Exam # 70-461) and counts as credit towards the Microsoft Certified Solutions Associate (MCSA): SQL Server 2012 certification.

Course Topics

1. Introduction to Microsoft SQL Server 2014


  • The Basic Architecture of SQL Server
  • SQL Server Editions and Versions
  • Getting Started with SQL Server Management Studio

Lab: Working with SQL Server 2014 Tools

2. Creating Virtual Machines


  • Introducing T-SQL
  • Understanding Sets
  • Understanding Predicate Logic
  • Understanding the Logical Order of Operations in SELECT statements

Lab: Introduction to Transact-SQL Querying

3. Writing SELECT Queries


  • Writing Simple SELECT Statements
  • Eliminate Duplicates with DISTINCT
  • Using Column and Table Aliases
  • Write Simple CASE Expressions

Lab: Writing Basic SELECT Statements

4. Querying Multiple Tables


  • Understanding Joins
  • Querying with Inner Joins
  • Querying with Outer Joins
  • Querying with Cross Joins and Self Joins

Lab: Querying Multiple Tables

5. Sorting and Filtering Data


  • Sorting Data
  • Filtering Data with Predicates
  • Filtering with the TOP and OFFSET-FETCH
  • Working with Unknown Values

Lab: Sorting and Filtering Data

6. Working with SQL Server 2014 Data Types


  • Introducing SQL Server 2014 Data Types
  • Working with Character Data
  • Working with Date and Time Data

Lab: Working with SQL Server 2014 Data Types

7. Using DML to Modify Data


  • Inserting Data
  • Modifying and Deleting Data

Lab: Using DML to Modify Data

8. Using Built-In Functions


  • Writing Queries with Built-In Functions
  • Using Conversion Functions
  • Using Logical Functions
  • Using Functions to Work with NULL

Lab: Using Built-In Functions

9. Grouping and Aggregating Data


  • Using Aggregate Functions
  • Using the GROUP BY Clause
  • Filtering Groups with HAVING

Lab: Grouping and Aggregating Data

10. Using Subqueries


  • Writing Self-Contained Subqueries
  • Writing Correlated Subqueries
  • Using the EXISTS Predicate with Subqueries

Lab: Using Subqueries

11. Using Table Expressions


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

Lab: Using Table Expressions

12. Using Set Operators


  • Writing Queries with the UNION Operator
  • Using APPLY

Lab: Using Set Operators

13. Using Window Ranking, Offset, and Aggregate Functions


  • Creating Windows with OVER
  • Exploring Window Functions

Lab: Using Window Ranking, Offset and Aggregate Functions

14. Pivoting and Grouping Sets


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

Lab: Pivoting and Grouping Sets

15. Querying data with Stored Procedures


  • Writing Queries with PIVOT and UNPIVOT
  • Passing Parameters to Stored Procedures
  • Creating Simple Stored Procedures
  • Working with Dynamic SQL

Lab: Executing Stored Procedures

16. Programming with T-SQL


  • T-SQL Programming Elements
  • Controlling Program Flow

Lab: Programming with T-SQL

17. Implementing Error Handling


  • Using TRY / CATCH Blocks
  • Working with Error Information

Lab: Implementing Error Handling

18. Implementing Transactions


  • Transactions and the Database Engine
  • Controlling Transactions

Lab: Implementing Transactions

19. Appendix 1: Improving Query Performance


  • Factors in Query Performance
  • Displaying Query Performance Data

Lab: Improving Query Performance

20. Appendix 2: Querying SQL Server


  • Querying System Catalog Views and Functions
  • Executing System Stored Procedures
  • Querying Dynamic Management Objects

Lab: Querying SQL Server Metdata