MOC On-Demand: 20461-Querying Microsoft SQL Server - On-Demand Course

Section 1: Introduction to Microsoft SQL Server 2014
This section discusses the SQL Server platform and major tools. It discusses editions, versions, tools used to query, documentation sources, and the logical structure of databases.
Topics :
The Basic Architecture of SQL Server
SQL Server Editions and Versions
Getting Started with SQL Server Management Studio
Lab 1: Working with SQL Server 2014 Tools
Working with SQL Server Management Studio
Creating and Organizing T-SQL scripts
Using Books Online

Section 2: Introduction to T-SQL Querying
This section discusses Transact SQL as the primary querying language of SQL Server. It covers the basic structure of T-SQL queries, the logical flow of a SELECT statement, and discusses concepts such as predicates and set-based operations.
Topics :
Introducing T-SQL
Understanding Sets
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statements
Lab 1: Introduction to Transact-SQL Querying
Executing Basic SELECT Statements
Executing queries which filter data using predicates
Executing queries which sort data using ORDER BY

Section 3: Writing SELECT Queries
This section discusses the fundamentals of the SELECT statement, focusing on queries against a single table.
Topics :
Writing Simple SELECT Statements
Eliminating Duplicates with DISTINCT
Using Column and Table Aliases
Writing Simple CASE Expressions
Lab 1: Writing Basic SELECT Statements
Writing simple SELECT Statements
Eliminating Duplicates Using Distinct
Using Table and Column Aliases
Using a Simple CASE Expression

Section 4: Querying Multiple Tables
This section explains how to write queries which combine data from multiple sources in SQL Server. The section discusses the use of JOINs in T-SQL queries as a mechanism for retrieving data from multiple tables.
Topics :
Understanding Joins
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins
Lab 1: Querying Multiple Tables
Writing Queries That Use Inner Joins
Writing Queries That Use Multiple-Table Inner Join
Writing Queries That Use Self Joins
Writing Queries That Use Outer Joins
Writing Queries That Use Cross Joins

Section 5: Sorting and Filtering Data
This section explains how to enhance queries to limit the rows they return, and controlling the order in which the rows are displayed. The section also discusses how to resolve missing and unknown results.
Topics :
Sorting Data
Filtering Data with a WHERE Clause
Filtering with the TOP and OFFSET-FETCH Options
Working with Unknown and Missing Values
Lab 1: Sorting and Filtering Data
Writing Queries That Filter Data Using a WHERE Clause
Writing Queries That Filter Data Using an ORDER BY Clause
Writing Queries That Filter Data Using the TOP Option
Writing Queries That Filter Data Using the OFFSET-FETCH Clause

Section 6: Working with SQL Server 2014 Data Types
This section explains the data types SQL Server uses to store data. It discusses the many types of numeric and special-use data types. It also explains conversions between data types, and the importance of type precedence.
Topics :
Introducing SQL Server 2014 Data Types
Working with Character Data
Working with Date and Time Data
Lab 1: Working with SQL Server 2014 Data Types
Writing Queries That Return Date and Time Data
Writing Queries That Use Date and Time Functions
Writing Queries That Return Character Data
Writing Queries That Use Character Functions

Section 7: Using DML to Modify Data
This section describes the use of Transact-SQL Data Manipulation Language to perform inserts, updates, and deletes to your data.
Topics :
Inserting Data
Modifying and Deleting Data
Lab 1: Using DML to Modify Data
Inserting Data
Updating and Deleting Data

Section 8: Using Built-In Functions
This section discusses the use of functions that are built in to SQL Server Denali, and will discuss some common usages including data type conversion, testing for logical results and nullability.
Topics :
Writing Queries with Built-In Functions
Using Conversion Functions
Using Logical Functions
Using Functions to Work with NULL
Lab 1: Using Built-In Functions
Writing queries which use conversion functions
Writing queries which use logical functions
Writing queries which test for nullability

Section 9: Grouping and Aggregating Data
This section discusses methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING. The section is designed to help the student grasp why a SELECT clause has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be listed in the SELECT clause.
Topics :
Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING
Lab 1: Grouping and Aggregating Data
Writing queries which use the GROUP BY clause
Writing queries which use aggregate functions
Writing queries which use distinct aggregate functions
Writing queries which filter groups with the HAVING clause

