Hi! Could we please enable some services and cookies to improve your experience and our website?

Privacy & Cookie Policy.
SQL code copied to buffer

SQLtest interactive SQL practice

Sharpen your SQL skills with our interactive exercises and assessments!
Practice a wide range of SQL tasks, from basic queries to advanced techniques. Get immediate feedback on your solutions, helping you learn from mistakes and improve your skills. Boost your confidence for job interviews and real-world SQL applications.
RU PT
Task 101: 
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 and total_payments - payment amount
Do not use the UNION command in your query.

Use MySQL syntax to write your answer. Descriptions of tables are provided in the right pane.

Write your request in the field below and click the "Check it!" button.
Get hint
Copy code Clear editor
Explore over 360 diverse tasks on our platform.

Sakila Database (MySQL)

Sakila is an example database designed by MySQL, specifically created for learning and demonstrating the capabilities of relational database management systems (RDBMS).

ER diagram of the Sakila database

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

Table List:

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)
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)
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)
film - list of films in Sakila database.
  • 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)
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)
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)
inventory - items in Sakila database.
  • 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)
language - films languages.
  • customer_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)
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_ididentifier of the staff member who 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)
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)
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)
The fun and effective way to learn a language with AI!
Practice speaking, listening & writing.
Start Learning Now