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.1 (7 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.
157 students enrolled
$19
$20
5% off
Take This Course
  • Lectures 25
  • Length 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
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 7/2015 English

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

What are the 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?

What am I going to get from this course?

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

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 session 1-3 courses before taking this session 4.
  • If you want more advanced topics, then please look at my later sessions.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
02:09

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

Resources
Article
Section 2: Objective 13: Combine database
12:30

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

11:28

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

13:58

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

13:33

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

13:26

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

11:12

We'll create our first MERGE statement.

09:22

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

11:21

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

02:01

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

Section 3: Objective 11 - Create and alter stored procedures (simple statements)
09:58

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

11:57

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.

05:14

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

04:59

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.

12:10

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

13:06

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

03:00

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

Section 4: Objective 18a - Implement try/catch/throw
11:18

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

09:46

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

10:12

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

08:46

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

01:19

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

Section 5: End of Session 4!
03:14

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.

02:29

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

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Phillip Burton, SQLIntro.com

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.

Ready to start learning?
Take This Course