SQLtest: interactive SQL quiz

Sakila Database (MySQL)

Sakila is a sample database developed by MySQL, specifically designed to teach and demonstrate the capabilities of database management systems (DBMS) based on the relational model.

Sakila DB ER diagram

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

Below is a list of these tables:

• actor - table of actors.
• address - table of customer and staff addresses
• category - table of films categories.
• city - table of cities.
• country - table of countries.
• customer - table of customers in Sakila database.
• film - table of films in Sakila database.
• film_actor - actors to films relation table.
• film_category - films to categories relation table.
• inventory - table of items in Sakila database.
• language - table of films languages.
• payment - table of customers payments.
• rental - table of customers rentals.
• staff - table of company staff.
• store - table of company stories.

Table actor

Table columns:

• actor_id - Unique identifier for each actor.
• first_name - First name of the actor.
• last_name - Last name of the actor.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (actor_id)

Table address

Table columns:

• address_id - Unique identifier for each address.
• address - Street address.
• address2 - Additional address.
• district - District or region.
• city_id - Foreign key referencing the city table.
• postal_code - Postal code.
• phone - Phone number.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (address_id)

Table category

Table columns:

• category_id - Unique identifier for each category.
• name - Name of the category.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (category_id)

Table city

Table columns:

• city_id - Unique identifier for each city.
• city - City name.
• country_id - Foreign key referencing the country table.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (city_id)

Table country

Table columns:

• country_id - Unique identifier for each country.
• country - Country name.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (country_id)

Table customer

Table columns:

• customer_id - Unique identifier for each customer.
• store_id - Foreign key referencing the store table.
• first_name - First name of the customer.
• last_name - Last name of the customer.
• email - Email address of the customer.
• address_id - Foreign key referencing the address table.
• active - Indicates whether the customer is active (0/1).
• create_date - Timestamp indicating when the customer was added to the database.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (customer_id)

Table film

Table columns:

• film_id - Unique identifier for each film.
• title - The title of the film.
• description - A short description or plot summary of the film.
• release_year - The year in which the movie was released.
• language_id - Foreign key referencing the language table; identifies the language of the film.
• original_language_id - A foreign key pointing at the language table; identifies the original language of the film. Used when a film has been dubbed into a new language.
• rental_duration - The length of the rental period, in days.
• rental_rate - The cost to rent the film for the period specified in the rental_duration column.
• length - Duration of the film, in minutes.
• replacement_cost - The amount charged to the customer if the film is not returned or is returned in a damaged state.
• rating - The rating assigned to the film. Can be one of: G, PG, PG-13, R, or NC-17.
• special_features - Lists which common special features are included on the DVD. Can be zero or more of: Trailers, Commentaries, Deleted Scenes, Behind the Scenes.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (film_id)

Table film_actor

Table columns:

• actor_id - Unique identifier for actor.
• film_id - Unique identifier for film.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (actor_id, film_id)

Table film_category

Table columns:

• film_id - Unique identifier for each film.
• category_id - Unique identifier for each category.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (film_id, category_id)

Table inventory

Table columns:

• inventory_id - Unique identifier for each inventory item.
• film_id - Unique identifier for each film in the inventory.
• store_id - Unique identifier for the store where the inventory item is located.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (inventory_id)

Table language

Table columns:

• language_id - Unique identifier for each language.
• name - Language name.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (language_id)

Table payment

Table columns:

• payment_id - Unique identifier for each payment.
• customer_id - Foreign key referencing the customer table.
• staff_id - Foreign key referencing the staff table.
• rental_id - Foreign key referencing the rental table.
• amount - Payment amount.
• payment_date - Date of the payment.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (payment_id)

Table rental

Table columns:

• rental_id - Unique identifier for each rental.
• rental_date - Date when the rental occurred.
• inventory_id - Foreign key referencing the inventory table.
• customer_id - Foreign key referencing the customer table.
• return_date - Date when the rental was returned.
• staff_id - Foreign key referencing the staff table.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (rental_id)

Table staff

Table columns:

• staff_id - Unique identifier for each staff member.
• first_name - First name of the staff member.
• last_name - Last name of the staff member.
• address_id - Foreign key referencing the address table.
• picture - Staff member picture.
• email - Email address of the staff member.
• store_id - Foreign key referencing the store table.
• active - Indicates whether the staff member is active (0/1).
• username - Username for login.
• password - Password for login.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (staff_id)

Table store

Table columns:

• store_id - Unique identifier for each store.
• manager_staff_id - Foreign key referencing the staff table for the store manager.
• address_id - Foreign key referencing the address table.
• last_update - When the row was created or most recently updated.

Indexes:

• PRIMARY KEY, btree (store_id)