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.

Type

Certified Training Course

Level

Professional

Cost

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

SATV Redeemable 

Yes

Delivery Method

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

Duration

5 days

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

Prerequisites

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

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Lessons

  • Inserting Data
  • Modifying and Deleting Data

Lab: Using DML to Modify Data

8. Using Built-In Functions

Lessons

  • 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

Lessons

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

Lab: Grouping and Aggregating Data

10. Using Subqueries

Lessons

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

Lab: Using Subqueries

11. Using Table Expressions

Lessons

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

Lab: Using Table Expressions

12. Using Set Operators

Lessons

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

Lab: Using Set Operators

13. Using Window Ranking, Offset, and Aggregate Functions

Lessons

  • Creating Windows with OVER
  • Exploring Window Functions

Lab: Using Window Ranking, Offset and Aggregate Functions

14. Pivoting and Grouping Sets

Lessons

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

Lab: Pivoting and Grouping Sets

15. Querying data with Stored Procedures

Lessons

  • 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

Lessons

  • T-SQL Programming Elements
  • Controlling Program Flow

Lab: Programming with T-SQL

17. Implementing Error Handling

Lessons

  • Using TRY / CATCH Blocks
  • Working with Error Information

Lab: Implementing Error Handling

18. Implementing Transactions

Lessons

  • Transactions and the Database Engine
  • Controlling Transactions

Lab: Implementing Transactions

19. Appendix 1: Improving Query Performance

Lessons

  • Factors in Query Performance
  • Displaying Query Performance Data

Lab: Improving Query Performance

20. Appendix 2: Querying SQL Server

Lessons

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

Lab: Querying SQL Server Metdata