Manage Data with T-SQL

0:00To become a true T-SQL ninja, we must

0:03master the SELECT statement.

0:05So in this Nugget, we're going to break down

0:06the syntax of the SELECT statement

0:08and talk about logical processing order, which

0:10is an incredibly important concept

0:12to understand so you can efficiently and properly write

0:16your SELECT queries.

0:17Let's do it.

0:18The SELECT statement is primarily

0:19used to retrieve rows from tables, although there

0:21are other database objects we can write

0:23SELECT statements against as well,

0:25such as views and functions.

0:26We begin our SELECT queries by specifying the SELECT clause.

0:30This begins with the actual SELECT keyword itself,

0:33followed potentially by some options, and then

0:36a SELECT list.

0:37This is where we specify what columns

0:39we want to pull out of tables.

0:41The SELECT list can also contain expressions.

0:44These are things other than just columns from a table.

0:47These can be calculations.

0:48Maybe you need to multiply two columns together,

0:51or you need to pass a column into a function

0:53to perform an operation.

0:55And this is where aliases come into play.

0:57Aliases provide a name-- more commonly to expressions,

1:01but you could also use it to rename columns, as well.

1:04Now, in the SELECT clause we have two options.

1:06We have the distinct option.

1:08This, if we specify SELECT DISTINCT,

1:11will remove duplicate records--

1:13being all of those columns combined

1:15have the exact same values, it will remove them.

1:18ALL is the default, and it's implied,

1:19which is why we don't need to specify it.

1:21And by the way, when you're reading syntax,

1:23and you see a bracket with a pipe, that means or.

1:25So either ALL or DISTINCT.

1:27And again, ALL is the default, and it's implied.

1:29We do need to pass in DISTINCT though,

1:31if we want to remove duplicates.

1:32We also have the TOP clause, and this

1:34is a limiter that will only return either

1:36the number of rows, which is the default-- so if we just

1:39pass in a number into this expression,

1:41it will only return, say, the top 10

1:42rows that this query returns.

1:44And we can also pass in a percentage.

1:46So if we specify percent, then this number

1:48will be applied as a percentage to the total number of rows.

1:51We'll see how both of these work in our virtual lab Nugget

1:54titled writing basic SELECT queries.

1:56We'll cover the rest of these major clauses

1:58shortly with some examples in this Nugget,

2:00and even deeper in the future with their own Nuggets.

2:03Here's an example SELECT query that pulls back

2:06the number of employees hired by year, by job

2:09title in the AdventureWorks database,

2:11out of the HumanResources.Employee table.

2:14One of the most important things to understand for beginners

2:16and experts alike is the logical processing order

2:19of our SELECT queries.

2:20Knowing this-- and I really can't stress this enough--

2:23will ensure that you properly form your queries

2:26the first time, which will lead to more efficient SQL

2:28developers.

2:30Here's how it works.

2:31We are used to composing or writing

2:32our SQL queries this way--

2:34SELECT from, WHERE, GROUP BY, et cetera, just like we have here.

2:38So this list matches this list exactly.

2:42And that's because it's user-friendly.

2:44It's human-friendly.

2:45This is how it was designed, because it makes sense for us

2:47to write the name of the operation--

2:50SELECT, followed by our columns, FROM which table, and then

2:53our filter, so on and so forth.

2:55But this is not how SQL logically processes it.

2:59Notice over here that FROM is actually processed first,

3:02and that's because it needs to know which table

3:05to pull these columns from.

3:07And by the way, I should point out

3:08that logical processing is different

3:10than physical processing, and that's

3:12because SQL Server does a lot of optimizations.

3:15It may take steps away, it may change steps within here,

3:18and it does so to more efficiently retrieve the rows.

3:21So logical processing is conceptual,

3:24but again, it's very important to understand because this

3:27defines the rules that we need to follow to properly write

3:31our SQL statements.

3:32Now, I like to think of this as one big data flow.

3:35So the FROM clause is going to kick everything off here,

3:38and it's going to pull back all of that raw data

3:40from the employee table.

3:42In this case, out of the AdventureWorks database,

3:44the employee table contains a total of 290 records,

3:47so it's going to fill up this virtual table

3:49with every single record in that employee table.

3:52So we have 290 records, and you can see them all right here.

3:56Then it's going to take that virtual table

3:57and pass it to the WHERE clause.

4:00The WHERE clause is going to filter out

4:02any records that don't match the search condition.

4:06This is actually known as a predicate here,

4:08and you can have multiple predicates separated

4:10by ORs and ANDs.

4:11A predicate evaluates to one of three values--

4:14true, false, or unknown, and only the predicates

4:18that evaluate to true will have their records included.

4:21So our predicate is saying here only include records were

4:23the year portion of the hire date--

4:25that's this portion right here--

4:28is greater than or equal to 2009.

4:29So that'll filter out anything that doesn't

4:31have 2009 in the hire date.

4:34There's the results, and that will contain 209 records now

4:38in our virtual table, which will get

4:39passed to the next phase, which is GROUP BY.

4:42GROUP BY will then operate on that data

4:44and roll it up, in this case, by job title and the year

4:47portion of that hire date.

4:49So here's an example record right

4:50here-- research and development has the same data here

4:54and the same year.

4:55So this will get squished up, and then

4:57an aggregate function here, COUNT, will roll that up

5:00and we'll have a value of 2 for this specific grouping.

5:03So it'll look something like this and result in 63 records.

5:07And it will take all of that data

5:09and pass it down to HAVING.

5:10And again, HAVING is just simply a filter on the group itself.

5:14And notice our predicate here for HAVING

5:16is saying where that count is greater than 1.

5:18So it's going to remove all of these ones out

5:21of the picture, which will result

5:23in a total of 34 records, and pass that down to SELECT.

5:28There's the result, and that brings us

5:31to the SELECT phase of logical processing order.

5:34This is where everything comes together,

5:36and this is why you need to keep this data

5:37flow in the back of your mind when writing queries.

5:40I see so many people struggle with writing

5:42queries because they're thinking about this when

5:44they're writing them, not this.

5:47Notice something really interesting

5:48here-- see our aliased column, year hired?

5:51Notice that we can only use the actual name of that

5:54aliased column in the ORDER BY clause,

5:56because ORDER BY comes after SELECT.

5:59In order to use these columns in your WHERE, GROUP BY,

6:02and HAVING clauses, you need to use the actual expression

6:06itself, just like we're doing here,

6:08because the SELECT clause hasn't been evaluated yet.

6:12I've seen so many bloated and inefficient queries

6:14over the years because whoever wrote

6:16it didn't understand logical processing order,

6:18so they used extreme methods to get around it,

6:20like subqueries or correlated subqueries

6:22or storing the results of one query in a temporary table

6:25and then joining to it.

6:26So you can avoid those scenarios by simply

6:28keeping this phased approach in the back of your mind.

6:32And by the way, that final phase is the ORDER BY clause,

6:35which will sort the results by one or more columns.

6:37Here we have a multi-level sort, first by job title ascending,

6:40and then by year hired descending.

6:43And you can see within buyer, we have two buy records--

6:46one for 2010, 2009, so it sorted that descending.

6:50And again, we can use the aliased name of the expression

6:53because ORDER BY comes after SELECT.

6:55In this CBT Nugget, we broke down the purpose and syntax

6:58of the SELECT statement and talked about logical processing

7:01order, which defines the rules we

7:02need to follow in order to properly write our SQL queries.

7:05I hope this has been informative for you,

7:07and I'd like to thank you for viewing.

0:00In this hands-on lab Nugget, we're

0:01going to learn how to write some basic select queries.

0:03We'll look at a couple of different ways

0:05to do this SQL Server Management Studio.

0:07We'll look at our options within the SELECT clause itself,

0:10and we'll get some tips and tricks along the way.

0:13Let's fire up the virtual lab and get started.

0:15I've logged into our SQL Server SQL-NUG.

0:17And from the desktop, we're going

0:18to get right into SQL Server Management Studio.

0:20And if you right click on the icon of the task bar,

0:22we can actually launch right into the solution we

0:25created in a previous Nugget.

0:27Underneath our second project here, select queries,

0:29let's go ahead and open up the first query 01 SELECT.

0:32Let's also get a connection over here in Object Explorer.

0:35You can do this the short way just by clicking

0:37on the shortcut here.

0:38And that will allow you to choose which component

0:40you want to connect to, the database engine being

0:41the default. You can also drop down the Connect button

0:43and choose which component you want to connect to.

0:46And that will lock you into that selection.

0:48So we're going to connect right to the instance, the default

0:50instance on our SQL-NUG database server here.

0:52So we'll go ahead and hit the Connect button, and here we go.

0:55We're going to begin working in the AdventureWorks database.

0:57So we expand databases, and expand AdventureWorks,

0:59and expand tables, we're going to start here

1:01working with the Human Resources dot Employee table.

1:04Now, let's maximize our screen real estate here

1:06by hiding Object Explorer.

1:07We can hit the Pin button there.

1:09That will push it over to the side.

1:10And same with Solution Explorer.

1:11If we ever need to get those back,

1:13we can just click on them, and that will fly them out.

1:15Another thing I want to show you here is getting help.

1:18Extremely important that you understand

1:20how to use context-sensitive help,

1:21because whenever you have a question,

1:23you can easily get to the documentation for whatever

1:26keyword, statement, function, anything you're

1:28looking for in Transact SQL.

1:30First, let me show you how to install it.

1:32If we drop down the Help button, and choose Add and Remove

1:35Help Content, this will open up the help viewer

1:37and allow us to download and install

1:40all of Microsoft's documentation here locally.

1:42And what we can do is search online.

1:44If I just search SQL, for instance, and hit Enter,

1:47you can see we have the Transact SQL reference.

1:49And I already clicked the Add button and then Update.

1:51And that downloads it and installs it locally.

1:53So we've got the SQL documentation all loaded

1:56locally.

1:56Another thing you'll want to do, because by default,

1:59it will search online, you'll want to drop down Help again,

2:01and choose Set Help Preference.

2:03By default, it will launch into the browser.

2:05And again, hit Online Help over on Microsoft's site.

2:07If you choose Launch and Help Viewer,

2:09and you've downloaded Help locally,

2:11then it will launch everything locally.

2:13And now that we Help local and available, and launching

2:17from our local documentation here, what we can do

2:20is anywhere you place your cursor--

2:21so I'm going to place it right on the select keyword there,

2:25and then hit F1 on your keyboard--

2:26that's context-sensitive help-- and that

2:28will launch the documentation right to that help topic.

2:31Look at that.

2:32And here is the SELECT statement.

2:34This will give you a nice overview of that keyword,

2:36the basic syntax.

2:37And if you scroll down, you'll get

2:39the expanded syntax and remarks, and then lots of examples.

2:42And most of these examples work with the AdventureWorks

2:45database.

2:47All right, let's do another one here.

2:48Let's this time. click on From.

2:50And hit F1 on your keyboard.

2:52Again, that will launch us right into the Help Viewer

2:54for that topic, and explain a little bit here

2:56about what the FROM clause is all about,

2:58along with syntax and example.

3:00So very cool very easy to do, and extremely useful,

3:05especially when you're learning Transact SQL.

3:07All right, let's write some basic SELECT queries.

3:09And let's start by changing our connection context

3:11for this query, this script here,

3:13to the AdventureWorks database.

3:15Again, you can just drop down this and choose it,

3:17or we can use the Use command here and specify our database

3:20name.

3:20And inside of your query, anytime you highlight something

3:23and hit Execute, or F5 on your keyboard, that will

3:26execute just your selection.

3:28If you don't do that, if you don't have a selection here

3:30to execute, it will execute the entire script,

3:33and every single query inside of there.

3:35So make sure that you select it before you hit Execute.

3:38Also, Control R is your friend here.

3:41See the Results pane down here?

3:42If you get Control R, it will hide it.

3:44If you hit Control R again, it'll show it.

3:46So that's really useful, again, when

3:48you're writing, and doing a lot of testing,

3:50and testing certain parts of a query.

3:52And you just simply highlight that part, hit Execute,

3:54hit Control R to hide it, and get back

3:56to designing your query.

3:57Now, let's start with the most basic of all SELECT queries.

4:00Here it is, right here.

4:01Select Asterisk, or Star, or Splat from human resources--

4:06that's the name of the schema dot employee.

4:07That's the table within that schema.

4:10And this will pull back every single row

4:12in every single column from that table.

4:13If we hit Execute or F5 on the keyboard, here it is.

4:16Now, SELECT Star is something that you should really only use

4:19again when you're eyeballing data,

4:21or just want to take a quick peek to understand what

4:23that data looks like.

4:25And we should never use this in a production environment.

4:28And there's many reasons for that, one,

4:30obviously for performance reasons.

4:32You want to pull back only exactly what you need.

4:34And another one is--

4:35if you use this inside of database objects--

4:37and developers use those objects from the front end--

4:40if our schema ever changes, it could break the front end,

4:43especially if they're referencing these columns

4:47by index or by location within the results set.

4:50So keep that one in mind, very important to understand.

4:53Only use the asterisk when you're testing.

4:56Also, if you hold your mouse over the asterisk,

4:58it will show you all of the columns

4:59that it's returning from that table.

5:01So let's fix that.

5:01Let's say we were really only interested in a couple

5:04of columns out of this table, Login ID, Job Title, Hire

5:06Date, and Vacation Hours.

5:08We can simply specify those columns,

5:10comma to limit them, and then specify our table name.

5:13If we highlight the query and hit Execute,

5:15now we're only pulling back specific columns.

5:18Easy stuff, right?

5:19All right, I'm going to hit Control R to hide our Results

5:21pane.

5:22And let's take a look at another example here.

5:24This time, we're going to add another column onto here.

5:26But this is not a column that exists within the table.

5:29This is an expression.

5:30We're going to take the column vacation hours

5:32and divide it by 24, and then put an alias on it

5:36so it's named.

5:36If you didn't add the alias, then it

5:38wouldn't have a column name.

5:39In fact, I'm going to comment that out.

5:41Dash, dash, by the way, is a single-line comment.

5:44And so if we highlight this, and execute it,

5:46it will execute everything except the alias inside

5:48of here.

5:49And you'll see that we will get no column name returned.

5:52So this is why we always want to alias our expressions.

5:55And we can also alias other columns

5:57that exist as well, if you want to change the name of them.

5:59Useful, again, if you're dealing with an older

6:01database with cryptic names.

6:03We always want to make those names human-friendly,

6:05especially when we start designing views and people are

6:08going to using those views.

6:09We can also obscure the actual names, which would give us

6:12a nice security benefit.

6:14All right, let's go ahead and execute this query.

6:16And now we have a name on that column.

6:18Another thing I want to point out here--

6:20notice this semi-colon at the end of all of our queries.

6:24This is a statement terminator.

6:25And it's actually implied.

6:27So you don't need it.

6:28In fact, we'll get the same result here

6:30if we execute it without it.

6:31But we should use it.

6:33Microsoft has stated in the future,

6:35this may not be implied.

6:36And it's good practice to be as explicit as possible

6:41when you're writing Transact SQL.

6:43The same goes with the as keyword

6:44when you're aliasing a column.

6:45You don't actually need it there,

6:47and you'll get the same result. But you

6:49will want to be explicit here because it

6:51makes your code much more readable and less error-prone.

6:55Now we can also alias table names.

6:57So we could do something like this.

6:58Now, this doesn't make much sense

7:00in the context of a single-table query.

7:01But once we get into joints, where

7:03we're working with columns for multiple tables,

7:05this will become very important, both for readability purposes,

7:08and especially if we have the same column

7:11name in multiple tables, we will need

7:13that to specify which table we want to pull that column from.

7:17Moving on, let's take a look at sorting our results

7:19with the ORDER BY clause.

7:21Now, if I hit Control R to bring up our previous results,

7:23you'll notice that there is no sorting on any of these.

7:26It's sorted by however the database retrieved the records.

7:29We can force a sort with ORDER BY

7:31and specifying the name of our column.

7:33Ascending is applied here.

7:35And again, you can be explicit and type in ASC.

7:37But the default is ascending.

7:39So this will now order those results by job title ascending.

7:44There it is.

7:44Now if we did want to sort through the results

7:46in descending order, we will need to explicitly place

7:50the DESC keyword after the column name

7:53that we're sorting by.

7:54And notice here, we're going to sort by vacation days, which

7:57is our expression.

7:58Remember, the reason we can do this

8:00is because in the logical processing order,

8:02ORDER BY happens after SELECT, which is why we

8:04can reference this alias name.

8:06And if we highlight this query and it Execute,

8:08we will now sort by vacation days in descending order.

8:12Moving on, let's take a look at a couple of options

8:14that we can include in the SELECT clause itself.

8:16The first one is the TOP clause.

8:18This is a limiter used in a couple of scenarios.

8:21Number one, it's used to just reduce the amount of records

8:25that we're pulling back from our tables

8:27where we're doing testing in building queries,

8:29that way we don't bring our database server to its knees.

8:32In fact, this was added into Management Studio

8:35a few releases ago for exactly that reason, because admins

8:39and developers were in here.

8:40They could right click on a table

8:41and choose Select The Rows, and it

8:44would bring back all the rows.

8:45And if there were a billion rows in that table, again,

8:47you could just crush the performance of your database

8:49server.

8:50So they put this limiter on here when you're selecting it.

8:52And that's why, by default here, it brings back 1,000 rows.

8:55So that's one way to use it, just simply

8:57to limit the number of records returned.

8:59A more powerful and more common way to use this

9:01is combining it with the order clause

9:03so we can generate top and bottom lists.

