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