SQL Server video course – Session 6

70-461 Session 6: Querying Microsoft SQL Server 2012

Building on Sessions 1-5, we’ll look at correlated subqueries, CTEs, Pivots, UDFs, uniqueidentifiers and Sequences.

Use the coupon code SQLINTRO to save 50%

About This Course

This course is the foundation for the Microsoft Certificate 70-461: “Querying Microsoft SQL Server 2012”.

Rather than present one huge course, this course takes some of the basics and then goes in depth. In Sessions 1 and 2, we learned all about dates, strings and number data types and DML statements. In sessions 3 and 4, we created views, procedures, triggers, constraints and combining datasets, and in session 5 we learned about ranking and aggregate functions.

$20  $10
Take This Course

(That’s $3 per hour!)

We’ll will now be creating sub-queries, working through objectives 7b-e of the exam 70-461. 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 using the WITH statement, and we’ll be using what we have learned to solve a common business problem.

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

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.

Lectures: 32
Video: 3.5 hours
Skill Level: Intermediate Level
Languages: English
Includes: Lifetime access
30 day money back guarantee!
Available on iOS and Android
Certificate of Completion

Preview video 13

(Why not view the below video full screen? Click the YouTube logo in the bottom-right hand corner.)

Want to see more preview videos?

What are the requirements?

You need to know about data types and functions, SELECT queries, and ranking functions.
If you don’t, then you should take my earlier Sessions first.
There is a 30-day money back guarantee of this Udemy course.

Why not have a look at the curriculum below and see what you can learn? If you already know them, please see my other sessions.

What am I going to get from this course?

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

What is the target audience?

  • This course is for you if want a refresher course in SQL, or are learning SQL for the first time.
  • This course is for you if you are working towards the requirements for exam 70-461, or if you just want to expand your knowledge of T-SQL.
  • If you are starting from the beginning, then you should look at my sessions 1 to 5 courses before taking this session 6.


Section 1: Introduction
Welcome back. A quick recap of where we are.
We’ll have a look at the structure of this session, and what we are going to learn.
Section 2: Objective 7: Sub-queries
A reminder of the tables that we created all the way back in Session 2.
We’ll have a look at using subqueries in the WHERE clause.
We’ll have a look at using subqueries in the WHERE clause, and how much care you have to use with using the word NOT.
We’ll expand our use of the WHERE clause by looking at ANY, SOME and ALL.
We’ll be looking at the use of subqueries in the FROM clause.
We’ll look at using subqueries in the SELECT clause, and see that we have to use correlated subqueries in the SELECT clause.
We’ll revisit the WHERE clause, and use correlated subqueries in the WHERE clause.
We’ll look at objective 6a, and use a subquery to obtain the top 5 from various categories.
Section 3: Objective 7e: WITH statement
We’ll introduce the WITH statement, and see how it can clarify our code.
We’ll find out what Employee Numbers have not been used, and find out why we have to generate a list of numbers using WITH and ROW_NUMBER to do so.
We’ll summarise the above table by grouping together consecutive numbers.
Section 4: Objective 7b: PIVOTing and UNPIVOTing
We’ll look at creating a PivotTable in Excel, and doing the same thing in SQL Server.
Unfortunately, the PIVOT function creates a lot of NULLs. We’ll see how to change them to zero,
We’ll do the reverse of PIVOT, and unspread columns back into rows.
Section 5: Objective 7d: CTE statement
We’ll add a Manager column to the tblEmployee, and find out why we have to a self-join to get the Manager’s name.
We’ll work out the number of bosses an employee has, by using a recursive CTE.
Section 6: Objective 14: Functions
We’ll have a look at objective 14, and see what we already know (and briefly revisit it).
We’ll create our first, basic, function.
We’ll write a second function, a bit more complicated.
We’ll write a function which uses one SELECT statement to return a table.
We’ll write a more complex function, creating a table and populating it with the results.
We’ll “join” the results of a function with a table.
Section 7: Objectives 6b and 6c: Synonyms and Dynamics
We’ll create another version of an alias.
We’ll use a string to create a SELECT statement, and find out how that could be dangerous.
Section 8: Objectives 8c: GUIDs; and Sequences
We’ll revisit the IDENTITY default, and see what the disadvantages are.
We’ll look at the uniqueidentifiers NEWID and NEWSEQUENTIALID, and how to use them.
We’ll create a sequence, and look at all the options.
Having created a SEQUENCE, how can we get the next value?
Section 9: Conclusion

Do you have a question – why not ask it in the box below?

Take This Course

Comments are closed.