Part 1 of 2 in the SQL Server 2012 Querying (70-461) series is designed to teach students how to query and work with a SQL Server 2012 database. Skills gained will include querying a database using joins and data aggregations and query optimization. This course includes basic management of database objects and using XML SQL Server.
Introduction to Relational Databases For those of you that are new to SQL Server and maybe databases, in general, there's quite a few different moving parts, a lot of terminology, and a lot of objects. And as a result it can be a little bit intimidating for people that are just trying to get into it. So what I want to do with this lesson, Introduction to Relational Databases, is hopefully start to clear some of that fog that you might have as you get in and start exploring inside of SQL Server. Now what we want to do is we want to define what a database is, what a database can be used for, talk about some of the different moving parts like tables, columns, stored procedures. Talk a little bit about how to design a database and, more importantly, why your database developer, why your DBA, may have made some of the choices that she did. And then finish it all off talking a little bit about a couple of cool little items that we're going to investigate as we go through the rest of the course.
Aggregating Data Quite frequently you or your users are going to be looking for information from the database that sums up information, gives you an average, or, basically, just gives you a bigger picture. Up until now, the queries that we've examined would allow us to go find orders for a particular product, addresses for a particular customer, things like that, but what happens if I want to go find the most popular products, maybe based on total profit, maybe based on the total quantity sold, maybe just simply based on the total of amount of money that was spent on those products. Well that's where aggregations come into play, and that's what we want to focus in on in this lesson of Aggregating Data. Now our main focus is going to, first of all, be on the select line, because it's going to be on the select line where you'll be placing the aggregate functions and then we're also going to be focusing a little bit on the group by and the having line. As we're going to see, the group by is going to be used to group things together, so, for example, if I'm looking for products inside a particular category or grouped by a particular category, then I could do that with a group by and then my having will allow me to eliminate groups. So if I'm looking maybe only our most popular product, then I could do that by utilizing a having statement.
Query Optimization Now that we've spent quite a bit of time creating queries, it's time to turn our attention to optimizing those queries and making sure that they're going to execute as quickly as possible. Now we talked about a few little things while we were discussing creating our queries. Now we want to focus a little bit more on some of the advanced topics. Now with that little disclaimer being tossed out there, these are advanced topics, I do want to highlight the fact that getting in and truly optimizing queries does take a fair amount of experience inside of SQL and some of the objects that we're going to be discussing are going to fall under the domain of your database developer, your database administrator. So some of the things that we'll be discussing in here, you may not have access to do when you get back to your desk.
Advanced Data Modification Having seen how to modify data inside of a SQL database, it's now time to turn our attention to a couple of advanced topics. The first thing that we want to take a look at is the OUTPUT keyword, which can allow you to very easily access the data that's been modified through your statement. And then we want to take a look at a MERGE statement, which allows us take data from one table and combine it with a table from another and then depending on what's currently in existence or not in existence, be able to update, delete, or even insert brand-new rows into that target table.