Course Deliverables/syllabus

Module 1: Introduction to Microsoft SQL Server 2012

Describe SQL Server components Create and save T-SQL scripts in SQL Server Management Studio Browse the objects in a database Search T-SQL syntax definitions in Books Online

Module 2: Introduction to Transact-SQL Querying

Describe the elements of T-SQL and its role in writing queries Describe the use of sets in SQL Server Describe the use of predicate logic in SQL Server Define the logical order of operations in SELECT statements

Module 3: Writing SELECT Queries

Write Simple SELECT statements Eliminate duplicates using the DISTINCT clause Use column and table aliases Write simple CASE expressions

Module 4: Querying Multiple Tables

Describe how multiple tables may be queried in a SELECT statement using joins Write queries which use inner joins Write queries which use outer joins Write queries which use self-joins and cross joins

Module 5: Sorting and Filtering Data

Filter data with predicates in the WHERE clause Sort data using ORDER BY Filter data in the SELECT clause with TOP Filter data with OFFSET and FETCH Understand and handle NULLs

Module 6: Working with SQL Server Denali Data Types

Describe numeric data types, type precedence and type conversions Write queries using character data types Write queries using date and time data types

Module 7: Using Built-In Functions

Write queries with built-in scalar functions Use conversion functions Use logical functions Use functions that work with NULL

Module 8: Grouping and Aggregating Data

Write queries which summarize data using built-in aggregate functions Use the GROUP BY clause to arrange rows into groups Use the HAVING clause to filter out groups based on a search condition

Module 9: Using Subqueries

Use self-contained subqueries, correlated subqueries, scalar subqueries, and multivalued subqueries Use the IN and EXISTS predicates

Module 10: Using Table Expressions

Write queries against views Use derived tables Use common table expressions Use inline table-valued expressions

Module 11: Use Set Operators

Describe the difference between a set and a multi-set Use the UNION and UNION ALL operators Use CROSS APPLY, OUTER APPLY and APPLY Use EXCEPT and INTERSECT operators

Module 12: Using Window Ranking, Offset and Aggregate Functions

Use window ranking functions ROW_NUMBER, RANK, DENSE_RANK, NTILE Use offset functions LAG, LEAD, FIRST_VALUE, LAST_VALUE Use window aggregate functions Use the basic forms of window partitioning, window ordering and window framing

Module 13: Pivoting and Grouping Sets

Use the basic form of PIVOT and UNPIVOT operators Use the fundamental form of GROUPING SETS clause

Module 14: Querying SQL Server Metadata

Write queries against system catalog views and functions Execute system stored procedures Identify and query system dynamic management objects Describe security permissions necessary to query system objects

Module 15: Executing Stored Procedures

Invoke existing stored procedures with EXECUTE Pass input parameters and receive output parameters Generate dynamic SQL statements

Module 16: Programming with T-SQL

Understand where T-SQL programming objects can be used Declare variables and delimit batches Use control of flow elements such as IF, WHILE, BEGIN, END, RETURN Use synonyms

Module 17: Implementing Error Handling

Describe the behavior of SQL Server when errors occur in T-SQL code Use TRY/CATCH blocks to handle error flow Understand ERROR class functions

Module 18: Implementing Transactions

Describe the benefits of using transactions Control transactions with BEGIN, COMMIT, ROLLBACK Describe error handling behavior in transactions

Module 19: Improving Query Performance

Describe how SQL Server uses indexes to support query execution Enable the display of execution plans in SQL Server Management Studio Enable system IO and time information in query results Describe the differences between accessing data in sets versus via cursors