70-461 Session 2: Querying Microsoft SQL Server (T-SQL)
4.5 (102 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.
576 students enrolled
Wishlisted Wishlist

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

Add to Wishlist

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

T-SQL tables, SELECT queries (FROM, WHERE, GROUP BY), JOIN tables, INSERT/DELETE/UPDATE. Server 2012, 2014, 2016, 70-761
4.5 (102 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.
576 students enrolled
Created by Phillip Burton
Last updated 12/2016
English
Current price: $10 Original price: $50 Discount: 80% off
30-Day Money-Back Guarantee
Includes:
  • 3.5 hours on-demand video
  • 3 Articles
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • create tables in a database
  • ALTER columns in the table.
  • retrieve data using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.
  • JOIN two or more tables together, finding missing data.
  • INSERT new data, UPDATE and DELETE existing data, and export data INTO a new table.
  • complete objective 1, and work towards other objectives from Microsoft Certification 70-461 "Querying Microsoft SQL Server 2012"
View Curriculum
Requirements
  • You need to know all about data types and functions. If you don't, you should take the Session 1 course first.
  • You should also know how to open SSMS.
  • 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

Reviews

"The instructor explain the things in great details. Very easy to follow." - Linda Shen

"Excellent course, valuable lessons, very well taught at a great pace." - Shane Tanberg

"Must get tutorial. Love it" - Hayford I Osumanu

"Perfect step by step guide to learning. Best I've seen." - Charles Schweiger

"This course is very well thought out. Its one of the better 70-461 courses on Udemy." - Isrrael M

-------------------------

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 session 1 we learnt all about dates, strings and number data types and functions in some detail.

We'll create tables which use these, and then INSERT some data into them. Then we'll write queries which will retrieve and summary this data, using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.

We'll then JOIN these tables together to find where we are missing data and where we have inconsistent data. We'll then UPDATE and DELETE data from the tables. This will allow up to fully complete objective number 1 from the 70-461 exam.

If this sounds too basic, then please look at my other sessions.

The only prior knowledge which is required for Session 2 is that which has been taught in Session 1.

There are regular quizzes to help you remember the information.

Once finished, you will know what how to create tables, insert data and create analyses, and have an appreciation of how they can all be used in 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.
  • If you are starting from the beginning, then you should look at my session 1 course before taking this session 2.
  • If you want more advanced topics, then please look at my later sessions.
Curriculum For This Course
23 Lectures
03:26:12
+
Welcome to Session 2
1 Lecture 01:59

I'll talk about what we learnt in Session 1, and what we will learn in this Session 2.

Preview 01:59
+
Creating and querying part of a table
6 Lectures 43:39
Resources
00:11

We will recreate the Employee table we tried to create earlier, and will consider what the appropriate data types should be used.

Creation of tblEmployee table
11:39

We will create a new column in existing tables , and then alter it.

Adding additional columns
10:56

Spreadsheet Data
00:11

We will use the WHERE clause to retrieve only part of a table, and the LIKE clause to use pattern matching.

SELECTing only part of a table - strings
11:01

A few questions, if I may.

SELECTing only part of a table - strings
2 questions

Still using the WHERE clause, we will use =, <, >, <=, >=, !, NOT, BETWEEN and IN.

Preview 09:40

Let's see what you remember about using the WHERE with numbers

SELECTing only part of a table - numbers
3 questions
+
Summarising and ordering data
3 Lectures 43:14

We investigate the WHERE clause for dates, and use a date criteria to summarise data, using the GROUP BY clause, and then order it using the ORDER BY clause.

Summarising and ordering data
12:53

That's a lot to remember. Let's test yourself, and maybe learn a bit more!

Summarising and ordering data
2 questions

The GROUP BY reduced the number of rows to look at. What if we want to run criteria on this summary? We look at the HAVING clause.

Criteria on summarised data
14:40

It's important to get the clauses in the right order - let's see if you can remember them.

Criteria on summarised data
4 questions

We do an exercise, based on what we have learnt so far.W

Exercise
15:41
+
Adding a second table
8 Lectures 01:23:20

In this lecture we will start designing a Transaction table. We'll consider what we need, and what data types should be used.

Adding a second table
08:45

Having designed part of tblTransaction, how can we in computing terms link it back to tblEmployee? We'll look at the possibilities.

Designing a connection
12:13

New spreadsheet data
00:12

We'll import random data that I've generated, using the MAX function to determine the limit of that data, and then graphically show these two tables together.

Importing data and showing tables graphically
10:35

In this lecture we'll write a query which JOINs both of these tables together. We'll look at aliasing the tables as part of the JOIN, and referring to the tables in the SELECT clause.

Writing a JOIN query
09:22

Two questions about what you've just learned.

Writing a JOIN query
2 questions

We'll look at the different types of JOIN - INNER, LEFT, RIGHT and CROSS (and why you probably shouldn't use the latter).

Different types of JOIN
12:53

Let's see if you can remember the different types of JOIN

Different types of JOIN
3 questions

Do you remember that Department field? Let's create a table based on that, and alter our tblEmployee to link to that table more efficiently. Then, let's create a table JOINing all three.

Creating a third table
15:30

Now we are going to join three tables together in a SELECT command, and use aliases for the table.

Preview 13:50

Quick question:

JOINing three tables
1 question
+
Find missing data, and delete and update data
3 Lectures 28:13

We'll use the LEFT and RIGHT joins to find Employees who have no transactions, and transactions which do not have a valid employee number.

Missing data
10:46

I've got a question...

Missing data
1 question

We'll update some of the transactions to correct the EmployeeNumber, and delete transactions which have an invalid number. We'll also investigate TRANsactions, with BEGIN TRAN, COMMIT TRAN and ROLLBACK TRAN.

Deleting data
08:21

A couple of questions.

Deleting data
3 questions

Finally, we will update rows based on certain criteria, and looking at the OUTPUT clause, with the two tables "inserted" and "deleted".

Updating data
09:06

Last quiz of the session. Here goes...

Updating data
2 questions
+
End of Session 2
2 Lectures 05:46

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:44

We'll recap what we learned in this Session 2, and look forward to what we can learn in Session 3.

Thank you
02:02
About the Instructor
Phillip Burton
4.4 Average rating
3,262 Reviews
45,998 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.