Querying Microsoft SQL Server 2012

This 5-day instructor led course provides students with the technical skills required to write basic 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. Many of the exercises in this course are SQL Azure enabled.

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.

Objectives:

• Write SELECT Queries
• Query Multiple Tables
• Use Built-In Functions
• Use Subqueries
• Execute Stored Procedures
• Use Set Operators
• Implement Error Handling
• Implementing Transactions
• Use Table Expressions
• Sort and Filter Data
• Use Window Ranking, Offset and Aggregate Functions
• Query SQL Server Metadata
• Program with T-SQL
• Improve Query Performance

Course Outline: 

Module 1: Introduction to Microsoft SQL Server 2012 

• SQL Server Denali Architecture
• Working with SQL Server Denali Tools
• Working with SQL Server Databases

Lab : Working with SQL Server Denali Tools (Azure enabled)

• Working with SQL Server Management Studio
• Exploring with Database Objects
• Organizing and Creating T-SQL Scripts
• Using Books Online

Module 2: Introduction to Transact-SQL Querying

• Introducing Transact-SQL
• Understanding Sets
• Understanding Predicate Logic
• Understanding the Logical Order of Operations in SELECT Statements

Lab : Introduction to Transact-SQL Querying

• Executing Basic SELECT Statements
• Executing queries which filter data using predicates
• Executing queries which sort data using ORDER BY

Module 3: Writing SELECT Queries

• Writing Simple SELECT Statements
• Eliminating Duplicates with DISTINCT
• Using Column and Table Aliases
• writing Simple CASE Expressions

Lab : Writing Basic SELECT statements

• Write simple SELECT statements
• Eliminate Duplicates using DISTINCT
• Use table and column aliases
• Use a simple CASE expression

Module 4: Querying Multiple Tables

• Understanding Joins
• Querying With Inner Joins
• Querying With Outer Joins
• Querying Using Self and Cross Joins

Lab : Querying Multiple Tables

• Write queries using inner joins
• Write queries using multi-table joins
• Write queries using self joins
• Write queries using outer joins

Module 5: Sorting and Filtering Data

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

Lab : Filtering and Sorting Data

• Write queries that filter data using a WHERE clause
• Write queries that sort data with the ORDER BY clause
• Write queries that filter data using the TOP option
• Write queries that filter data using the OFFSET-FETCH clause

Module 6: Working with SQL Server Denali Data Types

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

Lab : Working with SQL Server 2012 Data Types

• Write queries which return date and time data
• Write queries which use date and time functions
• Write queries which return character data
• Write queries which use character functions

Module 7: Using Built-In Functions

• Writing Queries with Built-In Functions
• Using Conversion Functions
• Using Logical Functions
• Using Functions to Test for Nullability

Lab : Using Built-In Functions

• Write queries which use conversion functions
• Write queries which use logical functions
• Write queries which test for nullability

Module 8: Grouping and Aggregating Data 

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

Lab : Grouping and Aggregating Data

• Write queries which use the GROUP BY clause
• Write queries which use aggregate functions
• Write queries which use distinct aggregate functions
• Write queries which filter groups with the HAVING clause

Module 9: Using Subqueries

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

Lab : Using Subqueries

• Write queries which use self-contained subqueries
• Write queries which use correlated subqueries
• Write queries which use scalar and multi-result subqueries
• Write queries which use the IN and EXISTS predicates

Module 10: Using Table Expressions

• Views
• Table Expressions

Lab : Using Table Expressions

• Write queries which use views
• Write queries which use derived tables
• Write queries which use common table expressions
• Write queries which use inline table-valued expressions

Module 11: Use Set Operators

• Write queries which use UNION set operators and UNION ALL multi-set operators
• Write queries which use CROSS APPLY and OUTER APPLY operators
• Write queries which use APPLY with derived tables and functions
• Write queries which use EXCEPT and INTERSECT operators

Module 12: 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 13: Pivoting and Grouping Sets

• Write queries which use the PIVOT operator
• Write queries which use the GROUPING SETS subclause
• Write queries which use GROUP BY ROLLUP
• Write queries which use GROUP BY CUBE

Module 14: Querying SQL Server Metadata

• Querying system catalog views
• Querying system functions
• Querying system dynamic management views

Module 15: Executing Stored Procedures

• Use the EXECUTE statement to invoke stored procedures
• Pass parameters to stored procedures
• Return results from a stored procedure using the OUTPUT clause
• Execute system stored procedures

Module 16: Programming with T-SQL

• Declaring variables and delimiting batches
• Using control of flow elements
• Generating dynamic SQL
• Using synonyms

Module 17: Implementing Error Handling

• Redirecting errors with TRY/CATCH
• Creating error handling routines in a CATCH block with ERROR functions
• Using THROW to pass an error message back to a client

Module 18: Implementing Transactions

• Controlling transactions with BEGIN and COMMIT
• Using XACT_ABORT
• Adding transaction handling logic to a CATCH block

Module 19: Improving Query Performance

• Viewing query execution plans
• Using SET STATISTICS statements
• Viewing index usage
• Comparing cursors to set-based queries