9:06So here, we're generating a top 10 list

9:08based on employees with vacation days.

9:11So this will bring us back the employees with the most

9:14vacation time available.

9:17And there it is.

9:18So there's all of our employees with four vacation days.

9:21Another thing you can do here is use the With Ties option.

9:24And what this will do is ensure that anything

9:28tied for last place will also get returned.

9:30And if hit Execute this time, rather than just 10,

9:33we're going to get 12 rows because one through 10

9:35was four, but 11 and 12 was also four.

9:38So With Ties we'll bring back anything where

9:40that last record has a four.

9:42And there may be more beyond that last record.

9:44It will return those as well.

9:46Another thing we can do is use a percentage rather than

9:48a number.

9:49So if we type in Percent here, this will return the top 10%

9:52rather than just the top 10.

9:54And we have 290 employees in this table,

9:56so this should bring back 20 rows.

9:58You can always see the number of rows

10:00that were returned here in the lower right-hand corner

10:02in Management Studio.

10:03And there it is.

10:04There's the top 10% rather than just the top 10 records.

10:07I should also point out that this value

10:09can be an expression.

10:11So you can place an expression inside of here.

10:13And also, it can be a variable, which is common

10:16when you have a stored procedure,

10:18and you want this value to be dynamic based on whatever,

10:20the front end, or the developer, or whoever

10:22is using our store procedures passes in as a parameter.

10:26So we can definitely make this dynamic

10:28by utilizing expressions.

10:30The other option we have is the Distinct option,

10:34which will eliminate duplicates, record duplicates,

10:37not value duplicates, but records.

10:38So the entire record must have the same value for it

10:41to be eliminated.

10:42To give you an example, let's remove distinct.

10:44And again, the default here is all.

10:46So that is implied.

10:47We don't need to do that.

10:48But what this will do is bring back every single job title.

10:51And you can see the duplicates here

10:53because we only have one column.

10:54So this is the entire record.

10:56But let's say we just wanted to get a list of job titles.

10:59Well, we could turn this into a distinct, execute it,

11:03and this will just bring back one record for each job title.

11:05And there is our list of unique job

11:07titles for the AdventureWorks database.

11:09All right, moving on, let's hit Solution Explorer over here

11:11on the right, and let's open up our second script

11:14here called O2 Logical.

11:16Now in our Nuggets titled The Select Statement,

11:18we talked about logical query processing.

11:20And we saw that it's logically processed

11:22different than how we write it.

11:24We write SQL with SELECT first.

11:26And that can give you the impression

11:28that SELECT resolves first.

11:29But it doesn't.

11:30We know that SELECT isn't actually resolved until after

11:33the HAVING clause, which is the reason why we cannot used

11:36our alias columns anywhere prior to the ORDER BY statement,

11:39ORDER BY does happen after SELECT,

11:41which is why we can use our alias column names in that

11:44ORDER BY clause, but not before.

11:46So I wanted to include this in here

11:47so you could see it all broken down step by step.

11:49These are the queries that I used

11:51to generate those whiteboards.

11:52And if you execute, execute the entire script,

11:55then it'll switch to AdventureWorks

11:56and execute each dataset individually.

11:58In the right scroll bar, you can scroll down

12:00and see how these statements all work,

12:03and how that data goes from raw data

12:05all the way down to our end result, which

12:08is all of the employee's job titles

12:10grouped together by year.

12:11And we get a count of the number of employees hired by job title

12:15by that year.

12:16Moving on, the last thing we're going to do

12:18is look at a SQL challenge.

12:19I'm going to challenge you to write

12:21a query, a very basic query here, that

12:23uses a table in the Wide World Importer's database.

12:25So I'm going to get you started here.

12:27And do the same here if you're following along.

12:29Highlight our use statement and switch our connection context

12:31here to Wide World Importers.

12:33I'm going to give you a scenario here,

12:36give you some requirements.

12:37And then you can pause me in an attempt to write this query.

12:40And then we'll walk through writing it together,

12:42and I'll give you some tips and tricks on writing and designing

12:45queries here in SQL Server Management Studio.

12:47So your challenge, if you choose to accept it,

12:49is to write a query that returns the 10 most expensive stock

12:52items out of the warehouse that stock items table here

12:55in Wide World Importers, and include a column that

12:57calculates sales tax.

12:59So in that table, Stock Item, Name, Unit Price, and Tax Rate

13:02are columns that you can just pull

13:03directly out of that table.

13:04Sales Tax, however, you will need

13:06to write an expression that calculates the sales tax.

13:09And you can do that by multiplying unit price

13:11by tax rate.

13:13Keep in mind though, tax rate is a percentage.

13:16So you'll need to actually convert this to a decimal.

13:18And you can do that by multiplying--

13:20or I should say dividing it by 100, all right?

13:22And then also, sort those results

13:24by unit price descending, which will give you the top 10.

13:28All right, so if you're up for the challenge, pause me now,

13:30try it out on your own, and when I come back,

13:32we'll do it together.

13:34OK, are you ready?

13:35Let's write this together.

13:36Let's head down to some white space here.

13:38And the first thing I'd like to do in writing a query,

13:40especially if I'm unfamiliar with the table,

13:41let's just do a select starter from it quickly.

13:44And again, if it's a big table, make

13:45sure you limit the results.

13:46But let's do a select starter from warehouse.

13:49Notice Intellisense here.

13:50We can take advantage of this.

13:51Any time you see this blue rectangle,

13:53that's the currently selected item in the list.

13:54If you hit Tab, it'll finish typing it for you.

13:56Then we can hit dot and start typing in our table name, which

13:59is Stock Items.

14:00And as soon as you see it pop up here, you can use your keyboard

14:03and hit the arrow keys to go down your item,

14:05highlight it, and hit Tab.

14:07And there it is.

14:07And we'll finish it off with a semi-colon

14:09for good best practice there.

14:11And then let's execute it to take a peek at the data.

14:13So here are all of the stock items.

14:15And we're going to be working with the names.

14:17We need to pull that back.

14:18If we scroll over a little bit here,

14:20we're also going to be working with the tax rate and the unit

14:23price.

14:24And these are what we're going to need to calculate sales tax.

14:27And, again, we're going to need to convert

14:29this percentage to a decimal.

14:31So we get an accurate sales tax there, all right?

14:34So I'm going to hit Control R. And now

14:35I'm going to hit Enter a few times to get another one up.

14:37And here's how I like writing queries from scratch.

14:39First, Select, then Enter, and then From.

14:42And enter your table name.

14:43So there is warehouse dot Stock Items.

14:46We'll scroll down there, and boom.

14:48So the reason I like doing this is because then we

14:50can take advantage of intellisense

14:51in our SELECT clause itself, right?

14:55And it feels right writing that from first

14:58because you're following logical query processing.

15:00You just get your brain thinking the right way.

15:02All right, so the first column we're going to pull back here

15:04is Stock Item Name.

15:05There it is.

15:06And what's also nice about this and using intellisense,

15:09is you never have to touch your mouse,

15:10you can start writing queries and just ripping through them.

15:12So stock item name.

15:13Then we'll type in unit price.

15:15We'll go down, hit Tab, Comma, tax rate, same thing,

15:18that's the only one.

15:19So we can hit Tab, Comma.

15:21And now we need to calculate sales tax. so let's

15:23get some parentheses up here.

15:25And we're going to calculate that by taking unit price

15:28and multiplying it by tax rate.

15:31But again, we need to convert tax rate here.

15:33So we're going to place this inside the parentheses

15:35as well, because we want this to resolve first.

15:38It's going to resolve from the inside

15:39out when you have parentheses here.

15:41And we're going to divide that by 100.

15:43And there we go.

15:44We also need to alias this column.

15:45So let's type an as sales tax.

15:48And finally, we want to order this

15:50by unit price descending because we want to add

15:55the top 10 in here, right?

15:56So select top 10.

15:59And there it is.

16:00There is our result. And for good measure

16:01here, we'll also add our statement terminator.

16:03Now we can highlight all of this.

16:05And there is our top 10 by unit price items with sales

16:10tax calculated over there on the left.

16:12Now one more really useful tip I want to give you here,

16:14is if you're learning Transact SQL for the first time,

16:16and you're not really comfortable with writing it

16:18from scratch, there is a graphical designer

16:21that you can use to do so, and actually

16:23watch it right the SQL for you.

16:24This is a great learning tool.

16:26And to access it here, make sure your cursor is

16:29in the white space, right click, and choose Design Query

16:32and Editor.

16:33Here, you would find the table.

16:34So we'll look for stock items here.

16:36There it is.

16:37We'll hit Add, close out of this dialogue,

16:39and now we can graphically design this query.

16:41We can choose what columns we want.

16:43Each column has its own entry down here.

16:46So there is Stock Item Name, here is Unit Price,

16:49there is Tax Rate.

16:50Notice here we have those three columns in here then.

16:52You can alias them here.

16:53You can choose what to sort by here.

16:55And, again, the cool thing here is that it

16:57is writing this SQL for us.

16:59Check that out.

17:00We can even go into the properties here of the query,

17:03scroll down to the bottom.

17:04Here is distinct.

17:05There's our top specification here.

17:07So if we expand that, we can turn the top on,

17:10we can specify our expression here as well as our percent,

17:13and WITH TIES option.

17:15And then, of course, if you want to put our expression in,

17:17there we can just start typing in here that expression

17:20and give it an alias.

17:22Very, very useful, because, again, it's

17:24writing the SQL for you.

17:25Once you hit OK, it'll pop it down here into the script pane.

17:29Now, another thing you can do is highlight an existing query.

17:32Let's take that query we just wrote,

17:33right click on it, and choose to design in query editor.

17:36And it will fill it all out inside of here.

17:39Check that out, including our expression

17:41that we wrote down here.

17:42In this hands-on lab Nugget, we learned

17:44how to write basic SELECT queries

17:46and solve all of the options that we can include inside

17:49of the SELECT clause itself.

17:50I hope this has been informative for you,

17:52and I'd like to thank you for viewing.

0:00In this hands-on lab Nugget, we are

0:01going to focus on the WHERE clause, which

0:03is how we add filters to our query to target specific data.

0:08Let's fire up the virtual lab and jump in.

0:10Once you've logged into our SQL-NUG database server,

0:12lets head down to the task bar, right click on SQL Server

0:15Management Studio, and launch right into our solution

0:18containing the project we're going

0:20to be working with throughout this lab.

0:22Once SQL Server Management Studio fully loads,

0:24we are going to be looking at the 03 filtering and results

0:27project.

0:28And let's open up the first script in here, 01 WHERE.

0:31From there, let's go ahead and push Solution Explorer

0:35and Object Explorer aside.

0:37And let's also switch our connection context here

0:39to the Wide World Importer's sample database.

0:42And let's start here by taking a peek

0:45at the data that lives inside of our stock items table.

0:47So I'm just going to highlight the select star

0:49from warehouse dot stock items.

0:51And this will bring back all 227 items inside of this table.

0:56Let's see if we wanted to filter for a specific supplier.

1:01And we can do that based on the supplier ID.

1:03Now, this will make much more sense when we get into joining.

1:05We can join these tables together.

1:06But supplier ID number five, if we just eyeball this data,

1:10looks like it's going to provide us with some funny mugs,

1:14funny-- we got a developer joke mugs, we've got IT joke mugs,

1:17we've got deviate joke mugs in here, all kinds of good stuff.

1:20Here's a good one right here.

1:22Select caffeine from mug.

1:24I love that shirt.

1:25I want that shirt.

1:26But anyway, let's say that we wanted to focus on supplier ID

1:29number five here.

1:30We can add a WHERE clause, which will just

1:33return these records where this column equals this value.

1:36If we execute that, there we go.

1:38Now we're down to 42 records.

1:40And looks like this supplier just

1:42supplies funny mugs, funny IT mugs.

1:46So that's about as easy as it gets,

1:47right, one single predicate inside of the WHERE clause.

1:50One thing I want to do before we go any further

1:52here is take a look at the help documentation for the WHERE

1:54clause itself.

1:55So again, place your cursor on that line,

1:57hit F1 on your keyboard, and that

1:59will pop open Context Sensitive Help for the WHERE clause.

2:03We can see it specifies the search condition

2:05for the rows returned by the queries, very simple syntax

2:08here.

2:08But, of course, the WHERE clause can be the most complex part

2:12of any query, because once we start

2:15adding multiple predicates, we need to understand

2:18how to glue them all together.

2:19Now, if you scroll down a little further here,

2:21you'll get lots of examples.

2:22Again, all of the examples here point

2:25at the AdventureWorks database.

2:27But you can see lots of different examples

2:29on how to use it.

2:30Feel free to test some of those examples

2:32out and run through them.

2:33You can copy and paste right from the help topic

2:35into the query here.

2:36And we have the AdventureWorks database available.

2:38So it's a good way to get, again multiple perspectives

2:41on how to use the WHERE clause.

2:43Let's scroll down a little bit, and let's

2:45combine predicates here by adding

2:48an and condition between them.

2:50So if we eyeball the data once again here,

2:53we see the supplier ID five provides us

2:55with some funny mugs.

2:57Color ID three, if we look at the color ID here,

3:00we can see this is probably going

3:02to be all of the black mugs.

3:03So let's say we just wanted to see all the mugs that

3:05are colored black.

3:06Let's go ahead and highlight this query, hit Execute,

3:09and that's going to trim it down even further.

3:11And now we have 21 rows.

3:12These are just all of the black mugs that supplier provides.

3:16Now here's where things get really interesting.

3:18Let's say that we wanted to bring back all the products

3:23that supplier ID four or five provided

3:27and that are the color black.

3:29Now, here's what's going to happen.

3:31This is going to give us unintended results here

3:33because if we highlight this and execute it,

3:36we're going to see more than just items with the color

3:38black.

3:39If we scroll down-- look at this--

3:40we've got some white items in there.

3:42We've got some blue and red items in there,

3:44and green, and gray, and brown, and pink.

3:47And uh-oh, what happened?

3:49This is because of precedence.

3:53How this works is not is greater precedence

3:57than and, which has greater precedence than or.

4:00So if we look at this, how SQL actually interpreted

4:03this looks like this right here.

4:06So it returned us supplier ID's black items.

4:10But the or here also gave us everything

4:13from supplier ID four.

4:15So this is why we really need to respect precedence

4:18and understand it so we can correctly

4:21form our WHERE clause.

4:23So this is where we would control it

4:25by using parentheses.

4:26Parentheses, anything in parentheses

4:28has the greatest precedence and will resolve first.

4:31So we can fix those results by wrapping parentheses around the

4:35or between supplier ID four and five.

4:38And we can leave the and on the outside, which

4:41means this will resolve last.

4:43So this will get us all of supplier four or supplier

4:45five's items, and make sure that the items are black.

4:49So if we execute this, the result

4:52then is going to be what we initially wanted, right?

4:56All black mugs, and black shirts, perfect.

5:01And by the way, speaking of the not operator.

5:03We can use this to negate a condition.

5:05So for example, let's say we wanted

5:06to see all the other suppliers that are not four or five that

5:10provide black items.

5:12We can place a not in front of this condition.

5:15And now if we highlight this-- in fact, let's

5:17actually turn stock item name to an asterisk

5:19here so we can see all of the columns that are returned.

5:22If we highlight that and execute it, here they all are.

5:24You can see these are the other suppliers.

5:26Four or five is not in here.

5:28And here is the color ID of three, which is black.

5:31And we can verify that by looking

5:32at the description here, or the name of all these products.

5:36Now, another common thing you'll be

5:37doing with your WHERE clauses is searching

5:39against character data.

5:41Here's an example.

5:42Let's say that we wanted to grab all of the items

5:44were the size column contains extra large

5:47or, double extra large.

5:49Here, very simple query to do that, multiple predicates here

5:52tied together with an or.

5:53And this will bring us back a bunch of shirts

5:56that are either extra large or extra, extra large.

5:58And you can see the size column right here.

6:02Now, these are equality comparisons.

6:04And oftentimes when you're looking for character data,

6:06you may be doing wildcard searches

6:08using the LIKE operator.

6:11Here we, can use the percent sign

6:13to indicate that we want anything

6:15after or before any sort of character.

6:18So in this case, we're seeing anything

6:19that starts with an x and any character after that returned.

6:24And if we highlight this, this will give us

6:26anything in the size column that starts

6:28with an x, including extra small and extra, extra small.

6:32And let's say, you know what?

6:33We just wanted extra large and above.

6:35Well, in that case, we can add a NOT to this.

6:38And we can say we're AND size not like S.

6:41And here's where we're using a wildcard identifier here

6:44before and after.

6:45So we're saying anything that contains S.

6:48And this time, it will return anything

6:50that contains extra large or double extra large, just

6:53like we had before with our equality comparisons,

6:56except now we're doing it using a LIKE with wildcards.

6:59I also want to point out that there

7:01are more wildcard characters you can

7:02use in conjunction with LIKE to further refine your results.

7:05In fact, if you place your cursor on LIKE

7:07and hit F1 to open up the help topic,

7:10we can scroll down just a little bit here,

7:12and we can see that other than percent, we also

7:14have underscore, which allows us to search

7:15for a single specific character within a string.

7:19We can also use brackets here to search

7:21for the range of characters.

7:23And we can use a carrot within the brackets

7:25to negate a range of characters.

7:27Another important thing to note here is if you want to use any

7:30of those wildcard characters as literals-- in other words,

7:33maybe you want to search for the percent sign in your data--

7:36you can surround it with an open and closed bracket

7:40inside of your pattern.

