[SQL Basic] Let’s see some Sample Database tables!

#8. Marketing/Data Analysis for Beginners

We’re halfway through the basic SQL tutorials now, so let’s take a break from learning SQL and take a closer look at our database tables. Even though we’ve dabbled in other tables, our main focus has been on the film table so far. In today’s lesson, we will take a closer look at our sample database tables for a better understanding.

So far we have used five tables: film, customer, address, country, and city. In the sakila database, there are a total of 16 tables. You can check them out on Object Explorer and Object panel in SQLGate.

🔑 If you closed this window or can’t find it, open it by going to the top menu:
View → Object Explorer.

As explained in our first tutorial, each table has columns and data that belongs to it. Let’s review the tables we’ve used so far!

#1. Film Table

View the film table by opening the table drop-down list (click the +) and double click on the film table.

There are 13 columns available in the film table: film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, and last_update.

We’ve mainly used film_id, title, description, rental_duration, and rental_rate. Let’s take a look at the database more closely by clicking the Data tab at the top (between the Column and Index tabs). If it’s not checked, click on the box next to Limit inside your Data tab. Set the limit to 100
and click Apply.

Here, 0 means the offset and 100 means the number of data you want to see.

#2. Customer Table

The customer table was used in the 4th tutorial in the Practice Time example. The number of columns is smaller and easier to navigate than the film table. There are only 9 columns: customer_id, store_id, first_name, last_name, email, address_id, active, create_date and last_update.

The address_id column is also in the address table.

#3. Address Table

We used the address table in the 5th tutorial and the 7th tutorial. This table has 9 columns as well: address_id, address, address2, district, city_id, postal_code, phone, location, and last_update. The address_id column links the customer and address tables together.

#4. Country Table

The country table is also covered in the 5th tutorial. This table only has 3 columns: country_id, country, and last_update.

The country_id column will appear again in the city table.

#5. City Table

The city table is also covered in the 5th tutorial just like our country table. There are a total of 4 columns here: city_id, city, country_id, and last_update. We can see the city_id column that appeared in the customer and address tables, connecting this table’s information to that one. The country_id column is connected to the country table.

As you can see, there are many columns that connect all the tables to each other (excluding the film table). Now that we have a good understanding of what’s inside each table, we can learn the function of joining these tables together. It will be easy to connect and use all the information if you know which columns are connecting which tables.

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