70-461 Session 2: Querying Microsoft SQL Server 2012

T-SQL tables, SELECT queries (FROM, WHERE, GROUP BY etc), JOIN tables, and INSERT/DELETE/UPDATE code using SSMS
4.6 (26 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.
214 students enrolled
$19
$20
5% off
Take This Course
  • Lectures 23
  • Length 3.5 hours
  • Skill Level Beginner 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".

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.

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

What am I going to get from this course?

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

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

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: Welcome to Session 2
01:59

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

Section 2: Creating and querying part of a table
Resources
Article
11:39

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

10:56

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

Spreadsheet Data
Article
11:01

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

2 questions

A few questions, if I may.

09:40

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

3 questions

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

Section 3: Summarising and ordering data
12:53

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.

2 questions

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

14:40

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.

4 questions

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

15:41

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

Section 4: Adding a second table
08:45

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

12:13

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

New spreadsheet data
Article
10:35

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.

09:22

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.

2 questions

Two questions about what you've just learned.

12:53

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

3 questions

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

15:30

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.

13:50

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

1 question

Quick question:

Section 5: Find missing data, and delete and update data
10:46

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.

1 question

I've got a question...

08:21

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.

3 questions

A couple of questions.

09:06

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

2 questions

Last quiz of the session. Here goes...

Section 6: End of Session 2
03:44

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

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

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