Hey everyone, welcome back to part 11 in topic 3 of our database class. And in this video, I'm going to provide you with an introduction to SQL select queries. And select statements are probably the most widely or well known SQL statements. And the reason why is that they're very, very useful. I select statements are really useful because this is the SQL statement that we use to get answers from the database. So by learning to write SQL select statements, what we're really learning to do is ask the database questions using a language that the database will understand. And if we can do that, then the amazing thing is that the database just goes off by itself and we'll figure out the answer for us with us doing no additional work. So it's all we need to do is just ask the question. And as long as that question could feasibly be answered given the data in the database, then it will go and figure out the answers and tell us what the answers are. So the result of the select statement is some kind of output and that output represents the answer to whatever question we asked. Now the most basic form of a select statement involves literally just the select keyword and then some additional things that we want to appear as part of the results. So as the most basic form of this is select and then whatever we want to appear as a result. In this case, we might select the employee name. But typically, we will also at least minimally have a from clause as part of our overall select statement. So we're going to select the column or columns that we want to appear in the output. And then we tell the database where we want to look for that information. So in this case, we're saying that what we're asking the question that we're asking here is a hey database, give me a list of the names of all of my employees in the employee table. So we're saying what we want as the output is a list of employee names and we're telling it you database need to look in the employee table in order to get this information. So this presupposes that we have knowledge of the structure of the database. If we do not know that employee names live in the employee table, then we would not be able to write this statement. So we have to be aware of the structure of the database in order to write these select statements. And of course, we can build out our select from framework by including a where clause as well. And just exactly the same way that we saw with our update or delete from statements. We can provide filtering criteria here that will limit the results. Okay, so if I were to run this statement, for example, it would get the name of the employee whose employee ID is equal to 33. So for any employees in our employee table where the value of employee ID is 33. Then that employee's name will be included in the results. If the value of an employee's ID is not equal to 33, then they will be excluded from the results. They will not appear in what we call the result set. So the output from a SQL select statement is called a result set or sometimes you might see it as two words. Good spell. This is all set. So this is the output, this is the general term that we use for describing the output. So let's switch over to our little employees database that we've been working with. And if you remember out here in this database, I'll bring the diagram. We don't have a complete one on the ad, those additional, we had some additional tables out here, right. And our project team tables will add that make it, make sure it appears in our diagram. And I already. So this was our little database that we had built. Try to rearrange these so you can see some of these relationships a little more clearly. But why it draws it like this. Weird. Okay, this is just turning into a disaster. All right, project team, you come up like this. Okay, that's a little bit better. Go like, not too bad. So here's our database and we're going to learn to write some SQL select statements based on this structure. So just as an example of what we saw, maybe we want to get some employee names out of the employee table. So we could write a select statement. And remember the pattern is select keyword select. And then whatever it is we want to appear in the output. So maybe I want first name. And in this case, maybe I want the last name as well of my employees. And then I tell it where I want it to look. So we're going to look here in the employees table because that's where first name and last names live in this database. And that's simple enough. We can end it right there and run this. And you'll see we just could list of the first names and last names of the employees in our database. Now, before we proceed and add a few more little details or complications to this, I do want to point out some salience and important valuable things related to what we've done here thus far. First, do note that if we want more than one column to appear. In this case, we want the first name and last name to appear in the results. All we need to do is list the columns that we want to appear as a comma separated list of names. So we say first name comma last name. Secondly, note that the order in which we specify the columns that we want to appear in the results there will match the order of the output. So it's first name and then last name. That's the output that we get. Now we could of course change that. So if I wanted to switch that around, maybe it makes more sense for me to do last name and then first name. I just change the order here in the select statement. And when I run this. You'll see that our results now reflect that change in preference. So now we're getting last names first and first names second. Okay. So pretty easy. Right. And as we saw on that other slide, we can add a where clause in as well. If we want to limit the results. So I don't know. I think I have a higher date column in here and let's put that in there as well. And maybe we only want to list people who were hired. After 2020. So we would say something like where the higher date is greater than the first of January. 2020. And do remember that whenever we're working with non numeric data. In this case a date. That will be enclosed. Apologies. Inside single quotes. So the value is enclosed inside single quotes. And if we were to run this, remember I did add the higher date column into the output. So we'll be able to see it. And you can see everybody out here. Was hired after January 1st 2020. And if we change that to before then anyone hired. So this is less than right. So anyone hired before 2020 will appear in that list. You can see here we have people from 2018 2019. So they're all hired before 2020 because we change that filter. Cool progress. And we're not limited to a single table. Of course, we can. Well information out of our other tables. So maybe I want to. To get some information out of my department table. And this has a name column that tells me the name of the department. So I can do something like. Select name from. Partner. And then I'll get the names of all of my departments in the database. See we have four different departments. All right. Switching back over to our slides. Now one of the things to note is that. So we write a SQL select statement. That retrieves information. Then the information that is. Returned to us as the answer to our question. Is always in the form of a relation. So if you remember early on in our class, we studied the difference between. A table and a relation. So the relation has certain properties and that concept re emerges here. Because whatever our results that we get back that result is going to be a table. That qualifies as a relation. So just as a brief switch back here, let's go back to. Our example. And maybe I just restore what we had before. All this information here. And all this is saying is that these results, as you can see, it's in a form of a table, right? It has rows and columns. That's the answer to our question is a table. But this table qualifies as a relation. And this is really interesting because what it means then is that we can use the output. Of one SQL select statement as input into. Another statement. So our results are a relation. And we can use that relation as input into another statement. And we'll learn how to do that. After we take a look at querying multiple tables. So. This is interesting. All right. Now as we saw on our live examples from just a moment ago, if we want to retrieve values from more than one column. We do so by providing a comma separated list of column names. And we saw that the order matters because whatever order we provide here. In this case, employee ID and then employee name. We'll be reflected in the results. That is the results will be provided to us in the format where the first column will be the employee ID. And the second column will be the employee name. And if we were to reverse that, then the employee name would be listed first followed by the employee ID. So as you can see, you have a great deal of control. As the person writing these SQL select statements over. How the data in the outputs, how the results of your SQL select query will appear. All right. The next little concept we'll learn is about the asterisk. So this little asterisk symbol. We'll star symbol on your keyboard on a North American keyboard. It's like a capital eight. The keyboard. Okay. So what this little star this asterisk means is all columns. Okay. So this is a shorthand or shortcut way of telling the database. We want it to retrieve values for every single column in the specified table. So if I run this statement, then the result would be with this statement with nowhere clause that I would get a list of all of the data in the employee table. Because this means all columns. And there's nowhere clause. So this will retrieve all rows as well. So let's see that in action. So here over here you can see we have have our employee table. Right. And I have last name first name higher date. It's fine. But if I just put an asterisk in there instead. And that tells me you get everything out of the employee table. So the asterisk means all columns. And if I run this, you'll see that we now get all columns in our output. Employee ID first name last name higher date and department ID. Those all coming the output. You'll note that we also get all rows in this case because there is no where clause. So if we omit a where clause, the result is we get all of the rows from the specified table or tables. But of course I could limit that, say for example, I only want to see people who work in department number two. And I could specify a where clause and limit it to just that department. And remember, it's always just about memorizing the pattern. And with the select statement that goes select from where in that order. Okay. So you can see in this case I provided a where clause. We now get all columns because we have the asterisk there, but we do not get all rows because we provided a where clause that constraints which data will be returned as the answer. So in this case, if I wanted to ask the question, just like give me all of my employee data for every employee that works into department number two. This is what the results would be. And again, the results are a relation. So everyone here, everyone in the results works in department number two. All right. Let's continue. So another useful key word that we can include in our SQL select statement is the distinct. keyword. And what distinct does is it retrieves unique values of the specified column. So if I want just a unique set of department IDs that appear in the employee table, I can do that by including the distinct keyword. In front of the column for which I want unique values to be returned. Okay. So without this, I might get some duplicates in my results. I might get duplicate department IDs. So if I have more than one employee per department, I would get many duplicates. But with the distinct keyword there, I'm only going to get those unique or distinct values of department ID in this case that appear in our employee table. So let's see a little live demonstration of that. So we can continue working on our employee table. We know that we have a department ID in here. And let's say that we began by just selecting department ID. I'm going to run this. And you'll see that we have duplicates. Right. Here's department one one one one one one two two two two two two two two. And that's because we have different employees that work in these departments. So if I were just to. I'll just toss their first names in there so we can see that what we're getting here is like. Tatness works in department number two, gale works in department number two, pita works in department number two and so on. So this is where these duplicate values come from. It's that we're looking inside the employee table and a not surprisingly we have more than one employee that works in each department. So we get duplicates of our department IDs. However, if we want to constrain the set of department IDs. That is returned to us to only unique values. We can do that by including the distinct keyword as we just saw in that lecture slide. So if I run this we'll see that we only get the unique set. Of department IDs one two three and four. And if we were to just pull all the data from there. Here's a little trick for you if you want to just run one statement you can highlight it and then click on execute. So here we're just pulling. We should now be able to look at this and say this means give me all of the data out of the employee table. And if we look over here at the various department IDs we can see that we've got some ones and twos. Here's a three here's a four. So that is our valid set one two three or four. So that means when we run this statement and we're asking for the unique values of department ID. And the results come back one two three four then those results make sense. That is the correct answer to the question of what is the unique set of department IDs in the employee table. So the distinct keyword can be very useful if you want to eliminate that duplicate data. All right, so continuing. A little bit more about the where clause. We've already seen several examples of this. We saw it's first when we looked at the update statement as a way of limiting. Which rows were updated to those that meet our filtering criteria as specified inside the where clause. And then we next saw it with the delete from statement. Where we were limiting which rows would be deleted to the criteria or the criterion specified by the where clause. And now we're seeing it with select. And in this case it's limiting the results that will be returned to those that meet our filtering criteria. So if we take a look at this example. And that we see here where it says select employee name from the employee tables. This is going to give us a list of employee names, but we're including a where clause. And that will limit our results. So in this case we're only going to get employees who work in department number 15. So that would limit our results to just those employees. So however, it's before we can proceed learning a little bit more about where we need to know that we have lots of different comparison operators that we can use. Because there are a total of six different comparisons that potentially could be done if you're comparing one value to another. And those six are listed here along with the way in which we implement them inside. the structure query language. So our first one we've already seen this is equals, right, we use an equals sign. So we saw that on the previous slide, for example, give me the names of all employees who work in department number 15. So if the value of department ID is equal to 15, then the associated employee name will be included in the results. Otherwise, the employee name would not be included because they don't work in department number 15. So that's an equal to we also have the opposite of that or the converse which is not equal to this is the preferred method here. But in SQL Server you can also use this. But to this one is much more common. So a little exclamation point and then equal sign means not equal to greater than and less than are pretty easy. We just use the greater than or less than symbols, which you should have learned in math classes long long ago. But these last two are a little different from maybe what you learned when you were studying mathematics. And the reason why is that we don't have a greater than or equal to sign on our keyboard. So we use two separate symbols to indicate that notion. And this is different than maybe when you were studying mathematics you learn these as greater than or equal to that symbol for that concept or less than or equal to. So we can't use those with our structured query language statements because we don't have those symbols on the keyboard. So we can't easily type them. And instead what we do is we use greater than or equal to by combining those two symbols or less than or equal to. This one I think looks like an arrow pointing left but that's just me. Okay, so let's see some examples of live examples of so we can filter more effectively in our SQL statements. Right. Oh, in this case let's pull some information from our. Employee table again and I'll get the first name. And the last name. And the department ID. And we'll play around some of these things so we saw this one already where we could say where the department ID. Is equal to no two. And if we run that on results will only include those employees who work in department number two, but we can mix this up as well. Right. So maybe we want every employee except those who work in department number two. In that case we could use not equal to. So this says, give me the first name last name and department ID of every employee in the employee table that does not work in department number two. So if the department ID is not equal to two, they will be included in the results. And you can see if I run this. We get employees who work in department one and department four and department three, but you'll see that there are no employees in the results who work in department two. And of course we can do less than and greater than. So if I want to say all of the employees that work in departments one or two, I might say, give me those that work in any department that's less than three. And if I run this you'll see that our results are constrained only to those whose department IDs are less than less than three. I could similarly do say greater than three if I wanted to. And in that case the only results will be those who work in departments where the ID is greater than three. We only have one employee in this case. You can see Joanna works in department number four. And then we have our less than or equal to concepts. As well. So here is less than or equal to three. And you can see all of the results. Our employees who work in the department where the department ID is less than or equal to three. So here we have Finneck, he's in three right and then everybody else is in one or two. Or we can do greater than or equal to. So if I did greater than or equal to three, that would mean three or above will be included in the results. And you can see all the employees here either work in department three or four. Those are the only employees in our table whose department IDs are greater than or equal to three as we specified here in the where clause. Cool. So you can do all kinds of useful filtering using these comparison operators, these comparison symbols. We have six different types of comparisons that we can make equal to not equal to greater than less than greater than or equal to and less than or equal to.