7:41This is known as your pattern that you're searching for.

7:44And that will return the exact literal value of that wildcard.

7:48So I would definitely advise going

7:49through some of the examples down here

7:51in the virtual lab just to do one

7:53example of each type of search to get it into your muscle

7:56memory, because it's good to know

7:58this stuff for the real world because you'll be doing

8:01a lot of character searches, especially inside

8:03of your stored procedures when users

8:05want to search textual data.

8:07And there are some pretty good examples in here

8:09that work with the AdventureWorks database.

8:11All right, I'm going to close out of our help topic here.

8:13And let's talk about date and time searches.

8:16Just a couple of rules to be aware of when you are doing

8:20date equality searches like we're doing here,

8:22or date range searches like we're doing down here.

8:25And we'll look at many other ways

8:26of performing date searches when we get into functions.

8:30But for now, the first rule is to use this format

8:33right here, four-character year, two-character month,

8:38two-character day.

8:39And the reason being is that this is language-neutral.

8:43If you use any other format, then you're

8:45binding yourself to specific languages.

8:49And it's like using standard SQL as opposed to using

8:52Transact SQL's specific things.

8:54You want to keep it as generic as possible so our code is

8:56portable and will work with more things.

8:59And this format works with all the things.

9:02If we highlight this and hit execute,

9:04this will bring back all of our orders

9:06that were placed on this date.

9:08Another thing, when you're searching for date ranges,

9:11be careful of using between.

9:13This works fine with character searches,

9:14but when you're using date time searches,

9:17it actually isn't very precise, and it cuts off

9:20some of the lower ends of time.

9:22So when you're dealing with date time columns-- and this order

9:25date is not a date time column.

9:26It's just a regular old date column, so this will work.

9:29But it will generally cut off the last day, all right,

9:32because this is an exclusive operator, not

9:34an inclusive operator, meaning it will

9:37exclude the outer bounds of it.

9:39And so this will actually work again

9:41because this order date is a date column.

9:43But if it was a date time column,

9:45we would actually lose the last day.

9:47So whenever you do date range searches,

9:49you should use what's known as an open-ended date range, where

9:52you should do a greater than or equal to your starting date

9:55and a less than the end of your date plus one.

10:00So in this case, this will cover all of January 2016 from 0101

10:04because we have a greater than or equal to, and then less than

10:08the 1st of February.

10:09And this, if it was a date/time field,

10:12would absolutely cover everything.

10:14So keep this in mind.

10:15It's also better for performance to do this,

10:17better than performance over between.

10:19But more importantly, it's much safer

10:22because you'll get exactly what you're looking for.

10:24Again, more on all of this stuff when

10:26we get into functions and data types as well.

10:28All right, are you ready for your SQL challenge?

10:31Let's open up Solution Explorer and open up O2 SQL.

10:35Once again, I'm going to give you some requirements here.

10:37You can pause me in attempt to build this query on your own.

10:40And then we'll walk through a solution together.

10:42Here we go.

10:42Your mission is to write a query that

10:44identifies people who are allowed

10:45to log in to the application, are not employees, and does not

10:49contain example dot com in their login name.

10:52You're going be working with the table application dot people.

10:55This is full of people who are allowed to log in to the Wide

10:58World Importer's system.

11:00You're going to pull back Full Name.

11:01Log On Name is permitted to log on, and is Employee.

11:04And those are also the columns that you'll

11:06be working with to create the predicates inside

11:09of your WHERE clause.

11:10So now is a good time.

11:11Pause me, and good luck.

11:13All right, are you ready?

11:14Let's head down to the white space here,

11:16and let's first get familiar with that table.

11:18So select star from application dot people.

11:22And we can just hit F5 here because we also

11:24want to change database, the connection contacts

11:26here to Wide World Importers.

11:28So if we don't highlight anything, and hit Execute

11:30or F5, it'll execute that, and successfully execute that query

11:33to target that table.

11:35So a couple of columns we're going to need here-- we're

11:37going to pull back Full Name just for presentation purposes.

11:40We're also going to need Log On Name

11:42because if we want to find or filter out anybody that belongs

11:45to example dot come, we're going need

11:46to run a not like against that and a wildcard search for that.

11:50So that's why we're going to need Log On Name here.

11:52And we've got a lot of example dot com folks inside of there.

11:55We're also going to need to identify people

11:57who are allowed to log in.

11:59Which column would we use for that?

12:00If we look at this, you're probably

12:02going to notice there is and Is Permitted To Log On column.

12:05This is a bit field.

12:06Zero is false.

12:08one is true.

12:09So we're going to want to add a predicate here

12:12for this column where that equals one, right?

12:15Those are folks that are allowed to log in.

12:17And if we scroll over here, we're

12:19also going to need to hit the Is Employee column here,

12:23because we want to identify people who are not employees.

12:25So this time, another bit field here, we're going to set this

12:28and our predicate equal to zero.

12:31All right, so that looks good.

12:32I'm going to Control R to hide that.

12:34And now let's write the actual query.

12:35Let's do a Select, Enter, From Application dot people.

12:43Now, let's head back up to Select here,

12:44and let's grab all those columns.

12:46There is Full Name, there's Log On Name, there's

12:49Is Permitted to log on, and there's Is Employee--

12:53all right, very easy to quickly type

12:55that out and tab through it when you use Intellisense.

12:59Now comes the important part, our WHERE clause containing

13:01all of our predicates.

13:03Let's do this condition by condition.

13:05Our first one here is to identify

13:06people who are allowed to log into the application.

13:08When we saw that, it was the is permitted to log on column.

13:12And we want to set that equal to one.

13:14Then we also want to identify people who are not employees.

13:17So we're going to an and here is employee equals zero.

13:22And then we want to identify people

13:24that do not contain example dot com in their log on name.

13:27So we can do an and log on name not like--

13:33and then inside of our pattern here,

13:35we're going to do anything that ends with the example dot com.

13:40And there it is.

13:41Oh, and don't forget about your semi-colon there.

13:43That's good form.

13:44Now we can highlight this query, hit Execute,

13:47and you should get 103 rows.

13:49These are all non-employees who are permitted to log in.

13:52And you will not see anybody from example dot

13:54com in this list.

13:55Perfect.

13:56One other tip I want to give you is,

13:58when you're building complex queries with a lot of search

14:01conditions, a lot of predicates in your WHERE clause,

14:03you may want to build it in phases, right?

14:06And this is where comments come in handy

14:07too because you can start with one of your conditions,

14:10just like so.

14:11We can still highlight the whole thing there at execute.

14:13So this is the query, which is permitted.

14:15We can see we've got 155 records here.

14:18And you can document the record counts,

14:20and then you can start adding in more conditions,

14:22and then negating other conditions.

14:24And you can, again, see what your record

14:26counts are going to be.

14:27And it's a nice phased approach to building your queries.

14:30You can even set up your query, copy,

14:32and paste it, and run that query for each condition

14:35if that's easier, if you didn't want to comment things out.

14:37But this will also give you a good idea

14:39of how to set up precedence, and if precedence

14:41is messing with your results.

14:43And then you can identify where it

14:45is, surround it with parentheses where

14:47you need to to ensure that those results first.

14:50In the hands-on lab Nugget, we learned

14:52how to add filters to our queries using the WHERE clause.

14:55I hope this has been informative for you,

14:57and I'd like to thank you for viewing.

0:00In this hands on lab Nugget, we're

0:01going to learn how to combine the results of multiple queries

0:04together using set operators.

0:07These operators are UNION, EXCEPT, and INTERSECT.

0:10And as you'll see here shortly, they

0:12can come in pretty handy when you

0:13need to combine the results of multiple queries

0:16together into a single result. Let's fire up the virtual lab

0:19and get started.

0:21Once you get logged into SQL-NUG, let's

0:22start by launching SQL Server Management Studio,

0:25and also launch directly in to our solution

0:27containing all of our projects for this course.

0:29So give that icon on the task bar a right click

0:31and choose 70-761 scripts.

0:34Once SQL Server Management Studio loads,

0:36we will have that solution on the right

0:38with all of our projects.

0:39We're going to be working with our project called 04 Combining

0:42Results.

0:43And let's begin here by launching 01UNION.sql.

0:46We're going to look at the UNION set operator first,

0:48and I'm also going to put solution explorer

0:50and object explorer aside.

0:52First things first here, let's get a connection over

0:54to our Wide World Importers sample database.

0:57And we're going to start by working with our table stock

1:00items.

1:00This is a table that we've worked

1:02with in previous Nuggets, which just contains all of the items

1:05that this warehouse holds that Wide World Importers resells.

1:09Taking a look at our first example,

1:11these are two separate queries.

1:13The first one is going to grab records 1

1:15through 10 based on the stock item ID,

1:17and the second one is going to grab records 5 through 15.

1:19If we highlight both of these and then execute,

1:21the result is going to be two separate result sets, right?

1:25We have results set one up here with items 1 through 10,

1:28and result set two down here with items 5 through 12.

1:32So, what if we wanted to combine these into a single result set?

1:36That's where the UNION operator comes into play.

1:38Now there are just a couple of rules

1:40that we need to follow in order to successfully combine

1:43the results of two or more queries

1:45together with the UNION operator.

1:46And yes, you can combine more than two queries.

1:49You can combine as many as your heart desires.

1:52But the very first rule, is that you

1:54must have the same number of columns returned

1:57across all those queries.

1:59So that's rule number one.

2:00If you have three columns returned from your first query,

2:02then you must have three columns returned

2:04from your second query and third query, and so on and so forth.

2:09And the second rule is that your data types must also

2:13line up across the results of these queries.

2:16If we look at our query here, do you think we're OK?

2:19Well, we're working with the same table,

2:21so of course we're going to be OK.

2:23We're grabbing all the columns from both

2:26of these queries, or that same table,

2:27and combining them all together.

2:29So we should just be able to stuff a UNION operator right

2:32in between these as we're doing down here,

2:34and it should take the results of all those.

2:36And instead of returning us with two separate results,

2:39we should have one.

2:40Should we test this out?

2:41Now before we do this, I want you to notice something here.

2:43Check out the lower right hand corner.

2:45We have 21 records from our previous query.

2:47If I hit Control R to bring this up,

2:49this means that if I click inside of our first result set,

2:52we have 10 rows.

2:53And if I scroll down and click in our second result set,

2:55we have 11 rows.

2:57So this first query returned 10 rows.

2:59The second one returned 11 rows for a grand total of 21 rows.

3:03Think we're going to get the same results here

3:05with the UNION?

3:06Well let's find out.

3:07I'm going to hit Control R just to hide this.

3:09Let's highlight everything with the UNION

3:10operator between these two.

3:12And also notice here, our semicolon is at the very end.

3:14Not at this one, that would actually break and terminate

3:17the statement right there.

3:18So keep that one in mind, semicolon at the very end,

3:20and just one.

3:21But if we hit execute here, we're

3:22going get a total of 15 rows.

3:25What happened?

3:26That is not what you were expecting, was it?

3:28And that's because the UNION operator, by default,

3:32removes duplicates.

3:33It has an implied distinct operator built into it.

3:37And we can verify that by scrolling down on the results.

3:39You will only see records 1 through 15.

3:42Even though we have crossover here,

3:44those duplicates were removed.

3:45And they weren't removed above because those

3:47were two separate queries, and two separate results.

3:50But what if we wanted those duplicates?

3:52Well, that's where UNION ALL comes into play.

3:56We can add this option to the end of UNION

3:58and it will not remove the duplicates.

4:01In fact if we highlight this same exact query here, just

4:04with UNION ALL rather than UNION,

4:05now we should be back up to 21 records.

4:09Because, as we scroll through the results here,

4:11we have duplicates from, in this case, 5 to 10.

4:14And you can see them right here, there's our dupes.

4:17Now an interesting side note I want to point out here,

4:19and a good performance tip for when you write your UNION

4:22queries.

4:23UNION performs a little bit worse than UNION ALL

4:26when there are duplicates.

4:27And even when there aren't duplicates,

4:29because it has to add another step in the process

4:31to actually remove those duplicates,

4:33and do what's known as a merge join under the hood.

4:36So the tip here, is that if you're UNIONing together

4:39results, and there's absolutely not ever going

4:43to be any duplicates coming from those tables,

4:45then add in ALL to the end of it for a slight performance boost.

4:48And we can actually see this in action.

4:50If we highlight both of these queries, and then

4:53we turn on our execution plan.

4:55This will actually show us how SQL Server retrieves the data

4:59under the hood.

5:00More on these in the future.

5:01But if we highlight those, and turn that on and hit execute,

5:04we're going to get another tab here

5:05that pops up once the query is complete, and shows

5:07our execution plan.

5:09And shows us how much each statement costs in relation

5:12to the entire batch.

5:14So here's our first one.

5:15Right?

5:15You can see that by holding your mouse over.

5:17We can actually see this was the UNION, not the Union All.

5:20And this is your additional cost right here.

5:23Notice this takes up 65%, again in relation

5:26to the entire batch.

5:27If we scroll down to our second, our Union All.

5:30This only took up 35% of the batch.

5:32So our first one, the regular UNION,

5:35was much more expensive than the second one,

5:37because the second one didn't have

5:39to worry about duplicates because of that ALL option

5:42that we included.

5:43It just simply had to concatenate the data together.

5:46Didn't have to worry about removing duplicates

5:48like you did with the first one, where it performed a merge join

5:51to remove the duplicates.

5:52Which incurred a lot of extra costs into that.

5:54So keep that performance tip in mind.

5:57Again, if you're writing union queries,

5:58and your queries are hitting tables

6:00where there will absolutely not ever be any duplicates,

6:03add an ALL to the end of it for a nice little performance

6:05boost.

6:06All right.

6:06I'm going to go ahead and turn off the execution plan here.

6:09And let's re-execute this query, because I want

6:11to point something out here.

6:12Notice that we don't have any ordering going on.

6:15So how do we sort results when we're

6:18dealing with multiple results combined together?

6:20And the answer there is the ORDER BY in the final query.

6:25So if you want to sort the results,

6:27you need to add an ORDER BY, but you can only

6:29add it to the very last query that you're combining these

6:32together.

6:32And that's exactly what we're doing here.

6:34Same exact query, although rather

6:35than pulling all the columns, we're just

6:37pulling the item name here.

6:38And then we're going to put an ORDER BY in the very

6:40last query, and this will sort them,

6:43in this case ascending by the name.

6:46Another thing I want to show you here

6:47is how to combine results from more than one table.

6:50Well you can just slap more UNIONs in.

6:52Pretty easy.

6:53Again, just remember if you're going ORDER BY,

6:54it needs to be the very last one.

6:56So this one will grab the results

6:59from three different queries, combine them all together,

7:02and then order it by, in this case, the stock item ID.

7:05One more thing you need to know about the UNION operator

7:07is aliasing.

7:08We can only alias columns or expressions

7:12in the very first query.

7:13So aliases are defined in the first query,

7:16sorts are defined in the last query.

7:18And just remember the number of columns returned must line up,

7:21as well as their data types.

7:22And actually, here's an example of that.

7:24We're pulling the city name from the cities

7:26table, and the country name from the countries table,

7:29and combining them together.

7:31The reason this works--

7:31if you hold your mouse over these columns,

7:33you can see the data type for city name is nvarchar,

7:36and the data type for country name is also nvarchar.

7:38So that's why this will work.

7:41There it is.

7:42All right, moving on, let's take a look

7:43at our other two set operators, EXCEPT and INTERSECT.

7:47So let's hit solution explorer.

7:48And let's open up our second query here,

7:50EXCEPT and INTERSECT.

7:52And we'll go ahead and switch databases here,

7:54for this query to point to Wide World Importers.

7:57Let's start with the EXCEPT operator,

7:59and get an example going here.

8:01So EXCEPT returns any rows from the left input

8:05that do not have a matching record in the right input.

8:09So let's get our inputs up here.

8:11Here's the results of query 1, which by the way

8:14is known as your left input.

8:17And here's the results from query 2,

8:18which is known as your right input.

8:21So what will happen here, is the EXCEPT operator

8:24will compare the results.

8:25It's going to do distinctive- based comparison here.

8:28Which means it's going to return the distinct versions

8:30of the entire record, all of the columns combined.

8:33An interesting side note here, is that nulls do equal nulls.

8:36About the only place in SQL Server where that's the case.

8:39That is not the case when you're dealing with your WHERE clause,

8:42or GROUP BY clause, or the ON clause when you joining tables

8:44together, because nulls do not equal nulls

8:47in equality based comparisons.

8:49Again, this is a distinctive based comparison, so they do.

8:52So here's how this works then.

8:53If record one exists in both the left and right hand table,

8:58but record two only exists in the left and not the right,

9:00then your output here is only going to be the second record.

9:04All right?

9:04So EXCEPT once again, will return any rows

9:07in the left hand table that do not

9:09have a matching row in the right hand table.

9:11Now, we have a simple example here

9:13that is only dealing with a single column.

9:14But if we had 10 columns here, remember

9:16that all of those columns would need

9:18to have the same value across both of these queries for it

9:21to be ruled out of the final result set.

9:23And by the way, the same rules apply here

9:25as they do with UNION.

9:26Same number of columns and same data types across these

9:29queries for this to work.

9:31And there are some good use cases for EXCEPT.

9:32For example, let's say our first query here

9:34grabbed all the products out of the products table.

9:36And our second query grabbed the product ID out

9:38of the sales order table.

9:40We could easily then identify which products do not

9:42contain any orders, all right?

9:44So we're kind of doing the same thing here.

9:47We're grabbing all of the colors out of the warehouse stock

