Querying Microsoft SQL Server 2012

101 

Loading....

Loading....

Loading....

Loading....

Loading....

Full text

(1)

Querying Microsoft SQL Server 2012

Cursusduur: 5 Dagen Cursuscode: M10774

Beschrijving:

Deze cursus is vervangen door cursus M20461 Querying Microsoft SQL Server. This course has been replaced by course M20461 Querying Microsoft SQL Server..

In deze vijfdaagse cursus onder leiding van een docent doen deelnemers de technische vaardigheden op voor het schrijven van eenvoudige Transact-SQL-query’s voor Microsoft SQL Server 2012. Deze cursus is het fundament voor alle disciplines die gerelateerd zijn aan SQL Server: databasebeheer, databaseontwikkeling en business intelligence.================================================== This 5-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence.

Doelgroep:

Deze cursus is bedoeld voor databasebeheerders, databaseontwikkelaars en business intelligence-professionals. De cursus zal waarschijnlijk ook SQL-powerusers van pas komen die niet per se databasegericht zijn of examen willen doen; denk hierbij aan opstellers van rapportages, bedrijfsanalisten en ontwikkelaars van klantapplicaties.

==================================================

This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. The course will very likely be well attended by SQL power users who aren’t necessarily database-focused or plan on taking the exam; namely, report writers, business analysts and client application developers.

Doelstelling:

After completing this course, students will be able to:

Write SELECT Queries Implementing Transactions

Query Multiple Tables Use Table Expressions

Use Built-In Functions Sort and Filter Data

Use Subqueries Use Window Ranking, Offset and Aggregate Functions

Execute Stored Procedures Query SQL Server Metadata

Use Set Operators Program with T-SQL

Implement Error Handling Improve Query Performance

Vereiste kennis en vaardigheden:

Examens en certificering

Working knowledge of relational databases. This course helps you prepare for exam 70-461 . Basic knowledge of the Microsoft Windows operating system and

(2)

its core functionality.

Vervolgcursussen:

(3)

Cursusinhoud:

Module 1: Introduction to Microsoft SQL Server

2012 line line

line Getting Started with SQL Server Getting Started with SQL Server Management Studio Management Studio

This module introduces the SQL Server Work with SQL Server Management Work with SQL Server Management platform and major tools. It discusses editions, Studio. Studio.

versions, tools used to query, documentation Working with SQL AzureAfter completing Working with SQL AzureAfter completing sources, and the logical structure of databases. this module, students will be able to: this module, students will be able to:

line Describe the basic features of SQL Describe the basic features of SQL Azure. Introducing Microsoft SQL Server 2012 Azure. Provision a SQL Azure server and

Provision a SQL Azure server and configure its security. configure its security. Understanding Sets

line Understanding Sets Understanding Predicate Logic

Getting Started with SQL Server Understanding Predicate Logic Understanding the Logical Order of Management Studio Understanding the Logical Order of Operations in SELECT statementsAfter Work with SQL Server Management Studio. Operations in SELECT statementsAfter completing this module, students will be Working with SQL AzureAfter completing completing this module, students will be able to:

this module, students will be able to: able to: Describe the elements of T-SQL and their Describe the basic features of SQL Azure. Describe the elements of T-SQL and their role in writing queries

Provision a SQL Azure server and configure role in writing queries Describe the use of sets in SQL Server its security. Describe the use of sets in SQL Server Describe the use of predicate logic in SQL Understanding Sets Describe the use of predicate logic in Server

Understanding Predicate Logic SQL Server Describe the logical order of operations in Understanding the Logical Order of Describe the logical order of operations in SELECT statements

Operations in SELECT statementsAfter SELECT statements Eliminating Duplicates with DISTINCT completing this module, students will be able Eliminating Duplicates with DISTINCT Using Column and Table Aliases to: Using Column and Table Aliases Writing Simple CASE Expressions Describe the elements of T-SQL and their Writing Simple CASE Expressions Eliminate duplicates using the DISTINCT role in writing queries Eliminate duplicates using the DISTINCT clause.

Describe the use of sets in SQL Server clause. Use column and table aliases. Describe the use of predicate logic in SQL Use column and table aliases. Write simple CASE expressions. Server Write simple CASE expressions. Querying with Inner Joins Describe the logical order of operations in Querying with Inner Joins Querying with Outer Joins SELECT statements Querying with Outer Joins Querying with Cross Joins and Self Eliminating Duplicates with DISTINCT Querying with Cross Joins and Self JoinsAfter completing this module, Using Column and Table Aliases JoinsAfter completing this module, students will be able to:

Writing Simple CASE Expressions students will be able to: Describe how multiple tables may be Eliminate duplicates using the DISTINCT Describe how multiple tables may be queried in a SELECT statement using clause. queried in a SELECT statement using joins.

Use column and table aliases. joins. Write queries that use inner joins. Write simple CASE expressions. Write queries that use inner joins. Write queries that use outer joins. Querying with Inner Joins Write queries that use outer joins. Write queries that use self-joins and cross Querying with Outer Joins Write queries that use self-joins and joins.

Querying with Cross Joins and Self cross joins. Filtering Data with a WHERE Clause JoinsAfter completing this module, students Filtering Data with a WHERE Clause Filtering with the TOP and

will be able to: Filtering with the TOP and OFFSET-FETCH Options

Describe how multiple tables may be OFFSET-FETCH Options Working with Unknown and Missing queried in a SELECT statement using joins. Working with Unknown and Missing ValuesAfter completing this module, Write queries that use inner joins. ValuesAfter completing this module, students will be able to:

Write queries that use outer joins. students will be able to: Filter data with predicates in the WHERE Write queries that use self-joins and cross Filter data with predicates in the WHERE clause.

joins. clause. Sort data using ORDER BY.

Filtering Data with a WHERE Clause Sort data using ORDER BY. Filter data in the SELECT clause with Filtering with the TOP and OFFSET-FETCH Filter data in the SELECT clause with TOP.

Options TOP. Filter data with OFFSET and FETCH.

Working with Unknown and Missing Filter data with OFFSET and FETCH. Working with Character Data

ValuesAfter completing this module, Working with Character Data Working with Date and Time DataAfter students will be able to: Working with Date and Time DataAfter completing this module, students will be Filter data with predicates in the WHERE completing this module, students will be able to:

clause. able to: Describe numeric data types, type

Sort data using ORDER BY. Describe numeric data types, type precedence and type conversions. Filter data in the SELECT clause with TOP. precedence and type conversions. Write queries using character data types. Filter data with OFFSET and FETCH. Write queries using character data types. Write queries using date and time data Working with Character Data Write queries using date and time data types.

(4)

Working with Date and Time DataAfter types. Using Conversion Functions completing this module, students will be able Using Conversion Functions Using Logical Functions

to: Using Logical Functions Using Functions to Work with NULLAfter Describe numeric data types, type Using Functions to Work with NULLAfter completing this module, students will be precedence and type conversions. completing this module, students will be able to:

Write queries using character data types. able to: Write queries with built-in scalar functions. Write queries using date and time data Write queries with built-in scalar Use conversion functions.

types. functions. Use logical functions.

