Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
So we have this very simple SQL query, selecting these three columns from the SalesLT.Customer table in the AdventureWorksLT database. If I execute that, it's going to bring back the entire contents of that table, which is 847 rows. But I can either see by scanning through it or down here in the status bar, 847 rows. But of course, if I don't want all those rows available, if I want to filter that information, it's a very common thing. So, the way that I do it is by using the WHERE keyword.
I only want to bring back that data where people have a certain last name or have a certain email address or their customer ID is in a particular range. Now it all depends on what kind of data that you're filtering on. So, for example, if I wanted to bring back information where the LastName of the person was equal to Vargas, well, if it's text I need to write it in quotes. Another single quote again here. If I execute that, I get in this case four rows coming back. It looks like my table may even have some duplicate data that I will need to look at a little later.
You'll find that this equality is case insensitive. That is based on the collation all the database. So whether it's lowercase v or uppercase V, it doesn't matter. But I do need the quotes. After all if I was missing the quotes, the SQL Server database is trying to imagine that this is probably a column. I try and execute it. It says I have no idea what this is meant to be. So if it's text, it's in the single quotes. If however it is a number, so for example this table has a CustomerID, which is numeric, it's just an integer, I could say CustomerID = 600.
I don't want the quotes here and I am now retrieving that piece of data. Now you notice that I can put a WHERE clause here based on a column that I'm not retrieving in my SELECT part of the statement, which is perfectly acceptable. I can either be bringing back CustomerID or not. it doesn't matter. I can still filter on it. Now just as we have the equal sign, we could always say WHERE CustomerID > 600 and execute that. 466 rows returned.
Well, surprisingly again, we have the less than sign, less than and equal to, all the usual suspects. We can also describe a range. I could say WHERE CustomerID >600 and it's >800 and the way that I would write is using the keyword AND actually written out here. AND CustomerID < 800. Execute that. We get 59 rows returned.
We also have a slightly more readable way of doing it where I could actually say WHERE CustomerID between 600 and 800. However, this one is considered an inclusive statement, meaning that if I was actually retrieving the CustomerID as part of my statement, just to take a look it at, we can see that when it says between 600 and 800. It's like a greater than or equal to. So we are retrieving 600 there rather than a greater than sign, which would skip the 600.
You can also do this with textual or character fields. Although in this case because we have no one exactly matching A and C, we are just bringing back all the B LastNames. If you wanted to check for multiple values where you're interested in several different LastNames, for example, we could of course say where a LastName = Smith or LastName = Bright or a LastName = Bremer.
Just like having the AND we can have an OR statement. But we can also use the keyword IN to describe a range. Because I'm checking for a text value, I do need to surround each independent value with the single quotes and just separate them by commas. Now getting a little bit more flexible with this, let's say for example that we have been retrieving some information including the CompanyName.
And when we are scanning things, we are finding several companies with very similar names, like Metro for example. Well, what I could ask is to bring back this information where the CompanyName begins with the word Metro. The way that I do this is I actually use the keyword LIKE. I type in the text that I want to find and for whatever else it could be, the wildcard is the percent sign.
In a lot of languages or other ways of doing this, this might be represented with an asterisk, but in SQL it's the percent sign and we use LIKE. So this should bring me back anything where the company name begins with Metro, anything else. We click execute, and oops! Unfortunately, what I've got to be careful with this is I had Metro actually highlighted. That means it was just trying to execute that word. I do need to make sure that I haven't got any particular piece of text selected there. And there we go. We are bringing back 14 rows with several different places, Metro Cycle Shop, Metropolitan Bicycle Supply, Metro Manufacturing.
Again, like any text operation this is case insensitive. So it doesn't matter whether it's an uppercase M or lowercase m. You can also put the % sign at the beginning of the word, and in this case this would do a wildcard search for anything in the company name column that had etro anywhere in that string. I execute that and we are also getting back Petroleum Products Distributors. If however, you're just looking for a single letter, you can represent that with the underscore in your LIKE statement, which in this case brings back the same information, but is a slightly more specific way to do it.
Do you bear in mind that if you're doing lots of wildcard searches, this can be very inefficient, particularly on large databases if it has to scan the tables and go through every column row by row figuring out if these letters occur anywhere there. Now depending on the data that you are retrieving, sometimes you will get a value and sometimes you won't. If I retrieve the MiddleName, for example, I can scan the data returned and see that several of them have NULL being returned.
This is also slightly highlighted to indicate it's not word null. It is a NULL value. There's nothing here. Well, what if I wanted to only retrieve those rows where that value was NULL? Well, I might be tempted to say WHERE MiddleName = NULL and we try and execute that and nothing comes back. Because that's not the word that we use when we are checking for the existence of NULL. What we want is the word IS where MiddleName IS NULL, not = NULL, because a quality is a value.
This is the lack of a value. So, now I'm retrieving the 343 rows where MiddleName is NULL. Conversely, if I'm just interested in the fact where it's not NULL, well, we just say IS NOT NULL and execute that. And now, we only have the rows where the MiddleName is not NULL and has a value. You can of course get a little deeper than this on your conditions and we'll see how to connect values to other tables and what's going on in other parts of the database.
But with what we've just seen, that'll get you writing most of the conditions in at least your basic SQL statements.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.