Manage Data with T-SQL

0:00To become a true T-SQL ninja, we must master the SELECT statement. So in this Nugget, we're going to break down the syntax of the SELECT statement and talk about logical processing order, which is an incredibly important concept to understand so you can efficiently and properly write your SELECT queries. Let's do it. The SELECT statement is primarily used to retrieve rows from tables, although there are other database objects we can write SELECT statements against as well, such as views and functions. We begin our SELECT queries by specifying the SELECT clause. This begins with the actual SELECT keyword itself, followed potentially by some options, and then a SELECT list.

0:37This is where we specify what columns we want to pull out of tables. The SELECT list can also contain expressions. These are things other than just columns from a table. These can be calculations. Maybe you need to multiply two columns together, or you need to pass a column into a function to perform an operation. And this is where aliases come into play. Aliases provide a name-- more commonly to expressions, but you could also use it to rename columns, as well. Now, in the SELECT clause we have two options. We have the distinct option.

1:08This, if we specify SELECT DISTINCT, will remove duplicate records-- being all of those columns combined have the exact same values, it will remove them. ALL is the default, and it's implied, which is why we don't need to specify it. And by the way, when you're reading syntax, and you see a bracket with a pipe, that means or. So either ALL or DISTINCT. And again, ALL is the default, and it's implied. We do need to pass in DISTINCT though, if we want to remove duplicates. We also have the TOP clause, and this is a limiter that will only return either the number of rows, which is the default-- so if we just pass in a number into this expression, it will only return, say, the top 10 rows that this query returns.

1:44And we can also pass in a percentage. So if we specify percent, then this number will be applied as a percentage to the total number of rows. We'll see how both of these work in our virtual lab Nugget titled writing basic SELECT queries. We'll cover the rest of these major clauses shortly with some examples in this Nugget, and even deeper in the future with their own Nuggets. Here's an example SELECT query that pulls back the number of employees hired by year, by job title in the AdventureWorks database, out of the HumanResources.Employee table. One of the most important things to understand for beginners and experts alike is the logical processing order of our SELECT queries.

2:20Knowing this-- and I really can't stress this enough-- will ensure that you properly form your queries the first time, which will lead to more efficient SQL developers. Here's how it works. We are used to composing or writing our SQL queries this way-- SELECT from, WHERE, GROUP BY, et cetera, just like we have here. So this list matches this list exactly. And that's because it's user-friendly. It's human-friendly. This is how it was designed, because it makes sense for us to write the name of the operation-- SELECT, followed by our columns, FROM which table, and then our filter, so on and so forth.

2:55But this is not how SQL logically processes it. Notice over here that FROM is actually processed first, and that's because it needs to know which table to pull these columns from. And by the way, I should point out that logical processing is different than physical processing, and that's because SQL Server does a lot of optimizations. It may take steps away, it may change steps within here, and it does so to more efficiently retrieve the rows. So logical processing is conceptual, but again, it's very important to understand because this defines the rules that we need to follow to properly write our SQL statements.

3:32Now, I like to think of this as one big data flow. So the FROM clause is going to kick everything off here, and it's going to pull back all of that raw data from the employee table. In this case, out of the AdventureWorks database, the employee table contains a total of 290 records, so it's going to fill up this virtual table with every single record in that employee table. So we have 290 records, and you can see them all right here. Then it's going to take that virtual table and pass it to the WHERE clause. The WHERE clause is going to filter out any records that don't match the search condition.

4:06This is actually known as a predicate here, and you can have multiple predicates separated by ORs and ANDs. A predicate evaluates to one of three values-- true, false, or unknown, and only the predicates that evaluate to true will have their records included. So our predicate is saying here only include records were the year portion of the hire date-- that's this portion right here-- is greater than or equal to 2009. So that'll filter out anything that doesn't have 2009 in the hire date. There's the results, and that will contain 209 records now in our virtual table, which will get passed to the next phase, which is GROUP BY.

4:42GROUP BY will then operate on that data and roll it up, in this case, by job title and the year portion of that hire date. So here's an example record right here-- research and development has the same data here and the same year. So this will get squished up, and then an aggregate function here, COUNT, will roll that up and we'll have a value of 2 for this specific grouping. So it'll look something like this and result in 63 records. And it will take all of that data and pass it down to HAVING. And again, HAVING is just simply a filter on the group itself.

5:14And notice our predicate here for HAVING is saying where that count is greater than 1. So it's going to remove all of these ones out of the picture, which will result in a total of 34 records, and pass that down to SELECT. There's the result, and that brings us to the SELECT phase of logical processing order. This is where everything comes together, and this is why you need to keep this data flow in the back of your mind when writing queries. I see so many people struggle with writing queries because they're thinking about this when they're writing them, not this.

5:47Notice something really interesting here-- see our aliased column, year hired? Notice that we can only use the actual name of that aliased column in the ORDER BY clause, because ORDER BY comes after SELECT. In order to use these columns in your WHERE, GROUP BY, and HAVING clauses, you need to use the actual expression itself, just like we're doing here, because the SELECT clause hasn't been evaluated yet. I've seen so many bloated and inefficient queries over the years because whoever wrote it didn't understand logical processing order, so they used extreme methods to get around it, like subqueries or correlated subqueries or storing the results of one query in a temporary table and then joining to it.

6:26So you can avoid those scenarios by simply keeping this phased approach in the back of your mind. And by the way, that final phase is the ORDER BY clause, which will sort the results by one or more columns. Here we have a multi-level sort, first by job title ascending, and then by year hired descending. And you can see within buyer, we have two buy records-- one for 2010, 2009, so it sorted that descending. And again, we can use the aliased name of the expression because ORDER BY comes after SELECT. In this CBT Nugget, we broke down the purpose and syntax of the SELECT statement and talked about logical processing order, which defines the rules we need to follow in order to properly write our SQL queries.

7:05I hope this has been informative for you, and I'd like to thank you for viewing.

0:00In this hands-on lab Nugget, we're going to learn how to write some basic select queries. We'll look at a couple of different ways to do this SQL Server Management Studio. We'll look at our options within the SELECT clause itself, and we'll get some tips and tricks along the way. Let's fire up the virtual lab and get started. I've logged into our SQL Server SQL-NUG. And from the desktop, we're going to get right into SQL Server Management Studio. And if you right click on the icon of the task bar, we can actually launch right into the solution we created in a previous Nugget. Underneath our second project here, select queries, let's go ahead and open up the first query 01 SELECT.

0:32Let's also get a connection over here in Object Explorer. You can do this the short way just by clicking on the shortcut here. And that will allow you to choose which component you want to connect to, the database engine being the default. You can also drop down the Connect button and choose which component you want to connect to. And that will lock you into that selection. So we're going to connect right to the instance, the default instance on our SQL-NUG database server here. So we'll go ahead and hit the Connect button, and here we go. We're going to begin working in the AdventureWorks database. So we expand databases, and expand AdventureWorks, and expand tables, we're going to start here working with the Human Resources dot Employee table.

1:04Now, let's maximize our screen real estate here by hiding Object Explorer. We can hit the Pin button there. That will push it over to the side. And same with Solution Explorer. If we ever need to get those back, we can just click on them, and that will fly them out. Another thing I want to show you here is getting help. Extremely important that you understand how to use context-sensitive help, because whenever you have a question, you can easily get to the documentation for whatever keyword, statement, function, anything you're looking for in Transact SQL. First, let me show you how to install it. If we drop down the Help button, and choose Add and Remove Help Content, this will open up the help viewer and allow us to download and install all of Microsoft's documentation here locally.

1:42And what we can do is search online. If I just search SQL, for instance, and hit Enter, you can see we have the Transact SQL reference. And I already clicked the Add button and then Update. And that downloads it and installs it locally. So we've got the SQL documentation all loaded locally. Another thing you'll want to do, because by default, it will search online, you'll want to drop down Help again, and choose Set Help Preference. By default, it will launch into the browser. And again, hit Online Help over on Microsoft's site. If you choose Launch and Help Viewer, and you've downloaded Help locally, then it will launch everything locally.

2:13And now that we Help local and available, and launching from our local documentation here, what we can do is anywhere you place your cursor-- so I'm going to place it right on the select keyword there, and then hit F1 on your keyboard-- that's context-sensitive help-- and that will launch the documentation right to that help topic. Look at that. And here is the SELECT statement. This will give you a nice overview of that keyword, the basic syntax. And if you scroll down, you'll get the expanded syntax and remarks, and then lots of examples. And most of these examples work with the AdventureWorks database.

2:47All right, let's do another one here. Let's this time. click on From. And hit F1 on your keyboard. Again, that will launch us right into the Help Viewer for that topic, and explain a little bit here about what the FROM clause is all about, along with syntax and example. So very cool very easy to do, and extremely useful, especially when you're learning Transact SQL. All right, let's write some basic SELECT queries. And let's start by changing our connection context for this query, this script here, to the AdventureWorks database. Again, you can just drop down this and choose it, or we can use the Use command here and specify our database name.

3:20And inside of your query, anytime you highlight something and hit Execute, or F5 on your keyboard, that will execute just your selection. If you don't do that, if you don't have a selection here to execute, it will execute the entire script, and every single query inside of there. So make sure that you select it before you hit Execute. Also, Control R is your friend here. See the Results pane down here? If you get Control R, it will hide it. If you hit Control R again, it'll show it. So that's really useful, again, when you're writing, and doing a lot of testing, and testing certain parts of a query.

3:52And you just simply highlight that part, hit Execute, hit Control R to hide it, and get back to designing your query. Now, let's start with the most basic of all SELECT queries. Here it is, right here. Select Asterisk, or Star, or Splat from human resources-- that's the name of the schema dot employee. That's the table within that schema. And this will pull back every single row in every single column from that table. If we hit Execute or F5 on the keyboard, here it is. Now, SELECT Star is something that you should really only use again when you're eyeballing data, or just want to take a quick peek to understand what that data looks like.

4:25And we should never use this in a production environment. And there's many reasons for that, one, obviously for performance reasons. You want to pull back only exactly what you need. And another one is-- if you use this inside of database objects-- and developers use those objects from the front end-- if our schema ever changes, it could break the front end, especially if they're referencing these columns by index or by location within the results set. So keep that one in mind, very important to understand. Only use the asterisk when you're testing.

4:56Also, if you hold your mouse over the asterisk, it will show you all of the columns that it's returning from that table. So let's fix that. Let's say we were really only interested in a couple of columns out of this table, Login ID, Job Title, Hire Date, and Vacation Hours. We can simply specify those columns, comma to limit them, and then specify our table name. If we highlight the query and hit Execute, now we're only pulling back specific columns. Easy stuff, right? All right, I'm going to hit Control R to hide our Results pane. And let's take a look at another example here. This time, we're going to add another column onto here.

5:26But this is not a column that exists within the table. This is an expression. We're going to take the column vacation hours and divide it by 24, and then put an alias on it so it's named. If you didn't add the alias, then it wouldn't have a column name. In fact, I'm going to comment that out. Dash, dash, by the way, is a single-line comment. And so if we highlight this, and execute it, it will execute everything except the alias inside of here. And you'll see that we will get no column name returned. So this is why we always want to alias our expressions. And we can also alias other columns that exist as well, if you want to change the name of them.

5:59Useful, again, if you're dealing with an older database with cryptic names. We always want to make those names human-friendly, especially when we start designing views and people are going to using those views. We can also obscure the actual names, which would give us a nice security benefit. All right, let's go ahead and execute this query. And now we have a name on that column. Another thing I want to point out here-- notice this semi-colon at the end of all of our queries. This is a statement terminator. And it's actually implied. So you don't need it. In fact, we'll get the same result here if we execute it without it.

6:31But we should use it. Microsoft has stated in the future, this may not be implied. And it's good practice to be as explicit as possible when you're writing Transact SQL. The same goes with the as keyword when you're aliasing a column. You don't actually need it there, and you'll get the same result. But you will want to be explicit here because it makes your code much more readable and less error-prone. Now we can also alias table names. So we could do something like this. Now, this doesn't make much sense in the context of a single-table query.

7:01But once we get into joints, where we're working with columns for multiple tables, this will become very important, both for readability purposes, and especially if we have the same column name in multiple tables, we will need that to specify which table we want to pull that column from. Moving on, let's take a look at sorting our results with the ORDER BY clause. Now, if I hit Control R to bring up our previous results, you'll notice that there is no sorting on any of these. It's sorted by however the database retrieved the records. We can force a sort with ORDER BY and specifying the name of our column.

7:33Ascending is applied here. And again, you can be explicit and type in ASC. But the default is ascending. So this will now order those results by job title ascending. There it is. Now if we did want to sort through the results in descending order, we will need to explicitly place the DESC keyword after the column name that we're sorting by. And notice here, we're going to sort by vacation days, which is our expression. Remember, the reason we can do this is because in the logical processing order, ORDER BY happens after SELECT, which is why we can reference this alias name.

8:06And if we highlight this query and it Execute, we will now sort by vacation days in descending order. Moving on, let's take a look at a couple of options that we can include in the SELECT clause itself. The first one is the TOP clause. This is a limiter used in a couple of scenarios. Number one, it's used to just reduce the amount of records that we're pulling back from our tables where we're doing testing in building queries, that way we don't bring our database server to its knees. In fact, this was added into Management Studio a few releases ago for exactly that reason, because admins and developers were in here.

8:40They could right click on a table and choose Select The Rows, and it would bring back all the rows. And if there were a billion rows in that table, again, you could just crush the performance of your database server. So they put this limiter on here when you're selecting it. And that's why, by default here, it brings back 1,000 rows. So that's one way to use it, just simply to limit the number of records returned. A more powerful and more common way to use this is combining it with the order clause so we can generate top and bottom lists. So here, we're generating a top 10 list based on employees with vacation days.