Using Conversion Functions Use conversion functions. Use functions that work with NULL. Using Logical Functions Use logical functions. Using the GROUP BY Clause Using Functions to Work with NULLAfter Use functions that work with NULL. Filtering Groups with HAVINGAfter completing this module, students will be able Using the GROUP BY Clause completing this module, students will be to: Filtering Groups with HAVINGAfter able to:

Write queries with built-in scalar functions. completing this module, students will be Write queries which summarize data using Use conversion functions. able to: built-in aggregate functions.

Use logical functions. Write queries which summarize data Use the GROUP BY clause to arrange Use functions that work with NULL. using built-in aggregate functions. rows into groups.

Using the GROUP BY Clause Use the GROUP BY clause to arrange Use the HAVING clause to filter out groups Filtering Groups with HAVINGAfter rows into groups. based on a search condition.

completing this module, students will be able Use the HAVING clause to filter out Writing Correlated Subqueries to: groups based on a search condition. Using the EXISTS Predicate with Write queries which summarize data using Writing Correlated Subqueries SubqueriesAfter completing this module, built-in aggregate functions. Using the EXISTS Predicate with students will be able to:

Use the GROUP BY clause to arrange rows SubqueriesAfter completing this module, Describe the uses of queries which are into groups. students will be able to: nested within other queries.

Use the HAVING clause to filter out groups Describe the uses of queries which are Write self-contained subqueries which based on a search condition. nested within other queries. return scalar or multi-valued results. Writing Correlated Subqueries Write self-contained subqueries which Write correlated subqueries which return Using the EXISTS Predicate with return scalar or multi-valued results. scalar or multi-valued results.

SubqueriesAfter completing this module, Write correlated subqueries which return Use the EXISTS predicate to efficiently students will be able to: scalar or multi-valued results. check for the existence of rows in a Describe the uses of queries which are Use the EXISTS predicate to efficiently subquery.

nested within other queries. check for the existence of rows in a Using Common Table Expressions Write self-contained subqueries which return subquery. Using Views

scalar or multi-valued results. Using Common Table Expressions Using Inline Table-Valued FunctionsAfter Write correlated subqueries which return Using Views completing this module, students will be scalar or multi-valued results. Using Inline Table-Valued FunctionsAfter able to:

Use the EXISTS predicate to efficiently completing this module, students will be Write queries which use derived tables. check for the existence of rows in a able to: Write queries which use common table subquery. Write queries which use derived tables. expressions.

Using Common Table Expressions Write queries which use common table Create simple views and write queries

Using Views expressions. against them.

Using Inline Table-Valued FunctionsAfter Create simple views and write queries Create simple inline table-valued functions completing this module, students will be able against them. and write queries against them.

to: Create simple inline table-valued Using EXCEPT and INTERSECT

Write queries which use derived tables. functions and write queries against them. Using APPLYAfter completing this module, Write queries which use common table Using EXCEPT and INTERSECT students will be able to:

expressions. Using APPLYAfter completing this Write queries which combine data using Create simple views and write queries module, students will be able to: the UNION operator

against them. Write queries which combine data using Write queries which compare sets using Create simple inline table-valued functions the UNION operator the INTERSECT and EXCEPT operators and write queries against them. Write queries which compare sets using Write queries which manipulate rows in a Using EXCEPT and INTERSECT the INTERSECT and EXCEPT operators table by using APPLY with the results of a Using APPLYAfter completing this module, Write queries which manipulate rows in a derived table or function

students will be able to: table by using APPLY with the results of Exploring Window Functions

Write queries which combine data using the a derived table or function Restrict window functions to rows defined UNION operator Exploring Window Functions in an OVER clause, including partitions Write queries which compare sets using the Restrict window functions to rows defined and frames.

INTERSECT and EXCEPT operators in an OVER clause, including partitions Write queries which use window functions Write queries which manipulate rows in a and frames. to operate on a window of rows and return table by using APPLY with the results of a Write queries which use window ranking, aggregation and offset

derived table or function functions to operate on a window of rows comparison results.

Exploring Window Functions and return ranking, aggregation and Working with Grouping SetsAfter Restrict window functions to rows defined in offset comparison results. completing this module, students will be an OVER clause, including partitions and Working with Grouping SetsAfter able to:

(5)

Write queries which use window functions to able to: result sets

operate on a window of rows and return Write queries which pivot and unpivot Write queries which specify multiple ranking, aggregation and offset comparison result sets groupings with grouping sets results. Write queries which specify multiple Executing System Stored Procedures Working with Grouping SetsAfter completing groupings with grouping sets Querying Dynamic Management this module, students will be able to: Executing System Stored Procedures ObjectsAfter completing this module, Write queries which pivot and unpivot result Querying Dynamic Management students will be able to:

sets ObjectsAfter completing this module, Write queries that retrieve system Write queries which specify multiple students will be able to: metadata using system views and groupings with grouping sets Write queries that retrieve system functions.

Executing System Stored Procedures metadata using system views and Execute system stored procedures to Querying Dynamic Management functions. return system information.

ObjectsAfter completing this module, Execute system stored procedures to Write queries that retrieve system students will be able to: return system information. metadata and state information using Write queries that retrieve system metadata Write queries that retrieve system system dynamic management views and using system views and functions. metadata and state information using functions.

Execute system stored procedures to return system dynamic management views and Passing Parameters to Stored Procedures system information. functions. Creating Simple Stored Procedures Write queries that retrieve system metadata Passing Parameters to Stored Working with Dynamic SQLAfter and state information using system dynamic Procedures completing this module, students will be management views and functions. Creating Simple Stored Procedures able to:

Passing Parameters to Stored Procedures Working with Dynamic SQLAfter Return results by executing stored Creating Simple Stored Procedures completing this module, students will be procedures.

Working with Dynamic SQLAfter completing able to: Pass parameters to procedures. this module, students will be able to: Return results by executing stored Create simple stored procedures which Return results by executing stored procedures. encapsulate a SELECT statement. procedures. Pass parameters to procedures. Construct and execute dynamic SQL with Pass parameters to procedures. Create simple stored procedures which EXEC and sp_executesql.

Create simple stored procedures which encapsulate a SELECT statement. Controlling Program FlowAfter completing encapsulate a SELECT statement. Construct and execute dynamic SQL with this module, students will be able to: Construct and execute dynamic SQL with EXEC and sp_executesql. Describe the language elements of T-SQL EXEC and sp_executesql. Controlling Program FlowAfter used for simple programming tasks. Controlling Program FlowAfter completing completing this module, students will be Describe batches and how they are this module, students will be able to: able to: handled by SQL Server.

Describe the language elements of T-SQL Describe the language elements of Declare and assign variables and used for simple programming tasks. T-SQL used for simple programming synonyms.

Describe batches and how they are handled tasks. Use IF and WHILE blocks to control by SQL Server. Describe batches and how they are program flow.

Declare and assign variables and synonyms. handled by SQL Server. Working with Error InformationAfter Use IF and WHILE blocks to control Declare and assign variables and completing this module, students will be

program flow. synonyms. able to:

Working with Error InformationAfter Use IF and WHILE blocks to control Describe SQL Server's behavior when completing this module, students will be able program flow. errors occur in T-SQL code.

to: Working with Error InformationAfter Implement structured exception handling in Describe SQL Server's behavior when errors completing this module, students will be T-SQL.

