Étiquette : Intermediate

Using aggregation functions
Using the GROUP BY clause
Filtering groups with HAVING

Practical Workshops:

• Write queries that summarize data using the built-in help in aggregate functions
• Use the GROUP BY clause to organize rows into groups
• Use the HAVING clause to filter groups based on a search condition

Writing subqueries
Writing correlated subqueries
Using the EXISTS predicate with subqueries

Practical Workshops:

• Describe the uses of queries that are nested in other queries
• Write stand-alone subqueries that return scalar or multi-valued results
• Write correlated sub-queries that return scalar results or multiple values
• Use the EXISTS predicate to efficiently check the existence of rows in a subquery

Use derived tables
Use common table expressions
Use views
Use inline table functions

Practical Workshops:

• Write queries that use derived tables
• Write queries that use common table expressions
• Create simple views and write queries against them
• Create simple inline table value functions and write queries against them

Write queries with the UNION operator
Use EXCEPT and INTERSECT
Use APPLY

Practical workshops:

• Writing queries combining data using the UNION operator
• Writing queries that compare sets using the INTERSECT and EXCEPT
• Writing queries that manipulate the rows of a table using APPLY with the results of a table or a derivative function

Using the OVER clause
Explorer of window functions

Practical Workshops:

• Describe the advantages of using window functions
• Restrict window functions to lines defined in an OVER clause, including partitions and frames
• Write queries that use window functions to run on a window rows and return the ranking, aggregation and offset comparison results

Writing queries with PIVOT and UNPIVOT
Working with Grouping Sets

Practical Workshops: Writing queries that rotate and UNPIVOT result sets – Writing queries that specify multiple groups with grouping sets

Query data with stored procedures
Pass parameters to stored procedures
Create simple stored procedures
Work with Dynamic SQL

Practical Workshops:

• Executing stored procedures
• Returning results by executing stored procedures
• Passing parameters to procedures
• Creating simple stored procedures that encapsulate a SELECT statement
• Building and executing dynamic SQL with EXEC and sp_executesql

T-SQL Programming Elements
Controlling Program Flow

Practical Workshops:

• Describe the language elements of T-SQL used for simple programming tasks
• Describe batches and how they are handled by SQL Server
• Declare and assign variables and synonyms
• Use IFs and time blocks to control the progress of the program

Use TRY and CATCH blocks
Work with error information

Practical Workshops:

• Describe SQL Server behavior when errors occur in T code SQL
• Implement T-structured exception handling SQL
• Raise user-defined errors and pass system errors to T code SQL

Transactions and Database Engine
Control Transactions
Isolation Levels

Practical Workshops:

• Describe transactions and the differences between batches and transactions
• Describe batches and how they are handled by SQL Server
• Create and manage transactions with transaction control language statements
• Use SET XACT_ABORT to define SQL Server manipulation of transactions outside of try / catch blocks
• Describe the effects of isolation levels on transactions

Query performance factors
Viewing query performance data

Practical Workshops:

• Describe the components of high-performance queries
• Display and interpret basic data of the performance of queries

Query System Catalog Views and Functions
Execute Systems Stored Procedures
Query Dynamic Management Objects

Hands-on Workshops:

• Write queries that extract system metadata using system views and functions
• Execute system stored procedures to return system information
• Write queries that extract system metadata and status information using dynamic management system views and functions