9:11So this will bring us back the employees with the most vacation time available. And there it is. So there's all of our employees with four vacation days. Another thing you can do here is use the With Ties option. And what this will do is ensure that anything tied for last place will also get returned. And if hit Execute this time, rather than just 10, we're going to get 12 rows because one through 10 was four, but 11 and 12 was also four. So With Ties we'll bring back anything where that last record has a four.

9:42And there may be more beyond that last record. It will return those as well. Another thing we can do is use a percentage rather than a number. So if we type in Percent here, this will return the top 10% rather than just the top 10. And we have 290 employees in this table, so this should bring back 20 rows. You can always see the number of rows that were returned here in the lower right-hand corner in Management Studio. And there it is. There's the top 10% rather than just the top 10 records. I should also point out that this value can be an expression. So you can place an expression inside of here.

10:13And also, it can be a variable, which is common when you have a stored procedure, and you want this value to be dynamic based on whatever, the front end, or the developer, or whoever is using our store procedures passes in as a parameter. So we can definitely make this dynamic by utilizing expressions. The other option we have is the Distinct option, which will eliminate duplicates, record duplicates, not value duplicates, but records. So the entire record must have the same value for it to be eliminated. To give you an example, let's remove distinct.

10:44And again, the default here is all. So that is implied. We don't need to do that. But what this will do is bring back every single job title. And you can see the duplicates here because we only have one column. So this is the entire record. But let's say we just wanted to get a list of job titles. Well, we could turn this into a distinct, execute it, and this will just bring back one record for each job title. And there is our list of unique job titles for the AdventureWorks database. All right, moving on, let's hit Solution Explorer over here on the right, and let's open up our second script here called O2 Logical.

11:16Now in our Nuggets titled The Select Statement, we talked about logical query processing. And we saw that it's logically processed different than how we write it. We write SQL with SELECT first. And that can give you the impression that SELECT resolves first. But it doesn't. We know that SELECT isn't actually resolved until after the HAVING clause, which is the reason why we cannot used our alias columns anywhere prior to the ORDER BY statement, ORDER BY does happen after SELECT, which is why we can use our alias column names in that ORDER BY clause, but not before.

11:46So I wanted to include this in here so you could see it all broken down step by step. These are the queries that I used to generate those whiteboards. And if you execute, execute the entire script, then it'll switch to AdventureWorks and execute each dataset individually. In the right scroll bar, you can scroll down and see how these statements all work, and how that data goes from raw data all the way down to our end result, which is all of the employee's job titles grouped together by year. And we get a count of the number of employees hired by job title by that year. Moving on, the last thing we're going to do is look at a SQL challenge.

12:19I'm going to challenge you to write a query, a very basic query here, that uses a table in the Wide World Importer's database. So I'm going to get you started here. And do the same here if you're following along. Highlight our use statement and switch our connection context here to Wide World Importers. I'm going to give you a scenario here, give you some requirements. And then you can pause me in an attempt to write this query. And then we'll walk through writing it together, and I'll give you some tips and tricks on writing and designing queries here in SQL Server Management Studio. So your challenge, if you choose to accept it, is to write a query that returns the 10 most expensive stock items out of the warehouse that stock items table here in Wide World Importers, and include a column that calculates sales tax.

12:59So in that table, Stock Item, Name, Unit Price, and Tax Rate are columns that you can just pull directly out of that table. Sales Tax, however, you will need to write an expression that calculates the sales tax. And you can do that by multiplying unit price by tax rate. Keep in mind though, tax rate is a percentage. So you'll need to actually convert this to a decimal. And you can do that by multiplying-- or I should say dividing it by 100, all right? And then also, sort those results by unit price descending, which will give you the top 10. All right, so if you're up for the challenge, pause me now, try it out on your own, and when I come back, we'll do it together.

13:34OK, are you ready? Let's write this together. Let's head down to some white space here. And the first thing I'd like to do in writing a query, especially if I'm unfamiliar with the table, let's just do a select starter from it quickly. And again, if it's a big table, make sure you limit the results. But let's do a select starter from warehouse. Notice Intellisense here. We can take advantage of this. Any time you see this blue rectangle, that's the currently selected item in the list. If you hit Tab, it'll finish typing it for you. Then we can hit dot and start typing in our table name, which is Stock Items. And as soon as you see it pop up here, you can use your keyboard and hit the arrow keys to go down your item, highlight it, and hit Tab.

14:07And there it is. And we'll finish it off with a semi-colon for good best practice there. And then let's execute it to take a peek at the data. So here are all of the stock items. And we're going to be working with the names. We need to pull that back. If we scroll over a little bit here, we're also going to be working with the tax rate and the unit price. And these are what we're going to need to calculate sales tax. And, again, we're going to need to convert this percentage to a decimal. So we get an accurate sales tax there, all right? So I'm going to hit Control R. And now I'm going to hit Enter a few times to get another one up.

14:37And here's how I like writing queries from scratch. First, Select, then Enter, and then From. And enter your table name. So there is warehouse dot Stock Items. We'll scroll down there, and boom. So the reason I like doing this is because then we can take advantage of intellisense in our SELECT clause itself, right? And it feels right writing that from first because you're following logical query processing. You just get your brain thinking the right way. All right, so the first column we're going to pull back here is Stock Item Name. There it is. And what's also nice about this and using intellisense, is you never have to touch your mouse, you can start writing queries and just ripping through them.

15:12So stock item name. Then we'll type in unit price. We'll go down, hit Tab, Comma, tax rate, same thing, that's the only one. So we can hit Tab, Comma. And now we need to calculate sales tax. so let's get some parentheses up here. And we're going to calculate that by taking unit price and multiplying it by tax rate. But again, we need to convert tax rate here. So we're going to place this inside the parentheses as well, because we want this to resolve first. It's going to resolve from the inside out when you have parentheses here. And we're going to divide that by 100.

15:43And there we go. We also need to alias this column. So let's type an as sales tax. And finally, we want to order this by unit price descending because we want to add the top 10 in here, right? So select top 10. And there it is. There is our result. And for good measure here, we'll also add our statement terminator. Now we can highlight all of this. And there is our top 10 by unit price items with sales tax calculated over there on the left. Now one more really useful tip I want to give you here, is if you're learning Transact SQL for the first time, and you're not really comfortable with writing it from scratch, there is a graphical designer that you can use to do so, and actually watch it right the SQL for you.

16:24This is a great learning tool. And to access it here, make sure your cursor is in the white space, right click, and choose Design Query and Editor. Here, you would find the table. So we'll look for stock items here. There it is. We'll hit Add, close out of this dialogue, and now we can graphically design this query. We can choose what columns we want. Each column has its own entry down here. So there is Stock Item Name, here is Unit Price, there is Tax Rate. Notice here we have those three columns in here then. You can alias them here. You can choose what to sort by here.

16:55And, again, the cool thing here is that it is writing this SQL for us. Check that out. We can even go into the properties here of the query, scroll down to the bottom. Here is distinct. There's our top specification here. So if we expand that, we can turn the top on, we can specify our expression here as well as our percent, and WITH TIES option. And then, of course, if you want to put our expression in, there we can just start typing in here that expression and give it an alias. Very, very useful, because, again, it's writing the SQL for you.

17:25Once you hit OK, it'll pop it down here into the script pane. Now, another thing you can do is highlight an existing query. Let's take that query we just wrote, right click on it, and choose to design in query editor. And it will fill it all out inside of here. Check that out, including our expression that we wrote down here. In this hands-on lab Nugget, we learned how to write basic SELECT queries and solve all of the options that we can include inside of the SELECT clause itself. I hope this has been informative for you, and I'd like to thank you for viewing.

0:00In this hands-on lab Nugget, we are going to focus on the WHERE clause, which is how we add filters to our query to target specific data. Let's fire up the virtual lab and jump in. Once you've logged into our SQL-NUG database server, lets head down to the task bar, right click on SQL Server Management Studio, and launch right into our solution containing the project we're going to be working with throughout this lab. Once SQL Server Management Studio fully loads, we are going to be looking at the 03 filtering and results project. And let's open up the first script in here, 01 WHERE.

0:31From there, let's go ahead and push Solution Explorer and Object Explorer aside. And let's also switch our connection context here to the Wide World Importer's sample database. And let's start here by taking a peek at the data that lives inside of our stock items table. So I'm just going to highlight the select star from warehouse dot stock items. And this will bring back all 227 items inside of this table. Let's see if we wanted to filter for a specific supplier. And we can do that based on the supplier ID.

1:03Now, this will make much more sense when we get into joining. We can join these tables together. But supplier ID number five, if we just eyeball this data, looks like it's going to provide us with some funny mugs, funny-- we got a developer joke mugs, we've got IT joke mugs, we've got deviate joke mugs in here, all kinds of good stuff. Here's a good one right here. Select caffeine from mug. I love that shirt. I want that shirt. But anyway, let's say that we wanted to focus on supplier ID number five here. We can add a WHERE clause, which will just return these records where this column equals this value.

1:36If we execute that, there we go. Now we're down to 42 records. And looks like this supplier just supplies funny mugs, funny IT mugs. So that's about as easy as it gets, right, one single predicate inside of the WHERE clause. One thing I want to do before we go any further here is take a look at the help documentation for the WHERE clause itself. So again, place your cursor on that line, hit F1 on your keyboard, and that will pop open Context Sensitive Help for the WHERE clause. We can see it specifies the search condition for the rows returned by the queries, very simple syntax here.

2:08But, of course, the WHERE clause can be the most complex part of any query, because once we start adding multiple predicates, we need to understand how to glue them all together. Now, if you scroll down a little further here, you'll get lots of examples. Again, all of the examples here point at the AdventureWorks database. But you can see lots of different examples on how to use it. Feel free to test some of those examples out and run through them. You can copy and paste right from the help topic into the query here. And we have the AdventureWorks database available. So it's a good way to get, again multiple perspectives on how to use the WHERE clause.

2:43Let's scroll down a little bit, and let's combine predicates here by adding an and condition between them. So if we eyeball the data once again here, we see the supplier ID five provides us with some funny mugs. Color ID three, if we look at the color ID here, we can see this is probably going to be all of the black mugs. So let's say we just wanted to see all the mugs that are colored black. Let's go ahead and highlight this query, hit Execute, and that's going to trim it down even further. And now we have 21 rows. These are just all of the black mugs that supplier provides.

3:16Now here's where things get really interesting. Let's say that we wanted to bring back all the products that supplier ID four or five provided and that are the color black. Now, here's what's going to happen. This is going to give us unintended results here because if we highlight this and execute it, we're going to see more than just items with the color black. If we scroll down-- look at this-- we've got some white items in there. We've got some blue and red items in there, and green, and gray, and brown, and pink.

3:47And uh-oh, what happened? This is because of precedence. How this works is not is greater precedence than and, which has greater precedence than or. So if we look at this, how SQL actually interpreted this looks like this right here. So it returned us supplier ID's black items. But the or here also gave us everything from supplier ID four. So this is why we really need to respect precedence and understand it so we can correctly form our WHERE clause.

4:23So this is where we would control it by using parentheses. Parentheses, anything in parentheses has the greatest precedence and will resolve first. So we can fix those results by wrapping parentheses around the or between supplier ID four and five. And we can leave the and on the outside, which means this will resolve last. So this will get us all of supplier four or supplier five's items, and make sure that the items are black. So if we execute this, the result then is going to be what we initially wanted, right?

4:56All black mugs, and black shirts, perfect. And by the way, speaking of the not operator. We can use this to negate a condition. So for example, let's say we wanted to see all the other suppliers that are not four or five that provide black items. We can place a not in front of this condition. And now if we highlight this-- in fact, let's actually turn stock item name to an asterisk here so we can see all of the columns that are returned. If we highlight that and execute it, here they all are. You can see these are the other suppliers.

5:26Four or five is not in here. And here is the color ID of three, which is black. And we can verify that by looking at the description here, or the name of all these products. Now, another common thing you'll be doing with your WHERE clauses is searching against character data. Here's an example. Let's say that we wanted to grab all of the items were the size column contains extra large or, double extra large. Here, very simple query to do that, multiple predicates here tied together with an or. And this will bring us back a bunch of shirts that are either extra large or extra, extra large.

5:58And you can see the size column right here. Now, these are equality comparisons. And oftentimes when you're looking for character data, you may be doing wildcard searches using the LIKE operator. Here we, can use the percent sign to indicate that we want anything after or before any sort of character. So in this case, we're seeing anything that starts with an x and any character after that returned. And if we highlight this, this will give us anything in the size column that starts with an x, including extra small and extra, extra small.

6:32And let's say, you know what? We just wanted extra large and above. Well, in that case, we can add a NOT to this. And we can say we're AND size not like S. And here's where we're using a wildcard identifier here before and after. So we're saying anything that contains S. And this time, it will return anything that contains extra large or double extra large, just like we had before with our equality comparisons, except now we're doing it using a LIKE with wildcards. I also want to point out that there are more wildcard characters you can use in conjunction with LIKE to further refine your results.

7:05In fact, if you place your cursor on LIKE and hit F1 to open up the help topic, we can scroll down just a little bit here, and we can see that other than percent, we also have underscore, which allows us to search for a single specific character within a string. We can also use brackets here to search for the range of characters. And we can use a carrot within the brackets to negate a range of characters. Another important thing to note here is if you want to use any of those wildcard characters as literals-- in other words, maybe you want to search for the percent sign in your data-- you can surround it with an open and closed bracket inside of your pattern.

7:41This is known as your pattern that you're searching for. And that will return the exact literal value of that wildcard. So I would definitely advise going through some of the examples down here in the virtual lab just to do one example of each type of search to get it into your muscle memory, because it's good to know this stuff for the real world because you'll be doing a lot of character searches, especially inside of your stored procedures when users want to search textual data. And there are some pretty good examples in here that work with the AdventureWorks database. All right, I'm going to close out of our help topic here.

8:13And let's talk about date and time searches. Just a couple of rules to be aware of when you are doing date equality searches like we're doing here, or date range searches like we're doing down here. And we'll look at many other ways of performing date searches when we get into functions. But for now, the first rule is to use this format right here, four-character year, two-character month, two-character day. And the reason being is that this is language-neutral. If you use any other format, then you're binding yourself to specific languages.

