Realdolmen Education

Details

Advanced Querying Microsoft SQL Server 2012

This course is currently not scheduled on the open calendar, but can be organized on request.

Request Course

Duration

2 day(s)

Audience

This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. The course will very likely be well attended by SQL power users who aren’t necessarily database-focused or plan on taking the exam; namely, report writers, business analysts and client application developers.

Prerequisites

Before attending this course, students must have a basic knowledge of relational databases and a basic knowledge of the Microsoft Windows operating system and its core functionality. Students must have followed, or have the knowlegde - of the training Basic Querying Microsoft SQL Server 2012.

Objectives

This course provides students with the technical skills required to write advanced Transact-SQL queries for Microsoft SQL Server 2012. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. This course helps people prepare for exam 70-461.

Methods

Instructor led training alternated with demos and exercises. Each student disposes of a workstation. All the labs for this course can be performed using the provided virtual machines.

Description

After completing this course, students will be able to:Work on the SQL Azure platform, Work with different SQL Server Datatypes, Execute stored procedures, Use table expressions, Use Pivoting and Grouping Sets, Use window ranking, offset and aggregate functions, Query SQL Server metadata, Improve query performance.

Contents

  • Module 1: Getting Started with SQL Azure
    • Describe the basic features of SQL Azure.
    • Provision a SQL Azure server and configure its security
  • Module 2: Working with SQL Server 2012 Data Types
    • Introducing SQL Server 2012 Data Types
    • Working with Character Data
    • Working with Date and Time Data
  • Module 3: Using Table Expressions
    • Using Derived Tables
    • Using Common Table Expressions
    • Using Views
    • Writing Simple CASE Expressions
  • Module 4: Using Window Ranking, Offset and Aggregate Functions
    • Write queries which use ranking functions
    • Write queries which use offset functions
    • Write queries which use window aggregate functions
  • Module 5: Pivoting and Grouping Sets
    • Write queries which use the PIVOT operator
    • Write queries which use the UNPIVOT operator
    • Filtering with the TOP and OFFSET-FETCH Options
  • Module 6: Querying SQL Server Metadata
    • Querying System Catalog Views and Functions
    • Executing System Stored Procedures
    • Querying Dynamic Management Objects
  • Module 7: Executing Stored Procedures
    • Querying Data with Stored Procedures
    • Passing Parameters to Stored Procedures
    • Creating Simple Stored Procedures Working with Dynamic SQL
    • Working with Dynamic SQL
  • Module 8: Improving Query Performance
    • Viewing Query Execution Plans
    • Viewing Index Usage and Using SET STATISTICS Statements