Task 70:

Payment Analysis

MySQL

Write a query to sum all transactions in the payment table, categorized by year and staff, including subtotals for each year and a grand total. The resulting table should have columns:

- income_year - year of payments
- staff_id - staff identifier
- total_payments - payment amount

Do not use the UNION command in your query.

Write your query below and click "Check it!". Tables are described in the right pane.

The query result will appear here. Ctrl+Enter - Run query

Sakila Database: table structure and schema overview

Sakila is a sample relational database designed by MySQL for learning and demonstrating SQL and relational database management system (RDBMS) capabilities.

This page presents Sakila table structure, key columns, and constraints commonly used in educational SQL queries.

The Sakila database contains 15 main tables describing various aspects of a DVD rental company.

ER diagram of the Sakila database

The list of tables

actor - actor table.

actor_idunique record identifier (PK)
first_nameactor's first name
last_nameactor's last name
last_updatedate and time of last update

actor_id	first_name	last_name	last_update
1	John	Doe	2023-01-01 12:00:00

PRIMARY KEY, btree (actor_id)

address - customer and staff addresses.

address_idunique record identifier (PK)
addresspostal address
address2additional address
districtdistrict or region
city_idcity identifier (FK)
postal_codepostal code
phonephone number
last_updatedate and time of last update

address_id	address	address2	district	city_id	postal_code	phone	last_update
1	123 Main St	[null]	Downtown	1	12345	+1234567890	2023-01-01 12:00:00

PRIMARY KEY, btree (address_id)

FOREIGN KEY (city_id) REFERENCES city(city_id)

category - film categories.

category_idunique record identifier (PK)
namecategory name
last_updatedate and time of last update

category_id	name	last_update
1	Action	2023-01-01 12:00:00

PRIMARY KEY, btree (category_id)

city - city table.

city_idunique record identifier (PK)
citycity name
country_idcountry identifier (FK)
last_updatedate and time of last update

city_id	city	country_id	last_update
1	Metropolis	1	2023-01-01 12:00:00

PRIMARY KEY, btree (city_id)

FOREIGN KEY (country_id) REFERENCES country(country_id)

country - country table.

country_idunique record identifier (PK)
countrycountry name
last_updatedate and time of last update

country_id	country	last_update
1	United States	2023-01-01 12:00:00

PRIMARY KEY, btree (country_id)

customer - customer table.

customer_idunique record identifier (PK)
store_idstore identifier (FK)
first_namecustomer's first name
last_namecustomer's last name
emailcustomer's email address
address_idaddress identifier (FK)
activecustomer activity indicator (0/1)
create_datedate and time the customer was added to the database
last_updatedate and time of last update

customer_id	store_id	first_name	last_name	email	address_id	active	create_date	last_update
1	1	John	Doe	john.doe@example.com	1	1	2023-01-01 12:00:00	2023-01-01 12:00:00

PRIMARY KEY, btree (customer_id)

FOREIGN KEY (store_id) REFERENCES store(store_id)
FOREIGN KEY (address_id) REFERENCES address(address_id)

film - table of films.

film_idunique record identifier (PK)
titlefilm title
descriptionbrief description or plot of the film
release_yearyear the film was released
language_ididentifier of the film's language (FK)
original_language_ididentifier of the original language of the film in case it is dubbed into a new language
rental_durationduration of rental period in days
rental_ratecost of renting the film for the duration specified in the rental_duration column
lengthlength of the film in minutes
replacement_costamount of penalty for loss or damage of the disc
ratingrating assigned to the film. Can be one of: G, PG, PG-13, R, or NC-17
special_featureslist of special features included on the DVD. Can be zero or more of: Trailers, Commentaries, Deleted Scenes, Behind the Scenes
last_updatedate and time of last update

film_id	title	description	release_year	language_id	original_language_id	rental_duration	rental_rate	length	replacement_cost	rating	special_features	last_update
1	Film Title	A brief description of the film.	2000	1	2	5	4.99	120	19.99	PG-13	Trailers, Commentaries	2023-01-01 12:00:00

PRIMARY KEY, btree (film_id)

FOREIGN KEY (language_id) REFERENCES language(language_id)
FOREIGN KEY (original_language_id) REFERENCES language(language_id)