8:49And it's like using standard SQL as opposed to using Transact SQL's specific things. You want to keep it as generic as possible so our code is portable and will work with more things. And this format works with all the things. If we highlight this and hit execute, this will bring back all of our orders that were placed on this date. Another thing, when you're searching for date ranges, be careful of using between. This works fine with character searches, but when you're using date time searches, it actually isn't very precise, and it cuts off some of the lower ends of time.

9:22So when you're dealing with date time columns-- and this order date is not a date time column. It's just a regular old date column, so this will work. But it will generally cut off the last day, all right, because this is an exclusive operator, not an inclusive operator, meaning it will exclude the outer bounds of it. And so this will actually work again because this order date is a date column. But if it was a date time column, we would actually lose the last day. So whenever you do date range searches, you should use what's known as an open-ended date range, where you should do a greater than or equal to your starting date and a less than the end of your date plus one.

10:00So in this case, this will cover all of January 2016 from 0101 because we have a greater than or equal to, and then less than the 1st of February. And this, if it was a date/time field, would absolutely cover everything. So keep this in mind. It's also better for performance to do this, better than performance over between. But more importantly, it's much safer because you'll get exactly what you're looking for. Again, more on all of this stuff when we get into functions and data types as well. All right, are you ready for your SQL challenge?

10:31Let's open up Solution Explorer and open up O2 SQL. Once again, I'm going to give you some requirements here. You can pause me in attempt to build this query on your own. And then we'll walk through a solution together. Here we go. Your mission is to write a query that identifies people who are allowed to log in to the application, are not employees, and does not contain example dot com in their login name. You're going be working with the table application dot people. This is full of people who are allowed to log in to the Wide World Importer's system. You're going to pull back Full Name.

11:01Log On Name is permitted to log on, and is Employee. And those are also the columns that you'll be working with to create the predicates inside of your WHERE clause. So now is a good time. Pause me, and good luck. All right, are you ready? Let's head down to the white space here, and let's first get familiar with that table. So select star from application dot people. And we can just hit F5 here because we also want to change database, the connection contacts here to Wide World Importers. So if we don't highlight anything, and hit Execute or F5, it'll execute that, and successfully execute that query to target that table.

11:35So a couple of columns we're going to need here-- we're going to pull back Full Name just for presentation purposes. We're also going to need Log On Name because if we want to find or filter out anybody that belongs to example dot come, we're going need to run a not like against that and a wildcard search for that. So that's why we're going to need Log On Name here. And we've got a lot of example dot com folks inside of there. We're also going to need to identify people who are allowed to log in. Which column would we use for that? If we look at this, you're probably going to notice there is and Is Permitted To Log On column.

12:05This is a bit field. Zero is false. one is true. So we're going to want to add a predicate here for this column where that equals one, right? Those are folks that are allowed to log in. And if we scroll over here, we're also going to need to hit the Is Employee column here, because we want to identify people who are not employees. So this time, another bit field here, we're going to set this and our predicate equal to zero. All right, so that looks good. I'm going to Control R to hide that. And now let's write the actual query.

12:35Let's do a Select, Enter, From Application dot people. Now, let's head back up to Select here, and let's grab all those columns. There is Full Name, there's Log On Name, there's Is Permitted to log on, and there's Is Employee-- all right, very easy to quickly type that out and tab through it when you use Intellisense. Now comes the important part, our WHERE clause containing all of our predicates. Let's do this condition by condition. Our first one here is to identify people who are allowed to log into the application.

13:08When we saw that, it was the is permitted to log on column. And we want to set that equal to one. Then we also want to identify people who are not employees. So we're going to an and here is employee equals zero. And then we want to identify people that do not contain example dot com in their log on name. So we can do an and log on name not like-- and then inside of our pattern here, we're going to do anything that ends with the example dot com.

13:40And there it is. Oh, and don't forget about your semi-colon there. That's good form. Now we can highlight this query, hit Execute, and you should get 103 rows. These are all non-employees who are permitted to log in. And you will not see anybody from example dot com in this list. Perfect. One other tip I want to give you is, when you're building complex queries with a lot of search conditions, a lot of predicates in your WHERE clause, you may want to build it in phases, right? And this is where comments come in handy too because you can start with one of your conditions, just like so.

14:11We can still highlight the whole thing there at execute. So this is the query, which is permitted. We can see we've got 155 records here. And you can document the record counts, and then you can start adding in more conditions, and then negating other conditions. And you can, again, see what your record counts are going to be. And it's a nice phased approach to building your queries. You can even set up your query, copy, and paste it, and run that query for each condition if that's easier, if you didn't want to comment things out. But this will also give you a good idea of how to set up precedence, and if precedence is messing with your results.

14:43And then you can identify where it is, surround it with parentheses where you need to to ensure that those results first. In the hands-on lab Nugget, we learned how to add filters to our queries using the WHERE clause. I hope this has been informative for you, and I'd like to thank you for viewing.

0:00In this hands on lab Nugget, we're going to learn how to combine the results of multiple queries together using set operators. These operators are UNION, EXCEPT, and INTERSECT. And as you'll see here shortly, they can come in pretty handy when you need to combine the results of multiple queries together into a single result. Let's fire up the virtual lab and get started. Once you get logged into SQL-NUG, let's start by launching SQL Server Management Studio, and also launch directly in to our solution containing all of our projects for this course. So give that icon on the task bar a right click and choose 70-761 scripts.

0:34Once SQL Server Management Studio loads, we will have that solution on the right with all of our projects. We're going to be working with our project called 04 Combining Results. And let's begin here by launching 01UNION.sql. We're going to look at the UNION set operator first, and I'm also going to put solution explorer and object explorer aside. First things first here, let's get a connection over to our Wide World Importers sample database. And we're going to start by working with our table stock items. This is a table that we've worked with in previous Nuggets, which just contains all of the items that this warehouse holds that Wide World Importers resells.

1:09Taking a look at our first example, these are two separate queries. The first one is going to grab records 1 through 10 based on the stock item ID, and the second one is going to grab records 5 through 15. If we highlight both of these and then execute, the result is going to be two separate result sets, right? We have results set one up here with items 1 through 10, and result set two down here with items 5 through 12. So, what if we wanted to combine these into a single result set? That's where the UNION operator comes into play. Now there are just a couple of rules that we need to follow in order to successfully combine the results of two or more queries together with the UNION operator.

1:46And yes, you can combine more than two queries. You can combine as many as your heart desires. But the very first rule, is that you must have the same number of columns returned across all those queries. So that's rule number one. If you have three columns returned from your first query, then you must have three columns returned from your second query and third query, and so on and so forth. And the second rule is that your data types must also line up across the results of these queries. If we look at our query here, do you think we're OK?

2:19Well, we're working with the same table, so of course we're going to be OK. We're grabbing all the columns from both of these queries, or that same table, and combining them all together. So we should just be able to stuff a UNION operator right in between these as we're doing down here, and it should take the results of all those. And instead of returning us with two separate results, we should have one. Should we test this out? Now before we do this, I want you to notice something here. Check out the lower right hand corner. We have 21 records from our previous query. If I hit Control R to bring this up, this means that if I click inside of our first result set, we have 10 rows.

2:53And if I scroll down and click in our second result set, we have 11 rows. So this first query returned 10 rows. The second one returned 11 rows for a grand total of 21 rows. Think we're going to get the same results here with the UNION? Well let's find out. I'm going to hit Control R just to hide this. Let's highlight everything with the UNION operator between these two. And also notice here, our semicolon is at the very end. Not at this one, that would actually break and terminate the statement right there. So keep that one in mind, semicolon at the very end, and just one. But if we hit execute here, we're going get a total of 15 rows.

3:25What happened? That is not what you were expecting, was it? And that's because the UNION operator, by default, removes duplicates. It has an implied distinct operator built into it. And we can verify that by scrolling down on the results. You will only see records 1 through 15. Even though we have crossover here, those duplicates were removed. And they weren't removed above because those were two separate queries, and two separate results. But what if we wanted those duplicates? Well, that's where UNION ALL comes into play.

3:56We can add this option to the end of UNION and it will not remove the duplicates. In fact if we highlight this same exact query here, just with UNION ALL rather than UNION, now we should be back up to 21 records. Because, as we scroll through the results here, we have duplicates from, in this case, 5 to 10. And you can see them right here, there's our dupes. Now an interesting side note I want to point out here, and a good performance tip for when you write your UNION queries. UNION performs a little bit worse than UNION ALL when there are duplicates.

4:27And even when there aren't duplicates, because it has to add another step in the process to actually remove those duplicates, and do what's known as a merge join under the hood. So the tip here, is that if you're UNIONing together results, and there's absolutely not ever going to be any duplicates coming from those tables, then add in ALL to the end of it for a slight performance boost. And we can actually see this in action. If we highlight both of these queries, and then we turn on our execution plan. This will actually show us how SQL Server retrieves the data under the hood.

5:00More on these in the future. But if we highlight those, and turn that on and hit execute, we're going to get another tab here that pops up once the query is complete, and shows our execution plan. And shows us how much each statement costs in relation to the entire batch. So here's our first one. Right? You can see that by holding your mouse over. We can actually see this was the UNION, not the Union All. And this is your additional cost right here. Notice this takes up 65%, again in relation to the entire batch. If we scroll down to our second, our Union All.

5:30This only took up 35% of the batch. So our first one, the regular UNION, was much more expensive than the second one, because the second one didn't have to worry about duplicates because of that ALL option that we included. It just simply had to concatenate the data together. Didn't have to worry about removing duplicates like you did with the first one, where it performed a merge join to remove the duplicates. Which incurred a lot of extra costs into that. So keep that performance tip in mind. Again, if you're writing union queries, and your queries are hitting tables where there will absolutely not ever be any duplicates, add an ALL to the end of it for a nice little performance boost.

6:06All right. I'm going to go ahead and turn off the execution plan here. And let's re-execute this query, because I want to point something out here. Notice that we don't have any ordering going on. So how do we sort results when we're dealing with multiple results combined together? And the answer there is the ORDER BY in the final query. So if you want to sort the results, you need to add an ORDER BY, but you can only add it to the very last query that you're combining these together. And that's exactly what we're doing here. Same exact query, although rather than pulling all the columns, we're just pulling the item name here.

6:38And then we're going to put an ORDER BY in the very last query, and this will sort them, in this case ascending by the name. Another thing I want to show you here is how to combine results from more than one table. Well you can just slap more UNIONs in. Pretty easy. Again, just remember if you're going ORDER BY, it needs to be the very last one. So this one will grab the results from three different queries, combine them all together, and then order it by, in this case, the stock item ID. One more thing you need to know about the UNION operator is aliasing.

7:08We can only alias columns or expressions in the very first query. So aliases are defined in the first query, sorts are defined in the last query. And just remember the number of columns returned must line up, as well as their data types. And actually, here's an example of that. We're pulling the city name from the cities table, and the country name from the countries table, and combining them together. The reason this works-- if you hold your mouse over these columns, you can see the data type for city name is nvarchar, and the data type for country name is also nvarchar. So that's why this will work.

7:41There it is. All right, moving on, let's take a look at our other two set operators, EXCEPT and INTERSECT. So let's hit solution explorer. And let's open up our second query here, EXCEPT and INTERSECT. And we'll go ahead and switch databases here, for this query to point to Wide World Importers. Let's start with the EXCEPT operator, and get an example going here. So EXCEPT returns any rows from the left input that do not have a matching record in the right input. So let's get our inputs up here.

8:11Here's the results of query 1, which by the way is known as your left input. And here's the results from query 2, which is known as your right input. So what will happen here, is the EXCEPT operator will compare the results. It's going to do distinctive- based comparison here. Which means it's going to return the distinct versions of the entire record, all of the columns combined. An interesting side note here, is that nulls do equal nulls. About the only place in SQL Server where that's the case. That is not the case when you're dealing with your WHERE clause, or GROUP BY clause, or the ON clause when you joining tables together, because nulls do not equal nulls in equality based comparisons.

8:49Again, this is a distinctive based comparison, so they do. So here's how this works then. If record one exists in both the left and right hand table, but record two only exists in the left and not the right, then your output here is only going to be the second record. All right? So EXCEPT once again, will return any rows in the left hand table that do not have a matching row in the right hand table. Now, we have a simple example here that is only dealing with a single column. But if we had 10 columns here, remember that all of those columns would need to have the same value across both of these queries for it to be ruled out of the final result set.

9:23And by the way, the same rules apply here as they do with UNION. Same number of columns and same data types across these queries for this to work. And there are some good use cases for EXCEPT. For example, let's say our first query here grabbed all the products out of the products table. And our second query grabbed the product ID out of the sales order table. We could easily then identify which products do not contain any orders, all right? So we're kind of doing the same thing here. We're grabbing all of the colors out of the warehouse stock colors table. And then we're grabbing the color ID out of the stock items table, and we're putting an EXCEPT between them.

9:56What this will show us is any colors that are unused in the system. The system being our stock items table. So if we were to highlight all of this and hit EXECUTE, this is going to return the colors that are unused. So we don't have any items containing any of these colors. If you want to see what those colors are, we can actually wrap this inside of a sub-query, and use it in the WHERE clause. And we'll get into sub-queries in the future if you're unfamiliar with them. We can do a SELECT * from Warehouse.colors where that color ID is IN, and then that IN is going to look at this result right here.

10:30Because that's the result of this EXCEPT query that we just wrote. So now if we highlight all of it, there you go. There is all of the colors that are unused in our system. So no items contain any of these colors. Now the next operator, INTERSECT, does the exact opposite. It will only return records from the left table that have a match in the right table. So now we can do the inverse of what we just did. We can take a look at all the colors there that are in use from the items inside the warehouse, that stock items table. And if we execute that, there they are.