9:49colors table.

9:51And then we're grabbing the color ID out of the stock items

9:53table, and we're putting an EXCEPT between them.

9:56What this will show us is any colors

9:58that are unused in the system.

10:00The system being our stock items table.

10:02So if we were to highlight all of this and hit EXECUTE,

10:06this is going to return the colors that are unused.

10:09So we don't have any items containing any of these colors.

10:11If you want to see what those colors are,

10:13we can actually wrap this inside of a sub-query,

10:16and use it in the WHERE clause.

10:17And we'll get into sub-queries in the future

10:18if you're unfamiliar with them.

10:20We can do a SELECT * from Warehouse.colors

10:23where that color ID is IN, and then

10:27that IN is going to look at this result right here.

10:30Because that's the result of this

10:32EXCEPT query that we just wrote.

10:33So now if we highlight all of it, there you go.

10:35There is all of the colors that are unused in our system.

10:40So no items contain any of these colors.

10:42Now the next operator, INTERSECT,

10:44does the exact opposite.

10:46It will only return records from the left table that have

10:49a match in the right table.

10:51So now we can do the inverse of what we just did.

10:53We can take a look at all the colors there

10:55that are in use from the items inside the warehouse,

10:58that stock items table.

10:59And if we execute that, there they are.

11:01We can also then take our sub-query--

11:04or turn this in to a sub-query.

11:05I'll just copy and paste this in,

11:08and we'll put parentheses around our other query.

11:11And now we can highlight all of it, hit EXECUTE,

11:14and these are the colors that are in use

11:15for the items in that table.

11:17All right, let's finish this Nugget up with a SQL challenge.

11:20So we'll get Solution Explorer over there on the right.

11:22And let's open up SQL.sql.

11:25Here is your challenge.

11:25Write a query that returns a unique list of cities

11:28both customers and suppliers reside in.

11:31So two tables here, the suppliers table

11:33and the customers table.

11:34The column you're looking for is postal city ID.

11:37Make sure that those results are unique.

11:40And again, pause me if you want to try this on your own,

11:44otherwise we're going to do this together right now.

11:46Let's begin by switching databases here

11:48to the Wide World Importers sample database.

11:50You'll need to do that so Intellisense

11:52will work, by the way.

11:53Let's rate our first query here.

11:55Select.

11:55And we know the column we're going to work with here,

11:58so I'll just type it up.

11:59PostalCityID FROM Purchasing.Suppliers.

12:04so that will give us a list of cities

12:06that all of our suppliers are from.

12:07And then our second query here is going to be the same thing.

12:10PostalCityID FROM Sales.Customers.

12:16There it is.

12:16And now if we throw a UNION between these two,

12:19that's all we need to do.

12:20So this will give us a unique list.

12:22Look at this right here, see what I did wrong?

12:24Got in the habit of writing a query like it

12:26was all by itself.

12:27We're going to remove that one.

12:29Any time you see red like that, you know something is wrong.

12:32And you can just eyeball your query

12:34to make sure that it isn't something

12:35you did like I did there.

12:37All right.

12:37So again, this will return us a unique list of cities

12:40that both our suppliers and our customers are from.

12:43We can highlight all that, execute, and there it is.

12:45And if you want to a bonus challenge here,

12:48turn this into a sub-query to pull the actual city

12:50names from the cities table.

12:53Again, that's the Application.Cities table.

12:56I'll do that one with you here too.

12:58Select city name, that's the friendly name of the city,

13:01from Application.Cities, where the city ID IN,

13:07and we'll turn the rest of that into a sub-query.

13:11Just like so.

13:12Now if we execute it, we get a list of friendly names.

13:14There's all the cities that we'll be sending holiday cards

13:17to for suppliers and customers.

13:19In this hands- on lab Nugget, we learned

13:21how to combine the results of multiple queries

13:24together with the UNION, EXCEPT, and

13:27INTERSECT set-based operators.

13:29Hope this has been informative for you,

13:30and I'd like to thank you for viewing.

0:00The join operator is what gives us

0:02the ability to write queries that span multiple tables.

0:05In this Nugget, we're gonna break down

0:06all the different types of joins that are available to us

0:09in SQL, including inner joins, outer joins,

0:11and the many flavors of outer joins,

0:13including outer joins with null search conditions.

0:16And we'll also look at cross joins and self joins.

0:19Let's get started.

0:20Whenever we write a query that targets multiple tables,

0:23the first thing we need to do is find the common column

0:26or columns between these tables that we can link on.

0:29Oftentimes, those are going to be

0:30related via primary and/or foreign keys.

0:34So they'll be easy to identify in a properly

0:36designed database.

0:38The next thing we need to identify

0:39is how that linkage, that join operation, is going to occur.

0:43And you can see we have a lot of different options

0:45here, which are usually going to be

0:46dictated by the requirements of your query.

0:49Let's start at the top and work our way down.

0:51The most common kind of join is an inner join.

0:54An inner join is the default kind

0:56of join Transact-SQL, which is why we don't need

0:59to explicitly state inner join.

1:02We can just use the join keyword by itself.

1:04What this will do is the results will contain only records where

1:08our key column or columns match between our join tables.

1:12So table one, in this case, is our left-hand table,

1:15table two is our right-hand table.

1:17So let's say that this is our key column right here.

1:20And let's say we've got values one, two, three here, and two,

1:23three, four over here.

1:25If we were to write this query and execute it against this,

1:27which rows will get returned?

1:29Well, only where we have a match in this column,

1:31which in this case would be two and three.

1:33So these records would get returned.

1:35This four and this one record would not.

1:37Next up we have outer joins.

1:38We have three different flavors--

1:40left, right, and full outer joins.

1:43These allow us to pull back matching records, but also

1:46all the records from one side of the join.

1:49So for example, our left table here is T1.

1:52Our right table here is T2.

1:54If we were to left join our T1 to our T2

1:58like we have here in our example on that key column--

2:01here's our key column--

2:02and then let's say that we have records one, two,

2:04and three over here, and we have records two, three, and four

2:07over here.

2:08All of the results from our left table

2:11are going to get returned because it's a left join, which

2:14means it'll give us everything from T1,

2:16but only the records from T2 where we have a match.

2:20So from T2, we're going to get records two and three.

2:23Now another tool that we have with outer joins

2:25is the ability to add a WHERE clause with a null condition

2:30against any of T2's columns.

2:32What this will allow us to do is find records

2:35that we have in our left table that do not have

2:38a match in our right table.

2:40So if we were to add this null search

2:41condition against T2's key here, what's that going to return?

2:45Well, it's not going to return records two and records three

2:49because we have a match.

2:51So all of these columns would now be null.

2:53What it would do, though, is return record one,

2:55because that was one of those records that

2:57was returned from our left join, but it

2:59didn't have a match in the right-hand table.

3:01So this will allow us to identify orphaned records

3:05in the left table when compared with our right table.

3:08Next up, we have a right outer join,

3:10which is the exact opposite of a left outer join.

3:12It operates on the right table.

3:14So in this case, if we were to execute this query,

3:16it would return every record from our right-hand table

3:20this time instead of our left-hand table,

3:22and only matching records in that left-hand table.

3:25So two and three we'd get returned

3:26from the left-hand table.

3:27All records would get returned from the right-hand table.

3:30We can also add a null search condition

3:32to a right outer join, which will

3:34allow us to identify records that do not

3:36have a match in our left table.

3:39So if we were to do this against T1's key,

3:41or again any column against the T1 table,

3:44this would help us to identify record number four.

3:46Two and three both have a match in this table.

3:49Record one doesn't, so it wouldn't get returned anyway.

3:51But record four would here in our right-hand table.

3:54Our final type of outer join is known as a full join.

3:58This allows us to get all records

4:00from our left table, all records from the right table,

4:02and match records from both.

4:04The real power, though, of the full join

4:07is, when we add a search condition,

4:09looking for null values in both keys.

4:12This will allow us to identify unmatched records

4:15in both tables.

4:16So in this case, one and four record

4:18would get return, but not two and three.

4:20It's also worth mentioning that you

4:21can get to your desired result using any of these joins.

4:25It all depends on how you design your query,

4:27and which table you designate as your left table

4:30and which table you designate as your right table.

4:32We also have two other kinds of joins here,

4:34self joins and cross joins, that you

4:36won't see used as frequently as our other join types.

4:39But they can still come in handy in certain situations.

4:42Our first one is a self join.

4:43This is where we join a table to itself,

4:45effectively creating the same table twice in our query,

4:48but aliasing it differently so we can reference each table

4:51individually.

4:52And this is often used when you want

4:54to represent hierarchies in your results,

4:57or cross-reference similar data within the table.

5:00Finally, we have what's known as a cross join.

5:02And notice we do not need to link on any common column

5:05between these tables.

5:06So this actually doesn't apply.

5:08And that's because every single record in one table

5:11is going to get crossed with every single record

5:14in another table.

5:15This is known as a Cartesian product

5:18because it's essentially the records in one table multiplied

5:21by the records in another table.

5:22This is useful when generating test data,

5:25or maybe you need to create kind of a matrix of all

5:28these records together.

5:29For example, maybe you have a shirts table,

5:32and over here you have a sizes table.

5:34And you have shirts that come in every size.

5:36So we could cross join these tables together

5:39to get all of our shirts matched with all of the sizes.

5:41So that's the join operator and the many ways

5:44that we can bring data together when writing queries

5:46that target multiple tables.

5:48Join me in our hands-on lab Nugget

5:49titled Querying Multiple Tables with Joins

5:52in order to see these in action with some real-world examples.

5:55In this CBT Nugget, we covered all of SQL's join operations,

5:59including inner joins, outer joins, self joins,

6:02and cross joins.

6:03I hope this has been informative for you,

6:04and I'd like to thank you for viewing.

0:00In this hands-on lab Nugget, we're

0:01going to learn how to query multiple tables

0:03with the many join operators available to us in SQL.

0:06So we'll cover inner joins, our three kinds of outer

0:09joins, cross joins, self joins, and we'll look at some tips

0:13here for writing multi-table queries.

0:15Let's fire the virtual lab and get started.

0:17From the desktop of our database server SQL-NUG,

0:20let's get into SQL Server Management Studio

0:22by right-clicking on that icon on the taskbar

0:24and launching right in to our 70-761 solution.

0:28Once Management Studio fires up, we're

0:30going to be working with our fifth project here called

0:32join queries.

0:33And let's start by opening up the very first one here,

0:36which is going to be focused on inner joins.

0:38I'm also going to hide Solution Explorer here,

0:41and let's get a connection in Object Explorer,

0:43because we're starting to work with multiple tables here.

0:45So I want to get you familiar with how they are tied together

0:48through relationships.

0:49So let's drop down the Connect button there

0:51in Object Explorer, hit Database Engine,

0:53and get a connection right to it.

0:55So we're going to start here with the Wide World Importers

0:57database, and we're going to be using two tables.

0:59So we'll start with a very basic inner

1:00join here, customers and customer categories.

1:03Let's say you've been tasked with writing

1:05a query that will pull back customers and their categories.

1:08How would you go about writing this?

1:09Well, the first thing you'll want to do

1:11is obviously get familiar with these tables, as well

1:14as the relationships between them,

1:15so you know what you can potentially join on.

1:18Lets use Object Explorer here and expand databases,

1:21and expand Wide World Importers, and expand Database Diagrams.

1:24I created a couple of them in here.

1:25And let's open up the first diagram

1:27here, customers categories.

1:28This, again, will give us a nice bird's eye view

1:30of these tables, and if their relationships

1:33were set up appropriately, then you will see these linkages.

1:36What we can see here is, in our customers table

1:38we have a customer category ID.

1:41This is a foreign key to the primary key

1:43of customer category ID in the customer categories table.

1:47And you can actually look at this linkage

1:49here and right-click on it and go into Properties.

1:52That will swing out the Properties window.

1:53And if you expand Tables and Columns,

1:55we can see our customer category ID

1:58is the primary key in the customer categories table,

2:01and there's our foreign key in the customers table.

2:05So that alone should give you an idea

2:07of how we're going to write the query to link these together

2:10on those columns.

2:11Those are the common columns between these tables.

2:14Notice, we also have a self-join set up here.

2:17More on that one later.

2:18But let's close out of this database diagram.

2:19Let's head back to our query.

2:21Let's change our connection context

2:22for the query to Wide World Importers.

2:24And then our first query very simply

2:26is going to pull back the customer name out

2:28of the customer table, and the category name out

2:30of that category table.

2:31And we're going to link these together

2:33with an inner join operation on those common columns

2:37across those tables.

2:38Also notice here that we're using

2:39an alias, which just makes our query a lot more readable

2:42in this case because we can use the alias to reference

2:45the columns from those specific tables

2:47rather than using the fully qualified table name.

2:50All right, so this looks good here.

2:52Let's go ahead and highlight this.

2:53Hit Execute.

2:54And there it is.

2:55There's our customers, and there's the categories

2:57that they belong to, all because of an inner join

3:00between those tables.

3:01Now, this is the recommended way to write our queries today

3:04using the join operator with the on key word

3:07to specify what columns we're joining on.

3:09This is still supported.

3:10This is the old school way of writing

3:12joins where we specify our tables,

3:14comma delimited in the FROM clause,

3:16and then use a WHERE clause to tie our common columns

3:19together.

3:19This still works but don't use this.

3:22And I only point this out because you may see this when

3:25supporting older databases.

3:26And you'll even see here that we will get the exact same result

3:30as we did previously.

3:31There it is.

3:32Now, let's crank it up a notch and write a query that

3:34targets four tables.

3:36So we're going to head down here, switch our connection

3:38context to Adventure Works, and I also

3:40want to just get you familiar with these tables.

3:42So over in Object Explorer, let's expand Adventure Works,

3:45expand database diagrams, and here we

3:48have the second one here.

3:50Product category inventory diagram.

3:52If we open this up, we're going to see all four

3:54of these tables.

3:56Now, when you're designing a big, complex query that's

3:58going to require access to many tables, what you want to do

4:01is work with one table at a time.

4:03And start with your base table.

4:05In this case, our base table if we flip over to our query here,

4:08is going to be our product table.

4:10That's our initial table.

4:11And then you can start adding in the tables

4:14that you need to pull columns from.

4:16And if you get to the point where

4:17there is a table that is going to require other tables

4:20and you're not really sure the path that

4:22you need to take to get there--

4:24for example, in order to get to our category table,

4:26we don't have a product category ID column in here--

4:30we need to go through our subcategory table to get there.

4:33Well, this is again, where you can

4:35look at the relationships for this table.

4:37So if we right-click on our product category table,

4:39we can head up here to relationships,

4:41which will open up a dialogue and show us that this connects

4:44to the subcategory table.

4:46Then we can go into that table, look at its relationships,

4:49and find its linkage back to our product table.

4:51It's a great technique to use when you begin writing queries

4:55for the first time in a database design

4:57that you're still unfamiliar with.

4:58Another thing that's useful for understanding

5:00the kind of join that you're going to need,

5:02is looking at the nullability of your foreign key columns.

5:05In other words, let's take a look at our subcategory table

5:08here.

5:08Let's right-click, choose Table View and standard.

5:11We can see that product category ID is not nullable,

5:14which means a subcategory must belong to a category.

5:17So it's pretty much only going make sense

5:19to inner join these tables together.

5:21If we look at our product table, on the other hand,

5:23and do the same thing, and look at our subcategory ID,

5:27look at this.

5:28I know it's tough to see.

5:29Let me zoom in a little bit here.

5:31Subcategory ID is nullable.

5:34So what this means is that if we do an inner join on this table

5:38to subcategory ID, we're only going

5:39to get records where our products have

5:43that subcategory ID.

5:44So that may be a good candidate for a left outer join,

5:48if we're writing a query that needs

5:49to include all the products whether they have a subcategory

5:52or not.

5:53Also, looking at our product inventory table,

5:56notice that the product ID is part of the primary key

5:58of this table.

5:59So we've got a one to one relationship between product

6:01and product inventory.

6:02In other words, we could potentially

6:04have products that don't have inventory.

6:06So this may be another candidate here for a left join.

6:09Again, if we want to include all products in the final results

6:13of our query.

6:14All right, let's go check this out here.

6:15So now we have an idea of how we're

6:17going to link these together.

6:19So if we head back to our query and we check this out,

6:24you can see we're inner joining everything right now.

6:27So we're inner joining product subcategory

6:30on the product subcategory ID across this and the product

6:32label.

6:33Product category we're inner joining

6:34to the product subcategory.

6:36And product inventory we're also joining to our base table

6:38here, product.

6:40So by default, because these are all inner joins,

6:42we're only going to get back products

6:45that have inventory that have a subcategory associated

6:48with them.

6:48460 total records.

6:51All right, moving on we have another multi-table query

6:53here that we're going to look at.

6:54Only this one is going to require

6:56multiple join conditions here on our special offer product

7:00table, and that's because we have an intermediate table

7:02with a composite primary key.

7:05So we're going to need to join this table with two

7:09columns, which is why we have an AND here after the ON keyword.

7:13Let's get familiar with this table structure here.

7:15If we head over to our database diagrams,

7:17this is going to be called product sales specials diagram.

7:19Again, here are these four tables.

7:21And what we're aiming to do here is

7:23write a query that shows us products that

7:26were sold with a special offer.

7:29So if this is our leftmost table in the query,

7:31in order to pull back the description

7:33of our special offer and the name of our product,

7:35again, we need to go through this intermediate table, which

7:38contains a composite primary key.

7:40That's a key consisting of two or more columns,

7:43in this case two.

