Single Table Queries Part 3

Video Transcription

Welcome back everyone. In this video series, we’re going to be taking our last look at single table queries, and primarily introducing some more operations and expressions that we can utilize as part of them, as well as a little bit of discussion on variables and different data types that we can work with. But before we work with that, let’s quickly review what we covered last time. So these are all of the SQL statements that we have looked at so far. And just as a refresher, right, remembering that our SQL query is not executed from top down, there is a particular processing order that we have to that the query adheres to, even though we are required to list the query in the particular order that’s shown here on the screen. So logically, our data is processed. From the from clause, we’re pulling the data from our tables, we can filter the rows, the where clause, group them, then filter those groups with the having clause, then we project or we project to our columns that we are wanting in our results.

So that’s the SELECT clause, then we, if distinct is there, that’s where distinct will happen, as well, so we get unique rows, then we can order those rows by the specific columns that we projected. And then if we include top top happens there after the order by the top is again, unique to SQL Server. Otherwise offset and fetch actually happens alongside the order by operation. And the offset fetch is just like the top as you remember, though, offset is unique, where top does not actually have the ability to offset a certain number of rows. And offset again, is also part of the ancy standard for SQL. But for this video, let’s take a look at some new stuff.

First off here, looking at some predicates. So these predicates are some expressions that we can actually work in, to give a little bit more life, or at least, a little bit more expression, to what we can actually filter our, our results for each of our queries. So we’ve got in between like, and a variety of other things, or there’s also a variety of other predicates that we will actually chat about throughout the semester. But most of these sounds and behave exactly like they’re actually listed here. So in is going to see if a, the value on the left is in the set on the right. So is so if we have a list of numbers, let’s say 510 15 is five in that set would be true, but negative one is not in that set. So very similar to the in operation that you see in Python. But nonetheless, very similar to what we see with tween. So check to see if a value is between a certain range, these boundary values are inclusive, so is five between five and 10. Yes. So So is five less than or equal to 10 Less than or or five greater than or equal to five less than or equal to 10. So between those two boundaries, inclusive, like is going to evaluate whether or not the left hand stream is like the specified pattern. So this is more so like a regular expression.

All of these predicates by the way can be negated. So not in not between not like, which expands what we can actually do with them. But let’s take a look at a couple of examples here. So here are predicates can be used mostly in place of where we would see like Boolean expressions and things like that. So here I’m selecting the order order date, and customer ID from the Orders table, where the customer ID is in this set. So the this, the set is denoted by this tuple the parentheses here 316 and 147. So this is long hand to if you wanted to do this with just a Boolean expression we would do customer ID equals three or customer ID equals 16 or customer ID equals 147. So the end operator duration helps us condense what would otherwise be a longer or long winded Boolean expression into something that is very succinct and easy to read. Now, we can also do a between clause here. So let’s take out our existing where replace it with this one here.

So give me all the orders where the order date is between 2016 One, one and 2016 131. So this is very much like our statement that we had in a previous video where we had where order date is less, or where order date is greater than or equal to 2016. One, one, and order date is less than or equal to the second date. So this is a more succinct way to do a range of values. So let’s go ahead and execute this. And so this gives us all of the orders that are in January of 2016. So the like is a little bit more difficult to actually show. In the notes, I will make sure to link to the documentation. So you can see all of the different pattern patterns that you can actually create for the regular expressions for our like operation or like predicate. So here, select C dot star. So this is give me all of the columns from table C, where customer name is like, tailspin. So tailspin, it’s going to it must start with tailspin.

But then the percent sign is I don’t care what comes after this. So tailspin, whatever, right, so let’s go ahead and run this. Oops, sorry, I had that highlighted that need to run like this. There we go. So Does that tickle took a little bit longer to actually execute because the like operation, the regular expression is a little bit more expensive, especially if you have a significant number of rows here. So we actually have 201 rows here, but we had to compare all of the different customer names. So we have Tailspin Toys, Tailspin Toys, so on and so forth. Where we filtered out all of the customers that started with tailspin, we can be a little bit more lacs with this. If we wanted to say well, give me all the customers that deal with toys. And so we can do percent sent space. Note here that the spaces are relevant. So they are considered. So give me all of the customer names that have something that starts with something has a space and then toys and then space percent, I can make this less strict by taking out the I don’t think there’s anything else in here.

Let’s double check our messages. So 402 rows that have the word toys in it surrounded by a space. And I believe we’re on this here. If we take a look at our messages, again, still same 402. But the this, the specification is a little less strict, right? So give me all the customer names that contain the word toys inside of them. Now this is case sensitive and this case, so do be careful about that. There are more like wildcard characters out there. So if you wanted to say like, give me everything that says that has oil in it, we can actually run that and get similar results there. So that T or that underscore is going to be a wildcard but it is a single wildcard, right single wildcard. So let’s take a look at a couple more examples of some things we can add into our our like predicate, right? So it does support a single character wildcard. So the the percent sign is one or more. But if you only wanted to substitute a single character, if you only wanted a single character there, you would use a underscore. So if you wanted anything, everything that started with some character, and then Ale, you get everything after that.

So that can be pretty useful, then we can also do ranges. And so we have this query here, this will all be in the notes for you. This query here actually polls and, or compares for a phone number. So that tab that down here, so it fits on one screen. But give me all the customers with phone numbers where their phone number is not like this. So we have basically giving me all the customer phone numbers that don’t have an area code of 215. Okay, so if I execute this, again, sorry, let me um, highlight that. So if I execute this, we get 626 rows. So these are all the customers that don’t have a phone number that starts with 215. But I can take out the knot here. And that gives me only the customers that start have a phone number of 215. So that’s how the knot works well, pretty well here. So we covered the multi character wildcard, which is the percent sign single character wildcard, which is the underscore. And then we have ranges and sets which are supported there.

So that’s the square bracket and the zero through nine, we can also do a through z, or we can do like something like 0123. If you don’t want to range inside the square brackets, you can only you can specify a specific set of numbers or letters in there, which helps quite a lot. But that pretty much concludes most of what we can do with predicates. Now I didn’t cover all of the expressions that you can use with like and all of that. But again, I will link to the documentation and have some more examples written up inside of the notes. But again, these predicates are very useful when trying to filter out results as part of your query anywhere where you use a Boolean expression. So now let’s take a look at some more examples for operators. So we’ve looked at a lot of operations and expressions that we can do so far in SQL. But just as a quick little coverage here, all of the different operators that we have.

So we parentheses, multiplication, division, sign, mod, all of these things, very similar to what the standard order of operations is, for your Python and Java code, where Boolean expressions and mathematical expressions so we have parentheses multiplication, all that all that for is all of your boolean expressions, followed by not, and and then all of our predicates, and then equals as the assignment operator, which will become more important when we start talking about variables here and a little bit. So that’s just one part where the equal sign can be a little bit confusing in SQL. We don’t have the double equals we just have the single equals which is both used as assignment operator in certain contexts, and the Boolean comparison operator equality and other contexts. So just be careful when you’re using that. But the notes will have some examples to show operator precedence. I’m not going to show those quite yet in the video. Next, we’ll take a look at some more expressions that we can utilize as part of our sequel.