11:01We can also then take our sub-query-- or turn this in to a sub-query. I'll just copy and paste this in, and we'll put parentheses around our other query. And now we can highlight all of it, hit EXECUTE, and these are the colors that are in use for the items in that table. All right, let's finish this Nugget up with a SQL challenge. So we'll get Solution Explorer over there on the right. And let's open up SQL.sql. Here is your challenge. Write a query that returns a unique list of cities both customers and suppliers reside in. So two tables here, the suppliers table and the customers table.

11:34The column you're looking for is postal city ID. Make sure that those results are unique. And again, pause me if you want to try this on your own, otherwise we're going to do this together right now. Let's begin by switching databases here to the Wide World Importers sample database. You'll need to do that so Intellisense will work, by the way. Let's rate our first query here. Select. And we know the column we're going to work with here, so I'll just type it up. PostalCityID FROM Purchasing.Suppliers. so that will give us a list of cities that all of our suppliers are from.

12:07And then our second query here is going to be the same thing. PostalCityID FROM Sales.Customers. There it is. And now if we throw a UNION between these two, that's all we need to do. So this will give us a unique list. Look at this right here, see what I did wrong? Got in the habit of writing a query like it was all by itself. We're going to remove that one. Any time you see red like that, you know something is wrong. And you can just eyeball your query to make sure that it isn't something you did like I did there. All right. So again, this will return us a unique list of cities that both our suppliers and our customers are from.

12:43We can highlight all that, execute, and there it is. And if you want to a bonus challenge here, turn this into a sub-query to pull the actual city names from the cities table. Again, that's the Application.Cities table. I'll do that one with you here too. Select city name, that's the friendly name of the city, from Application.Cities, where the city ID IN, and we'll turn the rest of that into a sub-query. Just like so. Now if we execute it, we get a list of friendly names.

13:14There's all the cities that we'll be sending holiday cards to for suppliers and customers. In this hands- on lab Nugget, we learned how to combine the results of multiple queries together with the UNION, EXCEPT, and INTERSECT set-based operators. Hope this has been informative for you, and I'd like to thank you for viewing.

0:00The join operator is what gives us the ability to write queries that span multiple tables. In this Nugget, we're gonna break down all the different types of joins that are available to us in SQL, including inner joins, outer joins, and the many flavors of outer joins, including outer joins with null search conditions. And we'll also look at cross joins and self joins. Let's get started. Whenever we write a query that targets multiple tables, the first thing we need to do is find the common column or columns between these tables that we can link on. Oftentimes, those are going to be related via primary and/or foreign keys.

0:34So they'll be easy to identify in a properly designed database. The next thing we need to identify is how that linkage, that join operation, is going to occur. And you can see we have a lot of different options here, which are usually going to be dictated by the requirements of your query. Let's start at the top and work our way down. The most common kind of join is an inner join. An inner join is the default kind of join Transact-SQL, which is why we don't need to explicitly state inner join. We can just use the join keyword by itself.

1:04What this will do is the results will contain only records where our key column or columns match between our join tables. So table one, in this case, is our left-hand table, table two is our right-hand table. So let's say that this is our key column right here. And let's say we've got values one, two, three here, and two, three, four over here. If we were to write this query and execute it against this, which rows will get returned? Well, only where we have a match in this column, which in this case would be two and three. So these records would get returned.

1:35This four and this one record would not. Next up we have outer joins. We have three different flavors-- left, right, and full outer joins. These allow us to pull back matching records, but also all the records from one side of the join. So for example, our left table here is T1. Our right table here is T2. If we were to left join our T1 to our T2 like we have here in our example on that key column-- here's our key column-- and then let's say that we have records one, two, and three over here, and we have records two, three, and four over here.

2:08All of the results from our left table are going to get returned because it's a left join, which means it'll give us everything from T1, but only the records from T2 where we have a match. So from T2, we're going to get records two and three. Now another tool that we have with outer joins is the ability to add a WHERE clause with a null condition against any of T2's columns. What this will allow us to do is find records that we have in our left table that do not have a match in our right table.

2:40So if we were to add this null search condition against T2's key here, what's that going to return? Well, it's not going to return records two and records three because we have a match. So all of these columns would now be null. What it would do, though, is return record one, because that was one of those records that was returned from our left join, but it didn't have a match in the right-hand table. So this will allow us to identify orphaned records in the left table when compared with our right table. Next up, we have a right outer join, which is the exact opposite of a left outer join.

3:12It operates on the right table. So in this case, if we were to execute this query, it would return every record from our right-hand table this time instead of our left-hand table, and only matching records in that left-hand table. So two and three we'd get returned from the left-hand table. All records would get returned from the right-hand table. We can also add a null search condition to a right outer join, which will allow us to identify records that do not have a match in our left table. So if we were to do this against T1's key, or again any column against the T1 table, this would help us to identify record number four.

3:46Two and three both have a match in this table. Record one doesn't, so it wouldn't get returned anyway. But record four would here in our right-hand table. Our final type of outer join is known as a full join. This allows us to get all records from our left table, all records from the right table, and match records from both. The real power, though, of the full join is, when we add a search condition, looking for null values in both keys. This will allow us to identify unmatched records in both tables. So in this case, one and four record would get return, but not two and three.

4:20It's also worth mentioning that you can get to your desired result using any of these joins. It all depends on how you design your query, and which table you designate as your left table and which table you designate as your right table. We also have two other kinds of joins here, self joins and cross joins, that you won't see used as frequently as our other join types. But they can still come in handy in certain situations. Our first one is a self join. This is where we join a table to itself, effectively creating the same table twice in our query, but aliasing it differently so we can reference each table individually.

4:52And this is often used when you want to represent hierarchies in your results, or cross-reference similar data within the table. Finally, we have what's known as a cross join. And notice we do not need to link on any common column between these tables. So this actually doesn't apply. And that's because every single record in one table is going to get crossed with every single record in another table. This is known as a Cartesian product because it's essentially the records in one table multiplied by the records in another table.

5:22This is useful when generating test data, or maybe you need to create kind of a matrix of all these records together. For example, maybe you have a shirts table, and over here you have a sizes table. And you have shirts that come in every size. So we could cross join these tables together to get all of our shirts matched with all of the sizes. So that's the join operator and the many ways that we can bring data together when writing queries that target multiple tables. Join me in our hands-on lab Nugget titled Querying Multiple Tables with Joins in order to see these in action with some real-world examples.

5:55In this CBT Nugget, we covered all of SQL's join operations, including inner joins, outer joins, self joins, and cross joins. I hope this has been informative for you, and I'd like to thank you for viewing.

0:00In this hands-on lab Nugget, we're going to learn how to query multiple tables with the many join operators available to us in SQL. So we'll cover inner joins, our three kinds of outer joins, cross joins, self joins, and we'll look at some tips here for writing multi-table queries. Let's fire the virtual lab and get started. From the desktop of our database server SQL-NUG, let's get into SQL Server Management Studio by right-clicking on that icon on the taskbar and launching right in to our 70-761 solution. Once Management Studio fires up, we're going to be working with our fifth project here called join queries.

0:33And let's start by opening up the very first one here, which is going to be focused on inner joins. I'm also going to hide Solution Explorer here, and let's get a connection in Object Explorer, because we're starting to work with multiple tables here. So I want to get you familiar with how they are tied together through relationships. So let's drop down the Connect button there in Object Explorer, hit Database Engine, and get a connection right to it. So we're going to start here with the Wide World Importers database, and we're going to be using two tables. So we'll start with a very basic inner join here, customers and customer categories. Let's say you've been tasked with writing a query that will pull back customers and their categories.

1:08How would you go about writing this? Well, the first thing you'll want to do is obviously get familiar with these tables, as well as the relationships between them, so you know what you can potentially join on. Lets use Object Explorer here and expand databases, and expand Wide World Importers, and expand Database Diagrams. I created a couple of them in here. And let's open up the first diagram here, customers categories. This, again, will give us a nice bird's eye view of these tables, and if their relationships were set up appropriately, then you will see these linkages. What we can see here is, in our customers table we have a customer category ID.

1:41This is a foreign key to the primary key of customer category ID in the customer categories table. And you can actually look at this linkage here and right-click on it and go into Properties. That will swing out the Properties window. And if you expand Tables and Columns, we can see our customer category ID is the primary key in the customer categories table, and there's our foreign key in the customers table. So that alone should give you an idea of how we're going to write the query to link these together on those columns. Those are the common columns between these tables.

2:14Notice, we also have a self-join set up here. More on that one later. But let's close out of this database diagram. Let's head back to our query. Let's change our connection context for the query to Wide World Importers. And then our first query very simply is going to pull back the customer name out of the customer table, and the category name out of that category table. And we're going to link these together with an inner join operation on those common columns across those tables. Also notice here that we're using an alias, which just makes our query a lot more readable in this case because we can use the alias to reference the columns from those specific tables rather than using the fully qualified table name.

2:50All right, so this looks good here. Let's go ahead and highlight this. Hit Execute. And there it is. There's our customers, and there's the categories that they belong to, all because of an inner join between those tables. Now, this is the recommended way to write our queries today using the join operator with the on key word to specify what columns we're joining on. This is still supported. This is the old school way of writing joins where we specify our tables, comma delimited in the FROM clause, and then use a WHERE clause to tie our common columns together. This still works but don't use this.

3:22And I only point this out because you may see this when supporting older databases. And you'll even see here that we will get the exact same result as we did previously. There it is. Now, let's crank it up a notch and write a query that targets four tables. So we're going to head down here, switch our connection context to Adventure Works, and I also want to just get you familiar with these tables. So over in Object Explorer, let's expand Adventure Works, expand database diagrams, and here we have the second one here. Product category inventory diagram.

3:52If we open this up, we're going to see all four of these tables. Now, when you're designing a big, complex query that's going to require access to many tables, what you want to do is work with one table at a time. And start with your base table. In this case, our base table if we flip over to our query here, is going to be our product table. That's our initial table. And then you can start adding in the tables that you need to pull columns from. And if you get to the point where there is a table that is going to require other tables and you're not really sure the path that you need to take to get there-- for example, in order to get to our category table, we don't have a product category ID column in here-- we need to go through our subcategory table to get there.

4:33Well, this is again, where you can look at the relationships for this table. So if we right-click on our product category table, we can head up here to relationships, which will open up a dialogue and show us that this connects to the subcategory table. Then we can go into that table, look at its relationships, and find its linkage back to our product table. It's a great technique to use when you begin writing queries for the first time in a database design that you're still unfamiliar with. Another thing that's useful for understanding the kind of join that you're going to need, is looking at the nullability of your foreign key columns.

5:05In other words, let's take a look at our subcategory table here. Let's right-click, choose Table View and standard. We can see that product category ID is not nullable, which means a subcategory must belong to a category. So it's pretty much only going make sense to inner join these tables together. If we look at our product table, on the other hand, and do the same thing, and look at our subcategory ID, look at this. I know it's tough to see. Let me zoom in a little bit here. Subcategory ID is nullable. So what this means is that if we do an inner join on this table to subcategory ID, we're only going to get records where our products have that subcategory ID.

5:44So that may be a good candidate for a left outer join, if we're writing a query that needs to include all the products whether they have a subcategory or not. Also, looking at our product inventory table, notice that the product ID is part of the primary key of this table. So we've got a one to one relationship between product and product inventory. In other words, we could potentially have products that don't have inventory. So this may be another candidate here for a left join. Again, if we want to include all products in the final results of our query. All right, let's go check this out here.

6:15So now we have an idea of how we're going to link these together. So if we head back to our query and we check this out, you can see we're inner joining everything right now. So we're inner joining product subcategory on the product subcategory ID across this and the product label. Product category we're inner joining to the product subcategory. And product inventory we're also joining to our base table here, product. So by default, because these are all inner joins, we're only going to get back products that have inventory that have a subcategory associated with them.

6:48460 total records. All right, moving on we have another multi-table query here that we're going to look at. Only this one is going to require multiple join conditions here on our special offer product table, and that's because we have an intermediate table with a composite primary key. So we're going to need to join this table with two columns, which is why we have an AND here after the ON keyword. Let's get familiar with this table structure here. If we head over to our database diagrams, this is going to be called product sales specials diagram.

7:19Again, here are these four tables. And what we're aiming to do here is write a query that shows us products that were sold with a special offer. So if this is our leftmost table in the query, in order to pull back the description of our special offer and the name of our product, again, we need to go through this intermediate table, which contains a composite primary key. That's a key consisting of two or more columns, in this case two. And again, we can verify that by looking at our relationship. If you right-click on that relationship and head into properties and expand tables and columns, there it is.

7:51We can see that this relationship consists of both special offer ID and product ID here in this table. And that's how we're going to need to link it from sales order detail using the product ID and special offer ID out of that table. Once those are linked, we can then perform inner joins on our other tables to pull out the fields that we need. So let's go ahead and close out of our diagram and head back here to our query. And that's exactly what we have going on here. We're linking special offer product to sales order detail on both of those columns. Then product to special offer product on the product ID, and our special offer through the special offer ID column on both of those tables as well.

8:30And we also have a WHERE clause in here to filter out anything that does not equal 1. 1 means no discount. So if we were to just execute this without that filter, it's going to pull back-- still pull back absolutely everything, 121,000 sales, and you can see most of them are going to consist of no discount. So that is special offer ID number one. And if we put that filter on there, this should only return a little over 5,000 records. So these are all the products, all the sales that were offered with the discount. And you can see the description of that special offer over there on the right.

9:00There's the product name, and there's the sales order ID. So those are inner joins. Let's move on and take a look at outer joins. I'm going to hit Solution Explorer and open up O2_Outer.sql. We're going to bounce back to the Wide World Importers database once again. So let's hook this query up to that database. There we go. And also, over here in Object Explorer let's head down to Wide World Importer and expand database diagrams. We're first going to be working with customers and their contact information. So two tables here, the customers table and the people table which stores their contact information.

