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
