The Anatomy of a SELECT statement

In this video segment, we’re going to discuss the anatomy of a SELECT statement. We’ll begin by breaking down the individual keywords or statements that comprise our query, in this case, the SELECT, FROM, WHERE, and ORDER BY statements. The SELECT statement is used to identify which fields that need to be returned, which fields or columns that contain the information of the data that you want to see.

Now we need to identify where that data is coming from. That’s where we use the FROM statement, when we can specify a table and/or a view. We also have the WHERE statement. The WHERE statement is actually used to filter out our records. And finally, how do we want to sort our query? In what order do we want to return those records?

So let’s take a look at an actual SELECT statement. In this example, we are looking at how to return or select the first name and last name column. You see that each of the columns are separated by a comma. So here I’m only returning two fields or two columns, but I could have more than one column. I can have multiple columns from that specific table. I just need to make sure I separate them by commas.

Then the FROM statement is telling me from where these columns are located, or where are these columns located. In this case, the Person.Person table. Now in segment 4 of this video series, we’ll break down a little bit more of the naming convention of our tables.

Next we have the WHERE clause. The WHERE clause is used to filter out the records. We could have a table with hundreds or thousands of records, but we don’t want to return all the records all the time. So we can use the WHERE statement to filter out those records. Now in the video segments 6, 7 and 8, we’ll talk about different ways of filtering those records, either by numeric values, character values or by dates.

And then finally, ORDER BY, how to sort those records. How do I want to retrieve the records in a specific order? Again, in video segments 9, 10 and 11, we’ll talk about different ways of sorting our records.

So let’s take a look at actually writing this code. Here I’ve opened up our SQL server, and in SQL server is where we’re going to write our query. Now we are using the AdventureWorks database for this video series for our demonstrations. So we’re going to start off with writing our SELECT statement by actually typing in the word “select.” S-E-L-E-C-T. Now I could specify the specific fields that I want. I’m going to start off with a wild card, or more specifically, the asterisk on my keyword, and this is going to retrieve all the fields from the table that I’ll specify in a second, if I actually want to see all of the columns or fields from a table.

The next thing we can do is type in our FROM statement. From which table do we want to retrieve this data? Now I can write everything, I can write the entire SELECT statement all in one line, but for readability purposes, we typically see that the keywords are lined up on the left so that I can fit it into a single screen and I can read it vertically instead of having to read it horizontally. So we want to select, asterisk, again, the asterisk is going to retrieve all of our fields, from a specific table. In this case, we’re going to retrieve the Person.Person table. This naming convention is actually the schema.tablename.

So now that we’ve written our SELECT statement, now let’s execute our query. So if we execute our query, you can see that all the records in that table have been returned. We actually have 19,972 records or rows that were returned. But you can also see that all the fields, since I specified an asterisk, we can see that all the fields were returned, such as Business Entity ID, normally known as an employer ID. We have the person type, whether they’re an employee or a customer. We see first name, middle name, last name. So we can see the different fields there.

But if I just want to focus on specific fields, this WHERE, instead of using the asterisk, I’m going to type in just a specific field, in this case firstname, firstname. And you can see as I start typing that my IntelliSense, if I have my IntelliSense turned on, it’s this button right here, that’s turned on. You see it’s depressed. But with that IntelliSense turned on and since I have actually selected that I want to retrieve this data from a table, we have the IntelliSense that actually kind of specifies the fields that are in that table, or tables, if we’re using multiple tables.

So as I type in, I can actually select that firstname field. So I’ll just tab over and it’ll fill in that information for me. I got some more IntelliSense there telling me from where the table is coming from. So once I have that field, I’ll go ahead and select lastname, and you see as I’m using the IntelliSense again, it has the last value function, but it also has the lastname field. So let’s actually type that correctly, lastname, and again, I can hit my tab to fill in the rest of that field.

Now if I specified another comma, I can add in even another field, maybe middle name or person type, something like that. Now if I try to run the code right now, you can see that there’s a red squiggly line under the FROM keyword. When you see that red squiggly line, that’s telling you you have a syntax error or you’ve typed something incorrectly. In my case, I have an extra comma in the SELECT list. That extra comma is expecting another field. I don’t want another field, I just want the firstname and lastname field. So I want to go back and make sure I remove that last comma, and you see the red squiggly line, the red squiggly line under the FROM statement, has disappeared. Now my syntax is correct.

So now, when I execute this code, I’ll execute, execute one more time, and you see I just have the first name and last name field. Again, still 19,972 rows. That’s how many people I have because I’ve not filtered out those rows.

So to wrap up this video segment, we’ve been discussing the anatomy of a SELECT statement. We’ve specifically discussed write a SELECT statement using the SELECT, FROM, WHERE, ORDER BY keywords. So thank you for watching this video segment.