Sometimes you need to use more than one SELECT statement to get what you need. Find out how to make a subselect.
- Sometimes, we need to use more than one SELECT statement to get the information we want. We can always set up a WHERE clause in the statement, otherwise. Using a secondary SELECT statement is called a sub-query or a sub-select and it helps us to narrow down or zero in on a specific set of data to use in a larger query. Let's get a listing of all the people in our database who achieved the highest score on our quiz. It may make sense to write; SELECT first_name, last_name, quiz_points.
WHERE quiz_points equals max quiz_points but when I run that, I have an error; a misuse of aggregate function max. This function like the other aggregate functions; mean, average and so on need to be part of a SELECT clause in order to work so instead of just max here, I will remove that for now. And inside of parentheses to keep it separate, I'll write a new SELECT statement.
SELECT MAX quiz_points FROM people. This statement will give us back one value; the highest number in the quiz points column and then in our outer SELECT statement, we'll have one value to look for in the WHERE clause. There we go. Of course, we could write this statement to just look for people where their quiz points equal 100 but using a secondary SELECT statement, lets us not have to know what specific values are in the data.
That makes the statement portable, maybe for another quiz that goes up to 200 points instead of just 100. We wouldn't want to have the wrong value in there and assume that a hundred will always be correct. We can also use a sub-query to use information from one table to inform a search on another table. Let's say you want to find all of the participants form Minnesota but you can never remember what the state abbreviation for Minnesota is. Is it Mi, Ma, Mn? There're so many that are so similar.
Instead of specifying a state abbreviation, let's look up the name Minnesota in the states' table, get us abbreviation and then use that to look up people. To do that, I'll write SELECT star FROM people. WHERE state equals, and a set of parentheses from my sub-query, SELECT state abbreviation, FROM states, WHERE state name equals Minnesota.
I'll be careful to double-check my parentheses here. I got an extra one and I'll run that. There we go. Without knowing the exact value for the state abbreviation which is Mn, we used a sub-query to look it up first and then we used that result to get what we needed.
- 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.