课程: SQL Essential Training

Query composition - SQL教程

课程: SQL Essential Training

开始一个月试用

Query composition

“

- [Instructor] Okay, the moment we have been waiting for is here. We get to compose our first SQL statement. So let's do that by first starting up a brand new tab. This icon all the way to the left here is Open Tab. By clicking on that, that gives us a brand new tab. The existing tab that I just had is right here still, but I just have now created a brand new tab and gives us a clean space to start composing our query. Now, jumping right into our best practice, I'm going to start with front slash star, star front slash, and in there I'm going to put my comments, as such, created by, and that will be me, by Walter Shields. You put the create date, and that would be month, followed by day, followed by year. And last but not least, we'll put the description and let's think about what description we're going to put. Well, let's say that we were tasked or asked by management of WSDA Music to give us a list of all the customers in the database, and they want to have their first names, their last names, and email addresses. So let's put that as a description. First, last, and we should just say properly first and last names. Okay. So we want to have a query that's going to display all customer's first and last names, together with their email addresses. How would we begin to compose such a query? Well, if we start by going to the Database Structure tab, we can remind ourselves about the entire structure of this database. And if we recall, there are eleven tables. So one of the key things is to look at the names of these tables, which gives us some clue as to what might be housed within it. And our current request is asking for information or data from our customers. And as we go down the line here, we do see that we have a customer table. So when we expand this particular table, we can now see the fields or the columns that are the part of this particular table. Now in examining it, we do see that we have a first name which is what was requested, a last name, and if we go down here, we do have an email address. So by going to the Database Structure tab, we've now given ourselves some more information as to where we can actually zero in to get this data. Now, if we go to the Browse Data tab and we take a look at that same customer table, we can see the contents of that particular table and confirm, yes, indeed, this does seem to have what it says it houses: first name, last names, and email addresses, if we scroll on over here. So how would we now compose a SQL statement that's going to give us this result? Now let's go below our comment and start with the keyword "from." Now, once I go to keyword "from," and I want to specify from where. So now we have just confirmed that the customer table is the table that actually houses that information that we're after. Now I'm going to pause here and take a look at this popup that you'll probably have noticed as we were typing before. This is referred to as IntelliSense and this actually gives us the option to not type as much. And it's the job of IntelliSense to guess what we're actually typing, again with the aim of saving us some typing time. We're after the customer table which is denoted here by this symbol. The other option here is Customer Id which is actually a field within the customer table, but in the from clause we want to specify what table we're after. So, this effectively is saying go to the customer table. All right, let's remind ourselves there again. Inside the customer table now we have all of these choices that we can choose to include in our query result. Back to our execute SQL tab again. We want to start above that From clause with the keyword Select. Now under Select, we can now select the columns or the fields that we're interested in. Our request specifically is asking for first name. And there it is. You can now select that item. And it's also asking for last name. Because another column is going to be included in our result, I separate column names with a comma. I go down to the second line, and then I put last name and I select that option from our helpful IntelliSense. Finally, I want to see email addresses, so I repeat the process, add a comma, and add email, and select that item. So with this done, I have effectively built a basic SQL statement and now it is ready to be run or executed. I go up here and I select this particular item, execute all, and when I do that, I see that we have a few results down here in our Results pane. So our results pane is now populated with the result of our constructed SQL statement, and it does give us the request that we have been tasked with: first name, last name, and email address. Down here in the messages pane, we see we have some important information as well. We can tell now that we have 60 rows or 60 customers that have been returned in our result. We can tell the time that it took for that result to be generated, which was 19 milliseconds. And we also have the repeat of our SQL statement that we've composed here. So a lot of pertinent information. And together we have just done our very first SQL statement, gotten a result, and this is something to be very, very proud of. Well done.

内容