Discover essential SQL practices to streamline your workflow and avoid common pitfalls. Learn how to efficiently filter data using numeric conditions and operators for cleaner query results.
Key Insights
- Select a data source first when working with SQL files to enable code hints and ensure smoother coding; a missing selection is indicated by no code hints appearing.
- Use numeric operators like less than, greater than, or between to precisely filter data; for example, using "BETWEEN 20 AND 30" returns all products priced within that specified range.
- Optimize your queries by using the "IN" operator to retrieve records matching multiple specific numeric values, making your SQL queries more efficient and clearly structured.
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.
Let me show you the warmups here. Now, I just opened up a new file, which DBeaver has never seen before on this computer. Notice up here, it says N/A, N/A. It does not assume to know which database this is connected with.
If I were to start typing, I'm not going to get any code hints. Notice no code hints. That's your first clue to say, ding, ding, ding.
You forgot to choose your data source, right? I'm showing this because you're excited, thinking, "Let me open a file and start working on it, " right?
And you forget. So, the first clue that you forgot something is the lack of code hints, because it should be code hinting by now. So that's a reminder to say, "Hey, go up here and choose your server."
In this case, I only have one server here. When I choose it, I'm just choosing one server, and it goes into the public schema that's in my company_data database. So I'm seeing that I'm in the correct database.
Now, as I start to type, notice code hints work again. Okay. That's how they should work.
Okay, if I get all of my products here, these are all of my products. I don't want to see all the products.
Notice this gorgeous concrete shirt, which feels so good on my skin. We used a random product generator, so the product names are pretty funny.
Trying to keep our data with a little bit of humor in it. But notice that it's 3198. If I want to find only rows where the price contains a certain number, notice how it's 123.
So that's a numeric value. So 3198, I do not put quotes because it's not text. It's a number that would only find rows that are 3198.
Could I see multiple rows here? Sure. If I had other products that were 3198, I would see all of the products with that price. But I only have one product with that price, so I only see one row. But don't think that you're only ever going to see one row.
You could potentially see hundreds, thousands, or millions of rows. You will see all rows where the price column contains 3198. Now, if I said less than 3198 instead of equals, it will not include the product that is 3198 because I said it has to be less than that amount.
So these are all the cheaper products. But if I said less than or equal to, now it would also include the 3198 because it's less than or equal to. Now I have a range of prices, but I could say between certain amounts. If I say where the price is between 20 and 30, anything between 20 and 30 I'll see.
Now, if I had a $20 product, it would be listed here. I don't have a $20 product, but I do have a 2887. So I'm going to type in the 2887, and I also have a 4493. I just want you to see that it includes these values.
So it's greater than or equal to this and less than or equal to this. So I'm seeing those values there and there and everything in between. If I want to just see two prices, prices that are 3198. This would show all rows that are 3198, or the price is—and I know I just saw—a 2887. Again, it could be multiple rows that are 3198 or multiple rows that are 2887.
It just means that in our case, we only have one product with each of these prices, but don't think I'm limited to just two rows. This could be a million rows that get returned, but they'd all have one of these two prices—either this or this. Now that's not very efficient, but a nice thing about this is you could say it's this price or it's greater than 50.
Oh, sorry—more than 50. So here it's this price or it's more than 50.
These are all greater than 50, showing you don't have to use the same operator each time. But if you are looking for multiple products with the same exact price, it would be more efficient to say that it's in a list, and in parentheses here, you can put multiple prices, and it's going to be equal to one of the things in this list. The way I think of this is "where the price is found in this list."
Do you find the price in this list? If so, include it down here. So that's always equality for that. It's like saying "price equals this or price equals this" for all of those.
