Transcription
Querying Microsoft SQL Server Duration: 5 DaysCourse Code: M20461Overview:This 5-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQLServer 2014. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Developmentand Business Intelligence. This course helps people prepare for exam 70-461.Target Audience:This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. The course will very likelybe well attended by SQL power users who aren’t necessarily database-focused or plan on taking the exam; namely, report writers, businessanalysts and client application developers.Objectives:Write SELECT queriesUse table expressionsQuery multiple tablesUse set operatorsSort and filter dataUse window ranking, offset and aggregate functionsDescribe the use of data types in SQL ServerImplement pivoting and grouping setsModify data using Transact-SQLExecute stored proceduresUse built-in functionsProgram with T-SQLGroup and aggregate dataImplement error handlingUse subqueriesImplement transactionsPrerequisites:Working knowledge of relational databases.Basic knowledge of the Microsoft Windows operating system andits core functionality.M20461www.clclearningafrica.com
Content:Module 1: Introduction to Microsoft SQL Server2014linelineThe Basic Architecture of SQL ServerSQL Server Editions and VersionsGetting Started with SQL ServerlineManagement StudioThe Basic Architecture of SQL ServerWorking with SQL Server ManagementSQL Server Editions and VersionsStudioGetting Started with SQL ServerCreating and Organizing T-SQL scriptsManagement StudioUsing Books OnlineWorking with SQL Server ManagementIntroducing T-SQLStudioUnderstanding SetsCreating and Organizing T-SQL scriptsUnderstanding Predicate LogicUsing Books OnlineUnderstanding the Logical Order ofIntroducing T-SQLOperations in SELECT statementsUnderstanding SetsExecuting Basic SELECT StatementsUnderstanding Predicate LogicExecuting queries which filter data usingUnderstanding the Logical Order ofpredicatesOperations in SELECT statementsExecuting queries which sort data usingExecuting Basic SELECT StatementsORDER BYExecuting queries which filter data usingWriting Simple SELECT StatementspredicatesEliminating Duplicates with DISTINCTExecuting queries which sort data usingUsing Column and Table AliasesORDER BYWriting Simple CASE ExpressionsWriting Simple SELECT StatementsWrite simple SELECT StatementsEliminating Duplicates with DISTINCTEliminate Duplicates Using DistinctUsing Column and Table AliasesUse Table and Column AliasesWriting Simple CASE ExpressionsUse a Simple CASE ExpressionWrite simple SELECT StatementsUnderstanding JoinsEliminate Duplicates Using DistinctQuerying with Inner JoinsUse Table and Column AliasesQuerying with Outer JoinsUse a Simple CASE ExpressionQuerying with Cross Joins and Self JoinsUnderstanding JoinsWriting Queries That Use Inner JoinsQuerying with Inner JoinsWriting Queries That Use Multiple-TableQuerying with Outer JoinsInner JoinQuerying with Cross Joins and Self JoinsWriting Queries That Use Self JoinsWriting Queries That Use Inner JoinsWriting Queries That Use Outer JoinsWriting Queries That Use Multiple-TableWriting Queries That Use Cross JoinsInner JoinSorting DataWriting Queries That Use Self JoinsFiltering Data with a WHERE ClauseWriting Queries That Use Outer JoinsFiltering with the TOP andWriting Queries That Use Cross JoinsOFFSET-FETCH OptionsSorting DataWorking with Unknown and MissingFiltering Data with a WHERE ClauseValuesFiltering with the TOP and OFFSET-FETCHWriting Queries That Filter Data Using aOptionsWHERE ClauseWorking with Unknown and Missing ValuesWriting Queries That Filter Data Using anWriting Queries That Filter Data Using aORDER BY ClauseWHERE ClauseWriting Queries That Filter Data UsingWriting Queries That Filter Data Using anthe TOP OptionORDER BY ClauseWriting Queries That Filter Data UsingWriting Queries That Filter Data Using thethe OFFSET-FETCH ClauseTOP OptionIntroducing SQL Server 2014 Data TypesWriting Queries That Filter Data Using theWorking with Character DataOFFSET-FETCH ClauseWorking with Date and Time DataIntroducing SQL Server 2014 Data TypesWriting Queries That Return Date andWorking with Character DataTime DataWorking with Date and Time DataWriting Queries That Use Date and TimeWriting Queries That Return Date and TimeFunctionsDataWriting Queries That Return CharacterWriting Queries That Use Date and TimeDataFunctionsWriting Queries That Use CharacterWriting Queries That Return Character DataFunctionWriting Queries That Use CharacterInserting DataM20461www.clclearningafrica.comlineThe Basic Architecture of SQL ServerSQL Server Editions and VersionsGetting Started with SQL ServerManagement StudioWorking with SQL Server ManagementStudioCreating and Organizing T-SQL scriptsUsing Books OnlineIntroducing T-SQLUnderstanding SetsUnderstanding Predicate LogicUnderstanding the Logical Order ofOperations in SELECT statementsExecuting Basic SELECT StatementsExecuting queries which filter data usingpredicatesExecuting queries which sort data usingORDER BYWriting Simple SELECT StatementsEliminating Duplicates with DISTINCTUsing Column and Table AliasesWriting Simple CASE ExpressionsWrite simple SELECT StatementsEliminate Duplicates Using DistinctUse Table and Column AliasesUse a Simple CASE ExpressionUnderstanding JoinsQuerying with Inner JoinsQuerying with Outer JoinsQuerying with Cross Joins and Self JoinsWriting Queries That Use Inner JoinsWriting Queries That Use Multiple-TableInner JoinWriting Queries That Use Self JoinsWriting Queries That Use Outer JoinsWriting Queries That Use Cross JoinsSorting DataFiltering Data with a WHERE ClauseFiltering with the TOP andOFFSET-FETCH OptionsWorking with Unknown and MissingValuesWriting Queries That Filter Data Using aWHERE ClauseWriting Queries That Filter Data Using anORDER BY ClauseWriting Queries That Filter Data Using theTOP OptionWriting Queries That Filter Data Using theOFFSET-FETCH ClauseIntroducing SQL Server 2014 Data TypesWorking with Character DataWorking with Date and Time DataWriting Queries That Return Date andTime DataWriting Queries That Use Date and TimeFunctionsWriting Queries That Return CharacterDataWriting Queries That Use CharacterFunctionInserting Data
FunctionInserting DataModifying and Deleting DataInserting DataUpdating and Deleting DataWriting Queries with Built-In FunctionsUsing Conversion FunctionsUsing Logical FunctionsUsing Functions to Work with NULLWrite queries which use conversionfunctionsWrite queries which use logical functionsWrite queries which test for nullabilityUsing Aggregate FunctionsUsing the GROUP BY ClauseFiltering Groups with HAVINGWrite queries which use the GROUP BYclauseWrite queries which use aggregate functionsWrite queries which use distinct aggregatefunctionsWrite queries which filter groups with theHAVING clauseWriting Self-Contained SubqueriesWriting Correlated SubqueriesUsing the EXISTS Predicate withSubqueriesWrite queries which use self-containedsubqueriesWrite queries which use scalar andmulti-result subqueriesWrite queries which use correlatedsubqueries and EXISTS predicateUsing Derived TablesUsing Common Table ExpressionsUsing ViewsUsing Inline Table-Valued FunctionsWrite Queries Which Use ViewsWrite Queries Which Use Derived TablesWrite Queries Which Use Common TableExpressionsWrite Queries Which Use InlineTable-Valued FunctionsWriting Queries with the UNION OperatorUsing EXCEPT and INTERSECTUsing APPLYWrite queries which use UNION setoperators and UNION ALL multi-setoperatorsWrite queries which use CROSS APPLYand OUTER APPLY operatorsWrite queries which use EXCEPT andINTERSECT operatorsCreating Windows with OVERExploring Window FunctionsWrite queries which use ranking functionsWrite queries which use offset functionsWrite queries which use window aggregatefunctionsWriting Queries with PIVOT and UNPIVOTWorking with Grouping SetsWrite queries which use the PIVOT operatorWrite queries which use the UNPIVOToperatorWrite queries which use the GROUPINGSETS subclauseM20461Modifying and Deleting DataInserting DataUpdating and Deleting DataWriting Queries with Built-In FunctionsUsing Conversion FunctionsUsing Logical FunctionsUsing Functions to Work with NULLWrite queries which use conversionfunctionsWrite queries which use logical functionsWrite queries which test for nullabilityUsing Aggregate FunctionsUsing the GROUP BY ClauseFiltering Groups with HAVINGWrite queries which use the GROUP BYclauseWrite queries which use aggregatefunctionsWrite queries which use distinctaggregate functionsWrite queries which filter groups with theHAVING clauseWriting Self-Contained SubqueriesWriting Correlated SubqueriesUsing the EXISTS Predicate withSubqueriesWrite queries which use self-containedsubqueriesWrite queries which use scalar andmulti-result subqueriesWrite queries which use correlatedsubqueries and EXISTS predicateUsing Derived TablesUsing Common Table ExpressionsUsing ViewsUsing Inline Table-Valued FunctionsWrite Queries Which Use ViewsWrite Queries Which Use Derived TablesWrite Queries Which Use Common TableExpressionsWrite Queries Which Use InlineTable-Valued FunctionsWriting Queries with the UNION OperatorUsing EXCEPT and INTERSECTUsing APPLYWrite queries which use UNION setoperators and UNION ALL multi-setoperatorsWrite queries which use CROSS APPLYand OUTER APPLY operatorsWrite queries which use EXCEPT andINTERSECT operatorsCreating Windows with OVERExploring Window FunctionsWrite queries which use ranking functionsWrite queries which use offset functionsWrite queries which use windowaggregate functionsWriting Queries with PIVOT andUNPIVOTWorking with Grouping SetsWrite queries which use the PIVOToperatorWrite queries which use the UNPIVOToperatorWrite queries which use the GROUPINGwww.clclearningafrica.comModifying and Deleting DataInserting DataUpdating and Deleting DataWriting Queries with Built-In FunctionsUsing Conversion FunctionsUsing Logical FunctionsUsing Functions to Work with NULLWrite queries which use conversionfunctionsWrite queries which use logical functionsWrite queries which test for nullabilityUsing Aggregate FunctionsUsing the GROUP BY ClauseFiltering Groups with HAVINGWrite queries which use the GROUP BYclauseWrite queries which use aggregatefunctionsWrite queries which use distinct aggregatefunctionsWrite queries which filter groups with theHAVING clauseWriting Self-Contained SubqueriesWriting Correlated SubqueriesUsing the EXISTS Predicate withSubqueriesWrite queries which use self-containedsubqueriesWrite queries which use scalar andmulti-result subqueriesWrite queries which use correlatedsubqueries and EXISTS predicateUsing Derived TablesUsing Common Table ExpressionsUsing ViewsUsing Inline Table-Valued FunctionsWrite Queries Which Use ViewsWrite Queries Which Use Derived TablesWrite Queries Which Use Common TableExpressionsWrite Queries Which Use InlineTable-Valued FunctionsWriting Queries with the UNION OperatorUsing EXCEPT and INTERSECTUsing APPLYWrite queries which use UNION setoperators and UNION ALL multi-setoperatorsWrite queries which use CROSS APPLYand OUTER APPLY operatorsWrite queries which use EXCEPT andINTERSECT operatorsCreating Windows with OVERExploring Window FunctionsWrite queries which use ranking functionsWrite queries which use offset functionsWrite queries which use window aggregatefunctionsWriting Queries with PIVOT and UNPIVOTWorking with Grouping SetsWrite queries which use the PIVOToperatorWrite queries which use the UNPIVOToperatorWrite queries which use the GROUPINGSETS subclause
Querying Data with Stored ProceduresPassing Parameters to Stored ProceduresCreating Simple Stored ProceduresWorking with Dynamic SQLUse the EXECUTE statement to invokestored proceduresPass parameters to stored proceduresExecute system stored proceduresT-SQL Programming ElementsControlling Program FlowDeclaring Variables and Delimiting BatchesUsing Control-of-Flow ElementsGenerating Dynamic SQLUsing SynonymsUsing TRY / CATCH BlocksWorking with Error InformationRedirecting Errors with TRY / CATCHUsing THROW to Pass an Error MessageBack to a ClientTransactions and the Database EngineControlling TransactionsIsolation LevelsControlling transactions with BEGIN,COMMIT, and ROLLBACKAdding error handling to a CATCH blocklineThe Basic Architecture of SQL ServerSQL Server Editions and VersionsGetting Started with SQL ServerManagement StudioWorking with SQL Server ManagementStudioCreating and Organizing T-SQL scriptsUsing Books OnlineIntroducing T-SQLUnderstanding SetsUnderstanding Predicate LogicUnderstanding the Logical Order ofOperations in SELECT statementsExecuting Basic SELECT StatementsExecuting queries which filter data usingpredicatesExecuting queries which sort data usingORDER BYWriting Simple SELECT StatementsEliminating Duplicates with DISTINCTUsing Column and Table AliasesWriting Simple CASE ExpressionsWrite simple SELECT StatementsEliminate Duplicates Using DistinctUse Table and Column AliasesUse a Simple CASE ExpressionUnderstanding JoinsQuerying with Inner JoinsQuerying with Outer JoinsQuerying with Cross Joins and Self JoinsWriting Queries That Use Inner JoinsWriting Queries That Use Multiple-TableInner JoinWriting Queries That Use Self JoinsWriting Queries That Use Outer JoinsWriting Queries That Use Cross JoinsSorting DataFiltering Data with a WHERE ClauseM20461SETS subclauseQuerying Data with Stored ProceduresPassing Parameters to StoredProceduresCreating Simple Stored ProceduresWorking with Dynamic SQLUse the EXECUTE statement to invokestored proceduresPass parameters to stored proceduresExecute system stored proceduresT-SQL Programming ElementsControlling Program FlowDeclaring Variables and DelimitingBatchesUsing Control-of-Flow ElementsGenerating Dynamic SQLUsing SynonymsUsing TRY / CATCH BlocksWorking with Error InformationRedirecting Errors with TRY / CATCHUsing THROW to Pass an Error MessageBack to a ClientTransactions and the Database EngineControlling TransactionsIsolation LevelsControlling transactions with BEGIN,COMMIT, and ROLLBACKAdding error handling to a CATCH blockLab : Working with SQL Server 2014 DataTypeslinelineThe Basic Architecture of SQL ServerSQL Server Editions and VersionsGetting Started with SQL ServerManagement StudioWorking with SQL Server ManagementStudioCreating and Organizing T-SQL scriptsUsing Books OnlineIntroducing T-SQLUnderstanding SetsUnderstanding Predicate LogicUnderstanding the Logical Order ofOperations in SELECT statementsExecuting Basic SELECT StatementsExecuting queries which filter data usingpredicatesExecuting queries which sort data usingORDER BYWriting Simple SELECT StatementsEliminating Duplicates with DISTINCTUsing Column and Table AliasesWriting Simple CASE ExpressionsWrite simple SELECT StatementsEliminate Duplicates Using DistinctUse Table and Column AliasesUse a Simple CASE ExpressionUnderstanding JoinsQuerying with Inner JoinsQuerying with Outer JoinsQuerying with Cross Joins and Self JoinsWriting Queries That Use Inner Joinswww.clclearningafrica.comQuerying Data with Stored ProceduresPassing Parameters to Stored ProceduresCreating Simple Stored ProceduresWorking with Dynamic SQLUse the EXECUTE statement to invokestored proceduresPass parameters to stored proceduresExecute system stored proceduresT-SQL Programming ElementsControlling Program FlowDeclaring Variables and DelimitingBatchesUsing Control-of-Flow ElementsGenerating Dynamic SQLUsing SynonymsUsing TRY / CATCH BlocksWorking with Error InformationRedirecting Errors with TRY / CATCHUsing THROW to Pass an Error MessageBack to a ClientTransactions and the Database EngineControlling TransactionsIsolation LevelsControlling transactions with BEGIN,COMMIT, and ROLLBACKAdding error handling to a CATCH blocklineThe Basic Architecture of SQL ServerSQL Server Editions and VersionsGetting Started with SQL ServerManagement StudioWorking with SQL Server ManagementStudioCreating and Organizing T-SQL scriptsUsing Books OnlineIntroducing T-SQLUnderstanding SetsUnderstanding Predicate LogicUnderstanding the Logical Order ofOperations in SELECT statementsExecuting Basic SELECT StatementsExecuting queries which filter data usingpredicatesExecuting queries which sort data usingORDER BYWriting Simple SELECT StatementsEliminating Duplicates with DISTINCTUsing Column and Table AliasesWriting Simple CASE ExpressionsWrite simple SELECT StatementsEliminate Duplicates Using DistinctUse Table and Column AliasesUse a Simple CASE ExpressionUnderstanding JoinsQuerying with Inner JoinsQuerying with Outer JoinsQuerying with Cross Joins and Self JoinsWriting Queries That Use Inner JoinsWriting Queries That Use Multiple-TableInner JoinWriting Queries That Use Self JoinsWriting Queries That Use Outer JoinsWriting Queries That Use Cross JoinsSorting Data
Filtering with the TOP and OFFSET-FETCHOptionsWorking with Unknown and Missing ValuesWriting Queries That Filter Data Using aWHERE ClauseWriting Queries That Filter Data Using anORDER BY ClauseWriting Queries That Filter Data Using theTOP OptionWriting Queries That Filter Data Using theOFFSET-FETCH ClauseIntroducing SQL Server 2014 Data TypesWorking with Character DataWorking with Date and Time DataWriting Queries That Return Date and TimeDataWriting Queries That Use Date and TimeFunctionsWriting Queries That Return Character DataWriting Queries That Use CharacterFunctionInserting DataModifying and Deleting DataInserting DataUpdating and Deleting DataWriting Queries with Built-In FunctionsUsing Conversion FunctionsUsing Logical FunctionsUsing Functions to Work with NULLWrite queries which use conversionfunctionsWrite queries which use logical functionsWrite queries which test for nullabilityUsing Aggregate FunctionsUsing the GROUP BY ClauseFiltering Groups with HAVINGWrite queries which use the GROUP BYclauseWrite queries which use aggregate functionsWrite queries which use distinct aggregatefunctionsWrite queries which filter groups with theHAVING clauseWriting Self-Contained SubqueriesWriting Correlated SubqueriesUsing the EXISTS Predicate withSubqueriesWrite queries which use self-containedsubqueriesWrite queries which use scalar andmulti-result subqueriesWrite queries which use correlatedsubqueries and EXISTS predicateUsing Derived TablesUsing Common Table ExpressionsUsing ViewsUsing Inline Table-Valued FunctionsWrite Queries Which Use ViewsWrite Queries Which Use Derived TablesWrite Queries Which Use Common TableExpressionsWrite Queries Which Use InlineTable-Valued FunctionsWriting Queries with the UNION OperatorUsing EXCEPT and INTERSECTUsing APPLYM20461Writing Queries That Use Multiple-TableInner JoinWriting Queries That Use Self JoinsWriting Queries That Use Outer JoinsWriting Queries That Use Cross JoinsSorting DataFiltering Data with a WHERE ClauseFiltering with the TOP andOFFSET-FETCH OptionsWorking with Unknown and MissingValuesWriting Queries That Filter Data Using aWHERE ClauseWriting Queries That Filter Data Using anORDER BY ClauseWriting Queries That Filter Data Usingthe TOP OptionWriting Queries That Filter Data Usingthe OFFSET-FETCH ClauseIntroducing SQL Server 2014 Data TypesWorking with Character DataWorking with Date and Time DataWriting Queries That Return Date andTime DataWriting Queries That Use Date and TimeFunctionsWriting Queries That Return CharacterDataWriting Queries That Use CharacterFunctionInserting DataModifying and Deleting DataInserting DataUpdating and Deleting DataWriting Queries with Built-In FunctionsUsing Conversion FunctionsUsing Logical FunctionsUsing Functions to Work with NULLWrite queries which use conversionfunctionsWrite queries which use logical functionsWrite queries which test for nullabilityUsing Aggregate FunctionsUsing the GROUP BY ClauseFiltering Groups with HAVINGWrite queries which use the GROUP BYclauseWrite queries which use aggregatefunctionsWrite queries which use distinctaggregate functionsWrite queries which filter groups with theHAVING clauseWriting Self-Contained SubqueriesWriting Correlated SubqueriesUsing the EXISTS Predicate withSubqueriesWrite queries which use self-containedsubqueriesWrite queries which use scalar andmulti-result subqueriesWrite queries which use correlatedsubqueries and EXISTS predicateUsing Derived TablesUsing Common Table ExpressionsUsing Viewswww.clclearningafrica.comFiltering Data with a WHERE ClauseFiltering with the TOP andOFFSET-FETCH OptionsWorking with Unknown and MissingValuesWriting Queries That Filter Data Using aWHERE ClauseWriting Queries That Filter Data Using anORDER BY ClauseWriting Queries That Filter Data Using theTOP OptionWriting Queries That Filter Data Using theOFFSET-FETCH ClauseIntroducing SQL Server 2014 Data TypesWorking with Character DataWorking with Date and Time DataWriting Queries That Return Date andTime DataWriting Queries That Use Date and TimeFunctionsWriting Queries That Return CharacterDataWriting Queries That Use CharacterFunctionInserting DataModifying and Deleting DataInserting DataUpdating and Deleting DataWriting Queries with Built-In FunctionsUsing Conversion FunctionsUsing Logical FunctionsUsing Functions to Work with NULLWrite queries which use conversionfunctionsWrite queries which use logical functionsWrite queries which test for nullabilityUsing Aggregate FunctionsUsing the GROUP BY ClauseFiltering Groups with HAVINGWrite queries which use the GROUP BYclauseWrite queries which use aggregatefunctionsWrite queries which use distinct aggregatefunctionsWrite queries which filter groups with theHAVING clauseWriting Self-Contained SubqueriesWriting Correlated SubqueriesUsing the EXISTS Predicate withSubqueriesWrite queries which use self-containedsubqueriesWrite queries which use scalar andmulti-result subqueriesWrite queries which use correlatedsubqueries and EXISTS predicateUsing Derived TablesUsing Common Table ExpressionsUsing ViewsUsing Inline Table-Valued FunctionsWrite Queries Which Use ViewsWrite Queries Which Use Derived TablesWrite Queries Which Use Common TableExpressionsWrite Queries Which Use Inline
Write queries which use UNION setoperators and UNION ALL multi-setoperatorsWrite queries which use CROSS APPLYand OUTER APPLY operatorsWrite queries which use EXCEPT andINTERSECT operatorsCreating Windows with OVERExploring Window FunctionsWrite queries which use ranking functionsWrite queries which use offset functionsWrite queries which use window aggregatefunctionsWriting Queries with PIVOT and UNPIVOTWorking with Grouping SetsWrite queries which use the PIVOT operatorWrite queries which use the UNPIVOToperatorWrite queries which use the GROUPINGSETS subclauseQuerying Data with Stored ProceduresPassing Parameters to Stored ProceduresCreating Simple Stored ProceduresWorking with Dynamic SQLUse the EXECUTE statement to invokestored proceduresPass parameters to stored proceduresExecute system stored proceduresT-SQL Programming ElementsControlling Program FlowDeclaring Variables and Delimiting BatchesUsing Control-of-Flow ElementsGenerating Dynamic SQLUsing SynonymsUsing TRY / CATCH BlocksWorking with Error InformationRedirecting Errors with TRY / CATCHUsing THROW to Pass an Error MessageBack to a ClientTransactions and the Database EngineControlling TransactionsIsolation LevelsControlling transactions with BEGIN,COMMIT, and ROLLBACKAdding error handling to a CATCH blocklineThe Basic Architecture of SQL ServerSQL Server Editions and VersionsGetting Started with SQL ServerManagement StudioWorking with SQL Server ManagementStudioCreating and Organizing T-SQL scriptsUsing Books OnlineIntroducing T-SQLUnderstanding SetsUnderstanding Predicate LogicUnderstanding the Logical Order ofOperations in SELECT statementsExecuting Basic SELECT StatementsExecuting queries which filter data usingpredicatesExecuting queries which sort data usingORDER BYM20461Using Inline Table-Valued FunctionsWrite Queries Which Use ViewsWrite Queries Which Use Derived TablesWrite Queries Which Use Common TableExpressionsWrite Queries Which Use InlineTable-Valued FunctionsWriting Queries with the UNION OperatorUsing EXCEPT and INTERSECTUsing APPLYWrite queries which use UNION setoperators and UNION ALL multi-setoperatorsWrite queries which use CROSS APPLYand OUTER APPLY operatorsWrite queries which use EXCEPT andINTERSECT operatorsCreating Windows with OVERExploring Window FunctionsWrite queries which use ranking functionsWrite queries which use offset functionsWrite queries which use windowaggregate functionsWriting Queries with PIVOT andUNPIVOTWorking with Grouping SetsWrite queries which use the PIVOToperatorWrite queries which use the UNPIVOToperatorWrite queries which use the GROUPINGSETS subclauseQuerying Data with Stored ProceduresPassing Parameters to StoredProceduresCreating Simple Stored ProceduresWorking with Dynamic SQLUse the EXECUTE statement to invokestored proceduresPass parameters to stored proceduresExecute system stored proceduresT-SQL Programming ElementsControlling Program FlowDeclaring Variables and DelimitingBatchesUsing Control-of-Flow ElementsGenerating Dynamic SQLUsing SynonymsUsing TRY / CATCH BlocksWorking with Error InformationRedirecting Errors with TRY / CATCHUsing THROW to Pass an Error MessageBack to a ClientTransactions and the Database EngineControlling TransactionsIsolation LevelsControlling transactions with BEGIN,COMMIT, and ROLLBACKAdding error handling to a CATCH blocklineThe Basic Architecture of SQL ServerSQL Server Editions and VersionsGetting Started with SQL ServerManagement Studiowww.clclearningafrica.comTable-Valued FunctionsWriting Queries with the UNION OperatorUsing EXCEPT and INTERSECTUsing APPLYWrite queries which use UNION setoperators and UNION ALL multi-setoperatorsWrite queries which use CROSS APPLYand OUTER APPLY operatorsWrite queries which use EXCEPT andINTERSECT operatorsCreating Windows with OVERExploring Window FunctionsWrite queries which use ranking functionsWrite queries which use offset functionsWrite queries which use window aggregatefunctionsWriting Queries with PIVOT and UNPIVOTWorking with Grouping SetsWrite queries which use the PIVOToperatorWrite queries which use the UNPIVOToperatorWrite queries which use the GROUPINGSETS subclauseQuerying Data with Stored ProceduresPassing Parameters to Stored ProceduresCreating Simple Stored ProceduresWorking with Dynamic SQLUse the EXECUTE statement to invokestored proceduresPass parameters to stored proceduresExecute system stored proceduresT-SQL Programming ElementsControlling Program FlowDeclaring Variables and DelimitingBatchesUsing Control-of-Flow ElementsGenerating Dynamic SQLUsing SynonymsUsing TRY / CATCH BlocksWorking with Error InformationRedirecting Errors with TRY / CATCHUsing THROW to Pass an Error MessageBack to a ClientTransactions and the Database EngineControlling TransactionsIsolation LevelsControlling transactions with BEGIN,COMMIT, and ROLLBACKAdding error handling to a CATCH blockLab : Using Set OperatorslinelineThe Basic Architecture of SQL ServerSQL Server Editions and VersionsGetting Started with SQL ServerManagement StudioWorking with SQL Server ManagementStudioCreating and Organizing T-SQL scriptsUsing Books OnlineIntroducing T-SQL
Writing Simple SELECT StatementsEliminating Duplicates with DISTINCTUsing Column and Table AliasesWriting Simple CASE ExpressionsWrite simple SELECT StatementsEliminate Duplicates Using DistinctUse Table and Column AliasesUse a Simple CASE ExpressionUnderstanding JoinsQuerying with Inner JoinsQuerying with Outer JoinsQuerying with Cross Joins and Self JoinsWriting Queries That Use Inner JoinsWriting Queries That Use Multiple-TableInner JoinWriting Queries That Use Self JoinsWriting Queries That Use Outer JoinsWriting Queries That Use Cross JoinsSorting DataFiltering Data with a WHERE ClauseFiltering with the TOP and OFFSET-FETCHOptionsWorking with Unknown and Missing ValuesWriting Queries That Filter Data Using aWHERE ClauseWriting Queries That Filter Data Using anORDER BY ClauseWriting Queries That Filter Data Using theTOP OptionWriting Queries That Filter Data Using theOFFSET-FETCH ClauseIntroducing SQL Server 2014 Data TypesWorking with Character DataWorking with Date and Time DataWriting Queries That Return Date and TimeDataWriting Queries That Use Date and TimeFunctionsWriting Queries That Return Character DataWriting Queries That Use CharacterFunctionInserting DataModifying and Deleting DataInserting DataUpdating and Deleting DataWriting Queries with Built-In FunctionsUsing Conversion FunctionsUsing Logical FunctionsUsing Functions to Work with NULLWrite queries which use conversionfunctionsWrite queries which use logical functionsWrite queries which test for nullabilityUsing Aggregate FunctionsUsing the GROUP BY ClauseFiltering Groups with HAVINGWrite queries which use the GROUP BYclauseWrite queries which use aggregate functionsWrite queries which use distinct aggregatefunctionsWrite queries which filter groups with theHAVING clauseWriting Self-Contained SubqueriesWriting Correlated SubqueriesUsing the EXISTS Predicate withM20461Working with SQL Server ManagementStudioCreating and Organizing T-SQL scriptsUsing Books OnlineIntroducing T-SQLUnderstanding SetsUnderstanding Predicate LogicUnderstanding the Logical Order ofOperations in SELECT statementsExecuting Basic SELECT StatementsExecuting queries which filter data usingpredicatesExecuting queries which sort data usingORDER BYWriting Simple SELECT StatementsEliminating Duplicates with DISTINCTUsing Column and Table AliasesWriting Simple CASE ExpressionsWrite simple SELECT StatementsEliminate Duplicates Using DistinctUse Table and Column AliasesUse a Simple CASE ExpressionUnderstanding JoinsQuerying with Inner JoinsQuerying with Outer JoinsQuerying with Cross Joins and Self JoinsWriting Queries That Use Inner JoinsWriting Queries That
Querying Microsoft SQL Server . Getting Started with SQL Server Creating and Organizing T-SQL scripts Creating and Organizing T-SQL scripts Management Studio Using Books Online Using Books Online Working with SQL