occur in T-SQL code. able to: Return information about errors from Implement structured exception handling in Describe SQL Server's behavior when system objects.

T-SQL. errors occur in T-SQL code. Raise user-defined errors and pass system Return information about errors from system Implement structured exception handling errors in T-SQL code.

objects. in T-SQL. Controlling TransactionsAfter completing

Raise user-defined errors and pass system Return information about errors from this module, students will be able to: errors in T-SQL code. system objects. Describe transactions and the differences Controlling TransactionsAfter completing Raise user-defined errors and pass between batches and transactions. this module, students will be able to: system errors in T-SQL code. Describe batches and how they are Describe transactions and the differences Controlling TransactionsAfter completing handled by SQL Server.

between batches and transactions. this module, students will be able to: Create and manage transactions with Describe batches and how they are handled Describe transactions and the differences transaction control language statements. by SQL Server. between batches and transactions. Use SET XACT_ABORT to define SQL Create and manage transactions with Describe batches and how they are Server's handling of transactions outside transaction control language statements. handled by SQL Server. TRY / CATCH blocks.

Use SET XACT_ABORT to define SQL Create and manage transactions with Displaying Query Performance DataAfter Server's handling of transactions outside transaction control language statements. completing this module, students will be TRY / CATCH blocks. Use SET XACT_ABORT to define SQL able to:

Displaying Query Performance DataAfter Server's handling of transactions outside Describe components of well-performing completing this module, students will be able TRY / CATCH blocks. queries.

(6)

Describe components of well-performing completing this module, students will be in SQL Server

queries. able to: Display and interpret basic query plans

Describe the role of indexes and statistics in Describe components of well-performing Display and interpret basic query

SQL Server queries. performance data

Display and interpret basic query plans Describe the role of indexes and statistics

Display and interpret basic query in SQL Server Module 14: Pivoting and Grouping Sets performance data Display and interpret basic query plans line

Display and interpret basic query

After completing this module, students will be performance data This module discusses techniques for pivoting

able to: data in T-SQL as well to introduce the

line fundamentals of the GROUPING SETS

Describe the architecture and editions of line clause. It will also cover the use of GROUP SQL Server 2012. Getting Started with SQL Server BY ROLLUP and GROUP BY CUBE syntax in Describe the benefits to using window Management Studio SQL Server 2012.

functions. Work with SQL Server Management line

Studio. Writing Queries with PIVOT and UNPIVOT Working with SQL AzureAfter completing

line this module, students will be able to:

Getting Started with SQL Server Describe the basic features of SQL line

Management Studio Azure. Getting Started with SQL Server

Work with SQL Server Management Studio. Provision a SQL Azure server and Management Studio

Working with SQL AzureAfter completing configure its security. Work with SQL Server Management this module, students will be able to: Understanding Sets Studio.

Describe the basic features of SQL Azure. Understanding Predicate Logic Working with SQL AzureAfter completing Provision a SQL Azure server and configure Understanding the Logical Order of this module, students will be able to: its security. Operations in SELECT statementsAfter Describe the basic features of SQL Azure. Understanding Sets completing this module, students will be Provision a SQL Azure server and Understanding Predicate Logic able to: configure its security.

Understanding the Logical Order of Describe the elements of T-SQL and their Understanding Sets

Operations in SELECT statementsAfter role in writing queries Understanding Predicate Logic completing this module, students will be able Describe the use of sets in SQL Server Understanding the Logical Order of to: Describe the use of predicate logic in Operations in SELECT statementsAfter Describe the elements of T-SQL and their SQL Server completing this module, students will be role in writing queries Describe the logical order of operations in able to:

Describe the use of sets in SQL Server SELECT statements Describe the elements of T-SQL and their Describe the use of predicate logic in SQL Eliminating Duplicates with DISTINCT role in writing queries

Server Using Column and Table Aliases Describe the use of sets in SQL Server Describe the logical order of operations in Writing Simple CASE Expressions Describe the use of predicate logic in SQL SELECT statements Eliminate duplicates using the DISTINCT Server

Eliminating Duplicates with DISTINCT clause. Describe the logical order of operations in Using Column and Table Aliases Use column and table aliases. SELECT statements

Writing Simple CASE Expressions Write simple CASE expressions. Eliminating Duplicates with DISTINCT Eliminate duplicates using the DISTINCT Querying with Inner Joins Using Column and Table Aliases clause. Querying with Outer Joins Writing Simple CASE Expressions Use column and table aliases. Querying with Cross Joins and Self Eliminate duplicates using the DISTINCT Write simple CASE expressions. JoinsAfter completing this module, clause.

Querying with Inner Joins students will be able to: Use column and table aliases. Querying with Outer Joins Describe how multiple tables may be Write simple CASE expressions. Querying with Cross Joins and Self queried in a SELECT statement using Querying with Inner Joins JoinsAfter completing this module, students joins. Querying with Outer Joins will be able to: Write queries that use inner joins. Querying with Cross Joins and Self Describe how multiple tables may be Write queries that use outer joins. JoinsAfter completing this module, queried in a SELECT statement using joins. Write queries that use self-joins and students will be able to:

Write queries that use inner joins. cross joins. Describe how multiple tables may be Write queries that use outer joins. Filtering Data with a WHERE Clause queried in a SELECT statement using Write queries that use self-joins and cross Filtering with the TOP and joins.

joins. OFFSET-FETCH Options Write queries that use inner joins. Filtering Data with a WHERE Clause Working with Unknown and Missing Write queries that use outer joins. Filtering with the TOP and OFFSET-FETCH ValuesAfter completing this module, Write queries that use self-joins and cross

Options students will be able to: joins.

Working with Unknown and Missing Filter data with predicates in the WHERE Filtering Data with a WHERE Clause ValuesAfter completing this module, clause. Filtering with the TOP and

students will be able to: Sort data using ORDER BY. OFFSET-FETCH Options

Filter data with predicates in the WHERE Filter data in the SELECT clause with Working with Unknown and Missing

clause. TOP. ValuesAfter completing this module,

(7)

Filter data in the SELECT clause with TOP. Working with Character Data Filter data with predicates in the WHERE Filter data with OFFSET and FETCH. Working with Date and Time DataAfter clause.

Working with Character Data completing this module, students will be Sort data using ORDER BY. Working with Date and Time DataAfter able to: Filter data in the SELECT clause with completing this module, students will be able Describe numeric data types, type TOP.

to: precedence and type conversions. Filter data with OFFSET and FETCH. Describe numeric data types, type Write queries using character data types. Working with Character Data

precedence and type conversions. Write queries using date and time data Working with Date and Time DataAfter Write queries using character data types. types. completing this module, students will be Write queries using date and time data Using Conversion Functions able to:

types. Using Logical Functions Describe numeric data types, type Using Conversion Functions Using Functions to Work with NULLAfter precedence and type conversions. Using Logical Functions completing this module, students will be Write queries using character data types. Using Functions to Work with NULLAfter able to: Write queries using date and time data completing this module, students will be able Write queries with built-in scalar types.

to: functions. Using Conversion Functions

Write queries with built-in scalar functions. Use conversion functions. Using Logical Functions

