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.
We'll be looking at UNION and UNION ALL, how to use them, and seeing the difference between them.
How to compare datasets - we'll be looking at INTERCEPT and EXCEPT, and comparing it with UNION.
We'll be looking both varieties of the CASE statement, and looking at the potential problems.
We'll be looking at functions which give non-NULL answers, and comparing and contrasting them.
We'll be combining two datasets, with updating original rows in the Target data with the Source data.
We'll create our first MERGE statement.
We'll build our MERGE statement on a grouped query instead of a table.
We'll add a comments column, and expand our MERGE statement further.
We've not only done objective 13, but objective 12 as well!
We'll get straight into it, creating our first procedure, and using them as an Data Access Layer.
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.
A small diversion. We'll expand our procedure and investigate the IF statement.
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.
We'll branch out and make loops, by returning multiple SELECT statements, and find out why you shouldn't.
Let's get data out of a procedure, using OUTPUT and RETURN.
We'll review what we have just learned, and link it to the next objective.
We'll create a new procedure, and find out that errors can happen...
We'll implement error trapping, so that errors can be caught without the program execution stopping.
We'll re-throw an error previously caught, and raise new errors.
We'll add PRINT statements to debug our routines and communicate with the end user.
We'll recap what we have learned this objective and this session.
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 just recap on what we have learned in Session 4, and look at what we can learn in Session 5.
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.