When it’s time to start retrieving information that is stored in a tables, queries will need to be written in the Transact-SQL language. In this video, Adam writes several queries using the SELECT clause to pull information out of the tables, the WHERE clause to filter the results to a specific subset of records, and the ORDER BY clause to sort the records for presentation.
- [Voiceover] 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 the shorter name. To get our first look at T-SQL, I'm going to drill down into the WideWorldImporters database, and find the tables group. I'll expand that and find the table called Sales.Orders. I'll right-click on it and choose Select Top 1000 Rows. This will issue a T-SQL query to the database to return the first 1000 records from this particular table.
On the top of the query window, we have the exact syntax of the query, and on the bottom we have the results. Let's focus on the query at the top. It begins with a key word, select. The select clause tells the database which columns are going to be returned. In this case, we're returning the Order ID column, the Customer ID column, the Salesperson ID, and so on. Then at the bottom, we have a from clause. The from clause states which table that we're going to be pulling information from. In this case, the Sales.Orders table from the WideWorldImporters database. I'd like to use this syntax as a model and show you how to write queries on your own.
In order to start a new query, we'll come up to the New Query button on the standard toolbar. When you click on it, you get a new tab with a blank space where we can start typing. I'll start by typing the key word select. Transact-SQL is case insensitive, so it doesn't really matter if you use all caps 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 makes them easier to read this way. After the select key word, we'll type in the names of the columns that we want to return, in this case the Order ID, Customer ID, and Order Date columns.
This will be a comma separated list. 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 you're writing queries. If you see an item on the list that you're after, just press the down arrow key and select it and press Enter key to accept the suggestion. At this point though, SQL Server doesn't know which table I'm going to be pulling columns from, so it can't make the appropriate suggestions. I'll just continue typing the column names, and you'll start to see these red underlines showing up.
This indicates that there's a potential problem with the fields that I'm typing in, but this will get resolved in just a moment when we specify the table we're pulling from. Also, because none of these names include spaces, I don't technically need to enclose them in square brackets like we saw with the previous query that was automatically created by SQL Server. Just separate the name of each column with a comma. At the end of the list, I've actually got an extra comma in here, so I'm going to backspace off of that, and then I'll press Enter to get down to the next line. The next line is going to include the from clause, and then the name of the table that we're pulling from, in this case, the Sales.Orders table.
Notice that this time, the IntelliSense system is working for us. I could just go down here and press the down arrow key, and select that from the list in order to populate it up above. Also notice that I'm not specifying the database that we're pulling from. That could be set up here on the toolbar with this drop down menu here. By selecting the WideWorldImporters database from this popup menu, we're ensuring that we're targeting all of our queries against this database here, so I don't have to specify it in the query itself. When you're ready to go, press the Execute button on the toolbar, and that will run our query, and we'll see the results down below. Because we didn't specify to limit the results to just the top 1000 records, we get back a row for each record in the database.
On the far bottom righthand side of your screen in this yellow section, we'll see that there's a total of 73,595 rows in this table. Right now, I'm seeing orders from 2013 at the top of the results window. If I scroll through this list, we'll see that they're in ascending order. This will put the most current orders at the very 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. Below the from clause, I'll type in a new line, and this will be called order by. In this case, we want to order our results based off of the order date, and we want them to come in in a descending order so that the most current order is at the top of the list.
I'll type in the key word desc for descending. When that turns blue, that means that I've typed it in correctly, and I can go ahead and press the Execute button, and we'll see the results of this query. Now we're still getting back the original 73,595 rows, so we're seeing all of the data, it's just presented in a different order, and now, May 31, 2016 is at the top of the list. 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 just wanted to view the orders placed by the customer with the ID of 578? I could add something called a where clause to define a filtering criteria.
I'll come back up here to the top, and belong the from line, I'll add in another space. Next, I'll type in the where clause, and we'll specify that we only want the records where the customer ID is equal to 578. If I execute the query again, we'll see the results down below. Now I can see I have a total of 108 records that match this single customer ID. And because we're still using the order by clause to sort the dates descending, I can see that the most recent order was placed on May 26 of 2016. So those are some of the ways that you could 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.
This course introduces the most important features of SQL Server 2016, and is designed to act as the student's very first look at how the server operates. Adam Wilbert shows how to install a local copy of SQL Server; demonstrates the working environment, SQL Server Management Studio; and walks students through their first hour creating tables, defining relationships, and writing and running queries with Transact-SQL commands. Everything you need to accomplish to create your first database is covered in just 80 minutes. Start watching to get up and running with this professional-grade database management system.
- What is SQL Server?
- Configuring the server
- Creating a new database and data tables
- Joining tables with keys
- Creating a SELECT statement
- Writing an UPDATE statement
- Adding data with the INSERT statement