70-461 Session 4: Querying Microsoft SQL Server 2012
4.9 (17 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.
291 students enrolled
Wishlisted Wishlist

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

Add to Wishlist

70-461 Session 4: Querying Microsoft SQL Server 2012

Using T-SQL code, we'll merge databasets together, create procedures including queries and incorporate error handling.
4.9 (17 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.
291 students enrolled
Created by Phillip Burton
Last updated 5/2016
English
Learn Fest Sale
Current price: $10 Original price: $30 Discount: 67% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 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
Requirements
  • 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?
Description

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
Expand All 25 Lectures Collapse All 25 Lectures 03:28:39
+
Introduction
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

Resources
00:11
+
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.

UNION and UNION ALL
12:30

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

Intercept and Except
11:28

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

CASE statement
13:58

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

IsNull and Coalesce
13:33

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

MERGE statement - in theory
13:26

We'll create our first MERGE statement.

Let's Build our MERGE statement
11:12

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
11:21

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

Summary
02:01
+
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
09:58

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
11:57

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
04:59

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

While
12:10

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

Return
13:06

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

Summary
03:00
+
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
11:18

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

TRY/CATCH
09:46

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

THROW and RAISERROR
10:12

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.

Summary
01:19
+
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
03:14

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

Well done!
02:29
About the Instructor
Phillip Burton
4.4 Average rating
2,446 Reviews
37,115 Students
23 Courses
Over 35,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.