Use conversion functions. Use logical functions. Using Functions to Work with NULLAfter Use logical functions. Use functions that work with NULL. completing this module, students will be Use functions that work with NULL. Using the GROUP BY Clause able to:

Using the GROUP BY Clause Filtering Groups with HAVINGAfter Write queries with built-in scalar functions. Filtering Groups with HAVINGAfter completing this module, students will be Use conversion functions.

completing this module, students will be able able to: Use logical functions.

to: Write queries which summarize data Use functions that work with NULL. Write queries which summarize data using using built-in aggregate functions. Using the GROUP BY Clause built-in aggregate functions. Use the GROUP BY clause to arrange Filtering Groups with HAVINGAfter Use the GROUP BY clause to arrange rows rows into groups. completing this module, students will be into groups. Use the HAVING clause to filter out able to:

Use the HAVING clause to filter out groups groups based on a search condition. Write queries which summarize data using based on a search condition. Writing Correlated Subqueries built-in aggregate functions.

Writing Correlated Subqueries Using the EXISTS Predicate with Use the GROUP BY clause to arrange Using the EXISTS Predicate with SubqueriesAfter completing this module, rows into groups.

SubqueriesAfter completing this module, students will be able to: Use the HAVING clause to filter out groups students will be able to: Describe the uses of queries which are based on a search condition.

Describe the uses of queries which are nested within other queries. Writing Correlated Subqueries nested within other queries. Write self-contained subqueries which Using the EXISTS Predicate with Write self-contained subqueries which return return scalar or multi-valued results. SubqueriesAfter completing this module, scalar or multi-valued results. Write correlated subqueries which return students will be able to:

Write correlated subqueries which return scalar or multi-valued results. Describe the uses of queries which are scalar or multi-valued results. Use the EXISTS predicate to efficiently nested within other queries.

Use the EXISTS predicate to efficiently check for the existence of rows in a Write self-contained subqueries which check for the existence of rows in a subquery. return scalar or multi-valued results. subquery. Using Common Table Expressions Write correlated subqueries which return Using Common Table Expressions Using Views scalar or multi-valued results.

Using Views Using Inline Table-Valued FunctionsAfter Use the EXISTS predicate to efficiently Using Inline Table-Valued FunctionsAfter completing this module, students will be check for the existence of rows in a completing this module, students will be able able to: subquery.

to: Write queries which use derived tables. Using Common Table Expressions Write queries which use derived tables. Write queries which use common table Using Views

Write queries which use common table expressions. Using Inline Table-Valued FunctionsAfter expressions. Create simple views and write queries completing this module, students will be Create simple views and write queries against them. able to:

against them. Create simple inline table-valued Write queries which use derived tables. Create simple inline table-valued functions functions and write queries against them. Write queries which use common table and write queries against them. Using EXCEPT and INTERSECT expressions.

Using EXCEPT and INTERSECT Using APPLYAfter completing this Create simple views and write queries Using APPLYAfter completing this module, module, students will be able to: against them.

students will be able to: Write queries which combine data using Create simple inline table-valued functions Write queries which combine data using the the UNION operator and write queries against them.

UNION operator Write queries which compare sets using Using EXCEPT and INTERSECT

Write queries which compare sets using the the INTERSECT and EXCEPT operators Using APPLYAfter completing this module, INTERSECT and EXCEPT operators Write queries which manipulate rows in a students will be able to:

Write queries which manipulate rows in a table by using APPLY with the results of Write queries which combine data using table by using APPLY with the results of a a derived table or function the UNION operator

derived table or function Exploring Window Functions Write queries which compare sets using Exploring Window Functions Restrict window functions to rows defined the INTERSECT and EXCEPT operators

(8)

Restrict window functions to rows defined in in an OVER clause, including partitions Write queries which manipulate rows in a an OVER clause, including partitions and and frames. table by using APPLY with the results of a frames. Write queries which use window derived table or function

Write queries which use window functions to functions to operate on a window of rows Exploring Window Functions

operate on a window of rows and return and return ranking, aggregation and Restrict window functions to rows defined ranking, aggregation and offset comparison offset comparison results. in an OVER clause, including partitions results. Working with Grouping SetsAfter and frames.

Working with Grouping SetsAfter completing completing this module, students will be Write queries which use window functions this module, students will be able to: able to: to operate on a window of rows and return Write queries which pivot and unpivot result Write queries which pivot and unpivot ranking, aggregation and offset

sets result sets comparison results.

Write queries which specify multiple Write queries which specify multiple Working with Grouping SetsAfter groupings with grouping sets groupings with grouping sets completing this module, students will be Executing System Stored Procedures Executing System Stored Procedures able to:

Querying Dynamic Management Querying Dynamic Management Write queries which pivot and unpivot ObjectsAfter completing this module, ObjectsAfter completing this module, result sets

students will be able to: students will be able to: Write queries which specify multiple Write queries that retrieve system metadata Write queries that retrieve system groupings with grouping sets using system views and functions. metadata using system views and Executing System Stored Procedures Execute system stored procedures to return functions. Querying Dynamic Management system information. Execute system stored procedures to ObjectsAfter completing this module, Write queries that retrieve system metadata return system information. students will be able to:

and state information using system dynamic Write queries that retrieve system Write queries that retrieve system management views and functions. metadata and state information using metadata using system views and Passing Parameters to Stored Procedures system dynamic management views and functions.

Creating Simple Stored Procedures functions. Execute system stored procedures to Working with Dynamic SQLAfter completing Passing Parameters to Stored return system information.

this module, students will be able to: Procedures Write queries that retrieve system Return results by executing stored Creating Simple Stored Procedures metadata and state information using procedures. Working with Dynamic SQLAfter system dynamic management views and Pass parameters to procedures. completing this module, students will be functions.

Create simple stored procedures which able to: Passing Parameters to Stored Procedures encapsulate a SELECT statement. Return results by executing stored Creating Simple Stored Procedures Construct and execute dynamic SQL with procedures. Working with Dynamic SQLAfter EXEC and sp_executesql. Pass parameters to procedures. completing this module, students will be Controlling Program FlowAfter completing Create simple stored procedures which able to:

this module, students will be able to: encapsulate a SELECT statement. Return results by executing stored Describe the language elements of T-SQL Construct and execute dynamic SQL with procedures.

used for simple programming tasks. EXEC and sp_executesql. Pass parameters to procedures. Describe batches and how they are handled Controlling Program FlowAfter Create simple stored procedures which by SQL Server. completing this module, students will be encapsulate a SELECT statement. Declare and assign variables and synonyms. able to: Construct and execute dynamic SQL with Use IF and WHILE blocks to control Describe the language elements of EXEC and sp_executesql.

program flow. T-SQL used for simple programming Controlling Program FlowAfter completing Working with Error InformationAfter tasks. this module, students will be able to: completing this module, students will be able Describe batches and how they are Describe the language elements of T-SQL to: handled by SQL Server. used for simple programming tasks. Describe SQL Server's behavior when errors Declare and assign variables and Describe batches and how they are occur in T-SQL code. synonyms. handled by SQL Server.

Implement structured exception handling in Use IF and WHILE blocks to control Declare and assign variables and

T-SQL. program flow. synonyms.

