70-461 Session 2: Querying Microsoft SQL Server 2012
Use the coupon code SQLINTRO to save 50%
About This Course
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.
Take This Course
(That’s $3 per hour!)
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 70-461 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.
Video: 3.5 hours
Skill Level: Beginner Level
Includes: Lifetime access
30 day money back guarantee!
Available on iOS and Android
Certificate of Completion
Preview video 13
(Why not view the below video full screen? Click the YouTube logo in the bottom-right hand corner.)
Want to see more preview videos?
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?
Why not have a look at the curriculum below and see what you can learn? If you already know them, please see my later sessions.
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.
|Section 1: Welcome to Session 2|
|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|
|We will recreate the Employee table we tried to create earlier, and will consider what the appropriate data types should be used.|
|We will create a new column in existing tables , and then alter it.|
|We will use the WHERE clause to retrieve only part of a table, and the LIKE clause to use pattern matching.|
|Quiz 1||2 questions|
|A few questions, if I may.|
|Still using the WHERE clause, we will use =, <, >, <=, >=, !, NOT, BETWEEN and IN.|
|Quiz 2||3 questions|
|Let’s see what you remember about using the WHERE with numbers|
|Section 3: Summarising and ordering data|
|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.|
|Quiz 3||2 questions|
|That’s a lot to remember. Let’s test yourself, and maybe learn a bit more!|
|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.|
|Quiz 4||4 questions|
|It’s important to get the clauses in the right order – let’s see if you can remember them.|
|We do an exercise, based on what we have learnt so far.W|
|Section 4: Adding a second table|
|In this lecture we will start designing a Transaction table. We’ll consider what we need, and what data types should be used.|
|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
|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.|
|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.|
|Quiz 5||2 questions|
|Two questions about what you’ve just learned.|
|We’ll look at the different types of JOIN – INNER, LEFT, RIGHT and CROSS (and why you probably shouldn’t use the latter).|
|Quiz 6||3 questions|
|Let’s see if you can remember the different types of JOIN|
|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.|
|Now we are going to join three tables together in a SELECT command, and use aliases for the table.|
|Quiz 7||1 question|
|Section 5: Find missing data, and delete and update data|
|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.|
|Quiz 8||1 question|
|I’ve got a 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.|
|Quiz 9||3 questions|
|A couple of questions.|
|Finally, we will update rows based on certain criteria, and looking at the OUTPUT clause, with the two tables “inserted” and “deleted”.|
|Quiz 10||2 questions|
|Last quiz of the session. Here goes…|
|Section 6: End of Session 2|
|We’ll look back at this session, look forward to the next session.|
Do you have a question – why not ask it in the box below?Take This Course