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.
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.
A few questions, if I may.
Still using the WHERE clause, we will use =, <, >, <=, >=, !, NOT, BETWEEN and IN.
Let's see what you remember about using the WHERE with numbers
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.
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.
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
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.
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.
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).
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.
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.
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.
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".
Last quiz of the session. Here goes...
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.
We'll recap what we learned in this Session 2, and look forward to what we can learn in Session 3.
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.