Return information about errors from system Working with Error InformationAfter Use IF and WHILE blocks to control objects. completing this module, students will be program flow.

Raise user-defined errors and pass system able to: Working with Error InformationAfter errors in T-SQL code. Describe SQL Server's behavior when completing this module, students will be Controlling TransactionsAfter completing errors occur in T-SQL code. able to:

this module, students will be able to: Implement structured exception handling Describe SQL Server's behavior when Describe transactions and the differences in T-SQL. errors occur in T-SQL code.

between batches and transactions. Return information about errors from Implement structured exception handling in Describe batches and how they are handled system objects. T-SQL.

by SQL Server. Raise user-defined errors and pass Return information about errors from Create and manage transactions with system errors in T-SQL code. system objects.

transaction control language statements. Controlling TransactionsAfter completing Raise user-defined errors and pass system Use SET XACT_ABORT to define SQL this module, students will be able to: errors in T-SQL code.

Server's handling of transactions outside Describe transactions and the differences Controlling TransactionsAfter completing TRY / CATCH blocks. between batches and transactions. this module, students will be able to:

(9)

Displaying Query Performance DataAfter Describe batches and how they are Describe transactions and the differences completing this module, students will be able handled by SQL Server. between batches and transactions. to: Create and manage transactions with Describe batches and how they are Describe components of well-performing transaction control language statements. handled by SQL Server.

queries. Use SET XACT_ABORT to define SQL Create and manage transactions with Describe the role of indexes and statistics in Server's handling of transactions outside transaction control language statements. SQL Server TRY / CATCH blocks. Use SET XACT_ABORT to define SQL Display and interpret basic query plans Displaying Query Performance DataAfter Server's handling of transactions outside Display and interpret basic query completing this module, students will be TRY / CATCH blocks.

performance data able to: Displaying Query Performance DataAfter Describe components of well-performing completing this module, students will be Module 2: Getting Started with SQL Azure queries. able to:

line Describe the role of indexes and statistics Describe components of well-performing

in SQL Server queries.

Display and interpret basic query plans Describe the role of indexes and statistics Overview of SQL Azure Display and interpret basic query in SQL Server

performance data Display and interpret basic query plans Display and interpret basic query

line performance data

Getting Started with SQL Server line

Management Studio Getting Started with SQL Server

Work with SQL Server Management Studio. Management Studio line

Working with SQL AzureAfter completing Work with SQL Server Management Getting Started with SQL Server this module, students will be able to: Studio. Management Studio

Describe the basic features of SQL Azure. Working with SQL AzureAfter completing Work with SQL Server Management Provision a SQL Azure server and configure this module, students will be able to: Studio.

its security. Describe the basic features of SQL Working with SQL AzureAfter completing Understanding Sets Azure. this module, students will be able to: Understanding Predicate Logic Provision a SQL Azure server and Describe the basic features of SQL Azure. Understanding the Logical Order of configure its security. Provision a SQL Azure server and Operations in SELECT statementsAfter Understanding Sets configure its security.

completing this module, students will be able Understanding Predicate Logic Understanding Sets

to: Understanding the Logical Order of Understanding Predicate Logic Describe the elements of T-SQL and their Operations in SELECT statementsAfter Understanding the Logical Order of role in writing queries completing this module, students will be Operations in SELECT statementsAfter Describe the use of sets in SQL Server able to: completing this module, students will be Describe the use of predicate logic in SQL Describe the elements of T-SQL and their able to:

Server role in writing queries Describe the elements of T-SQL and their Describe the logical order of operations in Describe the use of sets in SQL Server role in writing queries

SELECT statements Describe the use of predicate logic in Describe the use of sets in SQL Server Eliminating Duplicates with DISTINCT SQL Server Describe the use of predicate logic in SQL Using Column and Table Aliases Describe the logical order of operations in Server

Writing Simple CASE Expressions SELECT statements Describe the logical order of operations in Eliminate duplicates using the DISTINCT Eliminating Duplicates with DISTINCT SELECT statements

clause. Using Column and Table Aliases Eliminating Duplicates with DISTINCT Use column and table aliases. Writing Simple CASE Expressions Using Column and Table Aliases Write simple CASE expressions. Eliminate duplicates using the DISTINCT Writing Simple CASE Expressions Querying with Inner Joins clause. Eliminate duplicates using the DISTINCT Querying with Outer Joins Use column and table aliases. clause.

Querying with Cross Joins and Self Write simple CASE expressions. Use column and table aliases. JoinsAfter completing this module, students Querying with Inner Joins Write simple CASE expressions. will be able to: Querying with Outer Joins Querying with Inner Joins Describe how multiple tables may be Querying with Cross Joins and Self Querying with Outer Joins queried in a SELECT statement using joins. JoinsAfter completing this module, Querying with Cross Joins and Self Write queries that use inner joins. students will be able to: JoinsAfter completing this module, Write queries that use outer joins. Describe how multiple tables may be students will be able to:

Write queries that use self-joins and cross queried in a SELECT statement using Describe how multiple tables may be

joins. joins. queried in a SELECT statement using

Filtering Data with a WHERE Clause Write queries that use inner joins. joins.

Filtering with the TOP and OFFSET-FETCH Write queries that use outer joins. Write queries that use inner joins. Options Write queries that use self-joins and Write queries that use outer joins. Working with Unknown and Missing cross joins. Write queries that use self-joins and cross ValuesAfter completing this module, Filtering Data with a WHERE Clause joins.

students will be able to: Filtering with the TOP and Filtering Data with a WHERE Clause Filter data with predicates in the WHERE OFFSET-FETCH Options Filtering with the TOP and

(10)

Sort data using ORDER BY. ValuesAfter completing this module, Working with Unknown and Missing Filter data in the SELECT clause with TOP. students will be able to: ValuesAfter completing this module, Filter data with OFFSET and FETCH. Filter data with predicates in the WHERE students will be able to:

Working with Character Data clause. Filter data with predicates in the WHERE Working with Date and Time DataAfter Sort data using ORDER BY. clause.

completing this module, students will be able Filter data in the SELECT clause with Sort data using ORDER BY.

to: TOP. Filter data in the SELECT clause with

Describe numeric data types, type Filter data with OFFSET and FETCH. TOP.

precedence and type conversions. Working with Character Data Filter data with OFFSET and FETCH. Write queries using character data types. Working with Date and Time DataAfter Working with Character Data

Write queries using date and time data completing this module, students will be Working with Date and Time DataAfter

types. able to: completing this module, students will be

Using Conversion Functions Describe numeric data types, type able to:

Using Logical Functions precedence and type conversions. Describe numeric data types, type Using Functions to Work with NULLAfter Write queries using character data types. precedence and type conversions. completing this module, students will be able Write queries using date and time data Write queries using character data types.

to: types. Write queries using date and time data

Write queries with built-in scalar functions. Using Conversion Functions types.

Use conversion functions. Using Logical Functions Using Conversion Functions Use logical functions. Using Functions to Work with NULLAfter Using Logical Functions

Use functions that work with NULL. completing this module, students will be Using Functions to Work with NULLAfter Using the GROUP BY Clause able to: completing this module, students will be Filtering Groups with HAVINGAfter Write queries with built-in scalar able to:

