[SQL Basic] What kind of data is in the ‘film’ table? — Learning SQL SELECT/FROM/WHERE

#3. Marketing/Data Analysis for Beginners

If you finished downloading SQLGate and connecting to the database already, it’s time to manage and find data. Let’s go over what we can do with the query SELECT, FROM, and WHERE.

All the examples here are based on the ‘sakila’ sample database that we have provided in step 7 of the previous post. So if you don’t know how to install SQLGate or connect to a database, please refer back to tutorial 02.

# Table of Contents

Query 1. What kind of data is in the ‘film’ table? Show me!
SELECT * FROM film;

Query 2. There are too many columns in here!!
Let’s only look at ID, title, rating, and the special features in the film table.

SELECT film_id, title, rating, special_features FROM film;

Query 3. Okay, now let’s narrow it down more.
Let’s only look at the film ID, title, rating, and special features of R-rated films.

SELECT film_id, title, rating, special_features FROM film WHERE rating='R';

# SELECT, FROM, WHERE

The most basic ‘Questions’ that we can ask in SQL are SELECT, FROM, and WHERE. They have an intuitive meaning, so it’s pretty easy to guess their function by just reading them. But just in case, let’s go over them quickly.

📌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

🔑 Starting now, we will refer to our ‘Questions’ as queries.

So let’s go over our first query.

Q1. What kind of data is in the ‘film’ table? Show me!

SELECT(the desired ‘columns’) FROM(in specific ‘table’)

This is the most basic query. We can start a conversation with any database
by just using this sentence.

For our first example, let’s pretend that online streaming doesn’t exist and we work at a movie store. We want to look at some information about movies we have in stock, and an easy way to do that is type some simple queries into our database. We’re going to use data in the ‘Film’ table inside the Sample Database sakila.

Applying this idea to Query 1, we can read it as “SELECT ‘columns’ FROM the ‘film’ table”. We don’t need the word “table” after the name of the table. SQL already knows that the word following the query FROM is going to be the name of a table. So just write FROM film.

If you want to see all data of every column available in the specified table at once, use the asterisk symbol ( * ). The * symbol here means all data of all columns. Let’s say we want to show all the columns containing information inside the film table. In SQL, this roughly translates to “SELECT(show) *(all columns containing information) FROM(inside the) film(table)”. Remove all the parenthesis and the text inside of them and you will get the simple SQL query:

SELECT * FROM film;

Make sure to use a semicolon ( ; ) at the end of the sentence to let SQL know that this is the end of your query and you are ready to see the results.

✦ Click ‘Run’ at the top left to try your query (or press F5).
You should see something similar to the image shown below.

The result is a ton of columns loaded with information about movies!

But let’s say we want to only see information about the film itself without the extra clutter of the rental rate or duration of the movie.

Q2. There are too many columns in here!! Let’s only look at ID, title, rating, and the special features in the film table.

SELECT(specific desired ‘column’s) FROM(in specific ‘table’)

To do so, we’re going to focus on the columns film ID, title, rating, and special features. Just plug in the exact name of the column you want after SELECT to filter the information. In this case, you can use this:

SELECT film_id, title, rating, special_features FROM film;

✦ Click ‘Run’ at the top left to try your query (or press F5).
You should see something similar to the image shown below.

Q3. Okay, now let’s narrow it down more. Let’s only look at the film ID, title, rating, and special features of R-rated films.

SELECT(show this ‘column’) FROM(specific ‘table’) WHERE(meets condition)

If condition is number: WHERE film_id=5
If condition is character: WHERE rating=‘PG’ — (Add single quotations!!)

It’s good to see specific data, but let’s narrow it down some more. Let’s say we only want information about movies that are a specific rating. To do this, we need to start using the SQL query WHERE.

Just add WHERE to the end of your query following your desired rating. In this example, we are going to look up R-rated movies. Because, why not?

Plug in WHERE rating=‘R’ at the end of the statement. Notice here that we’ve added single quotation marks around the letter R ( ‘ ’ ). This is because SQL needs this extra bracket when the condition is not a number. There are ways around this, but for beginners it’s good to have a solid foundation. So let’s use those single quotations! Your final query should look something like this:

SELECT film_id, title, rating, special_features FROM film WHERE rating=‘R’;

✦ Remember to click ‘Run’ at the top left to run your query (or press F5).

🔑 Notice how while in our text examples we keep all our queries in one line, the example images display the queries on different lines. Both ways are fine! Some users like to use the Enter key to spread out their queries to keep it clean and easily be able to track the different queries. Some users like to keep everything tidy and on the same line. Since we use the semicolon to tell the database that we are closing the statement, hitting the Enter key doesn’t matter. Choose the style that best fits you!

# Practice Time

Let’s try using SELECT, FROM, and WHERE in an example scenario.

📰 A customer at your movie store approaches you and asks for a list of all the films that have a rental duration of 3 days. She’s also curious about the price (rental rate). How can you look up this information? Don’t forget to also search for the ID number so you can easily find the movies on the shelves.

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

If you need any help, don’t hesitate to and scroll down to the example answer we’ve provided for you. 😄

✦ Remember to click ‘Run’ at the top left to run your query (or press F5).

Did you get it?

Here’s the answer:

SELECT film_id, title, rental_rate, rental_duration FROM film WHERE rental_duration=3;

Good job! How was it? Not too difficult, right?

# Tutorial Video

The answer of the practice question is shown in this tutorial video. SELECT/FROM/WHERE are the most frequently using queries in basic SQL, so understanding how to use these queries is key.

😃 We hope you enjoyed this tutorial! We’ll 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!