7:44And again, we can verify that by looking at our relationship.

7:46If you right-click on that relationship

7:48and head into properties and expand tables and columns,

7:51there it is.

7:51We can see that this relationship consists

7:54of both special offer ID and product ID here in this table.

7:58And that's how we're going to need

7:59to link it from sales order detail

8:01using the product ID and special offer ID out of that table.

8:04Once those are linked, we can then

8:06perform inner joins on our other tables

8:08to pull out the fields that we need.

8:11So let's go ahead and close out of our diagram

8:13and head back here to our query.

8:15And that's exactly what we have going on here.

8:17We're linking special offer product

8:18to sales order detail on both of those columns.

8:21Then product to special offer product on the product ID,

8:24and our special offer through the special offer

8:27ID column on both of those tables as well.

8:30And we also have a WHERE clause in here

8:31to filter out anything that does not equal 1.

8:341 means no discount.

8:37So if we were to just execute this without that filter,

8:40it's going to pull back--

8:42still pull back absolutely everything, 121,000 sales,

8:44and you can see most of them are going

8:46to consist of no discount.

8:47So that is special offer ID number one.

8:49And if we put that filter on there,

8:51this should only return a little over 5,000 records.

8:54So these are all the products, all the sales that

8:56were offered with the discount.

8:57And you can see the description of that special offer

8:59over there on the right.

9:00There's the product name, and there's the sales order ID.

9:03So those are inner joins.

9:04Let's move on and take a look at outer joins.

9:07I'm going to hit Solution Explorer and open up

9:09O2_Outer.sql.

9:10We're going to bounce back to the Wide World Importers

9:13database once again.

9:14So let's hook this query up to that database.

9:16There we go.

9:17And also, over here in Object Explorer

9:20let's head down to Wide World Importer

9:21and expand database diagrams.

9:23We're first going to be working with customers

9:25and their contact information.

9:27So two tables here, the customers table

9:29and the people table which stores their contact

9:32information.

9:33So let's start here by opening up our database

9:35diagram, customer people.

9:36You can see here's both of these tables.

9:38We have quite a few relationships

9:39set up between these tables.

9:40Relationships that reference themselves.

9:42Again, more on that when you get into self joins.

9:44And relationships that reference each other.

9:47And if whoever created these relationships named them

9:50accordingly, you should be able to identify based on the name

9:52here, which you can see in the tool tip.

9:54So here's the relationship between the contact,

9:57the primary contact ID.

9:58So this ID right here references the person ID

10:02in the people table.

10:03So that will tell us who their primary individual for contact

10:06is.

10:07And then here's the alternate one, which

10:08is this relationship down here.

10:10So that's what we're going to be working with here

10:12for these outer queries.

10:13The first one we're going to write here

10:15is a very simple inner join just to show you that

10:18and get some context here.

10:20This will return all customers that

10:22have both a primary and an alternate contact

10:25because we're performing inner joins for both of these tables.

10:28One against the primary contact ID

10:30and one against the alternate contact ID.

10:32And the result here is 402 records.

10:35Again, only customers that have both

10:37a primary and alternate contact.

10:39But this alternate contact person ID

10:42is nullable in the customer table,

10:45meaning there could be customers that don't

10:47have an alternate contact.

10:48And what if we wanted to get a list of those

10:50or a list of both a customer with a primary contact

10:54ID and then with or without an alternate contact ID?

10:58That's where a left join can come in handy.

11:01And that's what our next query is doing.

11:02So this time, we're inner joining for the primary contact

11:05person ID, which is fine because this is actually a non nullable

11:09field.

11:10So every customer must have a primary contact,

11:12but they may or may not have an alternate contact.

11:14And that's why we're performing a left join.

11:16This should give us a lot more than 400 results.

11:19And there it is, 663.

11:21Now, we're getting customers that do or don't.

11:25These customers that have null in their alternate contact

11:29field here were not returned in our previous queries.

11:31And you can see, we have quite a few of them in there.

11:33Now, what if we wanted to write a query just

11:35to identify those with a primary contact ID and those

11:38without an alternate contact ID?

11:41This is where that null predicate comes in handy.

11:44And here it is.

11:45We can search for null values here for the alternate person

11:48contact ID, which will then give us all of our customers

11:52who only have a primary contact ID.

11:54See that?

11:55So that's where that null predicate can come in handy.

11:57Moving on, let's take a look at a right outer join.

12:00This time, we're going to be working

12:02with the customers table and the customer categories table.

12:04We worked at these before and you

12:06can find them in the first database diagram here.

12:08But this time, what we're going to say

12:10is, our customer categories table is the right table.

12:12So let's say we wanted to identify categories first

12:15with or without customers.

12:17Remember, this will show us all matches and anything

12:19that doesn't match.

12:20If we execute this, anything with a null value

12:23are customers that do not have that category.

12:26So there are no agents and there are no wholesalers as customers

12:29with those categories.

12:30And of course, we can identify just those

12:32by adding a null condition against any column out of there

12:36from our customer table, since that

12:38is the left table in this case and we're right joining to it

12:40with the customer categories.

12:42And this will return us with--

12:43and there was one more in there as well, general retailer.

12:45So those are the categories without customers.

12:49So again, left and right join, it just depends on which table

12:51you place in the left and which table you place on the right.

12:54And finally, we have a full join,

12:56which again, is essentially just a left join

12:58and a right join simultaneously.

13:00So our previous query written with a full join

13:02will give us customers with or without categories,

13:04and categories with or without customers.

13:08And if we execute this, really we're

13:09going to get the same results as we did before.

13:12There's those three categories without customers.

13:14And there are no customers without categories

13:16because it is required.

13:18But again, we could add a null condition onto either side.

13:21If we add it on one side it's a left join.

13:22Add it to the other side it's a right join.

13:24But if we add this to both sides,

13:25it will allow us to identify records that

13:27do not exist in either table.

13:28And in this case, we're going to get the same results

13:30as a right join, because again, customers must have a category

13:35but categories can be without customers,

13:36which is why we get that.

13:37And those are all of your outer joins.

13:39Let's look at our special join types here.

13:41If we head back into Solution Explorer,

13:43let's open up our third query, cross-self.sql.

13:47And let's start here with a cross join.

13:49Let's say, for instance, that we wanted

13:51to get a list of all stock items with all of their colors.

13:55Now, we're just going to do this for one supplier

13:57because if we didn't we would get an unbelievable amount

14:00of records.

14:01But this will return us with eight records.

14:03And we first need to switch our database here

14:05to the Wide World Importers.

14:06But our first quarter here is going

14:08to return us with all the stock items from supplier ID 1.

14:12There's a grand total of 8.

14:14And then our colors, we have a grand total of 36 colors.

14:17So if we were to cross join all of the items

14:20with all of the colors, for supplier ID 1

14:22the grand total is going to be 8 times 36, which

14:25is going to be 288 records.

14:28Every single item for supplier ID

14:31crossed with every single color in the colors table.

14:34And finally, we have a self-join.

14:35Again, let's open up the database

14:37diagram here for-- either one of these

14:39will work because the customer table's in both of them.

14:41But if we look at this joint right here or this relationship

14:44I should say, you can see that it's referencing itself.

14:47We have a column in here called bill to customer ID.

14:50Customers are billed to other customers.

14:53So this is why it's referencing itself.

14:57This is actually a customer ID inside of this column.

15:00So when we write our join, we're going

15:02to reference the same table twice

15:03and we're going to join the customer ID to bill to customer

15:05ID, and that will show US customers that

15:08were billed to other customers.

15:10And if we close out this and head back to our query,

15:12that's exactly what we're doing here.

15:13You can see our left-hand table of sales.customer,

15:16and then we're joining two another instance

15:18of that customers table on the right hand side.

15:20And we're joining the customer ID

15:22to the bill to customer ID column.

15:24And the results of this are customers that

15:27are billed to other customers.

15:28As you can see, we have our satellite offices that are all

15:31billing to the head office.

15:33And as you scroll down, you'll see individual customers

15:36are billed to themselves.

15:37All right, let's finish this Nugget up

15:39with a look at your SQL challenge.

15:41Let's bring out Solution Explorer,

15:42open up O4_SQL.sql where your mission is going to be to write

15:47a query that targets three tables.

15:49You're going to pull back employees

15:51and their personal information.

15:53So it's going to be a bunch of inner joints here

15:55between employee, person, and email address.

15:57Pull back these columns so we can get a nice, complete

16:00employee record.

16:02Also, if you're not sure how to link these tables together,

16:05what you can do is expand Adventure Works,

16:07head into database diagrams, and there is an employee diagram

16:10waiting for you.

16:10So you can use this diagram and these relationships

16:13to find out which columns you should join on to bring

16:16all these tables together.

16:18So now is a good time to pause me

16:19if you want to try this out on your own.

16:21If not, we're going to do this one together.

16:23All right, let's do this.

16:24Now, the absolute best way to become a SQL

16:26ninja when you're training is to type everything out by hand.

16:29So I would advise doing that because you'll

16:31commit the structure of SQL to your muscle memory

16:34and your physical memory.

16:35But I'm not going to make you sit here

16:37and watch me type all of this up.

16:39So there is a simple solution.

16:40And again, if you're not comfortable with writing

16:42transact SQL yet, feel free to use the query designer here

16:46and watch it write that SQL for you.

16:48So here is that our query looks like.

16:50We're grabbing the employee table--

16:51that's our leftmost table here--

16:53so that way we can grab the job title and the hire date.

16:56Now, we're going to join the person table to that employee

16:58table by performing an inner join on the business entity ID

17:01column between these tables.

17:03From there, we're going to join the Person.EmailAddress

17:05table so we can get the email address of our employees.

17:08We're joining that to the employee

17:09table using an inner join, again, on that same column.

17:12And that's what allows us to pull out the email address.

17:15Person allows us to pull out their first and last name,

17:17and again, employee, job title, and hire date.

17:19We don't need to highlight this because I didn't

17:21switch our connections here.

17:22So I'm going to hit Execute, which

17:23will switch our database connection

17:25and then execute the query.

17:27And there's the result. 290 records, all

17:29of our employees with their first

17:30and last name, job title, hire date, and email address.

17:34In the hands-on lab Nugget, we covered

17:36the many flavors of the join operator

17:38and how we can use them to write queries

17:40that target multiple tables.

17:42I hope this has been informative for you

17:43and I'd like to thank you for viewing.

0:00Transact SQL is loaded with many built-in functions

0:03that we can use for a variety of purposes.

0:06For example, we can transform and format

0:08the results of our query using string functions.

0:11We can perform calculations with mathematical functions,

0:14tally up data with aggregate functions,

0:16and even make decisions with logical functions.

0:18So in this Nugget, we're going to break down

0:19what a function is.

0:20We'll talk about scalar versus table valued functions,

0:23determinism and why it's important,

0:25and we'll talk about SARG-ability,

0:28which stands for search arguments,

0:29and why we need to be careful about where and how we

0:32use these functions within our queries.

0:34Let's get started.

0:35I like to think of functions like little command line

0:37utilities built into Transact SQL.

0:40We call them by name.

0:41We optionally pass them a parameter.

0:43They go and do work with that data and return a result.

0:47That's all a function is.

0:48They're little reusable black boxes of code

0:51that we can use to improve the results of our query.

0:54So in our example query here, we are making four function calls.

0:57Two of these calls or to the exact same function.

0:59And by the way, these are all built-in functions.

1:01We're going to talk about user-defined functions

1:03a little later on in the course and learn

1:05how to build their own.

1:06So the month built-in function here

1:09accepts an expression of a date time data type.

1:11Here, we're just passing in a column--

1:13the hire date column--

1:15and this will return the numeric representation

1:18of the month of that date.

1:20And the result here is hire month, which we have down here.

1:22So this will return a value of 1 to 12-- again,

1:24representing that month.

1:26Another example here is the intermediate

1:28if function that we're using.

1:30This is a logical function or a system function

1:32that evaluates an expression.

1:34If this expression evaluates to true, it returns the true part.

1:38If it's false, it returns the false part.

1:41So in the employee table, we have a gender field

1:43that is either F or M. So it's going

1:45to evaluate this for every record.

1:46If it's F, it's going to return female.

1:49If it's anything other than F, it's going to return male.

1:52Another useful built-in logical function is choose.

1:55This one, again, evaluates an expression

1:57as the first parameter.

1:59And then, it will return the index

2:01of whatever that expression evaluates to

2:02of the remaining parameters.

2:04So in case, we know we're using another function inside of here

2:07as the expression, but we know that's going to return

2:09a value of 1 through 12.

2:10So that means if it's 1, it's going to return this one.

2:132, this one.

2:133, this one.

2:14So on and so forth, all the way to 12.

2:17So those are just a couple of really simple examples

2:19of built-in functions, but you can

2:21begin to see how they really empower us

2:23and give us control and flexibility over the results

2:26of our queries.

2:27Now, there are two kinds of results

2:29that a function can return--

2:30scalar valued, which is just a fancy term for single valued.

2:34All built-in functions are scalar valued.

2:37Look at all these functions that we just looked at.

2:39They all return a single result. And there's also table value.

2:43These return a table, just like we have here and just

2:46as the name implies.

2:47And we build these using UDFs.

2:49In fact, they're actually called table

2:50valued user-defined functions.

2:52And you're generally going to use these to enhance a view--

2:55to parameterize it.

2:56So you'll place a UDF on top of a view that accepts parameters

3:00or to replace a stored procedure that returns a single table.

3:04Again, more on UDFs.

3:05We've got a Nugget dedicated to it

3:06down the road when we get into programmable database object.

3:09We also have the concept of determinism.

3:12Now, deterministic means that given

3:14the same set of parameters, a function will always

3:17return the same result. These are

3:18all examples of deterministic usages of built-in functions.

3:23So if you were to pass these exact same parameters into all

3:25these functions, just as we are here, over and over

3:28and over and over again, you will always

3:29get the same result. Non-deterministic

3:32means you will get a different result given those

3:35same parameters, and there are certain functions out there

3:38that can be either deterministic or non-deterministic depending

3:41on what you passed to it.

3:42Here's the random function, for instance.

3:44If you pass a seed in, like we are here,

3:46then it will always return this value.

3:49If you don't, it will always return of a random value.

3:52So the million dollar question, why does this matter?

3:55And the answer is using non-deterministic functions

3:57in certain database objects can limit what

4:00you can do with that object.

4:02For example, if you were to use a non-deterministic function

4:04inside of a view, you wouldn't be

4:06able to place an index on that view, which can dramatically

4:09affect performance.

4:10And there is a property, by the way,

4:11called deterministic on all columns from tables and views

4:15that you can see in SQL Server Management Studio.

4:17Something else we need to talk about here

4:19that functions can have a huge impact on when used

4:21in your query is SARG-ability.

4:24Now, SARG, again, stands for search arguments,

4:27and the whole idea is that a query is said to be SARG-able

4:31if the database can take advantage of the index

4:34to improve performance.

4:36Where and how you use functions within your query

4:39you can have a dramatic impact on if that query is SARG-able

4:42or not.

4:43Here's an example.

4:44Now, both of these queries return the exact same result.

4:47One of them is SARG-able and performs

4:50what's known as an index seek.

4:51So it only reads into memory exactly what it needs.

4:55So it performs very well.

4:57The other one is not SARG-able and performs an index scan.

4:59It actually you has to read the entire table into memory

5:03and therefore performs poorly.

5:05Can you guess which one it is?

5:06It's actually the first one here.

5:07This is bad because if you look at the predicate in the where

5:10clause, it's using the datediff function

5:12and we're passing in a column as a parameter

5:14to this function, which means it's

5:16going to need to evaluate this for every single record

5:20in the table that you specify in your query.

5:22And by the way, the datediff function is incredibly useful.

5:25We'll be using it quite a bit in the future.

5:27But essentially what it does is it gives us

5:29the difference between two dates,

5:32depending on your interval.

5:33Here we're saying day.

5:34So it's going to give us that difference between the order

5:37date and the current date, and if that value

5:40is less than or equal to 30, then it

5:43will return it in the results.

5:44So what we're doing here is trying

5:46to find orders that were placed in the last 30 days.

5:48And again, this is bad because it

5:49will need to run this function for every single record

5:52in the table to see if that record meets this condition.

5:56Now, we can actually rewrite this query

5:58to get the same results, only make it SARG-able.

6:01I love that word.

6:01It just rolls right off the tongue.

6:03And that's because this condition

6:05doesn't need to be evaluated for every single record

6:07in the table.

6:08So it's not going to need to do a table scan.

6:10In fact, this is going to get resolved once,

6:13which will give us what the date was 30 days ago

6:16and this will then return any orders that

6:18are greater than or equal to that date, which again,

6:21is the exact same result as above, the difference

6:24being this can take advantage of indexes

6:26and will not require a full table scan.

6:27So this is something you will want

6:29to be mindful of when designing your queries

6:31because it can have a huge impact on performance.

6:34And thankfully, execution plans can help you identify problems

6:38like these.

6:39One final note, again, Transact SQL

6:41is loaded with many functions spanning many categories.

6:44Learn as many as you can because the more functions

6:47you have in your skill set, the more problems

6:49you'll be able to solve in your queries.

6:51Make sure to check out our hands-on lab

6:53Nugget on built-in functions to see all the stuff we talked

6:56about here in action and look at some

6:58of the more popular functions that you

6:59see in everyday queries.

7:01In this CBC Nugget, we covered built-in functions.

7:04We defined what they are, how they're used,

7:06and how to use them.

7:07I hope this has been informative for you,

7:09and I'd like to thank you for viewing.

