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

Brief Summary

This course dives into T-SQL by focusing on subqueries, CTEs, and functions like UDFs while tackling real business problems with SQL Server. It's a great learning journey from SQL basics to more advanced concepts like XML data and sequences.

About This Course

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

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-SQL course.

We'll start with creating sub-queries, in the WHERE, FROM and 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 XML data, 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.

• 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.