9:33So let's start here by opening up our database diagram, customer people. You can see here's both of these tables. We have quite a few relationships set up between these tables. Relationships that reference themselves. Again, more on that when you get into self joins. And relationships that reference each other. And if whoever created these relationships named them accordingly, you should be able to identify based on the name here, which you can see in the tool tip. So here's the relationship between the contact, the primary contact ID. So this ID right here references the person ID in the people table. So that will tell us who their primary individual for contact is.

10:07And then here's the alternate one, which is this relationship down here. So that's what we're going to be working with here for these outer queries. The first one we're going to write here is a very simple inner join just to show you that and get some context here. This will return all customers that have both a primary and an alternate contact because we're performing inner joins for both of these tables. One against the primary contact ID and one against the alternate contact ID. And the result here is 402 records. Again, only customers that have both a primary and alternate contact.

10:39But this alternate contact person ID is nullable in the customer table, meaning there could be customers that don't have an alternate contact. And what if we wanted to get a list of those or a list of both a customer with a primary contact ID and then with or without an alternate contact ID? That's where a left join can come in handy. And that's what our next query is doing. So this time, we're inner joining for the primary contact person ID, which is fine because this is actually a non nullable field.

11:10So every customer must have a primary contact, but they may or may not have an alternate contact. And that's why we're performing a left join. This should give us a lot more than 400 results. And there it is, 663. Now, we're getting customers that do or don't. These customers that have null in their alternate contact field here were not returned in our previous queries. And you can see, we have quite a few of them in there. Now, what if we wanted to write a query just to identify those with a primary contact ID and those without an alternate contact ID?

11:41This is where that null predicate comes in handy. And here it is. We can search for null values here for the alternate person contact ID, which will then give us all of our customers who only have a primary contact ID. See that? So that's where that null predicate can come in handy. Moving on, let's take a look at a right outer join. This time, we're going to be working with the customers table and the customer categories table. We worked at these before and you can find them in the first database diagram here. But this time, what we're going to say is, our customer categories table is the right table.

12:12So let's say we wanted to identify categories first with or without customers. Remember, this will show us all matches and anything that doesn't match. If we execute this, anything with a null value are customers that do not have that category. So there are no agents and there are no wholesalers as customers with those categories. And of course, we can identify just those by adding a null condition against any column out of there from our customer table, since that is the left table in this case and we're right joining to it with the customer categories. And this will return us with-- and there was one more in there as well, general retailer.

12:45So those are the categories without customers. So again, left and right join, it just depends on which table you place in the left and which table you place on the right. And finally, we have a full join, which again, is essentially just a left join and a right join simultaneously. So our previous query written with a full join will give us customers with or without categories, and categories with or without customers. And if we execute this, really we're going to get the same results as we did before. There's those three categories without customers. And there are no customers without categories because it is required.

13:18But again, we could add a null condition onto either side. If we add it on one side it's a left join. Add it to the other side it's a right join. But if we add this to both sides, it will allow us to identify records that do not exist in either table. And in this case, we're going to get the same results as a right join, because again, customers must have a category but categories can be without customers, which is why we get that. And those are all of your outer joins. Let's look at our special join types here. If we head back into Solution Explorer, let's open up our third query, cross-self.sql. And let's start here with a cross join.

13:49Let's say, for instance, that we wanted to get a list of all stock items with all of their colors. Now, we're just going to do this for one supplier because if we didn't we would get an unbelievable amount of records. But this will return us with eight records. And we first need to switch our database here to the Wide World Importers. But our first quarter here is going to return us with all the stock items from supplier ID 1. There's a grand total of 8. And then our colors, we have a grand total of 36 colors. So if we were to cross join all of the items with all of the colors, for supplier ID 1 the grand total is going to be 8 times 36, which is going to be 288 records.

14:28Every single item for supplier ID crossed with every single color in the colors table. And finally, we have a self-join. Again, let's open up the database diagram here for-- either one of these will work because the customer table's in both of them. But if we look at this joint right here or this relationship I should say, you can see that it's referencing itself. We have a column in here called bill to customer ID. Customers are billed to other customers. So this is why it's referencing itself. This is actually a customer ID inside of this column.

15:00So when we write our join, we're going to reference the same table twice and we're going to join the customer ID to bill to customer ID, and that will show US customers that were billed to other customers. And if we close out this and head back to our query, that's exactly what we're doing here. You can see our left-hand table of sales.customer, and then we're joining two another instance of that customers table on the right hand side. And we're joining the customer ID to the bill to customer ID column. And the results of this are customers that are billed to other customers. As you can see, we have our satellite offices that are all billing to the head office.

15:33And as you scroll down, you'll see individual customers are billed to themselves. All right, let's finish this Nugget up with a look at your SQL challenge. Let's bring out Solution Explorer, open up O4_SQL.sql where your mission is going to be to write a query that targets three tables. You're going to pull back employees and their personal information. So it's going to be a bunch of inner joints here between employee, person, and email address. Pull back these columns so we can get a nice, complete employee record. Also, if you're not sure how to link these tables together, what you can do is expand Adventure Works, head into database diagrams, and there is an employee diagram waiting for you.

16:10So you can use this diagram and these relationships to find out which columns you should join on to bring all these tables together. So now is a good time to pause me if you want to try this out on your own. If not, we're going to do this one together. All right, let's do this. Now, the absolute best way to become a SQL ninja when you're training is to type everything out by hand. So I would advise doing that because you'll commit the structure of SQL to your muscle memory and your physical memory. But I'm not going to make you sit here and watch me type all of this up. So there is a simple solution. And again, if you're not comfortable with writing transact SQL yet, feel free to use the query designer here and watch it write that SQL for you.

16:48So here is that our query looks like. We're grabbing the employee table-- that's our leftmost table here-- so that way we can grab the job title and the hire date. Now, we're going to join the person table to that employee table by performing an inner join on the business entity ID column between these tables. From there, we're going to join the Person.EmailAddress table so we can get the email address of our employees. We're joining that to the employee table using an inner join, again, on that same column. And that's what allows us to pull out the email address. Person allows us to pull out their first and last name, and again, employee, job title, and hire date.

17:19We don't need to highlight this because I didn't switch our connections here. So I'm going to hit Execute, which will switch our database connection and then execute the query. And there's the result. 290 records, all of our employees with their first and last name, job title, hire date, and email address. In the hands-on lab Nugget, we covered the many flavors of the join operator and how we can use them to write queries that target multiple tables. I hope this has been informative for you and I'd like to thank you for viewing.

0:00Transact SQL is loaded with many built-in functions that we can use for a variety of purposes. For example, we can transform and format the results of our query using string functions. We can perform calculations with mathematical functions, tally up data with aggregate functions, and even make decisions with logical functions. So in this Nugget, we're going to break down what a function is. We'll talk about scalar versus table valued functions, determinism and why it's important, and we'll talk about SARG-ability, which stands for search arguments, and why we need to be careful about where and how we use these functions within our queries.

0:34Let's get started. I like to think of functions like little command line utilities built into Transact SQL. We call them by name. We optionally pass them a parameter. They go and do work with that data and return a result. That's all a function is. They're little reusable black boxes of code that we can use to improve the results of our query. So in our example query here, we are making four function calls. Two of these calls or to the exact same function. And by the way, these are all built-in functions. We're going to talk about user-defined functions a little later on in the course and learn how to build their own.

1:06So the month built-in function here accepts an expression of a date time data type. Here, we're just passing in a column-- the hire date column-- and this will return the numeric representation of the month of that date. And the result here is hire month, which we have down here. So this will return a value of 1 to 12-- again, representing that month. Another example here is the intermediate if function that we're using. This is a logical function or a system function that evaluates an expression. If this expression evaluates to true, it returns the true part.

1:38If it's false, it returns the false part. So in the employee table, we have a gender field that is either F or M. So it's going to evaluate this for every record. If it's F, it's going to return female. If it's anything other than F, it's going to return male. Another useful built-in logical function is choose. This one, again, evaluates an expression as the first parameter. And then, it will return the index of whatever that expression evaluates to of the remaining parameters. So in case, we know we're using another function inside of here as the expression, but we know that's going to return a value of 1 through 12.

2:10So that means if it's 1, it's going to return this one. 2, this one. 3, this one. So on and so forth, all the way to 12. So those are just a couple of really simple examples of built-in functions, but you can begin to see how they really empower us and give us control and flexibility over the results of our queries. Now, there are two kinds of results that a function can return-- scalar valued, which is just a fancy term for single valued. All built-in functions are scalar valued. Look at all these functions that we just looked at. They all return a single result. And there's also table value.

2:43These return a table, just like we have here and just as the name implies. And we build these using UDFs. In fact, they're actually called table valued user-defined functions. And you're generally going to use these to enhance a view-- to parameterize it. So you'll place a UDF on top of a view that accepts parameters or to replace a stored procedure that returns a single table. Again, more on UDFs. We've got a Nugget dedicated to it down the road when we get into programmable database object. We also have the concept of determinism. Now, deterministic means that given the same set of parameters, a function will always return the same result. These are all examples of deterministic usages of built-in functions.

3:23So if you were to pass these exact same parameters into all these functions, just as we are here, over and over and over and over again, you will always get the same result. Non-deterministic means you will get a different result given those same parameters, and there are certain functions out there that can be either deterministic or non-deterministic depending on what you passed to it. Here's the random function, for instance. If you pass a seed in, like we are here, then it will always return this value. If you don't, it will always return of a random value. So the million dollar question, why does this matter?

3:55And the answer is using non-deterministic functions in certain database objects can limit what you can do with that object. For example, if you were to use a non-deterministic function inside of a view, you wouldn't be able to place an index on that view, which can dramatically affect performance. And there is a property, by the way, called deterministic on all columns from tables and views that you can see in SQL Server Management Studio. Something else we need to talk about here that functions can have a huge impact on when used in your query is SARG-ability. Now, SARG, again, stands for search arguments, and the whole idea is that a query is said to be SARG-able if the database can take advantage of the index to improve performance.

4:36Where and how you use functions within your query you can have a dramatic impact on if that query is SARG-able or not. Here's an example. Now, both of these queries return the exact same result. One of them is SARG-able and performs what's known as an index seek. So it only reads into memory exactly what it needs. So it performs very well. The other one is not SARG-able and performs an index scan. It actually you has to read the entire table into memory and therefore performs poorly. Can you guess which one it is?

5:06It's actually the first one here. This is bad because if you look at the predicate in the where clause, it's using the datediff function and we're passing in a column as a parameter to this function, which means it's going to need to evaluate this for every single record in the table that you specify in your query. And by the way, the datediff function is incredibly useful. We'll be using it quite a bit in the future. But essentially what it does is it gives us the difference between two dates, depending on your interval. Here we're saying day. So it's going to give us that difference between the order date and the current date, and if that value is less than or equal to 30, then it will return it in the results.

5:44So what we're doing here is trying to find orders that were placed in the last 30 days. And again, this is bad because it will need to run this function for every single record in the table to see if that record meets this condition. Now, we can actually rewrite this query to get the same results, only make it SARG-able. I love that word. It just rolls right off the tongue. And that's because this condition doesn't need to be evaluated for every single record in the table. So it's not going to need to do a table scan. In fact, this is going to get resolved once, which will give us what the date was 30 days ago and this will then return any orders that are greater than or equal to that date, which again, is the exact same result as above, the difference being this can take advantage of indexes and will not require a full table scan.

6:27So this is something you will want to be mindful of when designing your queries because it can have a huge impact on performance. And thankfully, execution plans can help you identify problems like these. One final note, again, Transact SQL is loaded with many functions spanning many categories. Learn as many as you can because the more functions you have in your skill set, the more problems you'll be able to solve in your queries. Make sure to check out our hands-on lab Nugget on built-in functions to see all the stuff we talked about here in action and look at some of the more popular functions that you see in everyday queries.

7:01In this CBC Nugget, we covered built-in functions. We defined what they are, how they're used, and how to use them. I hope this has been informative for you, and I'd like to thank you for viewing.

0:00In this Hands-on Lab Nugget, we're going to write some queries that utilize built-in functions. We'll get familiar with some of the popular built-in functions that you'll be using in everyday queries, and we will look at how to write SARG-able queries. Let's fire up the virtual lab and get started. From the desktop of our SQL Server, let's head down the SQL Server Management Studio, give that a right click and launch right into our solution containing all of our projects. Once Management Studio loads up, it should expand our project called Built-in functions. And let's start just by going over some of the popular functions across many of these categories, and we also hide Solution Explorer here and we'll push Object Explorer aside as well.

0:38And first things first here, let's switch our connection over to the adventure work sample database. There we go. And we're going to start by looking at a couple of logical functions. Now, this is the exact same query that we were analyzing back in our whiteboard on Built-in functions. But one useful tip I want to give you here is, if you're looking at somebody else's query and you see them use a function that you're not familiar with, place your cursor on it, hit F1. Don't forget about help, it's extremely useful. Again, you can get a nice overview, get familiar with the syntax, the arguments that function supports, its return types, and then, of course, get some examples.

1:10Another really useful tip I want to give you here inside of the help UI is you can actually sync the page you're on with the table of contents. So notice here that the table of contents isn't synced at all. It's just on the default. Well, this button here on the toolbar will actually sync it up and take you right to where that exists in the help. And what's useful about this when it comes to functions is now you can see all the different categories of functions. So there's our logical functions in there. Say we wanted to get familiar with some string functions, we can come down here to the string functions category and look at them all.

1:40So this is just a great way, some good documentation here, good reading material, good learning material, especially, if you want to learn a lot about functions. Because it's pretty easy documentation to get the hang of it. Of course, the examples help. So keep that one in mind. All right, back to our logical functions here. Again, choose is useful because what you do is you pass in an index or something that will resolve to an index, and then it will return whatever value is placed in that index of the remaining parameters here. If is the intermediate if statement, you pass in a bolding expression, something that will resolve to true or false.

