In this video, learn how to tackle the most basic query possible to start as a foundation for everything else: SELECT * FROM table_name. Also, learn how to look at only specific columns and all column aliases. This is the first building block to get to more complex queries.
- [Instructor] We have finally arrived at the coding portion of our show, and we're going to start with the most basic operation, which is reading from a single table. To do that, we're going to build what's known as a select statement, where you're going to select what columns you want to look at, and tell SQL Server what tables those columns should be coming from using a from clause. So let's move over to SSMS so we can see this in action. As you can see, I've already connected to the server on my machine and I'm going to go ahead and expand my Databases folder by clicking on the plus sign to the left. And I'm going to be using the sandbox database for this demo, and expand that in the same way. And then lastly, we're going to look at our list of tables available. And if you ran the data script attached to chapter one, your database should look exactly like this as well. So to write my first query, first I'm going to select the database I want to work off of, which is the sandbox database. And I'm going to go up here and click on New Query. And I'm going to go into my query editor, and I'm going to select star. Star just means get all of the columns available in this table. And I'm going to say from dbo.additional_service. And I can run that by going over here and clicking on Execute. Run that. And I get all of the data available in that table. And if I go over back to my object explorer and expand the additional_service table and the Columns folder, I'll see that the columns here match what came back in my results set. A lot of times, you're not going to want to look at every column in your table, so you can limit that set by listing the columns individually. So I get rid of this star, and I just want to look at the service name. Add a comma to separate the column names. And I want to know the minimum number of participants for each service. And then again, I'm going to go over and execute my query. And I'm just going to see the data from those two columns. We haven't changed the table at all. If I go over here, I can refresh. And I can confirm that all of the columns are still there. I'm just changing what's presented. Another good tip when you're running queries like this is that sometimes you're dealing with a table with lots and lots of rows, but maybe you're just trying to get an idea of what the data looks like or you want to just look at a sample set. So to limit how many rows come back, after select you would just add top three, or top 10 or however many you'd want to. And again, execute. And I only get three rows back. So you'll notice in our results panel that by default our column headers are also the same as the names of the columns themselves, but sometimes you might want to change your presentation a little bit and change those column headers. So I'm going to show you a couple of ways to do that. There're several ways, but I'm just going to show you the two that I like best. First I'm going to just copy this query. So the first option is to put your alias at the front of your column name, and I'm going to call this additional_service, and you just put an equal sign. And then I'm going to say minimum_participants. And I'm going to run both of these so we can compare the results that we get. Same data. And you'll notice that the only thing that has changed is the column header. And again, we're not changing the structure of the tables at all, just their presentation. And then the other way that you can do this, we'll start fresh, is to add it after the column name. And it's not required, but I really recommend that you put these in square brackets. So I'm going to use the same ones, additional_service, and using the brackets also allows me to put a space in the column alias. If I didn't have those brackets, it wouldn't know what to do if I added this space. And then I'm going to say minimum_participants. Going to execute that. And again, same result. It's just a matter of which one you like better. I also really quickly want to point out this yellow bar at the bottom of our query window. And there's a lot of useful information here, like the server that we're connected to, the database we're connected to, the runtime, and the number of rows that we've gotten back. And I just really want to draw your attention to this runtime. Sometimes your query might be taking a really long time to run when you're only expecting a little bit of data back. So you might want to cancel your query in the middle. So to do that, you just go up here, and this Cancel button will be red while the query is running, and you just click on that, and it will stop whatever query you're running. Just a thing to keep an eye on. Realistically, there probably won't be very many scenarios where you just want to get all of the data from the table every time. More likely you'll have specific questions that you want to answer by filtering the results in your query, which is what we're going to cover in our next video.