It can be helpful to specify how many results you want to see. Learn to do this with the LIMIT keyword.
- [Instructor] There's another operator we can use in our queries too which lets us be a little bit less specific and lets us look for values that match some text called the LIKE operator. If we wanted to say give me all of the records whose state starts with the letter C, we could write state equals CA, state equals CO, state equals CT, and so on. Or we could say state like C%, saying match the letter C and then whatever is after that is fine. We just care about matching the first character. To write that, I'll write SELECT first_name, last_name, state WHERE state LIKE, and a single quote, and in this case, C%, and then a closing single quote.
Running this, I can see that it's matched the state where the C is at the beginning, and then whatever comes after it is fine. Here's California, Colorado, Connecticut, and so on. Or we could change that around and put N first. Or we could put the percent at the beginning to match any state ending with N.
Or we could say give me all of the first names that start with A, or that start with J. You can use more than one percent sign as well, if you want to match the text somewhere in the middle of a field. I'll change this to %J%, and I see that I have 147 rows. This is a little bit more subtle.
I can see that Benjamin snuck in here somewhere. Where is it? There it is. Switch it out for some other text, and you can see how it works. For example, on or ch. And we've got names that match that somewhere in the text. We could take a look at the company field instead, and ask the database to give us everything that ends with LLC, though it would be helpful to see what those company names are.
We can also tell the database how many responses we'd like to see with the LIMIT keyword. LIMIT tells the database to stop returning results after a given number of them have met our search criteria. So if I only wanted to see the first five results on this last query, I can do that with LIMIT 5, or I can say the first 10. I'll change the five to a 10 and run that, and I see I've only got 10 responses.
If wanted to see a specific range of them, like the second set of five responses, these here, Antonio West through Rachel Robinson, I can use the OFFSET command to tell the database to skip some records before counting off my five. I'll add OFFSET 5 to the end of my limit clause, and here I can see that the first record is Antonio West which was the sixth record before.
So we've skipped five rows and now I have 10 results starting from that point. The LIMIT keyword can be useful in conjunction with some other tests where a value is involved, as we'll see in the video about math, or when interacting with a very large database. Take some time to write your own queries with different criteria to explore the data.
- 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.