It's helpful to narrow down a query, especially if you're only looking for information that matches a particular condition. Explore the WHERE command in this video.
- When we're asking the database for information, it's very helpful to be able to give some parameters. You can use the WHERE keyword to add a condition to a SELECT statement. For example, if I wanted to see all of the records, only for people who live in California, I could use my SELECT * FROM people statement and then add a WHERE clause with the predicate or condition state='CA' for California in single quotes because it's a text string and I'll end that with a semicolon.
I see here that I have 127 rows returned and all of these results have California as the state. Try it out with a different state by changing the expression. I'll change CA to FL for Florida. And there's 93 rows. Or maybe Washington with WA. (clicking) 19 rows. (typing) And NY for New York.
62 rows. Let's try with a different field. Let's change our field in our predicate to ask instead who in our database asked for a shirt as opposed to a hat when they signed up for our contest. There's a column here call shirt_or_hat so I'll write shirt_or_hat and if I press run, I don't get any results. That's because I'm still looking for NY which is a state but it's not a valid response for a shirt or hat.
So I'll change this to hat and run it again. And there we go, 570 rows were returned. We can start to see how helpful that might be if we actually want to use this data for something. But we still have a lot of fields here. Let's narrow it down a little bit by using just a few fields. I'll switch our condition to shirt and instead of star I'll say first_name and last_name and then I'll press run.
So here's a list of the people who had wanted a shirt. We can verify that by adding the shirt_or_hat field. This is getting a little bit long so I'll break it across two lines so we can see the whole query. And now if I run it, I can see that, in fact, all of the results that we have have shirt in that shirt_or_hat field. But we know that so we can remove this field from the query, if we're going to keep building on top of it or something. It's important to note that these clauses need to be in this order to work.
I'm telling the database what to give me, where to find it, and the condition for filtering the responses. If I were to say put the WHERE statement in front of the FROM statement it still kind of makes sense to a human. SELECT the first_name and last_name WHERE shirt_or_hat is shirt FROM people. We know what that means but the database doesn't and it gives us an error.
So I'll put that back in the correct order (typing and clicking) and once again it works. Take some time to explore different WHERE clauses and see how specific or general you can get.
- Name the predicate of the following statement: SELECT EyeColor, Age FROM Student WHERE FirstName = 'Tim' ORDER BY LastName ASC;
- Explain what to use to enforce the order in which an expression must be evaluated if the WHERE clause contains multiple expressions to evaluate.
- Identify the best option to join two tables in a database to be able to display data from both.
- List a data type that is not numeric.
- Determine the result of running the following statement on a table containing columns col_1 and col_2:
- INSERT INTO Box (col_1, col_2) VALUES ('A', 'B'), ('A', 'B'), ('A', 'B'), ('A', 'B');
- Determine the best approach of deleting Jon Ramirez (ID 3452) from a Student table.