MOC 20461 - Querying Microsoft SQL Server 2014

Module 1: Introduction to Microsoft SQL Server 2014The Basic Architecture of SQL ServerSQL Server Editions and VersionsGetting Started with SQL Server Management StudioLab : Working with SQL Server 2014 ToolsWorking with SQL Server Management StudioCreating and Organizing T-SQL scriptsUsing Books OnlineModule 2: Introduction to T-SQL QueryingIntroducing T-SQLUnderstanding SetsUnderstanding Predicate LogicUnderstanding the Logical Order of Operations in SELECT statementsLab : Introduction to Transact-SQL QueryingExecuting Basic SELECT StatementsExecuting queries which filter data using predicatesExecuting queries which sort data using ORDER BYModule 3: Writing SELECT QueriesWriting Simple SELECT StatementsEliminating Duplicates with DISTINCTUsing Column and Table AliasesWriting Simple CASE ExpressionsLab : Writing Basic SELECT StatementsWrite simple SELECT StatementsEliminate Duplicates Using DistinctUse Table and Column AliasesUse a Simple CASE ExpressionModule 4: Querying Multiple TablesUnderstanding JoinsQuerying with Inner JoinsQuerying with Outer JoinsQuerying with Cross Joins and Self JoinsLab : Querying Multiple TablesWriting Queries That Use Inner JoinsWriting Queries That Use Multiple-Table Inner JoinWriting Queries That Use Self JoinsWriting Queries That Use Outer JoinsWriting Queries That Use Cross JoinsModule 5: Sorting and Filtering DataSorting DataFiltering Data with a WHERE ClauseFiltering with the TOP and OFFSET-FETCH OptionsWorking with Unknown and Missing ValuesLab : Sorting and Filtering DataWriting Queries That Filter Data Using a WHERE ClauseWriting Queries That Filter Data Using an ORDER BY ClauseWriting Queries That Filter Data Using the TOP OptionWriting Queries That Filter Data Using the OFFSET-FETCH ClauseModule 6: Working with SQL Server 2014 Data TypesIntroducing SQL Server 2014 Data TypesWorking with Character DataWorking with Date and Time DataLab : Working with SQL Server 2014 Data TypesWriting Queries That Return Date and Time DataWriting Queries That Use Date and Time FunctionsWriting Queries That Return Character DataWriting Queries That Use Character FunctionsModule 7: Using DML to Modify DataInserting DataModifying and Deleting DataLab : Using DML to Modify DataInserting DataUpdating and Deleting DataModule 8: Using Built-In FunctionsWriting Queries with Built-In FunctionsUsing Conversion FunctionsUsing Logical FunctionsUsing Functions to Work with NULLLab : Using Built-In FunctionsWrite queries which use conversion functionsWrite queries which use logical functionsWrite queries which test for nullabilityModule 9: Grouping and Aggregating DataUsing Aggregate FunctionsUsing the GROUP BY ClauseFiltering Groups with HAVINGLab : Grouping and Aggregating DataWrite queries which use the GROUP BY clauseWrite queries which use aggregate functionsWrite queries which use distinct aggregate functionsWrite queries which filter groups with the HAVING clauseModule 10: Using SubqueriesWriting Self-Contained SubqueriesWriting Correlated SubqueriesUsing the EXISTS Predicate with SubqueriesLab : Using SubqueriesWrite queries which use self-contained subqueriesWrite queries which use scalar and multi-result subqueriesWrite queries which use correlated subqueries and EXISTS predicateModule 11: Using Table ExpressionsUsing Derived TablesUsing Common Table ExpressionsUsing ViewsUsing Inline Table-Valued FunctionsLab : Using Table ExpressionsWrite Queries Which Use ViewsWrite Queries Which Use Derived TablesWrite Queries Which Use Common Table ExpressionsWrite Queries Which Use Inline Table-Valued FunctionsModule 12: Using Set OperatorsWriting Queries with the UNION OperatorUsing EXCEPT and INTERSECTUsing APPLYLab : Using Set OperatorsWrite queries which use UNION set operators and UNION ALL multi-set operatorsWrite queries which use CROSS APPLY and OUTER APPLY operatorsWrite queries which use EXCEPT and INTERSECT operatorsModule 13: Using Window Ranking, Offset, and Aggregate FunctionsCreating Windows with OVERExploring Window FunctionsLab : Using Window Ranking, Offset and Aggregate FunctionsWrite queries which use ranking functionsWrite queries which use offset functionsWrite queries which use window aggregate functionsModule 14: Pivoting and Grouping SetsWriting Queries with PIVOT and UNPIVOTWorking with Grouping SetsLab : Pivoting and Grouping SetsWrite queries which use the PIVOT operatorWrite queries which use the UNPIVOT operatorWrite queries which use the GROUPING SETS subclauseModule 15: Executing Stored ProceduresQuerying Data with Stored ProceduresPassing Parameters to Stored ProceduresCreating Simple Stored ProceduresWorking with Dynamic SQLLab : Executing Stored ProceduresUse the EXECUTE statement to invoke stored proceduresPass parameters to stored proceduresExecute system stored proceduresModule 16: Programming with T-SQLT-SQL Programming ElementsControlling Program FlowLab : Programming with T-SQLDeclaring Variables and Delimiting BatchesUsing Control-of-Flow ElementsGenerating Dynamic SQLUsing SynonymsModule 17: Implementing Error HandlingUsing TRY / CATCH BlocksWorking with Error InformationLab : Implementing Error HandlingRedirecting Errors with TRY / CATCHUsing THROW to Pass an Error Message Back to a ClientModule 18: Implementing TransactionsTransactions and the Database EngineControlling TransactionsIsolation LevelsLab : Implementing TransactionsControlling transactions with BEGIN, COMMIT, and ROLLBACKAdding error handling to a CATCH block