Master how SQL operators like AND, OR, and NOT can significantly refine your database queries. Learn practical tips for creating calculated columns and effectively naming results in SQL.
Key Insights
- Use logical SQL operators AND and OR to filter database results—AND retrieves rows meeting all criteria simultaneously (e.g., products starting with B and priced below $10), whereas OR includes rows meeting any single condition.
- Place the NOT operator before a logical condition to negate it—NOT reverses query conditions, such as selecting products whose names do not start with a certain letter.
- Create calculated columns with arithmetic operations (multiplication, addition, subtraction, division) and use double quotes for aliases that contain spaces; remember, single quotes in SQL are reserved strictly for strings.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
All right. So, we've seen OR already, but we also have an AND, and we also have a NOT. These are other ways we can put multiple things together.
For example, you can say, I want to find products where the name is like this. So, that would be a capital B and something after it. In other words, a product whose name starts with a B. And the price must be less than $10.
So, these would be B products that are cheap, that are less than $10. Both must be true. It must be starting with a B, and the price has to be less than $10.
If you simply switch that to OR, either one of these could be true. And when it thinks about a row—so, let's say you're looking at a row.
SQL goes row by row and says, do I include this row in the result set? So, imagine the result set being empty with nothing in it. And it's going to go row by row and decide, should I include this in the result set? In this first example, if both things are true—it starts with a B and it's less than $10—that row is good.
It'll include it in the result set. If one of those is false, it's not a match, and it will not include that row in the result set. But here, if either one of these is true, that's good enough to get it included in the result set.
So, if it starts with a B, boom, it's in the result set. Or, if the product is less than $10, boom, it's in the result set. Either one has to be true.
They don't both have to be true. So, I'm going to get all the cheap products or all of the products that start with a B. They don't have to be the cheap B products. If either one of them is true, it'll include that row.
They don't both have to be true. Now, if you insert NOT, and I say to harness your inner Shakespearean, WHERE NOT the name is LIKE B. NOT negates this whole thing. So, instead of looking for things that start with a B, you're looking for all the products that don't start with a B. Now, WHERE NOT will always work.
NOT would negate anything that comes after it. Sometimes you can move the NOT over. For example, if it's a word, you could say WHERE name NOT LIKE.
So, if this is a word, you could put NOT with it. But it doesn't work if it's a symbol, like less than, greater than, equal to. You can't put a word with a symbol.
So, the NOT will always work when it comes before. Sometimes you can move it over when the two things are words. But otherwise, if you want to be sure that it will always work, just always put NOT in front of the whole thing, and it will just invert that.
It'll just say everything that's not that thing. Now, as I just mentioned, I showed that you could do math, right? So, math is doing multiplication, addition, subtraction, division. Also, do you remember back to math class where you put parentheses, and it said do the math in the parentheses first? So, that's another form of math as well.
So, if you say price times quantity, that will create a new column with that math being performed. As we just saw, of course, it doesn't have a name. So, if we want to name it, we can say let it be known AS, and then we can come up with any name that we want.
If you look for that name in your database, you will not find it. You will only see it in your result set because we are coming up with that name. That is a name that we get to choose because that column as a column doesn't exist in my database.
So, I get to name it just for the sake of my result set. And as long as you use things like underscores in your name, you do not have to put double quotes. It's only if you want to have a space that you have to put double quotes so that you can have a nice, pretty name instead of more of a code-looking name.
So, if you want to use spaces, things like that, you have to use double quotes. Not to be confused with these single quotes for strings. SQL does not let you interchange these things.
So, SQL double quotes are column aliases. Strings are single quotes. There's a difference.
