We're going to move on to Lesson 2 in our SQL Primer and learn a little bit more about SQL. The first thing we're going to talk about is sorting and conditional searches. These are things that you'll run into on a very regular basis. Let's dive in. There are many ways to sort the data. These are just some of the most common ones. Again, the goal of this course is simply to teach you how to teach yourself and to give you the fundamentals and get you a really solid foundation. But don't be afraid to explore other ways as you see them or need them. Some of the key terms that we're going to cover are ORDER BY, BETWEEN, AND, OR and NOT, LIKE, DISTINCT. You'll get pretty comfortable with all of those. Again, we're working with a very straightforward database of students here. We've just got a few students and with first name and last name obviously a very simple table of 12 rows. We can use ORDER BY to sort the data. We can select whatever columns we want. In this case Column 1, Column 2. We're just making up from a table and then order by which column we want. You can then either choose ascending or descending. You'll note that you don't really have to say it if you want it ascending because that's the default ordering anyway. You'll notice in this example we select first name, last name from student and we order by the last name. You'll notice that we have sorted this table of students by their last name. Again, we did not have to put ascending because that's the order we wanted it in. These are ascending in alphabetical order as they go from B to W In our case here. If we wanted them descending, you can imagine what that would look like. In fact, we're going to do it pretty quickly. Just remember your syntax. Select, in this case, first name and last name from student, order by last name, that's all the information we need for this particular statement. Those are the only two fields that we chose. On the right-hand side of your screen now, you'll notice that we have added that order by last name, and then this time we've said descending. You'll notice that the last names are ordered exactly opposite. They start with W and go down to B in reverse alphabetical order. What about BETWEEN, what if we need to select something between a range of data? If you take a look at the statement, it just says select a column name from a table name. We're getting pretty good at that, I hope. Then here comes a keyword that we haven't used a lot where column name and another keyword between Value 1 and Value 2 looks a little busy until you see it and then you go, "Oh, okay, not so bad." It's select all from student where first name between A and F. You can imagine what we're going to get back now in our results. We're only going to see students whose first name fall between A and F. Now notice we didn't order them in this case. As you're looking at them, these are in no specific order, just happened to pull out of the database that way. But we have a D and A and the Cs, all of them are values falling between A and F. Then we have our AND, OR and NOT are Boolean logic, if you will. When you're thinking about AND, just remember that all the conditions must be true for the statement to return true and, OR is any one of the conditions must be true. In this case, we're going to select all from student, where the student ID is less than 110 and the first name is greater than the character C. You'll notice as we review, both of these had to be true. The student ID had to be less than 110. Yes, that's valid. Then the second condition was the first name had to be greater than C following that. You'll notice that we have Darryl, Shirley, Frederick, and so on and that we are not seeing any A's or B's or any of those values. You'll see our syntax once again, select Column 1, Column 2 from table name, where Condition 1 and our keyword Condition 2. The syntax for the OR statement is very similar but you can imagine, I hope, what the difference would be. We'll review that in just a minute. We can also throw in a keyword here for NOT. We can say select the values from Column 1 and Column 2 from this table where NOT, and then give it a condition. We ran this one that we've already discussed. Select OR from student where student ID is less than 110 and first name is greater than C. Again, I just encourage you at lab time or when you have a minute as you're playing around with different coding scenarios, go ahead and try some of these out. Say, "Well, wait a minute, what if I wanted it to be in this range or between this value and that value, or include a couple of other items." Feel free to do that. LIKE is a keyword that means we can search for a pattern in an attribute or column. There's a couple of wildcards, a percent placeholder for zero, one or multiple characters, and an underscore place holder for a single character. Let's take a look at some examples where first name LIKE, and then notice that we surround it with tick marks, put a percent and that will return any values that start with an a. Or we can say where last name is LIKE, percent IS percent and that will return any last name that contains the characters IS contiguously. Then the third one is where first name is like C underscore. That will return any values that start with a C and have at least two characters. You can look at the select statements down there and their formula. You could see that we say select first name, last name from student where first name is like C. That would just mean one that started with a C. Do note that the values are case sensitive in your LIKE. In this case, we said select first name, last name from student, where first name LIKE C and you'll notice that all of our first names that come back start with C. What about if I want unique values? What if I have two entries for something and that's where our keyword DISTINCT comes in. This means return one and only one copy of that value, regardless of whether I have two or 2 million, it really doesn't matter, I only want to see the DISTINCT value returned. There's your syntax. Select DISTINCT, Column 1, Column 2 from table name. To make it work in our database, let's add a duplicate first-name. A great way for us to practice our INSERT that we learned a while back. We're going to make sure that we add another Tony and we already have Tony Curtis. We're going to add Tony Roma. Then we're going to check it to make sure that we have the two Tony values. We first insert into student, student ID, first name, last name, values 115, Tony, and Roma and then we check our values with an insert. Now we're going to run our distinct statement and we're going to say select distinct first name from student. Sure enough, when we run that command, we only see one Tony versus if we'd said select all from students, we would have seen all of the first names including two Tony's. There's only one Tony in our DISTINCT query. You covered a lot of ground there. Let's just do a quick recap. We covered ORDER BY, BETWEEN, AND, OR and NOT, LIKE, and DISTINCT. As you've probably realized, all of these are pretty straightforward. Even though we covered quite a few options for you, the statements should be getting easier for you and we certainly don't expect you to memorize them. Just remember, if you forget a little bit of syntax, it's fine to look it up or look back at the video until you get the hang of it.