film_actor - actors to films relation.

actor_ididentifier for actor (FK)
film_ididentifier for film (FK)
last_updatedate and time of last update

actor_id	film_id	last_update
1	1	2023-01-01 12:00:00

PRIMARY KEY, btree (actor_id, film_id)

FOREIGN KEY (actor_id) REFERENCES actor(actor_id)
FOREIGN KEY (film_id) REFERENCES film(film_id)

film_category - films to categories relation.

film_ididentifier for each film (FK)
category_ididentifier for each category (FK)
last_updatedate and time of last update

film_id	category_id	last_update
1	1	2023-01-01 12:00:00

PRIMARY KEY, btree (film_id, category_id)

FOREIGN KEY (film_id) REFERENCES film(film_id)
FOREIGN KEY (category_id) REFERENCES category(category_id)

inventory - table of items.

inventory_idunique record identifier (PK)
film_ididentifier of the film (FK)
store_ididentifier of the store where the inventory is located (FK)
last_updatedate and time of last update

inventory_id	film_id	store_id	last_update
1	23	2	2023-01-01 12:00:00

PRIMARY KEY, btree (inventory_id)

FOREIGN KEY (film_id) REFERENCES film(film_id)
FOREIGN KEY (store_id) REFERENCES store(store_id)

language - films languages.

language_idunique record identifier (PK)
namelanguage name
last_updatedate and time of last update

language_id	name	last_update
1	English	2023-01-01 12:00:00

PRIMARY KEY, btree (language_id)

payment - customers payments.

payment_idunique identifier of the record (PK)
customer_ididentifier of the customer (FK)
staff_ididentifier of the staff member who received the payment (FK)
rental_ididentifier of the rental record (FK)
amountpayment amount
payment_datedate and time of the payment
last_updatedate and time of the last update

payment_id	customer_id	staff_id	rental_id	amount	payment_date	last_update
1	1	1	1	4.99	2023-01-01 12:13:14	2023-01-01 12:14:15

PRIMARY KEY, btree (payment_id)

FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
FOREIGN KEY (rental_id) REFERENCES rental(rental_id)

rental - customers rentals.

rental_idunique identifier of the record (PK)
rental_daterental start date
inventory_ididentifier of the disk (FK)
customer_ididentifier of the customer (FK)
return_datedate of returning the film
staff_idid of the staff member issued the disk (FK)
last_updatedate and time of the last update

rental_id	rental_date	inventory_id	customer_id	return_date	staff_id	last_update
1	2023-01-01 16:15:21	1	1	2023-01-10 09:12:36	1	2023-01-01 12:00:00

PRIMARY KEY, btree (rental_id)

FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id)
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
FOREIGN KEY (staff_id) REFERENCES staff(staff_id)

staff - company staff.

staff_idunique identifier of the record (PK)
first_namefirst name of the staff member
last_namelast name of the staff member
address_ididentifier of the address (FK)
picturephoto of the staff member
emailemail address of the staff member
store_idforeign key referencing the store table (FK)
activeindicator of staff member's activity (0/1)
usernameusername for system login
passwordpassword for login
last_updatedate and time of the last update

staff_id	first_name	last_name	address_id	picture	email	store_id	active	username	password	last_update
1	John	Doe	1	[null]	john.doe@example.com	1	1	johndoe	********	2023-01-01 12:00:00

PRIMARY KEY, btree (staff_id)

FOREIGN KEY (address_id) REFERENCES address(address_id)
FOREIGN KEY (store_id) REFERENCES store(store_id)

store - company stories.

store_idunique identifier of the record (PK)
manager_staff_ididentifier of the store manager (FK)
address_ididentifier of the address (FK)
last_updatedate and time of the last update

store_id	manager_staff_id	address_id	last_update
1	1	1	2023-01-01 12:00:00

PRIMARY KEY, btree (store_id)

FOREIGN KEY (manager_staff_id) REFERENCES staff(staff_id)
FOREIGN KEY (address_id) REFERENCES address(address_id)

Learn to code Python for Beginners Master Python one line at a time with interactive lessons, instant feedback, and real-world projects.

Start learning Get the book

Practice

Lessons

Playground

Test

Payment Analysis

Login

Register

Payment Analysis