completing this module, students will be able functions. Write queries with built-in scalar functions. to: Use conversion functions. Use conversion functions.

Write queries which summarize data using Use logical functions. Use logical functions.

built-in aggregate functions. Use functions that work with NULL. Use functions that work with NULL. Use the GROUP BY clause to arrange rows Using the GROUP BY Clause Using the GROUP BY Clause into groups. Filtering Groups with HAVINGAfter Filtering Groups with HAVINGAfter Use the HAVING clause to filter out groups completing this module, students will be completing this module, students will be based on a search condition. able to: able to:

Writing Correlated Subqueries Write queries which summarize data Write queries which summarize data using Using the EXISTS Predicate with using built-in aggregate functions. built-in aggregate functions.

SubqueriesAfter completing this module, Use the GROUP BY clause to arrange Use the GROUP BY clause to arrange students will be able to: rows into groups. rows into groups.

Describe the uses of queries which are Use the HAVING clause to filter out Use the HAVING clause to filter out groups nested within other queries. groups based on a search condition. based on a search condition.

Write self-contained subqueries which return Writing Correlated Subqueries Writing Correlated Subqueries scalar or multi-valued results. Using the EXISTS Predicate with Using the EXISTS Predicate with Write correlated subqueries which return SubqueriesAfter completing this module, SubqueriesAfter completing this module, scalar or multi-valued results. students will be able to: students will be able to:

Use the EXISTS predicate to efficiently Describe the uses of queries which are Describe the uses of queries which are check for the existence of rows in a nested within other queries. nested within other queries.

subquery. Write self-contained subqueries which Write self-contained subqueries which Using Common Table Expressions return scalar or multi-valued results. return scalar or multi-valued results. Using Views Write correlated subqueries which return Write correlated subqueries which return Using Inline Table-Valued FunctionsAfter scalar or multi-valued results. scalar or multi-valued results.

completing this module, students will be able Use the EXISTS predicate to efficiently Use the EXISTS predicate to efficiently to: check for the existence of rows in a check for the existence of rows in a Write queries which use derived tables. subquery. subquery.

Write queries which use common table Using Common Table Expressions Using Common Table Expressions

expressions. Using Views Using Views

Create simple views and write queries Using Inline Table-Valued FunctionsAfter Using Inline Table-Valued FunctionsAfter against them. completing this module, students will be completing this module, students will be Create simple inline table-valued functions able to: able to:

and write queries against them. Write queries which use derived tables. Write queries which use derived tables. Using EXCEPT and INTERSECT Write queries which use common table Write queries which use common table Using APPLYAfter completing this module, expressions. expressions.

students will be able to: Create simple views and write queries Create simple views and write queries Write queries which combine data using the against them. against them.

UNION operator Create simple inline table-valued Create simple inline table-valued functions Write queries which compare sets using the functions and write queries against them. and write queries against them.

INTERSECT and EXCEPT operators Using EXCEPT and INTERSECT Using EXCEPT and INTERSECT

Write queries which manipulate rows in a Using APPLYAfter completing this Using APPLYAfter completing this module, table by using APPLY with the results of a module, students will be able to: students will be able to:

(11)

Exploring Window Functions the UNION operator the UNION operator

Restrict window functions to rows defined in Write queries which compare sets using Write queries which compare sets using an OVER clause, including partitions and the INTERSECT and EXCEPT operators the INTERSECT and EXCEPT operators frames. Write queries which manipulate rows in a Write queries which manipulate rows in a Write queries which use window functions to table by using APPLY with the results of table by using APPLY with the results of a operate on a window of rows and return a derived table or function derived table or function

ranking, aggregation and offset comparison Exploring Window Functions Exploring Window Functions

results. Restrict window functions to rows defined Restrict window functions to rows defined Working with Grouping SetsAfter completing in an OVER clause, including partitions in an OVER clause, including partitions this module, students will be able to: and frames. and frames.

Write queries which pivot and unpivot result Write queries which use window Write queries which use window functions sets functions to operate on a window of rows to operate on a window of rows and return Write queries which specify multiple and return ranking, aggregation and ranking, aggregation and offset

groupings with grouping sets offset comparison results. comparison results.

Executing System Stored Procedures Working with Grouping SetsAfter Working with Grouping SetsAfter Querying Dynamic Management completing this module, students will be completing this module, students will be ObjectsAfter completing this module, able to: able to:

students will be able to: Write queries which pivot and unpivot Write queries which pivot and unpivot Write queries that retrieve system metadata result sets result sets

using system views and functions. Write queries which specify multiple Write queries which specify multiple Execute system stored procedures to return groupings with grouping sets groupings with grouping sets system information. Executing System Stored Procedures Executing System Stored Procedures Write queries that retrieve system metadata Querying Dynamic Management Querying Dynamic Management and state information using system dynamic ObjectsAfter completing this module, ObjectsAfter completing this module, management views and functions. students will be able to: students will be able to:

Passing Parameters to Stored Procedures Write queries that retrieve system Write queries that retrieve system Creating Simple Stored Procedures metadata using system views and metadata using system views and Working with Dynamic SQLAfter completing functions. functions.

this module, students will be able to: Execute system stored procedures to Execute system stored procedures to Return results by executing stored return system information. return system information.

procedures. Write queries that retrieve system Write queries that retrieve system Pass parameters to procedures. metadata and state information using metadata and state information using Create simple stored procedures which system dynamic management views and system dynamic management views and encapsulate a SELECT statement. functions. functions.

Construct and execute dynamic SQL with Passing Parameters to Stored Passing Parameters to Stored Procedures EXEC and sp_executesql. Procedures Creating Simple Stored Procedures Controlling Program FlowAfter completing Creating Simple Stored Procedures Working with Dynamic SQLAfter this module, students will be able to: Working with Dynamic SQLAfter completing this module, students will be Describe the language elements of T-SQL completing this module, students will be able to:

used for simple programming tasks. able to: Return results by executing stored Describe batches and how they are handled Return results by executing stored procedures.

by SQL Server. procedures. Pass parameters to procedures. Declare and assign variables and synonyms. Pass parameters to procedures. Create simple stored procedures which Use IF and WHILE blocks to control Create simple stored procedures which encapsulate a SELECT statement. program flow. encapsulate a SELECT statement. Construct and execute dynamic SQL with Working with Error InformationAfter Construct and execute dynamic SQL with EXEC and sp_executesql.

completing this module, students will be able EXEC and sp_executesql. Controlling Program FlowAfter completing to: Controlling Program FlowAfter this module, students will be able to: Describe SQL Server's behavior when errors completing this module, students will be Describe the language elements of T-SQL occur in T-SQL code. able to: used for simple programming tasks. Implement structured exception handling in Describe the language elements of Describe batches and how they are T-SQL. T-SQL used for simple programming handled by SQL Server.

Return information about errors from system tasks. Declare and assign variables and objects. Describe batches and how they are synonyms.

Raise user-defined errors and pass system handled by SQL Server. Use IF and WHILE blocks to control errors in T-SQL code. Declare and assign variables and program flow.

Controlling TransactionsAfter completing synonyms. Working with Error InformationAfter this module, students will be able to: Use IF and WHILE blocks to control completing this module, students will be Describe transactions and the differences program flow. able to:

