70-461 Session 6: Querying Microsoft SQL Server (SQL code)
4.5 (181 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,865 students enrolled

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

Using SQL, correlated subqueries, CTEs, Pivots, UDFs and Sequences. SQL Server 2012 to 2017 (inc. 70-761).
4.5 (181 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,865 students enrolled
Created by Phillip Burton
Last updated 9/2018
English
English [Auto-generated]
Current price: $34.99 Original price: $49.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 5.5 hours on-demand video
  • 2 articles
  • 3 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
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.
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?
Description

If you are in UfB, welcome.

If you are not, don't buy this course. Instead, please look at my 70-461 Session 1-7: Querying Microsoft SQL Server (SQL code) course instead, which includes this course and 6 other courses.

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

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.

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.

Who this course is for:
  • 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.
  • If you want more advanced topics, then please look at my other sessions.
Course content
Expand all 52 lectures 05:32:04
+ Introduction
4 lectures 05:57

Welcome back. A quick recap of where we are.

Preview 02:57

We'll have a look at the structure of this session, and what we are going to learn.

Preview 02:33
Is this your first session?
00:16
Resources
00:11
+ Objective 7: Sub-queries
8 lectures 01:00:39

A reminder of the tables that we created all the way back in Session 2.

Table structure
04:25

We'll have a look at using subqueries in the WHERE clause.

The WHERE clause
06:26

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.

WHERE and NOT
05:21

We'll expand our use of the WHERE clause by looking at ANY, SOME and ALL.

ANY, SOME and ALL
08:54

We'll be looking at the use of subqueries in the FROM clause.

Preview 10:58

We'll look at using subqueries in the SELECT clause, and see that we have to use correlated subqueries in the SELECT clause.

The SELECT clause
10:38

We'll revisit the WHERE clause, and use correlated subqueries in the WHERE clause.

Correlated subquery - WHERE
06:54
Sub-query
3 questions

We'll look at objective 6a, and use a subquery to obtain the top 5 from various categories.

Top 5 from various categories
07:03
+ Objective 7e: WITH statement
3 lectures 24:53

We'll introduce the WITH statement, and see how it can clarify our code.

WITH statement
05:23

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.

Generating a list of numbers
09:29

We'll summarise the above table by grouping together consecutive numbers.

Grouping numbers
10:01
WITH statement
2 questions
+ Objective 7b: PIVOTing and UNPIVOTing
3 lectures 17:44

We'll look at creating a PivotTable in Excel, and doing the same thing in SQL Server.

PIVOT
07:50

Unfortunately, the PIVOT function creates a lot of NULLs. We'll see how to change them to zero,

Replacing NULLs in PIVOTs.
06:40

We'll do the reverse of PIVOT, and unspread columns back into rows.

UNPIVOT
03:14
Pivotting and Unpivotting
3 questions
+ Objective 7d: CTE statement
2 lectures 22:00

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.

Preview 09:04

We'll work out the number of bosses an employee has, by using a recursive CTE.

Recursive CTE
12:56
Recursive CTE
1 question
+ Objective 14: Functions
6 lectures 39:59

We'll have a look at objective 14, and see what we already know (and briefly revisit it).

Introduction
05:12

We'll create our first, basic, function.

Scalar Functions
08:58

We'll write a second function, a bit more complicated.

A more complicated scalar function
06:11

We'll write a function which uses one SELECT statement to return a table.

Inline Table Function
05:33

We'll write a more complex function, creating a table and populating it with the results.

Multi-statement Table Function
05:34

We'll "join" the results of a function with a table.

APPLY
08:31
Functions
4 questions
+ Objectives 6b and 6c: Synonyms and Dynamics
2 lectures 14:15

We'll create another version of an alias.

Preview 07:26

We'll use a string to create a SELECT statement, and find out how that could be dangerous.

Dynamic SQL
06:49
Dynamic SQL
2 questions
+ Objectives 8c: GUIDs; and Sequences
4 lectures 25:01

We'll revisit the IDENTITY default, and see what the disadvantages are.

Preview 03:06

We'll look at the uniqueidentifiers NEWID and NEWSEQUENTIALID, and how to use them.

GUIDs
08:26

We'll create a sequence, and look at all the options.

Creating SEQUENCEs
05:47

Having created a SEQUENCE, how can we get the next value?

Using SEQUENCEs
07:42
GUIDs and SEQUENCEs
3 questions
+ Objective 10: Query and manage XML data - converting tables to XML
7 lectures 49:26
Introduction to XML - Let's make a shopping list
05:48
Attributes
07:58
Creating XML variable and XML field
06:02
FOR XML RAW
07:28
FOR XML AUTO
04:12
FOR XML PATH
09:58
FOR XML EXPLICIT
08:00
+ Objective 10: Shredding XML data
6 lectures 36:00
XQuery Value and Exist methods
06:41
XQuery Modify method
05:32
XQuery Query method using for and return
06:25
XQuery Query method using let, where and order by (FLWOR)
05:32
XQuery Nodes - shredding a variable
06:09
XQuery Nodes - shredding a table
05:41