[SQL Basic] Can I rent a 3-hour movie that’s only $0.99? — Using SQL AND/OR/IN

#5. Marketing/Data Analysis for Beginners

If you’ve been following our tutorials, you have a good grasp of some basic SQL queries. So first, let’s review!

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

📌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

This time, we’re going to cover 3 new queries. AND, OR, and IN.

Okay, let’s go back to our film table. We have tons of information in it: the movie title, release year, languages it’s available in, rental duration, price, rating, special features, and even the replacement cost if a customer doesn’t return the movie (remember, we’re still renting out movies in this example and streaming doesn’t exist… 😆)

Let’s say we want to look up all the movies that have two or more of those factors in common. How can we do that?

# Table of Contents

Query 1: Finding $0.99 films that are about 3 hours long

SELECT film_id, title, rental_rate, length FROM film WHERE rental_rate=0.99 AND length>180;

Query 2: Finding addresses in Texas or California in the Address Table

SELECT address_id, address, district, city_id FROM address WHERE district=‘Texas’ OR district=‘California’

SELECT address_id, address, district, city_id FROM address WHERE district IN (‘Texas’, ‘California’)

# AND/OR/IN

First on our list is the query AND. It’s self-explanatory, but let’s put a definition to it for the sake of our building glossary.

📌AND: find information that matches 2 specific categories

Q1. Finding $0.99 films over 3 hours long (film_id, title, rental_rate, length)

Use AND to satisfy 2 conditions at the same time
SELECT film_id, title, rental_rate, length FROM film WHERE rental_rate=0.99 AND length>180;

Let’s say we want to check the price of the movies in our stock. Our query might look something like: SELECT film_id, title, rental_rate FROM film;
We can narrow this down to focus only on movies that cost 99 cents by adding WHERE rental_rate=0.99.

SELECT film_id, title, rental_rate FROM film WHERE rental_rate=0.99;

This will give you a nice, tidy list of all the movies that are $0.99. But what if you want to see movies that cost 0.99 and they’re about 3 hours long? Easy! Just add AND.

SELECT film_id, title, rental_rate, length FROM film WHERE rental_rate=0.99 AND length>180;

Make sure to add “length” to your SELECT line to be able to see that information.
🔑 Notice how we didn’t write “length=3 hours”. SQL won’t understand that because that’s not how the information was input into our table. Computers process numbers much easier, so we entered the time in minutes. We also want to avoid the equal ( = ) sign here. How many movies do you know that are exactly 180 minutes? That’s why we used the less-than sign ( > ) instead. This will give us a better estimate.

✦ Click ‘Run’ to start the query (or press F5).

Tada! Here are all the movies that are about 3 hours long and cost only $0.99!

Q2: Finding addresses in Texas or California in the Address Table
(address_id, address, district, city_id)

OR: Satisfying one condition at least (conditions can be located in different columns)
SELECT address_id, address, district, city_id FROM address WHERE district=‘Texas’ OR district=‘California’

Next on our list is the query OR. It has the same structure but a very different meaning.

📌OR: find all information that matches at least 1 specified category

Let’s take a break from the film table and check out the address table. Try this query and take a look at the new content: SELECT * FROM address;

The results aren’t too complicated. We only have 9 columns this time:
address ID, address, address2, district, city ID, postal code, phone number, location and last update.

If we want to look up the addresses located in Texas as well as the addresses located in California, how could we structure the query? First, we need to specify what we want to see.

Let’s start with:
SELECT address_id, address, district, city_id FROM address;

We can use WHERE to find addresses in a specific district. Your first instinct might be to write WHERE district=‘Texas’ AND district=‘California’; but as we already covered, AND means that both conditions must be met equally. That means SQL will think you’re searching for an address that is located both in Texas and in California. Obviously, you won’t get any results. So we’re going to use the queryOR!

SELECT address_id, address, district, city_id FROM address WHERE district=‘Texas’ OR district=‘California’;

⚠️ Remember to use single quotations around the district names to tell SQL that we are not dealing with numbers. ( ‘ ’ )
✦ Click ‘Run’ to start the query (or press F5).

Another way to find this information is by using the IN query. Using OR could make your query long and complicated. For example, if you want to look up the addresses located in Texas, California, Seoul, and Japan you need to type out “district=” 4 times.

We’re here to make things easier for you. Let’s use IN.

📌IN: find information that matches any of the following categories

IN: Satisfying one condition at least (conditions are located in same columns) ex.) WHERE ‘column’ IN (condition 1, condition 2, condition 3, …)

SELECT address_id, address, district, city_id FROM address WHERE district IN (‘Texas’, ‘California’)

This query is going to be very similar to our last one. Start with SELECT address_id, address, district, city_id FROM address WHERE district like we did with OR. But instead of writing “district=” we’re going to make the search more broad. Erase the equal sign ( = ) and write IN followed by the locations you want. It’ll look like this:

SELECT address_id, address, district, city_id FROM address WHERE district IN (‘Texas’, ‘California’);

⚠️ We still need to use single quotations around the district names to tell SQL that we are not dealing with numbers. ( ‘ ’ )
✦ Click ‘Run’ to start the query (or press F5).

You’ll get the same results in a much cleaner query. It should look like this:

🔑 If you run both queries (making sure to end both with a ; ) then you will get both results! SQLGate will just open another window with the results of the second query. Cool, right?

# Practice Time

Time for another scenario!

📰 We’re back in the movie store, and you’re working diligently behind your computer. In fact, you’re working so hard that you don’t notice the customer talking to you until he has repeated his question a third time. He’s asking you to tell him the names of some movies that he can rent — but he has a rather interesting condition. The movie rental period of the movie must be less than 3 days or more than 5 days. How can you look up this information?

🔑 We’re working with films again, so remember to go back to the film table. Definitely look up the movie name, price, and rental period. Maybe finding the ID is also a good idea so you can find it easily on the shelves.

👍Take a moment to type out your own query using what we learned today.

🔑 Did you start with SELECT film_id, title, rental_rate, rental_duration FROM film;? Good. Now how can you use WHERE and IN to get the duration the customer specified?

👍Try on your own before looking at the answer!

🔑 If using IN isn’t giving you a clean, clear-to-read query, try NOT IN. And how about throwing in ORDER BY to show the results in order of rental period and price (rental rate)?

Here is the answer! Take a look!

SELECT film_id, title, rental_rate, rental_duration FROM film WHERE rental_duration NOT IN (3,4,5) ORDER BY rental_duration, rental_rate ASC;

Here we used ‘NOT IN’ to exclude the days our customer didn’t want. While IN is used for satisfying at least one condition, we can use NOT IN to exclude the ones you don’t want. So instead of writing IN (1,2,6,7,8,9,10,11…) we can just write NOT IN (3,4,5) to make life easier.

🔑 Bonus Tip

When you write multiple column names after ORDER BY, the result will be sorted by the order in which you wrote the query. In our example, the data was sorted by rental_duration first and rental_rate second (because we wrote it that way). As mentioned before, ORDER BY sorts data in ascending order by default so you don’t need to write ASC… but it’s good practice!

If you’re feeling lost, try following along in our tutorial video linked below:

# Tutorial Video — SQL AND/OR/IN

# [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!