between batches and transactions. Working with Error InformationAfter Describe SQL Server's behavior when Describe batches and how they are handled completing this module, students will be errors occur in T-SQL code.

by SQL Server. able to: Implement structured exception handling in Create and manage transactions with Describe SQL Server's behavior when T-SQL.

transaction control language statements. errors occur in T-SQL code. Return information about errors from Use SET XACT_ABORT to define SQL Implement structured exception handling system objects.

(12)

TRY / CATCH blocks. Return information about errors from errors in T-SQL code.

Displaying Query Performance DataAfter system objects. Controlling TransactionsAfter completing completing this module, students will be able Raise user-defined errors and pass this module, students will be able to: to: system errors in T-SQL code. Describe transactions and the differences Describe components of well-performing Controlling TransactionsAfter completing between batches and transactions. queries. this module, students will be able to: Describe batches and how they are Describe the role of indexes and statistics in Describe transactions and the differences handled by SQL Server.

SQL Server between batches and transactions. Create and manage transactions with Display and interpret basic query plans Describe batches and how they are transaction control language statements. Display and interpret basic query handled by SQL Server. Use SET XACT_ABORT to define SQL performance data Create and manage transactions with Server's handling of transactions outside

transaction control language statements. TRY / CATCH blocks.

Use SET XACT_ABORT to define SQL Displaying Query Performance DataAfter

line Server's handling of transactions outside completing this module, students will be Getting Started with SQL Server TRY / CATCH blocks. able to:

Management Studio Displaying Query Performance DataAfter Describe components of well-performing Work with SQL Server Management Studio. completing this module, students will be queries.

Working with SQL AzureAfter completing able to: Describe the role of indexes and statistics this module, students will be able to: Describe components of well-performing in SQL Server

Describe the basic features of SQL Azure. queries. Display and interpret basic query plans Provision a SQL Azure server and configure Describe the role of indexes and statistics Display and interpret basic query

its security. in SQL Server performance data

Understanding Sets Display and interpret basic query plans

Understanding Predicate Logic Display and interpret basic query Module 15: Querying SQL Server Metadata Understanding the Logical Order of performance data line

Operations in SELECT statementsAfter

completing this module, students will be able This module introduces the use of SQL

to: line Server system objects in T-SQL queries. It

Describe the elements of T-SQL and their Getting Started with SQL Server will cover the use of system catalog views, role in writing queries Management Studio system stored procedures, system functions, Describe the use of sets in SQL Server Work with SQL Server Management and dynamic management objects.

Describe the use of predicate logic in SQL Studio. line

Server Working with SQL AzureAfter completing Querying System Catalog Views and Describe the logical order of operations in this module, students will be able to: Functions

SELECT statements Describe the basic features of SQL Eliminating Duplicates with DISTINCT Azure.

Using Column and Table Aliases Provision a SQL Azure server and line

Writing Simple CASE Expressions configure its security. Getting Started with SQL Server Eliminate duplicates using the DISTINCT Understanding Sets Management Studio

clause. Understanding Predicate Logic Work with SQL Server Management Use column and table aliases. Understanding the Logical Order of Studio.

Write simple CASE expressions. Operations in SELECT statementsAfter Working with SQL AzureAfter completing Querying with Inner Joins completing this module, students will be this module, students will be able to: Querying with Outer Joins able to: Describe the basic features of SQL Azure. Querying with Cross Joins and Self Describe the elements of T-SQL and their Provision a SQL Azure server and JoinsAfter completing this module, students role in writing queries configure its security.

will be able to: Describe the use of sets in SQL Server Understanding Sets

Describe how multiple tables may be Describe the use of predicate logic in Understanding Predicate Logic queried in a SELECT statement using joins. SQL Server Understanding the Logical Order of Write queries that use inner joins. Describe the logical order of operations in Operations in SELECT statementsAfter Write queries that use outer joins. SELECT statements completing this module, students will be Write queries that use self-joins and cross Eliminating Duplicates with DISTINCT able to:

joins. Using Column and Table Aliases Describe the elements of T-SQL and their Filtering Data with a WHERE Clause Writing Simple CASE Expressions role in writing queries

Filtering with the TOP and OFFSET-FETCH Eliminate duplicates using the DISTINCT Describe the use of sets in SQL Server

Options clause. Describe the use of predicate logic in SQL

Working with Unknown and Missing Use column and table aliases. Server

ValuesAfter completing this module, Write simple CASE expressions. Describe the logical order of operations in students will be able to: Querying with Inner Joins SELECT statements

Filter data with predicates in the WHERE Querying with Outer Joins Eliminating Duplicates with DISTINCT clause. Querying with Cross Joins and Self Using Column and Table Aliases Sort data using ORDER BY. JoinsAfter completing this module, Writing Simple CASE Expressions Filter data in the SELECT clause with TOP. students will be able to: Eliminate duplicates using the DISTINCT Filter data with OFFSET and FETCH. Describe how multiple tables may be clause.

Working with Character Data queried in a SELECT statement using Use column and table aliases. Working with Date and Time DataAfter joins. Write simple CASE expressions.

(13)

completing this module, students will be able Write queries that use inner joins. Querying with Inner Joins to: Write queries that use outer joins. Querying with Outer Joins Describe numeric data types, type Write queries that use self-joins and Querying with Cross Joins and Self precedence and type conversions. cross joins. JoinsAfter completing this module, Write queries using character data types. Filtering Data with a WHERE Clause students will be able to:

Write queries using date and time data Filtering with the TOP and Describe how multiple tables may be types. OFFSET-FETCH Options queried in a SELECT statement using Using Conversion Functions Working with Unknown and Missing joins.

Using Logical Functions ValuesAfter completing this module, Write queries that use inner joins. Using Functions to Work with NULLAfter students will be able to: Write queries that use outer joins. completing this module, students will be able Filter data with predicates in the WHERE Write queries that use self-joins and cross

to: clause. joins.

Write queries with built-in scalar functions. Sort data using ORDER BY. Filtering Data with a WHERE Clause Use conversion functions. Filter data in the SELECT clause with Filtering with the TOP and

Use logical functions. TOP. OFFSET-FETCH Options

Use functions that work with NULL. Filter data with OFFSET and FETCH. Working with Unknown and Missing Using the GROUP BY Clause Working with Character Data ValuesAfter completing this module, Filtering Groups with HAVINGAfter Working with Date and Time DataAfter students will be able to:

completing this module, students will be able completing this module, students will be Filter data with predicates in the WHERE

to: able to: clause.

Write queries which summarize data using Describe numeric data types, type Sort data using ORDER BY. built-in aggregate functions. precedence and type conversions. Filter data in the SELECT clause with Use the GROUP BY clause to arrange rows Write queries using character data types. TOP.

into groups. Write queries using date and time data Filter data with OFFSET and FETCH. Use the HAVING clause to filter out groups types. Working with Character Data

based on a search condition. Using Conversion Functions Working with Date and Time DataAfter Writing Correlated Subqueries Using Logical Functions completing this module, students will be Using the EXISTS Predicate with Using Functions to Work with NULLAfter able to:

SubqueriesAfter completing this module, completing this module, students will be Describe numeric data types, type students will be able to: able to: precedence and type conversions. Describe the uses of queries which are Write queries with built-in scalar Write queries using character data types. nested within other queries. functions. Write queries using date and time data Write self-contained subqueries which return Use conversion functions. types.

scalar or multi-valued results. Use logical functions. Using Conversion Functions Write correlated subqueries which return Use functions that work with NULL. Using Logical Functions

scalar or multi-valued results. Using the GROUP BY Clause Using Functions to Work with NULLAfter Use the EXISTS predicate to efficiently Filtering Groups with HAVINGAfter completing this module, students will be check for the existence of rows in a completing this module, students will be able to:

subquery. able to: Write queries with built-in scalar functions. Using Common Table Expressions Write queries which summarize data Use conversion functions.

Using Views using built-in aggregate functions. Use logical functions.

Using Inline Table-Valued FunctionsAfter Use the GROUP BY clause to arrange Use functions that work with NULL. completing this module, students will be able rows into groups. Using the GROUP BY Clause to: Use the HAVING clause to filter out Filtering Groups with HAVINGAfter Write queries which use derived tables. groups based on a search condition. completing this module, students will be Write queries which use common table Writing Correlated Subqueries able to:

expressions. Using the EXISTS Predicate with Write queries which summarize data using Create simple views and write queries SubqueriesAfter completing this module, built-in aggregate functions.

against them. students will be able to: Use the GROUP BY clause to arrange Create simple inline table-valued functions Describe the uses of queries which are rows into groups.

and write queries against them. nested within other queries. Use the HAVING clause to filter out groups Using EXCEPT and INTERSECT Write self-contained subqueries which based on a search condition.

Using APPLYAfter completing this module, return scalar or multi-valued results. Writing Correlated Subqueries students will be able to: Write correlated subqueries which return Using the EXISTS Predicate with Write queries which combine data using the scalar or multi-valued results. SubqueriesAfter completing this module, UNION operator Use the EXISTS predicate to efficiently students will be able to:

Write queries which compare sets using the check for the existence of rows in a Describe the uses of queries which are INTERSECT and EXCEPT operators subquery. nested within other queries.

Write queries which manipulate rows in a Using Common Table Expressions Write self-contained subqueries which table by using APPLY with the results of a Using Views return scalar or multi-valued results. derived table or function Using Inline Table-Valued FunctionsAfter Write correlated subqueries which return Exploring Window Functions completing this module, students will be scalar or multi-valued results.

Restrict window functions to rows defined in able to: Use the EXISTS predicate to efficiently an OVER clause, including partitions and Write queries which use derived tables. check for the existence of rows in a frames. Write queries which use common table subquery.

(14)

operate on a window of rows and return Create simple views and write queries Using Views

ranking, aggregation and offset comparison against them. Using Inline Table-Valued FunctionsAfter results. Create simple inline table-valued completing this module, students will be Working with Grouping SetsAfter completing functions and write queries against them. able to:

this module, students will be able to: Using EXCEPT and INTERSECT Write queries which use derived tables. Write queries which pivot and unpivot result Using APPLYAfter completing this Write queries which use common table sets module, students will be able to: expressions.

Write queries which specify multiple Write queries which combine data using Create simple views and write queries groupings with grouping sets the UNION operator against them.

Executing System Stored Procedures Write queries which compare sets using Create simple inline table-valued functions Querying Dynamic Management the INTERSECT and EXCEPT operators and write queries against them.

ObjectsAfter completing this module, Write queries which manipulate rows in a Using EXCEPT and INTERSECT

students will be able to: table by using APPLY with the results of Using APPLYAfter completing this module, Write queries that retrieve system metadata a derived table or function students will be able to:

using system views and functions. Exploring Window Functions Write queries which combine data using Execute system stored procedures to return Restrict window functions to rows defined the UNION operator

system information. in an OVER clause, including partitions Write queries which compare sets using Write queries that retrieve system metadata and frames. the INTERSECT and EXCEPT operators and state information using system dynamic Write queries which use window Write queries which manipulate rows in a management views and functions. functions to operate on a window of rows table by using APPLY with the results of a Passing Parameters to Stored Procedures and return ranking, aggregation and derived table or function

Creating Simple Stored Procedures offset comparison results. Exploring Window Functions

Working with Dynamic SQLAfter completing Working with Grouping SetsAfter Restrict window functions to rows defined this module, students will be able to: completing this module, students will be in an OVER clause, including partitions Return results by executing stored able to: and frames.

procedures. Write queries which pivot and unpivot Write queries which use window functions Pass parameters to procedures. result sets to operate on a window of rows and return Create simple stored procedures which Write queries which specify multiple ranking, aggregation and offset

encapsulate a SELECT statement. groupings with grouping sets comparison results.

Construct and execute dynamic SQL with Executing System Stored Procedures Working with Grouping SetsAfter EXEC and sp_executesql. Querying Dynamic Management completing this module, students will be Controlling Program FlowAfter completing ObjectsAfter completing this module, able to:

this module, students will be able to: students will be able to: Write queries which pivot and unpivot Describe the language elements of T-SQL Write queries that retrieve system result sets

used for simple programming tasks. metadata using system views and Write queries which specify multiple Describe batches and how they are handled functions. groupings with grouping sets by SQL Server. Execute system stored procedures to Executing System Stored Procedures Declare and assign variables and synonyms. return system information. Querying Dynamic Management Use IF and WHILE blocks to control Write queries that retrieve system ObjectsAfter completing this module, program flow. metadata and state information using students will be able to:

Working with Error InformationAfter system dynamic management views and Write queries that retrieve system completing this module, students will be able functions. metadata using system views and

to: Passing Parameters to Stored functions.

Describe SQL Server's behavior when errors Procedures Execute system stored procedures to occur in T-SQL code. Creating Simple Stored Procedures return system information.

Implement structured exception handling in Working with Dynamic SQLAfter Write queries that retrieve system T-SQL. completing this module, students will be metadata and state information using Return information about errors from system able to: system dynamic management views and objects. Return results by executing stored functions.

Raise user-defined errors and pass system procedures. Passing Parameters to Stored Procedures errors in T-SQL code. Pass parameters to procedures. Creating Simple Stored Procedures Controlling TransactionsAfter completing Create simple stored procedures which Working with Dynamic SQLAfter this module, students will be able to: encapsulate a SELECT statement. completing this module, students will be Describe transactions and the differences Construct and execute dynamic SQL with able to:

between batches and transactions. EXEC and sp_executesql. Return results by executing stored Describe batches and how they are handled Controlling Program FlowAfter procedures.

by SQL Server. completing this module, students will be Pass parameters to procedures. Create and manage transactions with able to: Create simple stored procedures which transaction control language statements. Describe the language elements of encapsulate a SELECT statement. Use SET XACT_ABORT to define SQL T-SQL used for simple programming Construct and execute dynamic SQL with Server's handling of transactions outside tasks. EXEC and sp_executesql.

TRY / CATCH blocks. Describe batches and how they are Controlling Program FlowAfter completing Displaying Query Performance DataAfter handled by SQL Server. this module, students will be able to: completing this module, students will be able Declare and assign variables and Describe the language elements of T-SQL

to: synonyms. used for simple programming tasks.

Figure

Updating...