0:00In this Hands-on Lab Nugget, we're

0:01going to write some queries that utilize built-in functions.

0:04We'll get familiar with some of the popular built-in functions

0:07that you'll be using in everyday queries,

0:09and we will look at how to write SARG-able queries.

0:13Let's fire up the virtual lab and get started.

0:15From the desktop of our SQL Server,

0:17let's head down the SQL Server Management Studio,

0:19give that a right click and launch right into our solution

0:23containing all of our projects.

0:25Once Management Studio loads up, it

0:26should expand our project called Built-in functions.

0:29And let's start just by going over

0:31some of the popular functions across many

0:33of these categories, and we also hide Solution Explorer here

0:36and we'll push Object Explorer aside as well.

0:38And first things first here, let's

0:39switch our connection over to the adventure work sample

0:42database.

0:43There we go.

0:44And we're going to start by looking

0:44at a couple of logical functions.

0:46Now, this is the exact same query

0:48that we were analyzing back in our whiteboard

0:51on Built-in functions.

0:52But one useful tip I want to give you here

0:54is, if you're looking at somebody else's query

0:56and you see them use a function that you're not familiar with,

0:59place your cursor on it, hit F1.

1:00Don't forget about help, it's extremely useful.

1:02Again, you can get a nice overview,

1:04get familiar with the syntax, the arguments that

1:06function supports, its return types, and then, of course,

1:09get some examples.

1:10Another really useful tip I want to give you

1:11here inside of the help UI is you can actually

1:15sync the page you're on with the table of contents.

1:18So notice here that the table of contents isn't synced at all.

1:21It's just on the default. Well, this button here on the toolbar

1:24will actually sync it up and take you

1:26right to where that exists in the help.

1:28And what's useful about this when it comes to functions

1:30is now you can see all the different categories

1:32of functions.

1:33So there's our logical functions in there.

1:35Say we wanted to get familiar with some string functions,

1:37we can come down here to the string functions category

1:39and look at them all.

1:40So this is just a great way, some good documentation here,

1:44good reading material, good learning material,

1:46especially, if you want to learn a lot about functions.

1:50Because it's pretty easy documentation

1:51to get the hang of it.

1:52Of course, the examples help.

1:54So keep that one in mind.

1:55All right, back to our logical functions here.

1:58Again, choose is useful because what

2:00you do is you pass in an index or something that will resolve

2:02to an index, and then it will return

2:04whatever value is placed in that index of the remaining

2:07parameters here.

2:08If is the intermediate if statement,

2:10you pass in a bolding expression, something that

2:12will resolve to true or false.

2:14This is the true part.

2:15This is the false part.

2:16And again, here is what the result

2:17would look like against the AdventureWorks database.

2:20Let's also talk about conversion functions.

2:23There is something very important

2:24that you need to understand about converting

2:27between data types.

2:29When an operator combines two expressions of different data

2:32types like we have here, we have a character.

2:34This is obviously a character string,

2:36so it's going to result to a character datatype.

2:38And the get data is returning a date data type.

2:41What happens when we combine these expressions together.

2:44Well, let's find out.

2:45Let's highlight this and hit Execute.

2:47Something bad is going to happen.

2:49Conversion failed when converting data time

2:51from a character string.

2:52So how the rules work, there's a whole data type precedence

2:56that goes on here.

2:57And the rule is that the data type with the lower precedent

3:01will be converted to the data type with the higher precedent.

3:04And if that conversion is not implicitly supported,

3:07then an error will get returned just like we have here.

3:09And this is one of most important things

3:11you need to understand as a SQL Server developer and query

3:14writer is when you get things like this,

3:16you need to perform manual explicit conversions.

3:20And we do that using cast or convert.

3:23What's the difference between these two?

3:25One is the SQL standard in ANSI or ISO SQL standard,

3:29and the other is Transact SQL specific.

3:32So CAST is the standard, and you want

3:34to use this if you want your database

3:36to be portable across multiple systems,

3:38and you'll use convert if you're purely a SQL Server shop.

3:41And really, you get a couple of extra features with CONVERT.

3:43Like you can format it.

3:44It's got a formatting parameter on it,

3:46and the syntax is a little different between these.

3:49But generally again, use CAST whenever possible,

3:52especially if portability is important in your world.

3:55Use CONVERT if your a SQL Server only shop.

3:57But here's how we do it with CAST,

3:58we specify our expression and then as our data type.

4:03So this will actually explicitly convert our date data type here

4:07to a character which is compatible with, obviously,

4:10this stream here.

4:11So this will work if we highlight this block of code

4:14and hit Execute.

4:16Look at that, it works just fine.

4:18Here is the convert way of doing it using that third parameter

4:21which we will again format it and style a little bit

4:24different than we have down here.

4:25Go ahead and hit the white space over here

4:27to select that whole line.

4:28Hit Execute, and there it is.

4:30Just a little different format there.

4:31Let's look at a couple of more examples here, only this time

4:34in the context of a query.

4:35Let's start with this one right here.

4:36I'm going to highlight this and Execute it,

4:38so we can take a peek at it.

4:39And let's take a look at what's going on here.

4:41So this is a simple query that ties product and product

4:44inventory together to give us our quantity, standard cost,

4:47and the total cost, which is quantity times standard cost.

4:49But notice something here, if you hold your mouse

4:51over quantity, we can see it's data type is a small integer.

4:54If we hold our mouseover standard costs,

4:56we can see that the data type is money.

4:58So for this total cost column right here

5:01an implicit data type conversion happened.

5:04The smallest one, or the one with the lowest precedence

5:06here, was quantity.

5:07Small integer is low on the data type precedence,

5:10and it was converted to standard cost which

5:12is money, which is higher than small integer, all right?

5:15So that's why that implicit conversion took place.

5:18And in this case, it's OK.

5:19That was kind of our intended result

5:21here because the total cost would probably

5:22be a money debted thing.

5:24So that's just fine, this was a legal implicit conversion

5:27and what we were intending.

5:28But there are situations where a conversion may

5:31happen implicitly, but you didn't really

5:33intend it to be that way.

5:35In that case, you will need to explicitly convert

5:38your data types.

5:39Here's an example right here, I'm

5:40going to go ahead and execute this one as well.

5:43And what we're going to notice here

5:44is, let's say that our boss came in and said,

5:46you know what, I need you to reduce

5:48the quantity of inventory by 50% across everything.

5:50Can you write me a query to do that?

5:52And well, yeah, sure, why not.

5:54So we write the query of quantity

5:56and we multiply it by 0.5.

5:58What's going to happen here, quantity is a small integer,

6:010.5 is a decimal.

6:03So quantity is going to get converted to a decimal.

6:07Decimal has a higher data type precedence.

6:10And that's going to result in quantities with halves,

6:12with decimal voices on them.

6:13And we're not selling half bikes.

6:15We might sell bikes that are half off in price,

6:17but we're not selling half a bike, right?

6:19So that would definitely make our boss rather angry.

6:22So in this case, we would need to do an explicit conversion.

6:26We could still perform our calculation

6:27but inside of a cast here to cast the results of this

6:31to a small integer, and that's what we get here.

6:34So keep those things in mind.

6:35They're very important.

6:37Again, if something cannot be implicitly converted,

6:39you'll get an error, and you'll need to explicitly convert it,

6:42and then just keep unintended consequences in mind

6:45because they can certainly happen within your queries,

6:47and that again is where you'll need to explicitly convert

6:50your data types.

6:51Now moving on, let's take a look at a couple of popular string

6:54functions.

6:55Again Transact SQL has a lot of string functions in them.

6:57I would certainly advise going through the documentation,

6:59just running through a couple of samples

7:01because these are extremely useful to have

7:03in your back pocket, especially when it comes time to slice

7:05and dice up those character fields.

7:07And let's start with the left and right string functions.

7:10These are incredibly useful, incredibly powerful,

7:12and often used in combination with other string functions

7:15to get the results that you're looking for.

7:16And another tip here is if you hold your mouse

7:18over any function, you can get the syntax,

7:21including the parameters they accept,

7:23the data types on those parameters,

7:24and the data types that they return.

7:26Now the left function will return a portion

7:28of a string or an expression.

7:29So you pass in your expression, in this case,

7:31we're just passing in a column and then

7:33the number of characters you wanted to count.

7:35So in left case, it's going to count left to right.

7:37In the right string function, it will count right to left.

7:40So this query will actually return the first name,

7:42the last name and then the initials

7:43because we're only grabbing the first character

7:45of the first name plus the first character of the last name.

7:48If we execute this, that's what we'll get as a result.

7:51And I should also mention, these become

7:52very powerful when combined with other functions like CHARINDEX

7:55and PATINDEX because instead of statically placing a number

7:59to count over, we can make that dynamic by searching

8:02for specific characters or patterns of characters

8:05within a string.

8:06Speaking of CHARINDEX and PATINDEX,

8:08you will see these used quite a bit in stored procedures

8:11and queries that deal with string searching, manipulation,

8:14transformation, all that stuff.

8:16And essentially, the difference between these

8:18is CHARINDEX does not support wildcards, PATINDEX does.

8:21Both of them return the starting position of whatever string

8:26you're searching for.

8:27So in this case, CHARINDEX, we're

8:29just searching for any cities that contain the word city.

8:33And this will return the starting position of the sea

8:37in this case in city.

8:38PATINDEX, we're searching for anything that contains towns.

8:40So any cities that contain the pattern here of town will get

8:44returned.

8:45And this will return the starting position,

8:46and the WHERE clause is what is driving this

8:47because this will only return records

8:49where cities have the word city in it,

8:52and cities have the word town in it.

8:54And if we highlight all of this, and it executes,

8:56we should see just that.

8:57Everything's either going to have city or town in it.

9:00City starts at is driven by CHARINDEX,

9:01towns starts at is driven by PATINDEX.

9:03And if you scroll down, we should

9:05have a couple of towns in there and quite a bit of cities.

9:08Again, those are incredibly powerful when combined

9:11with other string functions.

9:13Now, we've also got substring replace and reverse, a couple

9:16of other useful ones here.

9:17Here we're going to take the product number

9:18and do all kinds of fun things with it.

9:20First, let's highlight this and we'll

9:21scroll down in a script pane here,

9:22so we can see what we're dealing with.

9:24So the very first column here is product number as it is stored.

9:27The second one, using the left function

9:29will return the first two characters.

9:31The third one here though, substring is the big one.

9:33This is a function that everybody

9:34should learn because it allows us to find strings

9:39within strings, again, one of the most common string

9:42functions you'll see out there.

9:43And if we look at the syntax, we've

9:44passed in an expression, a starting position,

9:46and the number of characters to return.

9:49You'll often use something like CHARINDEX or PATINDEX

9:52to find that starting position of a character

9:55for the second parameter here.

9:56But let's say that our boss came to us and said,

9:58you know what, I need the four characters after the dash.

10:02And you can see there's quite a few different formats,

10:04but they need that middle code here.

10:06So we get the partial product number,

10:08and that's exactly what we're doing here is we're saying,

10:10we're past the product number, then we're using CHARINDEX

10:13here, which by the way, has an optional third parameter

10:15as a starting position.

10:16We didn't actually need this here,

10:18but I wanted to show it to you because the default is

10:20starting at zero which is why we didn't use it up here.

10:22But this is going to search for the very first dash,

10:25and then it would count over including that dash, which

10:27is why we add a plus one to this because everything

10:29is zero based.

10:30So that means it will start at the character after the dash.

10:33And that's why we get 5381, 8327, so on and so forth

10:37because we're saying, only count over 4 after that dash,

10:40and that'll give us the result. So that's substring.

10:42Replace is another really useful one.

10:44In this case we want to replace the dash with nothing,

10:48with a blank string.

10:49So we get that dash out of there.

10:50So that will strip the dash out of here

10:52and give us a new product number.

10:53And reverses is a fun one that will just reverse a string.

10:56So there's just a small taste of some of the useful string

10:59functions that you'll be working with in SQL.

11:01We also have quite a few daytime functions.

11:04A couple of useful ones here are DATENAME,

11:06DATEDIFF, DATEED, DATEPART is another good one.

11:09But here's an example using DATENAME,

11:11you pass in the part of the date that you want to extract,

11:14and it will give you that date from whatever column

11:16or expression that you pass in.

11:18So here it's taking OrderDate and then slicing it up

11:20into all the different parts of that date.

11:23Moving on here to DATEDIFF, probably

11:25one of the most popular date and time functions out there.

11:28This will return the interval between two dates.

11:32And you specify that interval.

11:34That could be year, month, day, those probably being

11:36the most popular ones here.

11:37But you pass in your starting date and your ending date,

11:40and it will give you the differences between them.

11:42In this case, this will give us the number of years

11:45that an employer was employed.

11:46It will take their hire date as a start

11:48date and the current date as the end date

11:50and only give us how many years are between them.

11:52DATEADD, another really useful one that works similar.

11:55We passed in an interval, we passed in our increment which

11:57can be positive or negative.

11:58Now we pass in a date, and this will

12:00resolve then to, in this case, five years ago from right now.

12:06And we're saying, give us any employees

12:08that were hired where their hire date is less than

12:11or equal to 5 years ago from now.

12:13So the end result here will be any employees that

12:15were hired five years or more.

12:17So it will show us some veteran employees out there,

12:19and if we highlight this and hit Execute,

12:21you'll only see years employed that are five and up.

12:24See that, so no less than five in this case.

12:28Moving on, here's a small taste of some system functions

12:30as well.

12:31ISNULL is a popular one.

12:33There's also ISNUMERIC for testing out values.

12:35In this case, if a value is null,

12:38it will replace it with whatever you put as a second parameter

12:40here.

12:41New ID will generate a brand new global unique identifier,

12:44then you've got some other system level functions

12:46out here that are useful when you're auditing.

12:48You've got the system username, which

12:50is the currently logged on user name, database username, which

12:52is the database user's name, and the name of the machine.

12:55So if we highlight all of that, we

12:57can see here there's our first one where

12:58it's going to replace all the nulls with an A. There's

13:01our global unique identifier.

13:02And here is the name of the currently logged on user,

13:04the name of the database user, and the name of the machine.

13:07Again, I'll try to sprinkle in as many of these as possible

13:10as we write queries throughout the course.

13:12All right, let's move on and talk about how

13:13to design SARG-able queries.

13:15This is extremely important, something

13:17you want to ingrain into your head

13:19because non-SARG-able queries can be disastrous, especially

13:23in a production environment.

13:25Now remember, what makes a query SARG-able is if it

13:28can be covered by an index.

13:31And a lot of the times we can make

13:32queries non-SARG-able by placing things like scalar functions

13:37as predicates within our WHERE clause.

13:40Here's a good example of that.

13:41First, let's switch our database connection here

13:43to AdventureWorks.

13:44And our first query here uses a capacity order date column

13:49into DATEDIFF.

13:50And what this is going to result in is an index scan.

13:54And you actually see this if we pop open the actual execution

13:57plan here.

13:57So make sure that button is toggled on.

14:00Hit Execute, and you won't be able to tell, oh, sure,

14:02it was fast because we have a fast database server.

14:04But still that took two seconds to return 5500 rows.

14:07And if we head over to the execution plan,

14:08that performed an index scan.

14:11And scans are bad news because it would never

14:13be able to take advantage of an index.

14:14In order to resolve and run this function,

14:17it would need to do it for every single row in the table

14:20to see if it qualified.

14:21And by the way, what this query is doing

14:22is grabbing any orders that are over five years old.

14:27And if I hit Control in order to bring up our results here,

14:29that's what you'll see is, eh, all these orders

14:31are over five years old, all right?

14:33So we can make this much better.

14:36And we can still do it using a function in the WHERE clause

14:39as long as we don't actually pass

14:40a column into that function.

14:42So we can get the same exact result here

14:44and make this SARG-able by just designing it

14:47a little differently.

14:48And that's usually what it comes down

14:50to when you have a query that's performing poorly

14:53and you realize that it's doing an index scan rather

14:55than an index seek and need to make it SARG-able,

14:57it's just a matter of getting creative and trying to think

15:00your way around the problem.

15:01And that's exactly what we're doing with this predicate down

15:04here is we're saying, rather than give us the date

15:06difference using the order date and getting there

15:09where anything is greater than or equal to 5, we're saying,

15:11generate the date as it is of now five years ago.

15:15So this will give us five years ago from today,

15:17and it will give us any order date that's

15:19older than five years ago today with a less than or equal to.

15:22This will give us the exact same result

15:24if we execute this and go over to our execution plan.

15:27Now it's still doing index scan, that's

15:29because we don't have a proper index to cover this query,

15:32and that's another reason why these execution plans are

15:34great because it identified that missing index

15:37and look at the performance impact

15:39we can get here by creating it.

15:40So if you right click anywhere in the whitespace

15:42here and choose missing index details,

15:44it will actually create the index for us,

15:46or at least give us the T SQL.

15:47Now I'll just move this comment up here.

15:49And now we can execute this.

15:51And it's going to create a non-clustered index.

15:52All we need to do is give it a name here.

15:54I'm going to call this IX underscore SARG-able,

15:57there we go.

15:58So this will create an index on the order date

16:01and also include the sales order ID.

16:03But if we execute this, it's going

16:04to create that index, all right?

16:05Now watch, let's head back to our query here

16:07and they're actually going to-- well, yeah, at first,

16:10let's just execute this one to make sure

16:11that we're doing an index seek, beautiful, index seek.

16:15And now, let's highlight both of these

16:16to see the performance difference

16:18between a non-SARG-able and a SARG-able query.

16:21It should be pretty massive.

