#}
actor
Schema:
col_name | col_type
-------------+--------------------------
actor_id | integer
first_name | text
last_name | text
Sample data:
| actor_id | first_name | last_name |
|---|---|---|
| 1 | PENELOPE | GUINESS |
| 2 | NICK | WAHLBERG |
| 3 | ED | CHASE |
| 4 | JENNIFER | DAVIS |
| 5 | JOHNNY | LOLLOBRIGIDA |
#}
actor_movie
Actors who appeared in a movie.
Schema:
col_name | col_type
------------+-------------------
actor_id | integer
first_name | character varying
last_name | character varying
Sample data:
| actor_id | first_name | last_name |
|---|---|---|
| 1 | PENELOPE | GUINESS |
| 3 | ED | CHASE |
| 4 | JENNIFER | DAVIS |
| 7 | GRACE | MOSTEL |
| 11 | ZERO | CAGE |
#}
actor_tv
Actors who appeared in a TV show.
Schema:
col_name | col_type
------------+-------------------
actor_id | integer
first_name | character varying
last_name | character varying
Sample data:
| actor_id | first_name | last_name |
|---|---|---|
| 1 | PENELOPE | GUINESS |
| 2 | NICK | WAHLBERG |
| 4 | JENNIFER | DAVIS |
| 5 | JOHNNY | LOLLOBRIGIDA |
| 6 | BETTE | NICHOLSON |
#}
address
Schema:
col_name | col_type
-------------+--------------------------
address_id | integer
address | text
address2 | text
district | text
city_id | smallint
postal_code | text
phone | text
Sample data:
| address_id | address | address2 | district | city_id | postal_code | phone | last_update |
|---|---|---|---|---|---|---|---|
| 1 | 47 MySakila Drive | NULL | Alberta | 300 | 2017-02-15 09:45:30-08 | ||
| 2 | 28 MySQL Boulevard | NULL | QLD | 576 | 2017-02-15 09:45:30-08 | ||
| 3 | 23 Workhaven Lane | NULL | Alberta | 300 | 14033335568 | 2017-02-15 09:45:30-08 | |
| 4 | 1411 Lillydale Drive | NULL | QLD | 576 | 6172235589 | 2017-02-15 09:45:30-08 | |
| 5 | 1913 Hanoi Way | Nagasaki | 463 | 35200 | 28303384290 | 2017-02-15 09:45:30-08 |
#}
category
Movie categories.
Schema:
col_name | col_type
-------------+--------------------------
category_id | integer
name | text
Sample data:
| category_id | name | last_update |
|---|---|---|
| 1 | Action | 2017-02-15 09:46:27-08 |
| 2 | Animation | 2017-02-15 09:46:27-08 |
| 3 | Children | 2017-02-15 09:46:27-08 |
| 4 | Classics | 2017-02-15 09:46:27-08 |
| 5 | Comedy | 2017-02-15 09:46:27-08 |
#}
city
Schema:
col_name | col_type
-------------+--------------------------
city_id | integer
city | text
country_id | smallint
Sample data:
| city_id | city | country_id | last_update |
|---|---|---|---|
| 1 | A Corua (La Corua) | 87 | 2017-02-15 09:45:25-08 |
| 2 | Abha | 82 | 2017-02-15 09:45:25-08 |
| 3 | Abu Dhabi | 101 | 2017-02-15 09:45:25-08 |
| 4 | Acua | 60 | 2017-02-15 09:45:25-08 |
| 5 | Adana | 97 | 2017-02-15 09:45:25-08 |
#}
customer
Schema:
col_name | col_type
-------------+--------------------------
customer_id | integer
store_id | smallint
first_name | text
last_name | text
email | text
address_id | smallint
activebool | boolean
create_date | date
active | integer
Sample data:
| customer_id | store_id | first_name | last_name | address_id | activebool | create_date | last_update | active | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | MARY | SMITH | [email protected] | 5 | 1 | 2017-02-14 | 2017-02-15 09:57:20-08 | 1 |
| 2 | 1 | PATRICIA | JOHNSON | [email protected] | 6 | 1 | 2017-02-14 | 2017-02-15 09:57:20-08 | 1 |
| 3 | 1 | LINDA | WILLIAMS | [email protected] | 7 | 1 | 2017-02-14 | 2017-02-15 09:57:20-08 | 1 |
| 4 | 2 | BARBARA | JONES | [email protected] | 8 | 1 | 2017-02-14 | 2017-02-15 09:57:20-08 | 1 |
| 5 | 1 | ELIZABETH | BROWN | [email protected] | 9 | 1 | 2017-02-14 | 2017-02-15 09:57:20-08 | 1 |
#}
dates
Calendar dates from 01/01/2019 to 12/31/2025.
Schema:
col_name | col_type
----------+----------
year | smallint
month | smallint
date | date
Sample data:
| year | month | date |
|---|---|---|
| 2019 | 1 | 2019-01-01 |
| 2019 | 1 | 2019-01-02 |
| 2019 | 1 | 2019-01-03 |
| 2019 | 1 | 2019-01-04 |
| 2019 | 1 | 2019-01-05 |
#}
film
Schema:
col_name | col_type
----------------------+--------------------------
film_id | integer
title | text
description | text
release_year | integer
language_id | smallint
original_language_id | smallint
rental_duration | smallint
rental_rate | numeric
length | smallint
replacement_cost | numeric
rating | text
Sample data:
| film_id | title | description | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | last_update |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies | 2006 | 1 | NULL | 6 | 0.99 | 86 | 20.99 | PG | 2017-09-10 17:46:03.905795-07 |
| 2 | ACE GOLDFINGER | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China | 2006 | 1 | NULL | 3 | 4.99 | 48 | 12.99 | G | 2017-09-10 17:46:03.905795-07 |
#}
film_actor
Films and their casts
Schema:
col_name | col_type
-------------+--------------------------
actor_id | smallint
film_id | smallint
Sample data:
| actor_id | film_id | last_update |
|---|---|---|
| 1 | 1 | 2017-02-15 10:05:03-08 |
| 1 | 23 | 2017-02-15 10:05:03-08 |
| 1 | 25 | 2017-02-15 10:05:03-08 |
| 1 | 106 | 2017-02-15 10:05:03-08 |
| 1 | 140 | 2017-02-15 10:05:03-08 |
#}
film_category
A film can only belong to one category
Schema:
col_name | col_type
-------------+--------------------------
film_id | smallint
category_id | smallint
Sample data:
| film_id | category_id | last_update |
|---|---|---|
| 1 | 6 | 2017-02-15 10:07:09-08 |
| 2 | 11 | 2017-02-15 10:07:09-08 |
| 3 | 6 | 2017-02-15 10:07:09-08 |
| 4 | 11 | 2017-02-15 10:07:09-08 |
| 5 | 8 | 2017-02-15 10:07:09-08 |
#}
inventory
Each row is unique, inventoy_id is the primary key of this table.
Schema:
col_name | col_type
--------------+--------------------------
inventory_id | integer
film_id | smallint
store_id | smallint
Sample data:
| inventory_id | film_id | store_id | last_update |
|---|---|---|---|
| 1 | 1 | 1 | 2017-02-15 10:09:17-08 |
| 2 | 1 | 1 | 2017-02-15 10:09:17-08 |
| 3 | 1 | 1 | 2017-02-15 10:09:17-08 |
| 4 | 1 | 1 | 2017-02-15 10:09:17-08 |
| 5 | 1 | 2 | 2017-02-15 10:09:17-08 |
#}
payment
Movie rental payment transactions table
Schema:
col_name | col_type
--------------+--------------------------
payment_id | integer
customer_id | smallint
staff_id | smallint
rental_id | integer
amount | numeric
payment_ts | timestamp with time zone
Sample data:
| payment_id | customer_id | staff_id | rental_id | amount | payment_ts |
|---|---|---|---|---|---|
| 16077 | 279 | 2 | 1019 | 0.99 | 2020-05-31 03:05:07-07 |
| 16078 | 280 | 1 | 1014 | 4.99 | 2020-05-31 02:39:16-07 |
| 16079 | 281 | 2 | 650 | 2.99 | 2020-05-28 19:45:40-07 |
| 16080 | 281 | 2 | 754 | 2.99 | 2020-05-29 10:18:59-07 |
| 16081 | 282 | 2 | 48 | 1.99 | 2020-05-25 06:20:46-07 |
#}
rental
Schema:
col_name | col_type
--------------+--------------------------
rental_id | integer
rental_ts | timestamp with time zone
inventory_id | integer
customer_id | smallint
return_ts | timestamp with time zone
staff_id | smallint
Sample data:
| rental_id | rental_ts | inventory_id | customer_id | return_ts | staff_id |
|---|---|---|---|---|---|
| 11909 | 2020-02-14 15:16:03-08 | 871 | 474 | NULL | 1 |
| 12222 | 2020-02-14 15:16:03-08 | 3949 | 22 | NULL | 1 |
| 12574 | 2020-02-14 15:16:03-08 | 177 | 317 | NULL | 2 |
| 12891 | 2020-02-14 15:16:03-08 | 2764 | 388 | NULL | 2 |
| 13534 | 2020-02-14 15:16:03-08 | 2476 | 75 | NULL | 1 |
#}
sales_by_film_category
Total sales by movie categories.
Schema:
col_name | col_type
-------------+----------
category | text
total_sales | numeric
Sample data:
| category | total_sales |
|---|---|
| Sports | 5314.21 |
| Classics | 3639.59 |
| New | 4361.57 |
| Family | 4226.07 |
| Comedy | 4383.58 |
#}
sales_by_store
Movie sales by store
Schema:
col_name | col_type
-------------+----------
store | text
manager | text
total_sales | numeric
Sample data:
| store | manager | total_sales |
|---|---|---|
| Woodridge | Jon Stephens | 33927.04 |
| Lethbridge | Mike Hillyer | 33489.47 |
#}
staff
Schema:
col_name | col_type
-------------+--------------------------
staff_id | integer
first_name | text
last_name | text
address_id | smallint
email | text
store_id | smallint
active | boolean
username | text
picture | character varying
Sample data:
| staff_id | first_name | last_name | address_id | store_id | active | username | last_update | picture | |
|---|---|---|---|---|---|---|---|---|---|
| 2 | Jon | Stephens | 4 | [email protected] | 2 | 1 | Jon | 2017-05-16 16:13:11.79328-07 | NULL |
| 1 | Mike | Hillyer | 3 | [email protected] | 1 | 1 | Mike | 2020-06-19 12:45:26.827726-07 | picture_url1 |
#}
staff_list
Schema:
col_name | col_type
----------+----------
id | integer
name | text
address | text
zip code | text
phone | text
city | text
country | text
sid | smallint
Sample data:
| id | name | address | zip code | phone | city | country | sid |
|---|---|---|---|---|---|---|---|
| 1 | Mike Hillyer | 23 Workhaven Lane | 14033335568 | Lethbridge | Canada | 1 | |
| 2 | Jon Stephens | 1411 Lillydale Drive | 6172235589 | Woodridge | Australia | 2 |
R Playground, Practice R FREE
Your Results
Wanna check your results and see our official solution? Upgrade and unlock your SQL & Python mastery today.