[SQL Basic] How to sort film lists by price (rental rate)? — Using the SQL query ORDER BY

#4. Marketing/Data Analysis for Beginners

Now that we learned how to use SELECT, FROM, and WHERE in SQL, we can delve deeper into the world of marketing and data analysis. Next, we will sort the results by using another query: ORDER BY.

# Table of Contents

Query 1: How can I sort the film lists by price?

SELECT film_id, title, rental_rate, rental_duration FROM film WHERE rental_duration=5 ORDER BY rental_rate DESC;

# ORDER BY

ORDER BY sorts the result in a specified order. It’s another query that has a direct meaning, but let’s break it down while we review the other queries.

📌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

🆕 🌟
📌ORDER BY: you’re asking for a specific order for the list to be displayed in

#ORDER BY

Q1: How can I sort the film lists by price?

ORDER BY (Sort result by ‘column’)

For our first example, let’s search for movies by film id, title, rental rate, and a rental duration of 5 days. Remember, we’re using the sample database sakila. If you forgot how to connect to this database, check out this tutorial.

So let’s review what information we have inside this database. Remember the basic queries SELECT and FROM? Plug those into your SQL and you should get a list like the image displayed below.

🔑 While SELECT * FROM film; is the basic query we went over, this only works if you’re connected to a single database. Let’s say you have 2 databases, and they both have a table named film. What do you do now? Easy! Tell SQL that you only want it to look at the film table in the sakila database. You can do this by running the query SELECT * FROM `sakila`.`film`;

⚠️ When you do this, be careful not to use the single quote ( ‘ ). You need the back quote ( ` ) which is on your tilde key (~).

✦ Click ‘Run’ at the top left to try your query (or press F5).

Whoa, that’s a lot of information. Right now the factors we want to focus on are film ID, title, rental rate and rental duration. So let’s tell SQL that we want those specific factors by writing the column names after SELECT:

SELECT film_id, title, rental_rate, rental_duration FROM film;

Let’s filter it further by looking at movies with a 5-day rental period.
Plug in WHERE rental_duration=5 at the end of your query.

Wait! Before you run the query, let’s go one more step and ask SQL to change the order of the list for us. Plug in ORDER BY and add “rental_rate”. You can also tell SQL if you want the list to be in ascending or descending order.
SQL will use ascending by default, but you can use these abbreviations to easily specify what you want.

📌ASC: ascending order
📌DESC: descending order

SELECT film_id, title, rental_rate, rental_duration FROM film WHERE rental_duration=5 ORDER BY rental_rate DESC;

✦ Click ‘Run’ at the top left to try your query (or press F5).

You can also use the column number instead of the column name. For example, the “rental rate” column is the 3rd column (because we wrote it 3rd in our query). Instead of typing out the full name, we can just write
ORDER BY 3; to specify the 3rd column. Try this:

SELECT film_id, title, rental_rate, rental_duration FROM film WHERE rental_duration=5 ORDER BY 3 ASC;

How was it? Are you ready to put what you learned to the test?

# Practice Time

📰 Your coworker is having problems finding some information about a customer. He asks you for help, since you’re a master at navigating databases already. What he needs is a list of all the customer names in the first store location, including their email addresses. He adds that it would be best to have them in alphabetical order by their first name, so it will be easier to sort through them. How do you look up the list?

🔑 A good place to start would be looking up customer_id, store_id, first_name, last_name and email. Keep in mind that we’re not working with films anymore, so FROM film is not the location we need. We’re looking up customers. Maybe… FROM customer?

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

🔑 Did you already try SELECT customer_id, store_id, first_name, last_name, email FROM customer;? Did you notice how there are 2 store IDs? Your coworker only asked for the first store! So the store ID you want is 1. Specify that with the WHERE query.

👍 Try it on your own before looking at the answer!

🔑 We hope you didn’t forget to use the ORDER BY query! Your coworker asked for the first names to be in alphabetical order!

Here’s the answer. Take a look!

SELECT customer_id, store_id, first_name, last_name, email FROM customer WHERE store_id=1 ORDER BY first_name;

Your results should look similar to the image displayed below:

Don’t forget that an alternative is using the number of the column instead of the name. In this case, the “first name” information is in the 3rd column (because we wrote it 3rd in our query) so we can use the number 3.

[Order by Column Number] — You’ll get the Same Result

Column Name :
SELECT customer_id, store_id, first_name, last_name, email FROM customer WHERE store_id=1 ORDER BY first_name;

Column Number:
SELECT customer_id, store_id, first_name, last_name, email FROM customer WHERE store_id=1 ORDER BY 3;

Feeling a bit lost? Try following along in the tutorial video provided below:

# Tutorial Video

Things can get complicated when you can’t organize all the information presented in tables, so using ORDER BY can really come in handy to sort through it all.

😃 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!