Lecture 14--Create a SELECT statement
- [Instructor] When it's time to start retrieving information that's stored in your tables, you'll turn to an activity called querying the database. In order to query the database, you'll need to issue commands written in a language called Transact-SQL, or T-SQL if you prefer a shorter name. To get our first look at T-SQL, I'm going to drill down into a database called WideWorldImporters that we loaded in the last chapter. We'll come into the Tables folder, and I'm going to find a table here called Sales.Orders. Let's go ahead and right-click on it, and choose Select Top 1000 Rows. On the top of the query window, we have the exact syntax of the query, and down in the bottom we have the results.
Let's focus on the query here at the top. It begins with the keyword SELECT. The SELECT clause tells the database what columns are going to be returned. In this case, we're returning the OrderID, the CustomerID, the SalespersonID, and all these other columns, and you can see that in the results down here. Then, if we scroll down through our syntax a little bit, we'll get to the next clause, which is the FROM clause. The FROM clause is where we state the table that we want to pull information from. In this case we're pulling information from the Sales.Orders table from the WideWorldImporters database.
So this is the basics of a SELECT query. Let's go ahead and use this syntax as a model, and we'll see how we can write our own queries. Let's go ahead and close this tab and we'll start a new query by pressing the New Query button. I'll start by typing in the keyword SELECT. Transact-SQL is case-insensitive, so it really doesn't matter if you use all caps or not, like I am, but the general convention that's used by nearly everyone that writes queries is that these keywords are written in all capital letters. It simply makes them easier to read this way. Next we'll write out the names of the columns we want to see from one of our tables.
I'm going to select the OrderID, CustomerID, and OrderDate columns. Notice that as I type SQL Server is prompting me with some suggestions. This is called IntelliSense, and it can help speed up the process when writing queries. If you see the item on a list that you're after, just press the down-arrow key to select it, and press Enter to accept that suggestion. At this point, though, SQL Server doesn't yet know which table I'm going to pull columns from, so it can't make the appropriate suggestions.
Also, you'll notice that the column names get this red underline. That indicates that there's a potential problem with my entry, but that'll get resolved here in just a moment. Because none of these names include spaces, I technically don't need to enclose them in square brackets like we saw with the previous query. Just separate the names of each column with a comma. Then, on the next line, I'll define the table where these columns are going to be found. I'll type in FROM Sales.Orders. Up on the SQL Editor toolbar is a dropdown menu where we can specify the target database.
Right now, it's set to WideWorldImporters for me. Any queries that we run will happen against this database, so we don't have to specify it again in the FROM line of our query. When you're ready, press the Execute button to get the results. On the bottom, in the results section, we'll see the three columns that we asked for: OrderID, CustomerID, and OrderDate. Now because we didn't specify to limit the results to just the top 1,000 records, we'll get back a row for each record in the database. On the bottom right-hand side of the screen, in this yellow section, we can see that the full table includes 73,595 rows.
Right now, I'm seeing orders from 2013 at the top of these results windows, and if I scroll down through the list, we'll see that they are in an ascending order, so they get higher as we scroll down. This will put the most current orders at the bottom of this very long list. I can reverse the order by modifying our query a bit. Let's go back up here to the top. On the next line, I'll type in ORDER BY OrderDate, and I'll specify that I want it to go in reverse order, so I'll type in DESC, which is the abbreviation for descending.
This will instruct SQL Server how I want to view the results. In this case, I'll go ahead and execute them, and I'll see the most recent orders at the top, so this one here, from May 31, 2016. Finally, we can also use a query to filter the records that are being returned. Instead of viewing all 73,000 orders, what if I wanted to just view the orders placed by the customer with the ID of 578? I can add something called a WHERE clause to define a filtering criteria. Right below the FROM line, we'll come up here to the top, and I'll create a new line, and I'll type in WHERE CustomerID = 578.
Let's actually get the query again to get those results, and now we can see, if I take a look down here in the bottom, that I have 108 rows that represent the 108 records placed by this single customer. Because they're in a descending order based off of their order date, I can see that the most recent order was May 26, 2016. So those are some of the ways that you can begin to pull information out of the database. The SELECT clause defines the columns that you'd like to see, the FROM clause identifies the table that the data is in, the WHERE clause will allow you to supply any filtering criteria, and the ORDER BY clause will allow you to sort the records to suit your needs.
▶️ DTube
▶️ IPFS