Often, you need to ask for data that meets more than one criterion. Explore logic in a SELECT statement.
- [Instructor] When we're asking for a set of rows from a database, we can pretty easily find values that match a term exactly. All of the people who signed up to get shirts, or all of the people in California. But we can add a little bit of logic to our statements too, in order to start asking more interesting questions about the data. For example, we can ask for the names of everyone who is in California and who asked for a shirt. For that I'll write select, first name, last name.
From our people table, where state equals California, and, shirt or hat equals shirt. This and term here is a logical operator, and SQL supports some of them as part of a predicate. You can chain them together if you want to ask for records that have more conditions fulfilled. I'll run this, and I can see the result.
I'll add the team field here into my select clause. And then in the where clause, I'll add, and team equals blue. I can search for people in California who wanted a shirt who signed up for the blue team. We could also take this last condition here and use the operator is, to replace the equals operator.
And that's the same result. Or we could switch out is to is not, and get the records that have anything except blue in the team field. If you're used to working with other programming languages, you might recognize that exclamation equals also means not equal to. So we could substitute that in here as well. And we get the same result. Rather than asking for many conditions that are all true, I could use the or operator to tell the engine that I want results where one of two conditions is true.
Let's say we want to see everyone who wanted a shirt but was in California or Colorado. I'll erase my statement and write select, first name, last name, from people, where state equals California or state equals Colorado.
And, shirt or hat equals shirt. Let's add shirt or hat into the select clause along with the state, so we can see what's going on. Running the query, I can see that I'm not getting back what I expected.
Here's some results that are hat, even though I'm asking for shirt or hat equals shirt. Why is that? The engine is interpreting each of my conditions as it goes along, so it's giving me records where the state is California, or the state is Colorado, and also the records where shirt or hat is shirt. It's paired those last two conditions together, so we see every record where California is the state, in addition to records where the state is Colorado and the shirt or hat field is shirt.
It's considering this compound term together as one term. If we added more conditions in here, we'd be getting the last two as a compound condition. What we want to see is the state being either California or Colorado, and within that selection, only the records where shirt or hat is shirt. So we need to add some parentheses here, around the state selections, to make it clear that we want to get back either California or Colorado, and then ask, within that set, for records that match shirt.
There we go. Now, all of the shirt or hat fields say shirt. That's what I'm looking for. Parentheses are a really useful tool for shaping the meaning of your question for the database. You can use more than one set if your query is extra complicated too. Incorrect grouping is a common mistake in writing statements, and it can be a kind of subtle thing to troubleshoot.
- 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.