70-461 Session 4: Querying Microsoft SQL Server 2012 (T-SQL)
4.5 (32 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
432 students enrolled
Wishlisted Wishlist

Please confirm that you want to add 70-461 Session 4: Querying Microsoft SQL Server 2012 (T-SQL) to your Wishlist.

Add to Wishlist

70-461 Session 4: Querying Microsoft SQL Server 2012 (T-SQL)

Using TSQL code, merge databasets together, create procedures and incorporate error handling. SQL 2012-2016, inc. 70-761
4.5 (32 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
432 students enrolled
Created by Phillip Burton
Last updated 5/2016
Curiosity Sale
Current price: $10 Original price: $50 Discount: 80% off
30-Day Money-Back Guarantee
  • 3.5 hours on-demand video
  • 1 Article
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Combine and compare datasets using UNION, UNION ALL, INTERSECT and EXCEPT
  • Understand the difference between and use of CASE, ISNULL and COALESCE
  • MERGE two tables together, taking different actions if similar rows exist in both tables than if they don't.
  • Create parameterised PROCEDUREs, allowing you to store multiple commands together for later use.
  • EXECUTE those procedures, and add OUTPUT parameters to obtain RESULTs.
  • Introduce error checking with TRY, CATCH, THROW and more!
View Curriculum
  • You need to know all about data types and functions.
  • You also need to know about how to create SELECT statements and INSERT, DELETE and UPDATE statements.
  • It would be helpful if you knew about views, triggers and triggers
  • If you don't, then please take my Sessions 1-3 courses 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?

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

In Session 1, we learned all about dates, strings and number data types, and in Session 2 we constructed SELECT queries, JOINed multiple tables together and used DML commands, and in Session 3 we created views, constraints and triggers to encapsulate language and improve security.

We will further encapsulate our routines by creating procedures, allowing us to EXECUTE parameterised commands with just one statement, and we'll add some error handling with TRY, CATCH and THROW.

We'll also combine datasets together, by looking at UNION and UNION ALL, INTERSECT and EXCEPT, CASE, ISNULL and Coalesce, and the mighty MERGE statement. By doing this, we will complete objectives 11, 12, 13 and parts of 6 and 18 from the 70-461 exam.

No prior knowledge other than what we covered in Sessions 1 to 3 is required. However, prior experience with Excel or Access would be beneficial.

There are regular quizzes to help you remember the information.

Once finished, you will know what how to write procedures, add error handling, and combine datasets together, and we'll have expanded on our current knowledge of T-SQL.

Who 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 session 1-3 courses before taking this session 4.
  • If you want more advanced topics, then please look at my later sessions.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
25 Lectures
2 Lectures 02:20

We'll recap on sessions 1-3, and find out what we are going to learn in this session 4.

Preview 02:09

Objective 13: Combine database
9 Lectures 01:38:51

We'll be looking at UNION and UNION ALL, how to use them, and seeing the difference between them.


How to compare datasets - we'll be looking at INTERCEPT and EXCEPT, and comparing it with UNION.

Intercept and Except

We'll be looking both varieties of the CASE statement, and looking at the potential problems.

CASE statement

We'll be looking at functions which give non-NULL answers, and comparing and contrasting them.

IsNull and Coalesce

We'll be combining two datasets, with updating original rows in the Target data with the Source data.

MERGE statement - in theory

We'll create our first MERGE statement.

Let's Build our MERGE statement

We'll build our MERGE statement on a grouped query instead of a table.

Preview 09:22

We'll add a comments column, and expand our MERGE statement further.

Merge with additional columns

We've not only done objective 13, but objective 12 as well!

Objective 11 - Create and alter stored procedures (simple statements)
7 Lectures 01:00:24

We'll get straight into it, creating our first procedure, and using them as an Data Access Layer.

Let's create our first procedure

We'll revise our procedure, so that instead of returning all the employees, we'll return just one. We'll learn how to use arguments.

Ask for a specific employee

A small diversion. We'll expand our procedure and investigate the IF statement.

Preview 05:14

Instead of just one employee, let's return a range of them. We'll find out how to pass two values, and how to use named arguments.

Multiple arguments

We'll branch out and make loops, by returning multiple SELECT statements, and find out why you shouldn't.


Let's get data out of a procedure, using OUTPUT and RETURN.


We'll review what we have just learned, and link it to the next objective.

Objective 18a - Implement try/catch/throw
5 Lectures 41:21

We'll create a new procedure, and find out that errors can happen...

Procedure Exercise

We'll implement error trapping, so that errors can be caught without the program execution stopping.


We'll re-throw an error previously caught, and raise new errors.


We'll add PRINT statements to debug our routines and communicate with the end user.

Preview 08:46

We'll recap what we have learned this objective and this session.

End of Session 4!
2 Lectures 05:43

We'll look back at this session, look forward to the next session, and give you a voucher code so that you can save money on your next sessions.

BONUS - Recap, Look ahead and Voucher Code

We'll just recap on what we have learned in Session 4, and look at what we can learn in Session 5.

Well done!
About the Instructor
Phillip Burton
4.4 Average rating
3,285 Reviews
46,033 Students
25 Courses
Best Selling Instructor - over 45,000 students so far

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.

He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.

His interests are working with data, including Microsoft Excel, Access and SQL Server.