16:23It is, look at there.

16:2588% of that batch was focused on resources handling

16:30that first non-SARG-able query and only

16:3212% for that second query.

16:34And again, the big difference there is the index

16:36scan versus the index seek.

16:39Impressive right?

16:40Let's take a look at another example

16:41here only this time with strings.

16:43And look at this, this is non-SARG-able here.

16:46Why?

16:46Because we're using a function as a predicate in the WHERE

16:50clause and passing a column to it.

16:52So this is non-SARG-able and this

16:54will result in an index scan.

16:57Our second one however is SARG-able

16:59because like when you place the wildcard at the end,

17:01it's SARG-able.

17:02If you place the wildcard at the beginning, it's not.

17:04And there are other things.

17:05Anything that's not equal to or not

17:07like, those are all non-SARG-ables

17:08so be careful about using those.

17:10But this should be SARG-able, and if we

17:12highlight both of those and hit Execute

17:14and go to our execution plan, look at the difference here.

17:17We've got an index seek down here,

17:18and we've got an index scan up here.

17:203% are bought in one particular, as

17:22opposed to 97% of the batch for a top one.

17:25And again, a lot of the times it's not going to be obvious.

17:28So that's why execution plans are your good friend

17:31in paying attention to the actual operations that

17:33are performed under the hood to retrieve the data.

17:36All right, let's finish up here with a SQL challenge.

17:38I've got a good one for you.

17:40Let's open it up.

17:41Here it is.

17:42I've written you a non-SARG-able query

17:44here because we are using the year scalar function

17:48in passing in order date to it.

17:51Our goal here is to get all the orders in the year 2016.

17:55And we get them with this query, but again, it's not SARG-able.

17:58So can you rewrite the WHERE clause of this query

18:02to make it SARG-able.

18:03And I'll give you a little hint here, view the execution plan

18:06and also you will need to create the necessary index for that

18:08to work.

18:09Make sure you switch to the wide world importer's database,

18:12you should be by default. And pause me now

18:14if you want to try this on your own.

18:15If not, we're going to do it right now.

18:17So let's copy this query, and let's paste it down here.

18:20And let's fix this WHERE clause.

18:21This is actually a lot easier than it

18:23looks to make this SARG-able.

18:24We can use an open-ended date search

18:27by doing something like this where OrderDate

18:29is greater than or equal to.

18:31Remember our date format here, remember it's year,

18:34and then 0101, and OrderDate, less than or equal to 2016 12,

18:4431.

18:45There it is.

18:46That's an open-ended date range search.

18:48They're covering all of 2016, and this is SARG-able

18:52because we're not referencing any function,

18:54were not passing any columns into a function.

18:57So if we highlight both of these--

18:58and don't forget to turn on your execution plan here--

19:01and then hit Execute, we should hover over our execution plan,

19:04and we're going to see that we are

19:05going to need a non-clustered index down here

19:08to cover this query.

19:09Again, right click in the whitespace.

19:11Choose to find our missing index here,

19:13and it will write it for us.

19:15We'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.

19:21And we can re-execute this query.

19:24And this time we should see a big difference, once again,

19:2688% up top, 12% at the bottom.

19:29And so that's why we can take our poor-performing,

19:31database-crushing, non-SARG-able-queries and turn

19:34them into high performing SARG-able ones.

19:36In this Hands-on Lab Nugget, we took a journey

19:38through the world of built-in functions.

19:40We took a sampling of some of the popular ones

19:42out there, saw how to get help on them

19:44and looked at SARG-ability and how it can affect our querie's

19:48performance.

19:48I hope this has been informative for you,

19:50and I'd like to thank you for viewing.

0:00In order to aggregate data, we use the GROUP BY clause

0:04in conjunction with one or more aggregate functions,

0:07like SUM or AVERAGE or COUNT or MIN or MAX.

0:11In this Nugget, we're going to break down the Group By clause

0:13and get familiar with some of these aggregate functions.

0:16Let's get started.

0:17The Group By clause operates on one or more columns,

0:20and turns those columns into groupings.

0:22So for example, if we take this raw data here--

0:25and this is just a sampling of data out

0:27of the WideWorldImporters sample database--

0:29and we were a group on this first column here--

0:31color name--

0:32it would squish all of these together, just like so.

0:36That will then allow us to perform aggregate functions

0:38on the rest of the data per group.

0:41So for example, we could use the COUNT aggregate function

0:43to get a count of items in each group.

0:45For example, we have three black items, five blue items,

0:48two red items, one steel-gray item, and five white items.

0:52And that just happens to be exactly what

0:54this number of sales column is in the aggregated results.

0:57We could use the SUM aggregate function to total up

1:00the value of a column-- or columns

1:02or expression-- in each group.

1:04And that's actually what we're doing here with total sales,

1:06is this is quantity times unit price for each group

1:09and that value all totalled up.

1:12And average sales is doing the same thing,

1:13but instead of a sum, it's doing an average of quantity times

1:16price per group.

1:18Just a couple of notes about GROUP BY.

1:19Number one-- HAVING is, again, our filter

1:22for the group's data.

1:23Remember, the WHERE clause is our filter for our FROM clause.

1:26For the raw data, HAVING works on groups data.

1:29We'll look at some good examples there when

1:30we get into our lab Nugget.

1:32Also, if your grouped columns contain null values,

1:34all of the nulls will belong to the same group.

1:38So I'll get placed in the same group,

1:39and aggregates will work against any grouping with a null value.

1:43Finally, we have rollup, cube, and grouping sets.

1:45These are all extensions to GROUP BY,

1:47as you can see here in the syntax, that essentially allow

1:50us to add extra records to the end of each group for totals,

1:53subtotals, and grand totals.

1:55You can do it by group, across groups, or groups

1:58crossed with other groups.

1:59And we'll look at some good examples

2:00of those when we get into our hands-on lab

2:02Nugget on writing queries that aggregate data.

2:04How about an example?

2:06So here is the GROUP BY query that powers

2:09those aggregated results.

2:10And for reference, again, here's the raw data

2:12that it goes through to get to those aggregated results.

2:15So again, we're grouping by the color name, which

2:17is the first column, which is going

2:18to create the basis of all of these groups

2:21that we were looking at before.

2:23And anything that you group by, you can pull back

2:25in your Select list as is.

2:27But anything else that isn't in your group,

2:30you will need to perform an aggregate function against.

2:33So that's why everything else we're

2:35using an aggregate function on.

2:36We're doing a sum of the quantity times the unit price

2:40here-- just like we see.

2:41And we're doing an average of the quantity times the unit

2:44price here.

2:44So we have an expression that we're passing

2:46into these aggregate functions.

2:48And then we're just also grabbing

2:49a count of each grouping, which is where the number of sales

2:52comes into play.

2:53The most common aggregate functions

2:55that you'll see in everyday queries are these.

2:57We've talked about average sum and count.

2:59You also have MIN and MAX.

3:00MIN for the lowest value in a grouping,

3:02MAX for the highest value in a grouping.

3:04And also, notice here that you have all.

3:06This is, again, implied.

3:07And it's the default, which is why you

3:09don't see it specified in here.

3:10And that just means count every item in the group.

3:13We can also explicitly pass in DISTINCT to each one of these,

3:16and you would pass it in after your expression.

3:18And that would only count unique items within that grouping.

3:22So for example here, if we were to apply

3:24a DISTINCT to, say, our sum--

3:27our total sales here--

3:28then it would do this after our expression.

3:30So 4 times 13 is 52.

3:324 times 13 is 52.

3:338 times 13 is 104.

3:35So it would only count one of these 52's in that group.

3:39That's what DISTINCT is all about.

3:40Finally, let's say that we wanted to add a HAVING

3:42clause into this query.

3:44Let's say that we only wanted results

3:47where the average sales were greater than 100,

3:50which means these last two records wouldn't make the cut,

3:53because they're less than 100.

3:54Well, we would add that right after our GROUP BY.

3:57And remember, with HAVING, it still--

3:59in the logical order of operations--

4:01happens before SELECT.

4:02So we wouldn't be able to use the actual name

4:04of our alias column.

4:05We would need to use the expression itself.

4:09And that's that for the basics of grouping and aggregating

4:11data.

4:12Again, remember to join me in our hands-on lab Nugget titled,

4:14Writing Queries that Aggregate Data to see all this stuff

4:17in action.

4:18In this CBT Nugget, we covered the GROUP BY clause,

4:20which is used in conjunction with aggregate functions

4:23to summarize and aggregate data.

4:25I hope this has been informative for you,

4:27and I'd like to thank you for viewing.

0:00I've mentioned in previous Nuggets

0:01how a database server spends most of its time responding

0:04to read requests.

0:06And we as SQL Server developers spend a good majority

0:08of our time writing SELECT queries.

0:11But there are three other lesser used statements

0:13in that subset of SQL known as DML, the data manipulation

0:17language, that we use to modify data.

0:20We're talking about INSERT, UPDATE, and DELETE.

0:22And that's what we're going to cover in this Nugget.

0:24Let's jump right in.

0:26Let's begin with the INSERT statement.

0:27This is the statement that we use

0:29to add new records into our database tables.

0:32And you can see we have quite a few ways that we can use it.

0:35The first one we're going to look at here

0:36is it's most basic form, INSERT values.

0:39This is where we run an INSERT INTO, specify our table,

0:42specify our column order, and then values

0:45contain the actual data that we're going to insert in.

0:48And this part right here where we specify our column list

0:51is actually optional.

0:52If you don't specify this, then you

0:54need to have your values line up with the exact order

0:57that your columns are in the table.

0:59If you do specify your column names,

1:01you can omit columns or change the order.

1:04That way you can supply your values in any order

1:06that you like.

1:07Now this INSERT statement is actually

1:08inserting three records.

1:10There's record one, bronze.

1:11Two is crimson, and three is turquoise.

1:13If we were to execute this statement,

1:14it would add these three records into this Colors table.

1:17Now the three things you need to look out

1:19for when inserting data into a table

1:21are defaults, NULL values, and constraints.

1:25When you attempt to insert a record into a table

1:28and you do not supply a value for a column,

1:30just like we aren't here for ColorID, ValidFrom,

1:33and ValidTo.

1:34Notice we didn't supply a value for these

1:35in our INSERT statement.

1:36The first thing SQL Server is going to do

1:38is check to see if it has a default associated with it.

1:41ColorID is an autogenerated field.

1:44And these can come in two flavors.

1:45It can be marked as an identity column, will increment

1:49that column based on its seed.

1:51And identity is bound to a table.

1:53And there's also a sequence object,

1:55which is another autogenerated object.

1:57Only it doesn't live with the table,

1:59it lives outside of the table.

2:00So it's global.

2:02But in either of those cases the default

2:05will be picked up and applied by SQL Server.

2:08So that's why we didn't need to supply a value for ColorID.

2:11It grabbed those values automatically based on,

2:13in this case, a sequence object.

2:15And by the way, ValidFrom and ValidTo

2:17also have default values associated with them

2:20through temporal tables.

2:21Now another thing, if a column doesn't have a default

2:25value associated with it but it is allowed nullables,

2:28then SQL Server will put a NULL value into that field.

2:31And if it doesn't allow NULLS, well, then you'll

2:33get an error with your INSERT statement.

2:35The other thing you got to look out for, again, is constraints.

2:37And constraints can come in many flavors.

2:39But essentially they ensure that a column

2:42lives by a set of rules.

2:44And that could be a check constraint.

2:46This column must have this format of data

2:48or this range of data.

2:50Or it could be a foreign key constraint.

2:52This column must contain a value that exists in another table,

2:56and therefore must respect that relationship.

2:58This LastEditedBy is a good example of that.

3:02This is actually a foreign key that

3:05references a primary key in the Person table.

3:07That way this table can keep track

3:09of the individual that either entered or modified

3:13this record.

3:13So that's why we needed to supply that here,

3:16because if we didn't, it doesn't accept NULL values,

3:19and it has a foreign key constraint,

3:21again referencing the primary key inside of that person

3:24table.

3:24Next up we have one of my favorite ways

3:26to run INSERT statements into tables to quickly load them up

3:29with information, is INSERT SELECT.

3:31This is where we write a SELECT query and take the results

3:35and just slam it all in to a table.

3:38So we still form our insert statement the same way.

3:40We specify our table, specify our columns and their order.

3:43And then we make sure that we write our SELECT query so it

3:46lines up, those columns line up, and the results

3:49of that SELECT will get shoved in to that table.

3:51Finally we have INSERT EXEC.

3:53And this is where we take the results of a stored procedure

3:56and again, pipe them right into an INSERT statement.

4:00Next up we have the UPDATE statement.

4:01This is what we use to modify existing records.

4:05So we have UPDATE followed by our table name

4:07and then set followed by the columns that we want to modify.

4:10If we have more than one column like we do in our example here,

4:13we separate them by a column.

4:15Also notice that we can have WHERE clauses

4:18here for update statements.

4:19And this is something that you want

4:20to pay close attention to when you're dealing with update

4:23and deleting.

4:24Because if you're going to delete a range of data,

4:26you want to make sure that your WHERE clause is accurate

4:29so you intend exactly what you intend to target.

4:32When we get into our virtual lab for modifying data,

4:35I'll show you a great way to test out UPDATE statements

4:37to see what they would hit before you actually run it

4:40in a production environment.

4:42So if we were to look at this example,

4:43here's our table before the statement is ran.

4:48We are going to reduce the quantity on hand, this column

4:50right here, by 30%.

4:52We do that by multiplying it by itself,

4:55which is what the multiply equal sign will do,

4:58and then by 0.7, which will give us

5:01the 30% reduction in quantity on hand.

5:03And that's how we go from this value to this value.

5:06And we're doing the same thing with reorder level

5:08except we're only going to reduce this one by 10%, which

5:11is why we multiply it by itself and then by 0.9, which will

5:15give us a 10% in reduction.

5:17So here is the after table.

5:20Now this is all dealing with a single table.

5:23What if we wanted to update data in one table

5:26based on information in another table?

5:28Well that's where we bring our join skills into the mix.

5:32This way we can update columns in one table

5:34but create a WHERE clause that spans many tables, exactly

5:38like we're doing here.

5:40Here, rather than update our QuantityOnHand and ReorderLevel

5:43based on this bin location, we're

5:45actually going to do it based on the StockGroupID.

5:48In this case think of that like the category of the item.

5:50And StockItemGroupID of 3 is mugs.

5:52So we're only going to update the QuantityOnHand

5:54and the ReorderLevel for strictly mug items.

5:58And I know this can look a little funky at first,

6:00but I like to write them.

6:02First write a SELECT statement.

6:03Because everything below, from and below,

6:06is the exact same, no matter if it's an UPDATE, a DELETE,

6:09or a SELECT statement.

6:10So write your SELECT statement so

6:12that you can see all your data, and then rip out the SELECT

6:15and replace it with an UPDATE and just specify

6:18either the table name that you want to update or the alias

6:22that you give it like we are here.

6:23And everything else will work just like it does over here,

6:26except that, again, we can create a WHERE clause that

6:29makes decisions based on rows and columns in other tables.

6:32Our final statement is for removing records,

6:34and this is done with the DELETE statement, probably the easiest

6:37of all of them because we don't have to worry about columns,

6:39we're just deleting records.

6:41So we specify DELETE followed by our table name,

6:44and then again very important that we

6:46don't forget our WHERE clause when it comes to DELETE,

6:48so we don't accidentally delete more than we intend to.

6:52And both of these statements do the same thing by the way.

6:54One of them is sargable.

6:55One of them is not sargable.

6:57But both of them will remove any data that is 2014 and prior.

7:01So everything above 6 here, this will be the final result.

7:05And also, you can delete with JOIN statements as well.

7:08Again, it works the same exact way here.

7:10Just remember to write your SELECT statement,

7:13remove SELECT, and replace it with the DELETE and the table

7:16or alias name that you intend to delete those records from.

7:20Now the last thing we're going to cover is the OUTPUT clause.

7:22This is something that we can attach before the FROM clause

7:26to any of our data modification DML statements,

7:29INSERT, UPDATE, and DELETE.

7:31And what this will do is output any records

7:32that were affected by that operation.

7:35This is extremely useful when you

7:37want to do something else with that data,

7:38maybe confirm it before somebody deletes it

7:40or archive it or audit it.

7:43So putting an output statement here will then, rather than

7:46just say this operation completed,

7:48these are the number of rows affected,

7:49will actually output those rows that

7:51were affected so we can do something with them.

7:53Now how this works is whenever we run a DELETE statement,

7:55like this one here-- this is the one we're just looking

7:57at in our previous slide--

7:58it will create a virtual table called DELETED.

8:01And by outputting DELETED.*, we're saying give us all

8:03of the columns from that DELETED table in the output.

8:07And again, we could then do something with this data,

8:10maybe pipe it to an audit table or a history

8:12or an archive table for storage, so that way

8:14we have a record of everything that

8:16was deleted from this table.

8:18The other virtual table is the INSERTED table.

8:20And we can access this in every way we run an UPDATE statement.

8:23Because an UPDATE is really a DELETE followed by INSERT.

8:28So by running OUTPUT here, when we run an UPDATE statement,

8:31we can look at the deleted table to see

8:33how it looked before that update occurred

8:36and the INSERTED table to see how it looked

8:38after that update occurred.

8:40So these are from the DELETED table.

8:43This is the old data.

8:44And this is from the INSERTED table.

8:47And that's that.

8:48Join me in our hands on lab Nugget

8:49titled "Writing queries that modify

8:51data" to see INSERT, UPDATE, DELETE, and OUTPUT in action.