2:14This is the true part. This is the false part. And again, here is what the result would look like against the AdventureWorks database. Let's also talk about conversion functions. There is something very important that you need to understand about converting between data types. When an operator combines two expressions of different data types like we have here, we have a character. This is obviously a character string, so it's going to result to a character datatype. And the get data is returning a date data type. What happens when we combine these expressions together.

2:44Well, let's find out. Let's highlight this and hit Execute. Something bad is going to happen. Conversion failed when converting data time from a character string. So how the rules work, there's a whole data type precedence that goes on here. And the rule is that the data type with the lower precedent will be converted to the data type with the higher precedent. And if that conversion is not implicitly supported, then an error will get returned just like we have here. And this is one of most important things you need to understand as a SQL Server developer and query writer is when you get things like this, you need to perform manual explicit conversions.

3:20And we do that using cast or convert. What's the difference between these two? One is the SQL standard in ANSI or ISO SQL standard, and the other is Transact SQL specific. So CAST is the standard, and you want to use this if you want your database to be portable across multiple systems, and you'll use convert if you're purely a SQL Server shop. And really, you get a couple of extra features with CONVERT. Like you can format it. It's got a formatting parameter on it, and the syntax is a little different between these. But generally again, use CAST whenever possible, especially if portability is important in your world.

3:55Use CONVERT if your a SQL Server only shop. But here's how we do it with CAST, we specify our expression and then as our data type. So this will actually explicitly convert our date data type here to a character which is compatible with, obviously, this stream here. So this will work if we highlight this block of code and hit Execute. Look at that, it works just fine. Here is the convert way of doing it using that third parameter which we will again format it and style a little bit different than we have down here.

4:25Go ahead and hit the white space over here to select that whole line. Hit Execute, and there it is. Just a little different format there. Let's look at a couple of more examples here, only this time in the context of a query. Let's start with this one right here. I'm going to highlight this and Execute it, so we can take a peek at it. And let's take a look at what's going on here. So this is a simple query that ties product and product inventory together to give us our quantity, standard cost, and the total cost, which is quantity times standard cost. But notice something here, if you hold your mouse over quantity, we can see it's data type is a small integer. If we hold our mouseover standard costs, we can see that the data type is money.

4:58So for this total cost column right here an implicit data type conversion happened. The smallest one, or the one with the lowest precedence here, was quantity. Small integer is low on the data type precedence, and it was converted to standard cost which is money, which is higher than small integer, all right? So that's why that implicit conversion took place. And in this case, it's OK. That was kind of our intended result here because the total cost would probably be a money debted thing. So that's just fine, this was a legal implicit conversion and what we were intending.

5:28But there are situations where a conversion may happen implicitly, but you didn't really intend it to be that way. In that case, you will need to explicitly convert your data types. Here's an example right here, I'm going to go ahead and execute this one as well. And what we're going to notice here is, let's say that our boss came in and said, you know what, I need you to reduce the quantity of inventory by 50% across everything. Can you write me a query to do that? And well, yeah, sure, why not. So we write the query of quantity and we multiply it by 0.5. What's going to happen here, quantity is a small integer, 0.5 is a decimal.

6:03So quantity is going to get converted to a decimal. Decimal has a higher data type precedence. And that's going to result in quantities with halves, with decimal voices on them. And we're not selling half bikes. We might sell bikes that are half off in price, but we're not selling half a bike, right? So that would definitely make our boss rather angry. So in this case, we would need to do an explicit conversion. We could still perform our calculation but inside of a cast here to cast the results of this to a small integer, and that's what we get here.

6:34So keep those things in mind. They're very important. Again, if something cannot be implicitly converted, you'll get an error, and you'll need to explicitly convert it, and then just keep unintended consequences in mind because they can certainly happen within your queries, and that again is where you'll need to explicitly convert your data types. Now moving on, let's take a look at a couple of popular string functions. Again Transact SQL has a lot of string functions in them. I would certainly advise going through the documentation, just running through a couple of samples because these are extremely useful to have in your back pocket, especially when it comes time to slice and dice up those character fields.

7:07And let's start with the left and right string functions. These are incredibly useful, incredibly powerful, and often used in combination with other string functions to get the results that you're looking for. And another tip here is if you hold your mouse over any function, you can get the syntax, including the parameters they accept, the data types on those parameters, and the data types that they return. Now the left function will return a portion of a string or an expression. So you pass in your expression, in this case, we're just passing in a column and then the number of characters you wanted to count. So in left case, it's going to count left to right. In the right string function, it will count right to left.

7:40So this query will actually return the first name, the last name and then the initials because we're only grabbing the first character of the first name plus the first character of the last name. If we execute this, that's what we'll get as a result. And I should also mention, these become very powerful when combined with other functions like CHARINDEX and PATINDEX because instead of statically placing a number to count over, we can make that dynamic by searching for specific characters or patterns of characters within a string. Speaking of CHARINDEX and PATINDEX, you will see these used quite a bit in stored procedures and queries that deal with string searching, manipulation, transformation, all that stuff.

8:16And essentially, the difference between these is CHARINDEX does not support wildcards, PATINDEX does. Both of them return the starting position of whatever string you're searching for. So in this case, CHARINDEX, we're just searching for any cities that contain the word city. And this will return the starting position of the sea in this case in city. PATINDEX, we're searching for anything that contains towns. So any cities that contain the pattern here of town will get returned. And this will return the starting position, and the WHERE clause is what is driving this because this will only return records where cities have the word city in it, and cities have the word town in it.

8:54And if we highlight all of this, and it executes, we should see just that. Everything's either going to have city or town in it. City starts at is driven by CHARINDEX, towns starts at is driven by PATINDEX. And if you scroll down, we should have a couple of towns in there and quite a bit of cities. Again, those are incredibly powerful when combined with other string functions. Now, we've also got substring replace and reverse, a couple of other useful ones here. Here we're going to take the product number and do all kinds of fun things with it. First, let's highlight this and we'll scroll down in a script pane here, so we can see what we're dealing with.

9:24So the very first column here is product number as it is stored. The second one, using the left function will return the first two characters. The third one here though, substring is the big one. This is a function that everybody should learn because it allows us to find strings within strings, again, one of the most common string functions you'll see out there. And if we look at the syntax, we've passed in an expression, a starting position, and the number of characters to return. You'll often use something like CHARINDEX or PATINDEX to find that starting position of a character for the second parameter here.

9:56But let's say that our boss came to us and said, you know what, I need the four characters after the dash. And you can see there's quite a few different formats, but they need that middle code here. So we get the partial product number, and that's exactly what we're doing here is we're saying, we're past the product number, then we're using CHARINDEX here, which by the way, has an optional third parameter as a starting position. We didn't actually need this here, but I wanted to show it to you because the default is starting at zero which is why we didn't use it up here. But this is going to search for the very first dash, and then it would count over including that dash, which is why we add a plus one to this because everything is zero based.

10:30So that means it will start at the character after the dash. And that's why we get 5381, 8327, so on and so forth because we're saying, only count over 4 after that dash, and that'll give us the result. So that's substring. Replace is another really useful one. In this case we want to replace the dash with nothing, with a blank string. So we get that dash out of there. So that will strip the dash out of here and give us a new product number. And reverses is a fun one that will just reverse a string. So there's just a small taste of some of the useful string functions that you'll be working with in SQL.

11:01We also have quite a few daytime functions. A couple of useful ones here are DATENAME, DATEDIFF, DATEED, DATEPART is another good one. But here's an example using DATENAME, you pass in the part of the date that you want to extract, and it will give you that date from whatever column or expression that you pass in. So here it's taking OrderDate and then slicing it up into all the different parts of that date. Moving on here to DATEDIFF, probably one of the most popular date and time functions out there. This will return the interval between two dates.

11:32And you specify that interval. That could be year, month, day, those probably being the most popular ones here. But you pass in your starting date and your ending date, and it will give you the differences between them. In this case, this will give us the number of years that an employer was employed. It will take their hire date as a start date and the current date as the end date and only give us how many years are between them. DATEADD, another really useful one that works similar. We passed in an interval, we passed in our increment which can be positive or negative. Now we pass in a date, and this will resolve then to, in this case, five years ago from right now.

12:06And we're saying, give us any employees that were hired where their hire date is less than or equal to 5 years ago from now. So the end result here will be any employees that were hired five years or more. So it will show us some veteran employees out there, and if we highlight this and hit Execute, you'll only see years employed that are five and up. See that, so no less than five in this case. Moving on, here's a small taste of some system functions as well. ISNULL is a popular one. There's also ISNUMERIC for testing out values. In this case, if a value is null, it will replace it with whatever you put as a second parameter here.

12:41New ID will generate a brand new global unique identifier, then you've got some other system level functions out here that are useful when you're auditing. You've got the system username, which is the currently logged on user name, database username, which is the database user's name, and the name of the machine. So if we highlight all of that, we can see here there's our first one where it's going to replace all the nulls with an A. There's our global unique identifier. And here is the name of the currently logged on user, the name of the database user, and the name of the machine. Again, I'll try to sprinkle in as many of these as possible as we write queries throughout the course.

13:12All right, let's move on and talk about how to design SARG-able queries. This is extremely important, something you want to ingrain into your head because non-SARG-able queries can be disastrous, especially in a production environment. Now remember, what makes a query SARG-able is if it can be covered by an index. And a lot of the times we can make queries non-SARG-able by placing things like scalar functions as predicates within our WHERE clause. Here's a good example of that. First, let's switch our database connection here to AdventureWorks.

13:44And our first query here uses a capacity order date column into DATEDIFF. And what this is going to result in is an index scan. And you actually see this if we pop open the actual execution plan here. So make sure that button is toggled on. Hit Execute, and you won't be able to tell, oh, sure, it was fast because we have a fast database server. But still that took two seconds to return 5500 rows. And if we head over to the execution plan, that performed an index scan. And scans are bad news because it would never be able to take advantage of an index.

14:14In order to resolve and run this function, it would need to do it for every single row in the table to see if it qualified. And by the way, what this query is doing is grabbing any orders that are over five years old. And if I hit Control in order to bring up our results here, that's what you'll see is, eh, all these orders are over five years old, all right? So we can make this much better. And we can still do it using a function in the WHERE clause as long as we don't actually pass a column into that function. So we can get the same exact result here and make this SARG-able by just designing it a little differently.

14:48And that's usually what it comes down to when you have a query that's performing poorly and you realize that it's doing an index scan rather than an index seek and need to make it SARG-able, it's just a matter of getting creative and trying to think your way around the problem. And that's exactly what we're doing with this predicate down here is we're saying, rather than give us the date difference using the order date and getting there where anything is greater than or equal to 5, we're saying, generate the date as it is of now five years ago. So this will give us five years ago from today, and it will give us any order date that's older than five years ago today with a less than or equal to.

15:22This will give us the exact same result if we execute this and go over to our execution plan. Now it's still doing index scan, that's because we don't have a proper index to cover this query, and that's another reason why these execution plans are great because it identified that missing index and look at the performance impact we can get here by creating it. So if you right click anywhere in the whitespace here and choose missing index details, it will actually create the index for us, or at least give us the T SQL. Now I'll just move this comment up here. And now we can execute this. And it's going to create a non-clustered index. All we need to do is give it a name here.

15:54I'm going to call this IX underscore SARG-able, there we go. So this will create an index on the order date and also include the sales order ID. But if we execute this, it's going to create that index, all right? Now watch, let's head back to our query here and they're actually going to-- well, yeah, at first, let's just execute this one to make sure that we're doing an index seek, beautiful, index seek. And now, let's highlight both of these to see the performance difference between a non-SARG-able and a SARG-able query. It should be pretty massive. It is, look at there.

16:2588% of that batch was focused on resources handling that first non-SARG-able query and only 12% for that second query. And again, the big difference there is the index scan versus the index seek. Impressive right? Let's take a look at another example here only this time with strings. And look at this, this is non-SARG-able here. Why? Because we're using a function as a predicate in the WHERE clause and passing a column to it. So this is non-SARG-able and this will result in an index scan.

16:57Our second one however is SARG-able because like when you place the wildcard at the end, it's SARG-able. If you place the wildcard at the beginning, it's not. And there are other things. Anything that's not equal to or not like, those are all non-SARG-ables so be careful about using those. But this should be SARG-able, and if we highlight both of those and hit Execute and go to our execution plan, look at the difference here. We've got an index seek down here, and we've got an index scan up here. 3% are bought in one particular, as opposed to 97% of the batch for a top one. And again, a lot of the times it's not going to be obvious.

17:28So that's why execution plans are your good friend in paying attention to the actual operations that are performed under the hood to retrieve the data. All right, let's finish up here with a SQL challenge. I've got a good one for you. Let's open it up. Here it is. I've written you a non-SARG-able query here because we are using the year scalar function in passing in order date to it. Our goal here is to get all the orders in the year 2016. And we get them with this query, but again, it's not SARG-able. So can you rewrite the WHERE clause of this query to make it SARG-able.

18:03And I'll give you a little hint here, view the execution plan and also you will need to create the necessary index for that to work. Make sure you switch to the wide world importer's database, you should be by default. And pause me now if you want to try this on your own. If not, we're going to do it right now. So let's copy this query, and let's paste it down here. And let's fix this WHERE clause. This is actually a lot easier than it looks to make this SARG-able. We can use an open-ended date search by doing something like this where OrderDate is greater than or equal to. Remember our date format here, remember it's year, and then 0101, and OrderDate, less than or equal to 2016 12, 31.

18:45There it is. That's an open-ended date range search. They're covering all of 2016, and this is SARG-able because we're not referencing any function, were not passing any columns into a function. So if we highlight both of these-- and don't forget to turn on your execution plan here-- and then hit Execute, we should hover over our execution plan, and we're going to see that we are going to need a non-clustered index down here to cover this query. Again, right click in the whitespace. Choose to find our missing index here, and it will write it for us. We'll just move that comment up there, and then we can execute.