Section 10: Using Subqueries
This section will introduce the use of subqueries in various parts of a SELECT statement. It will include the use of scalar and multi-result subqueries, and the use of the IN and EXISTS operators.
Topics :
Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Lab 1: Using Subqueries
Writing queries which use self-contained subqueries
Writing queries which use scalar and multi-result subqueries
Writing queries which use correlated subqueries and EXISTS predicate

Section 11: Using Table Expressions
This section discusses T-SQL expressions which return a valid relational table, typically for further use in the query. The section discusses views, derived tables, common table expressions and inline table-valued functions.
Topics :
Using Derived Tables
Using Common Table Expressions
Using Views
Using Inline Table-Valued Functions
Lab 1: Using Table Expressions
Writing Queries Which Use Views
Writing Queries Which Use Derived Tables
Writing Queries Which Use Common Table Expressions
Writing Queries Which Use Inline Table-Valued Functions

Section 12: Using Set Operators
This section discusses using Microsoft SharePoint Server as a platform for BI, and focuses on building BI dashboards and scorecards with PerformancePoint Services.
Topics :
Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Using APPLY
Lab 1: Using Set Operators
Writing queries which use UNION set operators and UNION ALL multi-set operators
Writing queries which use CROSS APPLY and OUTER APPLY operators
Writing queries which use EXCEPT and INTERSECT operators

Section 13: Using Window Ranking, Offset, and Aggregate Functions
This section discusses window functions including aggregate, ranking and offset functions. Much of this functionality is new to SQL Server 2012. It covers the use of T-SQL functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and LAST_VALUE to perform calculations against a set, or window, of rows.
Topics :
Creating Windows with OVER
Exploring Window Functions
Lab 1: Using Window Ranking, Offset and Aggregate Functions
Writing queries which use ranking functions
Writing queries which use offset functions
Writing queries which use window aggregate functions

Section 14: Pivoting and Grouping Sets
This section discusses techniques for pivoting data in T-SQL and introduces the fundamentals of the GROUPING SETS clause. It also covers the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server.
Topics :
Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets
Lab 1: Pivoting and Grouping Sets
Writing queries which use the PIVOT operator
Writing queries which use the UNPIVOT operator
Writing queries which use the GROUPING SETS subclause

Section 15: Executing Stored Procedures
This section discusses the use of existing stored procedures in a T-SQL querying environment. It discusses the use of EXECUTE, how to pass input and output parameters to a procedure, and how to invoke system stored procedures.
Topics :
Querying Data with Stored Procedures
Passing Parameters to Stored Procedures
Creating Simple Stored Procedures
Working with Dynamic SQL
Lab 1: Executing Stored Procedures
Using the EXECUTE statement to invoke stored procedures
Pass parameters to stored procedures
Execute system stored procedures

Section 16: Programming with T-SQL
This section provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.
Topics :
T-SQL Programming Elements
Controlling Program Flow
Lab 1: Programming with T-SQL
Declaring Variables and Delimiting Batches
Using Control-of-Flow Elements
Generating Dynamic SQL
Using Synonyms

Section 17: Implementing Error Handling
This section discusses the use of error handlers in T-SQL code. It will introduce the difference between compile errors and run-time errors, and will cover how errors affect batches. The section will also cover how to control error handling using TRY/CATCH blocks, the use of the ERROR class of functions, and the use of the new THROW statement.
Topics :
Using TRY / CATCH Blocks
Working with Error Information
Lab 1: Implementing Error Handling
Redirecting Errors with TRY / CATCH
Using THROW to Pass an Error Message Back to a Client

Section 18: Implementing Transactions
This section discusses the concepts of transaction management in SQL Server. It will provide a high-level overview of transaction properties, cover the basics of marking transactions with BEGIN, COMMIT and ROLLBACK.
Topics :
Transactions and the Database Engine
Controlling Transactions
Isolation Levels
Lab 1: Implementing Transactions
Controlling transactions with BEGIN, COMMIT, and ROLLBACK
Adding error handling to a CATCH block