70-461 Session 6: Querying Microsoft SQL Server (T-SQL code)

Using SQL, correlated subqueries, CTEs, Pivots, UDFs and Sequences. SQL Server 2012 to 2022, including SQL Server 2019

What you'll learn:

• Learn all about subqueries, using them in the SELECT, FROM and WHERE clauses.
• Learn about correlated subqueries, where the results of the subquery depends on the main query.
• Learn about Common Table Expressions (CTE), using the WITH statement.
• Use ROW_NUMBER, LAG and CTEs to group consecutive numbers.
• Learn about PIVOT and UNPIVOT to spread data across columns.
• Find out all about functions, including UDFs (user defined functions): scalar, inline table and multi-statement table functions
• "Join" the functions into tables using the CROSS APPLY and OUTER APPLY.
• Learn about synonyms, dynamic SQL and SQL injection.
• Find out about uniqueidentifiers (NEWID and NEWSEQUENTIALID functions), and create and use Sequences.

This course is the foundation for the Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012/2014". Please note - this certificate is no longer being offered by Microsoft. However, the exam requirements allow you to have a good understanding of T-SQL.

Welcome to Session 6 of this T-SQLcourse.

We'll start with creating sub-queries, in the WHERE, FROMand SELECT clause. We'll be created correlated subqueries, where the results of the subquery depend on the main query. We'll be looking at Common Table Expressions (CTE) using the WITH statement and PIVOTting and UNPIVOTting, and we'll be using what we have learned to solve a common business problem.

We'll be looking at functions, including the three different types of User Defined Functions (UDF): scalar functions, inline table functions, and multi-statement table functions. We'll then look at synonyms, dynamic SQL, and GUIDs. We'll also look at sequences.

Finally, we'll look at use XMLdata, shredding and querying it, manipulating JSON data, and exploring Temporal Data, so you can query data that used to be in your tables.

No prior knowledge other than what we covered in Sessions 1 to 5 is required. This course builds on the knowledge previously gained in those previous sessions.

There are regular quizzes to help you remember the information.

Once finished, you will know what how to write subqueries, functions, sequences and more, and we'll have expanded on our current knowledge of T-SQL.