8:55In this CBT Nugget we covered our three statements

8:58used to modify data with Transact SQL,

9:01as well as how to track those modifications

9:03with the OUTPUT clause.

9:04I hope this has been informative for you and I'd like to thank

9:06you for viewing. .

0:00In the hands on lab Nugget, we're

0:01going to learn how to write queries that modify

0:03data, and look at the many ways that we can do so

0:06within each modification statement.

0:08Let's fire up the virtual lab and get started.

0:10Once you've logged into our SQL server and the desktop loads

0:13up, let's get right into SQL Server Management Studio

0:15with a right click and launch in to our 70-761 Scripts Solution.

0:20And in Management Studio, we're going to be working with

0:22our project here, 08 Modification Queries,

0:25and we're start with the INSERT statement.

0:27So let's go ahead and fire that up.

0:28Let's also get a connection over here to Object Explorer.

0:30So I'll just hit that button on the toolbar there, the shortcut

0:33button, and hit connect.

0:35Let's also push everything aside for now.

0:38Let's start with insert value.

0:40Now, insert value is good for those one

0:42off situations, where you just need

0:43to get a record or a handful of records into a table,

0:46and also for scripting.

0:47This is actually what Management Studio

0:49will generate when you script out a table and it's data.

0:51I'll show you that here in a little bit.

0:53Now, whenever you write these statements,

0:55again, we need to respect the columns, that data

0:58types, as well as their nullability,

1:00as well as constraints.

1:02So it's always good to get familiar with the table and all

1:04those aspects.

1:05Let me show you an easy way to do that.

1:07If we head into Object Explorer and expand databases,

1:09and expand wide world importers, and expand tables,

1:12we'll eventually get to the table

1:14that we're looking for here, Application.People.

1:16We're going to enter in some people records here.

1:18If we right click on that, head down to script table,

1:20and head over it CREATE To, you can create this

1:22to a file, a clipboard.

1:23Let's just use new query window.

1:25It'll just pop open a new query window

1:26and pop in the definition of this table.

1:29And now, we can see the columns in here,

1:31as well as their definitions, their data types,

1:33If they're nullable, if they have--

1:35if they're a calculated column, like search name

1:37here is a calculated column.

1:39And also, if we scroll down, any constraints that

1:42are associated with them.

1:43So we can see we have a constraint on person ID.

1:45It actually gets its value from a sequence option.

1:48We also have a check constraint, which

1:49is a foreign key constraint on that last edited by.

1:53So we must put something in that last edited

1:55by column that references itself,

1:58so a person record that already exists.

2:01That's about it.

2:02All the rest of these down here are just

2:04extended properties that are added to the object.

2:06So those are the things that we need to respect, as well

2:08as nullability.

2:09And if we close out of this, those are pretty much

2:11all the things we have here.

2:13So we need to enter a full name, that's required.

2:15And again, you hold your mouse here over any of these columns,

2:17you can see their data type, as well as their nullability.

2:20So we need to provide a full name,

2:21we need to provide a preferred name, and then

2:23all these bit columns as well.

2:25And finally, last edited by, which

2:27must be an ID of a person that already exists in this table.

2:31So let's go ahead and highlight this first block

2:33of code, which will create a new record here

2:35for Kylo Ren in the Application.People table.

2:38Now, I also mentioned how we can supply multiple records

2:42in one insert value statement.

2:44In here, all we do is comma delimit each record.

2:47So we're adding three records in this case, to the table.

2:50If we highlight all this and hit execute,

2:52all three of those rows will go into the table.

2:54Now, if you want to see how this data looks

2:56inside the Application.People table,

2:58we can write a quick SELECT statement.

3:00Or I'll show you the easy way here.

3:01Let's just go into Object Explorer, right click,

3:03let's have it right that's SQL statement for us.

3:06And it's only to return the top 1,000, but no problem.

3:09We'll go to the bottom here and put an ORDER BY

3:11about person ID descending, re-execute it,

3:15and there is the four records we just added.

3:18See all the defaults that got filled in there too from things

3:20that we did not enter, like the person ID.

3:23Again, this is generated from a sequence object that

3:26lives outside of the table.

3:28So there it is, all four of those records

3:30entered in using insert value.

3:33Next up, we're going to look at INSERT SELECT.

3:35Now, INSERT SELECT is easily the most popular and useful

3:39of all of these inserts statements, just simply because

3:42of its utility.

3:43I find myself using this whenever

3:45migrating data across databases, or flattening

3:48data out between database types, or just

3:51transforming or formatting data into different output.

3:55Here's an example of that.

3:56Let's say that we wanted to take information out

3:59of the customers table and sort of flatten it out

4:02into one view.

4:03Maybe we're creating a query for the marketing department

4:05because they want to send holiday cards out

4:07to all of our real customers.

4:10So what we need to do then, is combine a bunch of tables

4:12together to get rid of these IDs and pull out the actual city

4:15name, the state name, their delivery method, their address,

4:18so on and so forth.

4:19And then we can take this and stuff it

4:21into a table, if we already have one out there,

4:23or we can create a brand new table

4:24and then put it in there as well.

4:26And I'll also show you SELECT INTO,

4:28which is a wonderful statement that we

4:29can use to take the results of a query

4:31and create a brand new table and put data into it.

4:34But here, we already have an existing table

4:36called customer stuff.

4:37And if we look at this table, you'll

4:38notice here that we have first name, last name, city name,

4:42state name, all this information broken out and flattened out.

4:45Where if we look at our customer's table

4:47here, which is down here under sales dot customers

4:50and we expand this, notice here, that we have just customer

4:54name.

4:54So it's not actually first name and last name,

4:56so we're going to use some string parsing skills

4:59to make this out.

5:00In fact, let's take a quick peek at the data

5:02here just to show you what I mean.

5:04So here, we have customers name.

5:05We also have people that aren't actual customers, but offices

5:08in here.

5:08So we're going to need to filter those out.

5:10And that should be pretty easy to do,

5:11because anybody that's an actual customer,

5:13not like an office or a branch office

5:15or whatever, has a null buying group ID.

5:18So right there, that's going to be something

5:20that we need to take into account for this query.

5:22But also, notice here our customer name

5:24is just one big name.

5:25So we're going to need, again, some string parsing skills here

5:28to break out our first name into its own column, last name

5:30into its own column.

5:31And then we have a bunch of IDs referencing

5:33other tables for their city, for their state, all of that stuff.

5:36So we're going to break all of that down as well.

5:39So that's why we have this one big query here

5:41that does exactly that.

5:42Pull out the customer ID, we slice up

5:45using the left combined with CHARINDEX

5:47here to get their first name.

5:49And that's going to detect the space here and then

5:51pull everything back before that space as the first name.

5:53And then SUBSTRING with CHARINDEX

5:55to pull everything after the space into their last name.

5:58And then we pull out the city name,

5:59because we have a join here on cities, state,

6:02delivery method, so on and so forth.

6:04And here's the result. If we just highlight this query,

6:06here's all the data.

6:07But we want to put that data somewhere,

6:09and that's where the insert statement comes into play.

6:12And as long as you design your select queries so they line up

6:16with the columns in your insert, you

6:17do not need to specify your column list, which

6:20is why we're not specifying here with our insert statement.

6:23Because all of these line up exactly.

6:26And by exactly, I mean data types or data types

6:29must line up.

6:30So if we highlight all of this and hit execute,

6:32all that data is going to go right into that customer stuff

6:35table.

6:36And if we head in Object Explorer here,

6:37we can take a peek at that data.

6:39So we'll right click on customer stuff,

6:41look at the top 1000 rows, and there it

6:43is, all flattened out using an INSERT SELECT.

6:47Now, I want it to show you a SELECT INTO.

6:51This is another way to do it.

6:52Only this time, rather than needing that table to exist,

6:56we'll create it on the fly.

6:58So this is the same query.

6:59The only difference here is we have an INTO right here.

7:02So this will automatically create this table

7:04and generate the columns, create the columns inside

7:07of that table based on the results of this query

7:11and their data types.

7:12So this will essentially give us the exact same results

7:15as we just did.

7:16A table with this data from this query inside of it,

7:18it's just going to create that table for us.

7:20So now, there's a table called CustomerStuff_v2

7:24in Object Explorer.

7:25We can see this if we right click and refresh our tables,

7:28there it is.

7:29Pretty cool.

7:30So that's another really useful trick there

7:31of getting information into the table.

7:33Another one I want to show you here is INSERT EXEC.

7:35This is, again, how we can take the results

7:37of a stored procedure and put it inside of a table.

7:40So I created a very simple stored procedure called

7:42Warehouse.SearchforCustomers.

7:45So all you need to do is pass in a string here

7:47and it will search for customers.

7:49And you can also limit the number of results.

7:51And we'll look more at stored procedures down the road.

7:53But if you want to take a look at the definition

7:55here, in Object Explorer, if you expand programmability, expand

7:57stored procedures, there's quite a few of them

7:59here in the wide world importer's database.

8:01Here's one that I threw together.

8:02If you right click and choose Modify,

8:04it will actually show us the definition here.

8:06And there it is.

8:07So it's essentially the same query

8:09we were just looking at, just with the WHERE clause on it

8:12that we can parametrize and pass into here

8:14through this stored procedure.

8:15So you can execute the stored procedure all by itself.

8:18Just highlight it, hit execute, pass

8:20whatever values you want in here and a limit to how many records

8:22you want returned, and it'll give us those rows.

8:25And again, we can then take the results of this

8:27and use it as the basis for an insert statement.

8:31And once again, here, we're not we're not actually specifying

8:34our column names, because they line up,

8:35since it was the same query that we were just working with.

8:38And by the way, to make this work,

8:41you'll need to delete the data out of our previous table.

8:43Because watch this--

8:44If we try to execute this right now,

8:46it's going to say no, violation of primary key.

8:48Because we have a customer ID and they're already in there

8:51from our previous query.

8:52And a quick and easy way to do that

8:54is to execute a TRUNCATE table and specify our table

8:58name, which is customer stuff.

9:00There we go, we can highlight that, hit execute,

9:02that will dump all the records out of that table.

9:04Now we can highlight this and those 20 records

9:07will go into that table.

9:08TRUNCATE, by the way, is very similar

9:10to the DELETE statement, in that it removes records

9:13from a table, but very different in how it does it.

9:15TRUNCATE is a non logged operation,

9:17so it doesn't fill up the transaction log

9:19with all of those deleted entries.

9:20Which also means that data is not

9:22recoverable via the transaction log.

9:24More on that in future SQL courses.

9:26Now, one more thing I want to show you

9:28with the INSERT statement is we can

9:29have Management Studio generate the skeleton

9:32of an INSERT statement for us.

9:33If we head into Object Explorer, pick any table here.

9:36I'm just going to choose our customers stuff table.

9:38Right click, choose script table as,

9:40and we can head down here to INSERT and generate

9:42a new query window.

9:43It will write that insert statement for us

9:47and then fill out the values with placeholders

9:49and their data types here.

9:50So this is a great way to get a good start

9:52on writing insert statements, insert values specifically

9:56here, through Management Studio.

9:59Let's move on here and let's talk about update statements.

10:02So let's bring back Solution Explorer and open up 02 UPDATE.

10:06So the update statement allows us to modify

10:09existing records in a table.

10:11After update, we specify our target table.

10:13Then, we use a set to specify the columns and the values

10:17that we want to set those columns to,

10:19comma separated if we want to apply it to multiple columns.

10:22And again, don't forget your WHERE clause.

10:24This right here will update every single record

10:27in the table, and reduce quantity on hand by 30%,

10:30and reduce the reorder level by 10%.

10:33Now, let me show you a great little trick

10:35that you can use to test your update

10:37statements before actually running them

10:39in a production environment.

10:40And we're going to talk about transactions a little later.

10:42But if we wrap our statements with a begin trend and then a

10:47rollback, this will actually encapsulate

10:50the statement inside of a transaction and roll it back.

10:53And what I like to do here, is actually

10:54place select statements in here.

10:56So we can do a select star from warehouse, that stock item

10:59holdings, and we'll do it again at the bottom.

11:02So we basically are wrapping all of this inside

11:05of a transaction.

11:06We're going to look at the data, execute the statement,

11:09and then look at the data again.

11:10And this is just a great way to do it.

11:12Because since the transaction will get rolled back,

11:14none of this will have actually happened.

11:17So if we push the window up a little bit here,

11:19here's the data before, here's the data after,

11:22and since none of that happened, the data is still right here.

11:25So if we look at our first record, 175609

11:27and we just execute the SELECT statement here,

11:31look at that, 175609.

11:33So even though it happened, it didn't happen, thanks

11:36to wrapping that into a transaction

11:38and rolling it back.

11:39Now, we can also write our update statements using a join.

11:42And again, this is really useful when

11:44you want to update columns in one table,

11:46but use columns and other tables in your WHERE clause.

11:50So that way, you can target specific data.

11:53So if we take our previous UPDATE statement

11:55in here, which targeted everything in the stock item

11:57holding table, and say we only want

11:59it to target the quantity and reorder level for mugs.

12:02Well, we'll need to hit the stock group ID--

12:05three is mugs, by the way--

12:07which means we'll need to join this to that table

12:09so we can write a WHERE clause that hits that.

12:11Again, an easy way to do this, if you

12:13want to see what data is targeted

12:14is just write a select statement with your joins

12:17and then take a peek at that.

12:19So here is the data that would be affected.

12:21We can verify that these are indeed quantities for mugs.

12:23And then we can just replace that select with an update.

12:27And again, your update can target

12:28if you have an alias, the alias, as well as the table name.

12:32And then you just specify your columns and their values.

12:35So this time, we will run this.

12:36We can, again, wrap it around in a transaction

12:38if you want to see what it would do without actually doing it.

12:41But there it is, all of those records were updated.

12:44And finally, here we can use update with output.

12:46So we can see the data that was actually affected.

12:50And again, output is extremely useful

12:52when you want to audit the information that you're

12:55changing.

12:56And using output here with update,

12:58we can both look at the deleted virtual table

13:00to see how the data looked before the update happened

13:04and insert it to see how the data looked after it updated.

13:07And we can pull out specific columns as well.

13:09I'll show you another cool trick that we can use with the output

13:12statement to actually do something with that data when

13:14we get to the DELETE statement.

13:15But first, let's change these values.

13:17Let's say, you know what, mugs, they're just not selling well.

13:20So we're going to reduce the quantity and the reorder level

13:22by another 50%.

13:24Let's highlight all that, execute it,

13:27and now, our output here will be both record sets.

13:30So first, here on the left hand side, is going to be deleted.

13:34So this is our deleted right here, all of that

13:37is from the deleted table.

13:38So this is what it looked like before.

13:40Notice our quantity on hand here and then notice

13:42our reorder level, there it is.

13:44And now, if we scroll over, this is from that inserted table.

13:48Notice, quantity on hand is less and your reorder level is also

13:52less than it was before by 50%.

13:55So left here, deleted, right, inserted.

13:58So you can see the data, how it looked before and after

14:01that update occurred.

14:02One more thing.

14:02With update, you can actually script out the skeleton

14:05of an update statement as well.

14:06By right clicking on any table here, go to script table

14:09as, choose update, and pick your output there.

14:13And here it is.

14:14This will actually generate the update statement

14:16for every single column, and even place a WHERE clause there

14:20for you, just to remind you.

14:21Don't forget about your WHERE clause when you update records.

14:24Let's take a look at our last data modification statement

14:27here, which is the DELETE statement.

14:29Again, DELETE used to remove records from our tables.

14:32So DELETE from specify your table name,

14:35and then don't forget your WHERE clause.

14:37And again, you can wrap these in a transaction

14:39as well to see what records would actually

14:42get deleted from your table.

14:44We can use joins here as well.

14:46And again when you're building these statements update,

14:50or delete with joins, just write a select first

14:53to see what they would effect.

14:55Verify that data, make sure it looks good,

14:58make sure the structure of your SQL statement is good,

15:00and then you can just replace your select with your update,

15:02or in this case, your delete and target

15:04your table name or your alias.

15:06And by the way, this will actually delete anything

15:09from the order lines table, which are the individual line

15:12items that belong to an order that belong to stock group

15:15ID four, which is shirts.

15:16So this would get rid of all the orders containing shirts.

15:19And finally, again, we can use the output clause here

15:22with delete.

15:23The deleted virtual table will contain

15:25any records that were affected by your DELETE statement.

15:29And again, the asterisk here means return all columns,

15:32output all columns.

15:32You can also comma delimit to pull out specific columns.

15:35And what I wanted to show you here

15:37is that you can actually use the INTO statement here and specify

15:41your table.

15:42And what that will then do is output all that data

15:46into your table.

15:47And this table does need to exist.

15:49So actually, let me show you a cool way to make this.

15:52Let's turn this into a select into.

15:55So we'll do an ol dot star, grab everything

15:57from that order lines table, we'll select this into--

16:00how about we just call this one sales dot order lines

16:03underscore back.

16:05There we go.

16:05And then we don't want any data in there, so let's

16:07just put a bum stock group item ID in there.

16:10And what this will do is generate that table

16:12with nothing in it.

16:14And now, we can just undo all the way back here.

16:17And then, we'll throw in into sales

16:20dot orderlines underscore back, just like so.

16:23And now, all those deleted records

16:25will go into this table.

16:26And this just says invalid object name,

16:28because our IntelliSense hasn't caught up here.

16:31If you actually hit edit, head down here to IntelliSense

16:34and refresh the local cache, now it knows that table exists.

16:38And there we go, now it can highlight all this.