19:18It'll create the index, perfect, and we can close out of this. And we can re-execute this query. And this time we should see a big difference, once again, 88% up top, 12% at the bottom. And so that's why we can take our poor-performing, database-crushing, non-SARG-able-queries and turn them into high performing SARG-able ones. In this Hands-on Lab Nugget, we took a journey through the world of built-in functions. We took a sampling of some of the popular ones out there, saw how to get help on them and looked at SARG-ability and how it can affect our querie's performance.

19:48I hope this has been informative for you, and I'd like to thank you for viewing.

0:00In order to aggregate data, we use the GROUP BY clause in conjunction with one or more aggregate functions, like SUM or AVERAGE or COUNT or MIN or MAX. In this Nugget, we're going to break down the Group By clause and get familiar with some of these aggregate functions. Let's get started. The Group By clause operates on one or more columns, and turns those columns into groupings. So for example, if we take this raw data here-- and this is just a sampling of data out of the WideWorldImporters sample database-- and we were a group on this first column here-- color name-- it would squish all of these together, just like so.

0:36That will then allow us to perform aggregate functions on the rest of the data per group. So for example, we could use the COUNT aggregate function to get a count of items in each group. For example, we have three black items, five blue items, two red items, one steel-gray item, and five white items. And that just happens to be exactly what this number of sales column is in the aggregated results. We could use the SUM aggregate function to total up the value of a column-- or columns or expression-- in each group. And that's actually what we're doing here with total sales, is this is quantity times unit price for each group and that value all totalled up.

1:12And average sales is doing the same thing, but instead of a sum, it's doing an average of quantity times price per group. Just a couple of notes about GROUP BY. Number one-- HAVING is, again, our filter for the group's data. Remember, the WHERE clause is our filter for our FROM clause. For the raw data, HAVING works on groups data. We'll look at some good examples there when we get into our lab Nugget. Also, if your grouped columns contain null values, all of the nulls will belong to the same group. So I'll get placed in the same group, and aggregates will work against any grouping with a null value.

1:43Finally, we have rollup, cube, and grouping sets. These are all extensions to GROUP BY, as you can see here in the syntax, that essentially allow us to add extra records to the end of each group for totals, subtotals, and grand totals. You can do it by group, across groups, or groups crossed with other groups. And we'll look at some good examples of those when we get into our hands-on lab Nugget on writing queries that aggregate data. How about an example? So here is the GROUP BY query that powers those aggregated results. And for reference, again, here's the raw data that it goes through to get to those aggregated results.

2:15So again, we're grouping by the color name, which is the first column, which is going to create the basis of all of these groups that we were looking at before. And anything that you group by, you can pull back in your Select list as is. But anything else that isn't in your group, you will need to perform an aggregate function against. So that's why everything else we're using an aggregate function on. We're doing a sum of the quantity times the unit price here-- just like we see. And we're doing an average of the quantity times the unit price here. So we have an expression that we're passing into these aggregate functions.

2:48And then we're just also grabbing a count of each grouping, which is where the number of sales comes into play. The most common aggregate functions that you'll see in everyday queries are these. We've talked about average sum and count. You also have MIN and MAX. MIN for the lowest value in a grouping, MAX for the highest value in a grouping. And also, notice here that you have all. This is, again, implied. And it's the default, which is why you don't see it specified in here. And that just means count every item in the group. We can also explicitly pass in DISTINCT to each one of these, and you would pass it in after your expression.

3:18And that would only count unique items within that grouping. So for example here, if we were to apply a DISTINCT to, say, our sum-- our total sales here-- then it would do this after our expression. So 4 times 13 is 52. 4 times 13 is 52. 8 times 13 is 104. So it would only count one of these 52's in that group. That's what DISTINCT is all about. Finally, let's say that we wanted to add a HAVING clause into this query. Let's say that we only wanted results where the average sales were greater than 100, which means these last two records wouldn't make the cut, because they're less than 100.

3:54Well, we would add that right after our GROUP BY. And remember, with HAVING, it still-- in the logical order of operations-- happens before SELECT. So we wouldn't be able to use the actual name of our alias column. We would need to use the expression itself. And that's that for the basics of grouping and aggregating data. Again, remember to join me in our hands-on lab Nugget titled, Writing Queries that Aggregate Data to see all this stuff in action. In this CBT Nugget, we covered the GROUP BY clause, which is used in conjunction with aggregate functions to summarize and aggregate data.

4:25I hope this has been informative for you, and I'd like to thank you for viewing.

0:00I've mentioned in previous Nuggets how a database server spends most of its time responding to read requests. And we as SQL Server developers spend a good majority of our time writing SELECT queries. But there are three other lesser used statements in that subset of SQL known as DML, the data manipulation language, that we use to modify data. We're talking about INSERT, UPDATE, and DELETE. And that's what we're going to cover in this Nugget. Let's jump right in. Let's begin with the INSERT statement. This is the statement that we use to add new records into our database tables.

0:32And you can see we have quite a few ways that we can use it. The first one we're going to look at here is it's most basic form, INSERT values. This is where we run an INSERT INTO, specify our table, specify our column order, and then values contain the actual data that we're going to insert in. And this part right here where we specify our column list is actually optional. If you don't specify this, then you need to have your values line up with the exact order that your columns are in the table. If you do specify your column names, you can omit columns or change the order.

1:04That way you can supply your values in any order that you like. Now this INSERT statement is actually inserting three records. There's record one, bronze. Two is crimson, and three is turquoise. If we were to execute this statement, it would add these three records into this Colors table. Now the three things you need to look out for when inserting data into a table are defaults, NULL values, and constraints. When you attempt to insert a record into a table and you do not supply a value for a column, just like we aren't here for ColorID, ValidFrom, and ValidTo.

1:34Notice we didn't supply a value for these in our INSERT statement. The first thing SQL Server is going to do is check to see if it has a default associated with it. ColorID is an autogenerated field. And these can come in two flavors. It can be marked as an identity column, will increment that column based on its seed. And identity is bound to a table. And there's also a sequence object, which is another autogenerated object. Only it doesn't live with the table, it lives outside of the table. So it's global. But in either of those cases the default will be picked up and applied by SQL Server.

2:08So that's why we didn't need to supply a value for ColorID. It grabbed those values automatically based on, in this case, a sequence object. And by the way, ValidFrom and ValidTo also have default values associated with them through temporal tables. Now another thing, if a column doesn't have a default value associated with it but it is allowed nullables, then SQL Server will put a NULL value into that field. And if it doesn't allow NULLS, well, then you'll get an error with your INSERT statement. The other thing you got to look out for, again, is constraints. And constraints can come in many flavors.

2:39But essentially they ensure that a column lives by a set of rules. And that could be a check constraint. This column must have this format of data or this range of data. Or it could be a foreign key constraint. This column must contain a value that exists in another table, and therefore must respect that relationship. This LastEditedBy is a good example of that. This is actually a foreign key that references a primary key in the Person table. That way this table can keep track of the individual that either entered or modified this record.

3:13So that's why we needed to supply that here, because if we didn't, it doesn't accept NULL values, and it has a foreign key constraint, again referencing the primary key inside of that person table. Next up we have one of my favorite ways to run INSERT statements into tables to quickly load them up with information, is INSERT SELECT. This is where we write a SELECT query and take the results and just slam it all in to a table. So we still form our insert statement the same way. We specify our table, specify our columns and their order. And then we make sure that we write our SELECT query so it lines up, those columns line up, and the results of that SELECT will get shoved in to that table.

3:51Finally we have INSERT EXEC. And this is where we take the results of a stored procedure and again, pipe them right into an INSERT statement. Next up we have the UPDATE statement. This is what we use to modify existing records. So we have UPDATE followed by our table name and then set followed by the columns that we want to modify. If we have more than one column like we do in our example here, we separate them by a column. Also notice that we can have WHERE clauses here for update statements. And this is something that you want to pay close attention to when you're dealing with update and deleting.

4:24Because if you're going to delete a range of data, you want to make sure that your WHERE clause is accurate so you intend exactly what you intend to target. When we get into our virtual lab for modifying data, I'll show you a great way to test out UPDATE statements to see what they would hit before you actually run it in a production environment. So if we were to look at this example, here's our table before the statement is ran. We are going to reduce the quantity on hand, this column right here, by 30%. We do that by multiplying it by itself, which is what the multiply equal sign will do, and then by 0.7, which will give us the 30% reduction in quantity on hand.

5:03And that's how we go from this value to this value. And we're doing the same thing with reorder level except we're only going to reduce this one by 10%, which is why we multiply it by itself and then by 0.9, which will give us a 10% in reduction. So here is the after table. Now this is all dealing with a single table. What if we wanted to update data in one table based on information in another table? Well that's where we bring our join skills into the mix. This way we can update columns in one table but create a WHERE clause that spans many tables, exactly like we're doing here.

5:40Here, rather than update our QuantityOnHand and ReorderLevel based on this bin location, we're actually going to do it based on the StockGroupID. In this case think of that like the category of the item. And StockItemGroupID of 3 is mugs. So we're only going to update the QuantityOnHand and the ReorderLevel for strictly mug items. And I know this can look a little funky at first, but I like to write them. First write a SELECT statement. Because everything below, from and below, is the exact same, no matter if it's an UPDATE, a DELETE, or a SELECT statement.

6:10So write your SELECT statement so that you can see all your data, and then rip out the SELECT and replace it with an UPDATE and just specify either the table name that you want to update or the alias that you give it like we are here. And everything else will work just like it does over here, except that, again, we can create a WHERE clause that makes decisions based on rows and columns in other tables. Our final statement is for removing records, and this is done with the DELETE statement, probably the easiest of all of them because we don't have to worry about columns, we're just deleting records.

6:41So we specify DELETE followed by our table name, and then again very important that we don't forget our WHERE clause when it comes to DELETE, so we don't accidentally delete more than we intend to. And both of these statements do the same thing by the way. One of them is sargable. One of them is not sargable. But both of them will remove any data that is 2014 and prior. So everything above 6 here, this will be the final result. And also, you can delete with JOIN statements as well. Again, it works the same exact way here. Just remember to write your SELECT statement, remove SELECT, and replace it with the DELETE and the table or alias name that you intend to delete those records from.

7:20Now the last thing we're going to cover is the OUTPUT clause. This is something that we can attach before the FROM clause to any of our data modification DML statements, INSERT, UPDATE, and DELETE. And what this will do is output any records that were affected by that operation. This is extremely useful when you want to do something else with that data, maybe confirm it before somebody deletes it or archive it or audit it. So putting an output statement here will then, rather than just say this operation completed, these are the number of rows affected, will actually output those rows that were affected so we can do something with them.

7:53Now how this works is whenever we run a DELETE statement, like this one here-- this is the one we're just looking at in our previous slide-- it will create a virtual table called DELETED. And by outputting DELETED.*, we're saying give us all of the columns from that DELETED table in the output. And again, we could then do something with this data, maybe pipe it to an audit table or a history or an archive table for storage, so that way we have a record of everything that was deleted from this table. The other virtual table is the INSERTED table. And we can access this in every way we run an UPDATE statement.

8:23Because an UPDATE is really a DELETE followed by INSERT. So by running OUTPUT here, when we run an UPDATE statement, we can look at the deleted table to see how it looked before that update occurred and the INSERTED table to see how it looked after that update occurred. So these are from the DELETED table. This is the old data. And this is from the INSERTED table. And that's that. Join me in our hands on lab Nugget titled "Writing queries that modify data" to see INSERT, UPDATE, DELETE, and OUTPUT in action.

8:55In this CBT Nugget we covered our three statements used to modify data with Transact SQL, as well as how to track those modifications with the OUTPUT clause. I hope this has been informative for you and I'd like to thank you for viewing. .

0:00In the hands on lab Nugget, we're going to learn how to write queries that modify data, and look at the many ways that we can do so within each modification statement. Let's fire up the virtual lab and get started. Once you've logged into our SQL server and the desktop loads up, let's get right into SQL Server Management Studio with a right click and launch in to our 70-761 Scripts Solution. And in Management Studio, we're going to be working with our project here, 08 Modification Queries, and we're start with the INSERT statement. So let's go ahead and fire that up. Let's also get a connection over here to Object Explorer.

0:30So I'll just hit that button on the toolbar there, the shortcut button, and hit connect. Let's also push everything aside for now. Let's start with insert value. Now, insert value is good for those one off situations, where you just need to get a record or a handful of records into a table, and also for scripting. This is actually what Management Studio will generate when you script out a table and it's data. I'll show you that here in a little bit. Now, whenever you write these statements, again, we need to respect the columns, that data types, as well as their nullability, as well as constraints.

1:02So it's always good to get familiar with the table and all those aspects. Let me show you an easy way to do that. If we head into Object Explorer and expand databases, and expand wide world importers, and expand tables, we'll eventually get to the table that we're looking for here, Application.People. We're going to enter in some people records here. If we right click on that, head down to script table, and head over it CREATE To, you can create this to a file, a clipboard. Let's just use new query window. It'll just pop open a new query window and pop in the definition of this table. And now, we can see the columns in here, as well as their definitions, their data types, If they're nullable, if they have-- if they're a calculated column, like search name here is a calculated column.

1:39And also, if we scroll down, any constraints that are associated with them. So we can see we have a constraint on person ID. It actually gets its value from a sequence option. We also have a check constraint, which is a foreign key constraint on that last edited by. So we must put something in that last edited by column that references itself, so a person record that already exists. That's about it. All the rest of these down here are just extended properties that are added to the object. So those are the things that we need to respect, as well as nullability. And if we close out of this, those are pretty much all the things we have here.

2:13So we need to enter a full name, that's required. And again, you hold your mouse here over any of these columns, you can see their data type, as well as their nullability. So we need to provide a full name, we need to provide a preferred name, and then all these bit columns as well. And finally, last edited by, which must be an ID of a person that already exists in this table. So let's go ahead and highlight this first block of code, which will create a new record here for Kylo Ren in the Application.People table. Now, I also mentioned how we can supply multiple records in one insert value statement.

