[SQL Basic] How can I find a movie that’s thrilling? — Using SQL LIKE
#6. Marketing/Data Analysis for Beginners
Let’s go over what we’ve learned so far.
Lesson 3:
📌SELECT: you’re asking the database to show you something
📌FROM: you’re giving the database a location to search
📌WHERE: you’re narrowing down/specifying the location
Lesson 4:
📌ORDER BY: you’re asking for a specific order for the list to be displayed
Lesson 5:
📌AND: find information that matches 2 specific categories
📌OR: find all information that matches at least 1 specified category
📌IN: find information that matches any of the following categories
The next query on our list is LIKE. This is used for finding patterns when we don’t know the exact data.
# Table of Contents
Query 1: How can I find a movie that’s thrilling? Finding thrillers through the film description.
SELECT title, description FROM film WHERE description LIKE ‘%thrilling%’#LIKE
Q1. How can I find a movie that’s thrilling? Finding thrillers through the film description.
Let’s say you’re browsing the sakila database and you want to look up some information about the cities in the database. You can start with:SELECT * FROM `sakila`.`city`;
If you wanted to just look up a specific city, like New Delhi, you could add WHERE city=‘New Delhi’ to find information on that city.
But let’s say you don’t quite remember the name of the city. You know it has the word “New” in it, but you’re not sure of the second half. In cases like this, we need the query LIKE.
📌LIKE: find a specific pattern in a column
We like movies. Don’t you? Let’s go back to our film table. We want to look up all the movies in our database in the thriller genre. 🎥
If there was a column named “GENRE” we could just use the WHERE query. But in this database, the genre of the movie is in the description column. So we need to get creative. Let’s change “Thriller” into “thrilling” and search the descriptions. Try writing “title, description” after SELECT and ‘film’ after FROM, like this:
SELECT title, description FROM film WHERE description LIKE thrilling;
✦ Hit F5 to run your query.
⛔️STOP! You’re going to get an error. This is because we didn’t put the single quotes ( ‘ ’ ) around thrilling. But even when we do, you won’t get any results. Why, you ask?
🔑 In SQL, the percentage sign ( % ) and underscore ( _ ) are called Wildcard characters.
📨 % is used when the number of characters/words that surround the term you’re searching doesn’t matter.
📩 _ is for selecting something exact, and you want an exact match.
For this example, we need %. So let’s try it again:
SELECT title, description FROM film WHERE description LIKE ‘%thrilling%’;
✦ Hit F5 to run your query.
Nice!
Here are some examples of the wildcards.
[whatever→ ex]LIKE ‘whatever%’:ex%→ example (Start with ‘ex’)LIKE ‘% whatever’:%ex→ complex (End with ‘ex’)LIKE ‘%whatever%’:%ex%→ lexicon (Include ‘ex’)LIKE ‘whatever_’:‘ex__’→ exit (Start with ‘ex’)
🔑 LIKE checks every data for the ‘whatever’ that you’re searching for, so it can take a lot of time when there is a lot of data. If the amount of data is heavy, it’s better to avoid the LIKE query.
# Practice Time
📰 Today’s example scenario requires you to apply previously learned queries in various tables. We want to find the addresses of all our customers that live in South Korea. We’ll be using three different tables to do this: country, city, and address. We’ve mostly been using the film table until now, so it might be hard to find data in other tables. Let’s try this query first:
SELECT country_id, country FROM country;
First, let’s find the country_id of South Korea in the country table. We don’t want to scroll through all this new information to find it, so let’s try a query. A good place to start is to search for a pattern, so let’s try plugging in WHERE country LIKE ‘%korea%’ because there may be many values that contain the word ‘Korea’.
SELECT country_id, country FROM country WHERE country LIKE ‘%korea%’;
Once you run this query you’ll see there are two results: South Korea and North Korea. The one we want is South Korea, so make note of the ID.
Now that we have the ID number of South Korea (country_id=86) we can go to the next step. This time we will look at the cities of this country in the city table. Add city to the SELECT query and change the table name for the FROM query to city. We’re also going to keep using the WHERE query, so go ahead and plug in the country ID at the end.
SELECT city_id, city, country_id FROM city WHERE country_id=86;
In the results, you’ll see a total of 5 values. You can get the city name and number (113, 268, 357, 539, 553) of each city from this new query result.
Now let’s look at the addresses that we want to find. We want to view the address ID, address, district and city ID so designate that in your SELECT query. We’ve also moved over to the address table now, so make sure to change that in your FROM query.
In our first step we found the country code for South Korea (86) and we used that in the second step to find city ID numbers. So in the address table, you only need to search for the addresses corresponding to these city IDs. An easy way to use them is to combine WHERE and IN, as we just covered in this tutorial.
SELECT address_id, address, district, city_id FROM address WHERE city_id IN (113, 268, 357, 539, 553);
There! Now we have the addresses of all customers in South Korea. Below we’ve provided an image of all these queries together. You can run them at the same time to compare the information.
#Quick Look
SELECT country_id, country FROM country WHERE country LIKE ‘%korea%’;SELECT city_id, city, country_id FROM city WHERE country_id=86;SELECT address_id, address, district, city_id FROM address WHERE city_id IN (113, 268, 357, 539, 553);If you’re feeling a little lost, follow along in our video tutorial.
# Video Tutorial
😃 We hope you enjoyed this tutorial! See you next time~
# [SQL Basic Series] — Marketing/Data Analysis for Beginners
1. What is SQL? — Database Introduction
2. Downloading SQLGate and Connecting to a Database
3. What kind of data is in the ‘film’ table? — SELECT/FROM/WHERE
4. How to sort film lists by price (rental rate)? — ORDER BY
5. Can I rent a 3 hour movie for $0.99? — AND/OR/IN
6. How can I find a movie that’s thrilling? — LIKE
7. How to rename columns and see unique values? — AS/DISTINCT/IS NULL
8. Let’s see Sample Database tables!
9. What is the total length of all the films added together? — COUNT/SUM
10. What is the average film length? — AVG/MIN/MAX
11. How many movies have the same rating? — GROUP BY/HAVING
12. Let’s designate a value based on the rental duration. — CASE
13. Let’s find out the movie genres by joining tables! — INNER JOIN
14. Let’s check movie rentals by the inventory list! — LEFT/RIGHT JOIN
15. Let’s find customers that have rented movies priced $9.99! — Subquery
16. How to work with Date Functions in SQL — DATE_FORMAT, DATEDIFF
17. How to work with String Functions in SQL — CONCAT, LENGTH, SUBSTR
18. Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD
19. Let’s learn some advanced CASE queries!
20. Let’s learn some advanced JOIN queries!