2:44In here, all we do is comma delimit each record. So we're adding three records in this case, to the table. If we highlight all this and hit execute, all three of those rows will go into the table. Now, if you want to see how this data looks inside the Application.People table, we can write a quick SELECT statement. Or I'll show you the easy way here. Let's just go into Object Explorer, right click, let's have it right that's SQL statement for us. And it's only to return the top 1,000, but no problem. We'll go to the bottom here and put an ORDER BY about person ID descending, re-execute it, and there is the four records we just added.

3:18See all the defaults that got filled in there too from things that we did not enter, like the person ID. Again, this is generated from a sequence object that lives outside of the table. So there it is, all four of those records entered in using insert value. Next up, we're going to look at INSERT SELECT. Now, INSERT SELECT is easily the most popular and useful of all of these inserts statements, just simply because of its utility. I find myself using this whenever migrating data across databases, or flattening data out between database types, or just transforming or formatting data into different output.

3:55Here's an example of that. Let's say that we wanted to take information out of the customers table and sort of flatten it out into one view. Maybe we're creating a query for the marketing department because they want to send holiday cards out to all of our real customers. So what we need to do then, is combine a bunch of tables together to get rid of these IDs and pull out the actual city name, the state name, their delivery method, their address, so on and so forth. And then we can take this and stuff it into a table, if we already have one out there, or we can create a brand new table and then put it in there as well.

4:26And I'll also show you SELECT INTO, which is a wonderful statement that we can use to take the results of a query and create a brand new table and put data into it. But here, we already have an existing table called customer stuff. And if we look at this table, you'll notice here that we have first name, last name, city name, state name, all this information broken out and flattened out. Where if we look at our customer's table here, which is down here under sales dot customers and we expand this, notice here, that we have just customer name. So it's not actually first name and last name, so we're going to use some string parsing skills to make this out.

5:00In fact, let's take a quick peek at the data here just to show you what I mean. So here, we have customers name. We also have people that aren't actual customers, but offices in here. So we're going to need to filter those out. And that should be pretty easy to do, because anybody that's an actual customer, not like an office or a branch office or whatever, has a null buying group ID. So right there, that's going to be something that we need to take into account for this query. But also, notice here our customer name is just one big name. So we're going to need, again, some string parsing skills here to break out our first name into its own column, last name into its own column.

5:31And then we have a bunch of IDs referencing other tables for their city, for their state, all of that stuff. So we're going to break all of that down as well. So that's why we have this one big query here that does exactly that. Pull out the customer ID, we slice up using the left combined with CHARINDEX here to get their first name. And that's going to detect the space here and then pull everything back before that space as the first name. And then SUBSTRING with CHARINDEX to pull everything after the space into their last name. And then we pull out the city name, because we have a join here on cities, state, delivery method, so on and so forth.

6:04And here's the result. If we just highlight this query, here's all the data. But we want to put that data somewhere, and that's where the insert statement comes into play. And as long as you design your select queries so they line up with the columns in your insert, you do not need to specify your column list, which is why we're not specifying here with our insert statement. Because all of these line up exactly. And by exactly, I mean data types or data types must line up. So if we highlight all of this and hit execute, all that data is going to go right into that customer stuff table.

6:36And if we head in Object Explorer here, we can take a peek at that data. So we'll right click on customer stuff, look at the top 1000 rows, and there it is, all flattened out using an INSERT SELECT. Now, I want it to show you a SELECT INTO. This is another way to do it. Only this time, rather than needing that table to exist, we'll create it on the fly. So this is the same query. The only difference here is we have an INTO right here. So this will automatically create this table and generate the columns, create the columns inside of that table based on the results of this query and their data types.

7:12So this will essentially give us the exact same results as we just did. A table with this data from this query inside of it, it's just going to create that table for us. So now, there's a table called CustomerStuff_v2 in Object Explorer. We can see this if we right click and refresh our tables, there it is. Pretty cool. So that's another really useful trick there of getting information into the table. Another one I want to show you here is INSERT EXEC. This is, again, how we can take the results of a stored procedure and put it inside of a table. So I created a very simple stored procedure called Warehouse.SearchforCustomers.

7:45So all you need to do is pass in a string here and it will search for customers. And you can also limit the number of results. And we'll look more at stored procedures down the road. But if you want to take a look at the definition here, in Object Explorer, if you expand programmability, expand stored procedures, there's quite a few of them here in the wide world importer's database. Here's one that I threw together. If you right click and choose Modify, it will actually show us the definition here. And there it is. So it's essentially the same query we were just looking at, just with the WHERE clause on it that we can parametrize and pass into here through this stored procedure.

8:15So you can execute the stored procedure all by itself. Just highlight it, hit execute, pass whatever values you want in here and a limit to how many records you want returned, and it'll give us those rows. And again, we can then take the results of this and use it as the basis for an insert statement. And once again, here, we're not we're not actually specifying our column names, because they line up, since it was the same query that we were just working with. And by the way, to make this work, you'll need to delete the data out of our previous table. Because watch this-- If we try to execute this right now, it's going to say no, violation of primary key.

8:48Because we have a customer ID and they're already in there from our previous query. And a quick and easy way to do that is to execute a TRUNCATE table and specify our table name, which is customer stuff. There we go, we can highlight that, hit execute, that will dump all the records out of that table. Now we can highlight this and those 20 records will go into that table. TRUNCATE, by the way, is very similar to the DELETE statement, in that it removes records from a table, but very different in how it does it. TRUNCATE is a non logged operation, so it doesn't fill up the transaction log with all of those deleted entries.

9:20Which also means that data is not recoverable via the transaction log. More on that in future SQL courses. Now, one more thing I want to show you with the INSERT statement is we can have Management Studio generate the skeleton of an INSERT statement for us. If we head into Object Explorer, pick any table here. I'm just going to choose our customers stuff table. Right click, choose script table as, and we can head down here to INSERT and generate a new query window. It will write that insert statement for us and then fill out the values with placeholders and their data types here. So this is a great way to get a good start on writing insert statements, insert values specifically here, through Management Studio.

9:59Let's move on here and let's talk about update statements. So let's bring back Solution Explorer and open up 02 UPDATE. So the update statement allows us to modify existing records in a table. After update, we specify our target table. Then, we use a set to specify the columns and the values that we want to set those columns to, comma separated if we want to apply it to multiple columns. And again, don't forget your WHERE clause. This right here will update every single record in the table, and reduce quantity on hand by 30%, and reduce the reorder level by 10%.

10:33Now, let me show you a great little trick that you can use to test your update statements before actually running them in a production environment. And we're going to talk about transactions a little later. But if we wrap our statements with a begin trend and then a rollback, this will actually encapsulate the statement inside of a transaction and roll it back. And what I like to do here, is actually place select statements in here. So we can do a select star from warehouse, that stock item holdings, and we'll do it again at the bottom. So we basically are wrapping all of this inside of a transaction.

11:06We're going to look at the data, execute the statement, and then look at the data again. And this is just a great way to do it. Because since the transaction will get rolled back, none of this will have actually happened. So if we push the window up a little bit here, here's the data before, here's the data after, and since none of that happened, the data is still right here. So if we look at our first record, 175609 and we just execute the SELECT statement here, look at that, 175609. So even though it happened, it didn't happen, thanks to wrapping that into a transaction and rolling it back.

11:39Now, we can also write our update statements using a join. And again, this is really useful when you want to update columns in one table, but use columns and other tables in your WHERE clause. So that way, you can target specific data. So if we take our previous UPDATE statement in here, which targeted everything in the stock item holding table, and say we only want it to target the quantity and reorder level for mugs. Well, we'll need to hit the stock group ID-- three is mugs, by the way-- which means we'll need to join this to that table so we can write a WHERE clause that hits that.

12:11Again, an easy way to do this, if you want to see what data is targeted is just write a select statement with your joins and then take a peek at that. So here is the data that would be affected. We can verify that these are indeed quantities for mugs. And then we can just replace that select with an update. And again, your update can target if you have an alias, the alias, as well as the table name. And then you just specify your columns and their values. So this time, we will run this. We can, again, wrap it around in a transaction if you want to see what it would do without actually doing it.

12:41But there it is, all of those records were updated. And finally, here we can use update with output. So we can see the data that was actually affected. And again, output is extremely useful when you want to audit the information that you're changing. And using output here with update, we can both look at the deleted virtual table to see how the data looked before the update happened and insert it to see how the data looked after it updated. And we can pull out specific columns as well. I'll show you another cool trick that we can use with the output statement to actually do something with that data when we get to the DELETE statement.

13:15But first, let's change these values. Let's say, you know what, mugs, they're just not selling well. So we're going to reduce the quantity and the reorder level by another 50%. Let's highlight all that, execute it, and now, our output here will be both record sets. So first, here on the left hand side, is going to be deleted. So this is our deleted right here, all of that is from the deleted table. So this is what it looked like before. Notice our quantity on hand here and then notice our reorder level, there it is. And now, if we scroll over, this is from that inserted table.

13:48Notice, quantity on hand is less and your reorder level is also less than it was before by 50%. So left here, deleted, right, inserted. So you can see the data, how it looked before and after that update occurred. One more thing. With update, you can actually script out the skeleton of an update statement as well. By right clicking on any table here, go to script table as, choose update, and pick your output there. And here it is. This will actually generate the update statement for every single column, and even place a WHERE clause there for you, just to remind you.

14:21Don't forget about your WHERE clause when you update records. Let's take a look at our last data modification statement here, which is the DELETE statement. Again, DELETE used to remove records from our tables. So DELETE from specify your table name, and then don't forget your WHERE clause. And again, you can wrap these in a transaction as well to see what records would actually get deleted from your table. We can use joins here as well. And again when you're building these statements update, or delete with joins, just write a select first to see what they would effect.

14:55Verify that data, make sure it looks good, make sure the structure of your SQL statement is good, and then you can just replace your select with your update, or in this case, your delete and target your table name or your alias. And by the way, this will actually delete anything from the order lines table, which are the individual line items that belong to an order that belong to stock group ID four, which is shirts. So this would get rid of all the orders containing shirts. And finally, again, we can use the output clause here with delete. The deleted virtual table will contain any records that were affected by your DELETE statement.

15:29And again, the asterisk here means return all columns, output all columns. You can also comma delimit to pull out specific columns. And what I wanted to show you here is that you can actually use the INTO statement here and specify your table. And what that will then do is output all that data into your table. And this table does need to exist. So actually, let me show you a cool way to make this. Let's turn this into a select into. So we'll do an ol dot star, grab everything from that order lines table, we'll select this into-- how about we just call this one sales dot order lines underscore back.

16:05There we go. And then we don't want any data in there, so let's just put a bum stock group item ID in there. And what this will do is generate that table with nothing in it. And now, we can just undo all the way back here. And then, we'll throw in into sales dot orderlines underscore back, just like so. And now, all those deleted records will go into this table. And this just says invalid object name, because our IntelliSense hasn't caught up here. If you actually hit edit, head down here to IntelliSense and refresh the local cache, now it knows that table exists.

16:38And there we go, now it can highlight all this. And it will delete that data, but take those deleted records and place them into that table that we just created. So that's a great way to actually do something with your data, such as audit it and whatnot. And now, those 44,000 records live in that new table, so it's almost like they weren't even deleted, right? They were just migrated over to another table. Let's finish this Nugget up with a challenge. Let's open up Solution Explorer, head down there to 04 SQL.sql. We actually have two challenges here for you this time.

17:08Challenge number one is to write an INSERT statement that adds a new customer category named best category ever. And if we head over Object Explorer, you'll notice that we have this customer category table down in sales, there it is, customer category. And if we just take a quick peek at this table, you're going to add a brand new category in here called best category ever. And then, for the second part of this challenge, you're going to write an update statement that modifies customers in category five to the new category created above. Good luck. Try it out on your own. If not, we're going to do this one together right now.

17:41So again, an easy way to get started here is to script out your insert statement against that customer category table. So let's head back into Object Explorer. I'm going to right click script this table as an insert to the clipboard. Now, it's on my clipboard and I can just come in here, right click, and paste it in there. Cool, there it is. We can get rid of our use statement, we already got that going on up top here. And then, all we need to do is just replace what we need here. Now, we've already got a default on customer category ID. Again, that's running off of a sequence, so we can actually ignore that. Oh, another thing you can do, by the way, if you wanted to be explicit here and type out every column, let's just type in default there.

18:15So that will fill that in with its default, which will be the next value from that sequence. Our category name here is going to be best category ever. And then, last edited by-- let's just go ahead and put in the first record here. Again, that just has to be a valid person ID that exists. And there we go. We just created a brand new customer category. Now, we can execute that. It'll create it. And if we head back in Object Explorer, we can take a quick peek to make sure that it did indeed make it into the table, and it did. And also, remember this customer category ID here of nine, because we're going to need that for our next statement, which is updating the customers that have a category of five to that new category of nine.

18:56And let's do the same thing here. Let's go into Object Explorer. We want to update our customers table here, so let's right click on customers, script it, head down here to update, and throw that into the clipboard. Now, we can come down here, get some room to work with. Paste it in here, and whoa, look at all that stuff. There's quite a few there, right? Well, we know this is an update statement, so we really only need to update exactly what we need to update. In this case, that is going to be the customer category right here of nine. So we're going to put that in here. We're going to get rid of everything else inside of here.

19:28Everything else is gone, all the way to the bottom. And then we can't forget our where condition. And what do we want to do for our where condition? Yeah, where the customer category ID is currently five, right? So we can do customer category ID equals five, there it is. So that will take all of these customers that were five and turn them into our new category of nine. And again, if you wanted to test this out to make sure that it works first, wrap this in a transaction. And then instead of putting in select statement before and after, that's what output is for.

20:01So we could easily use that to see our before and after. In this hands on lab Nugget, we looked at the many ways to write insert, update, and DELETE statements to modify data. I hope this has been informative for you, and I'd like to thank you for viewing.