Table of Contents
This document describes the Sakila sample database—its history, installation, structure and usage.
Document generated on: 2012-02-01 (revision: 28943)
For legal information, see the Legal Notice.
This document describes the Sakila sample database—its history, installation, structure and usage.
Legal Notice
Copyright © 2007, 2011, Oracle and/or its affiliates. All rights reserved.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. MySQL is a trademark of Oracle Corporation and/or its affiliates, and shall not be used without Oracle's express written authorization. Other names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this material is subject to the terms and conditions of your Oracle Software License and Service Agreement, which has been executed and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle or as specifically provided below. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.
This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms:
You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights to this documentation not expressly granted above.
For more information on the terms of this license, or for details on how the MySQL documentation is built and produced, please visit MySQL Contact & Questions.
For help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists where you can discuss your issues with other MySQL users.
For additional documentation on MySQL products, including translations of the documentation into other languages, and downloadable versions in variety of formats, including HTML and PDF formats, see the MySQL Documentation Library.
The Sakila sample database was developed by Mike Hillyer, a former member of the MySQL AB documentation team, and is intended to provide a standard schema that can be used for examples in books, tutorials, articles, samples, and so forth. Sakila sample database also serves to highlight the latest features of MySQL such as Views, Stored Procedures, and Triggers.
Additional information on the Sakila sample database and its usage can be found through the MySQL forums.
The Sakila sample database is the result of support and feedback from the MySQL user community and feedback and user input is always appreciated. Please direct all feedback using the http://www.mysql.com/company/contact/. For bug reports, use MySQL Bugs.
The Sakila sample database was designed as a replacement to the
world
sample database, also provided by MySQL AB.
The world sample database provides a set of
tables containing information on the countries and cities of the
world and is useful for basic queries, but lacks structures for
testing MySQL-specific functionality and new features found in
MySQL 5.
Development of the Sakila sample database began in early 2005. Early designs were based on the database used in the Dell whitepaper Three Approaches to MySQL Applications on Dell PowerEdge Servers.
Where Dell's sample database was designed to represent an online DVD store, the Sakila sample database is designed to represent a DVD rental store. The Sakila sample database still borrows film and actor names from the Dell sample database.
Development was accomplished using MySQL Query Browser for schema design, with the tables being populated by a combination of MySQL Query Browser and custom scripts, in addition to contributor efforts (see Section 7, “Acknowledgments”).
After the basic schema was completed, various views, stored routines, and triggers were added to the schema; then the sample data was populated. After a series of review versions, the first official version of the Sakila sample database was released in March 2006.
The Sakila sample database is available from
http://dev.mysql.com/doc/index-other.html. A downloadable
archive is available in compressed tar file or
Zip format. The archive contains three files:
sakila-schema.sql,
sakila-data.sql, and
sakila.mwb.
The sakila-schema.sql file contains all the
CREATE statements required to create the
structure of the Sakila database including tables, views, stored
procedures, and triggers.
The sakila-data.sql file contains the
INSERT statements required to populate the
structure created by the sakila-schema.sql
file, along with definitions for triggers that must be created
after the initial data load.
The sakila.mwb file is a MySQL Workbench data
model that you can open within MySQL Workbench to examine the
database structure. For more information, see
MySQL
Workbench.
To install the Sakila sample database, follow these steps:
Extract the installation archive to a temporary location such as
C:\temp\or/tmp/. When you unpack the archive, it creates a directory namedsakila-dbthat contains thesakila-schema.sqlandsakila-data.sqlfiles.Connect to the MySQL server using the mysql command-line client with the following command:
shell>
mysql -u root -pEnter your password when prompted. A non-
rootaccount can be used as long as the account has privileges to create new databases.Execute the
sakila-schema.sqlscript to create the database structure by using the following command:mysql>
SOURCEC:/temp/sakila-db/sakila-schema.sql;Replace
C:/temp/sakila-dbwith the path to thesakila-schema.sqlfile on your system.NoteOn Windows you should use slashes, rather than backslashes, when executing the
SOURCEcommand.Execute the
sakila-data.sqlscript to populate the database structure with the following command:mysql>
SOURCEC:/temp/sakila-db/sakila-data.sql;Replace
C:/temp/sakila-dbwith the path to thesakila-data.sqlfile on your system.Confirm that the sample database is installed correctly. Execute the following statements. You should see output similar to that shown here.
mysql>
USE sakila;Database changed mysql>SHOW TABLES;+----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 22 rows in set (0.00 sec) mysql>SELECT COUNT(*) FROM film;+----------+ | COUNT(*) | +----------+ | 1000 | +----------+ 1 row in set (0.02 sec) mysql>SELECT COUNT(*) FROM film_text;+----------+ | COUNT(*) | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec)
The following diagram provides an overview of the structure of the
Sakila sample database. The diagram source file (for use with
MySQL Workbench) is included in the Sakila distribution and is
named sakila.mwb.
- 5.1.1. The actor Table
- 5.1.2. The address Table
- 5.1.3. The category Table
- 5.1.4. The city Table
- 5.1.5. The country Table
- 5.1.6. The customer Table
- 5.1.7. The film Table
- 5.1.8. The film_actor Table
- 5.1.9. The film_category Table
- 5.1.10. The film_text Table
- 5.1.11. The inventory Table
- 5.1.12. The language Table
- 5.1.13. The payment Table
- 5.1.14. The rental Table
- 5.1.15. The staff Table
- 5.1.16. The store Table
The following sections describe the tables that make up the Sakila sample database, in alphabetic order.
The actor table lists information for all
actors.
The actor table is joined to the
film
table by means of the
film_actor
table.
Columns
actor_id: A surrogate primary key used to uniquely identify each actor in the table.first_name: The actor's first name.last_name: The actor's last name.last_update: The time that the row was created or most recently updated.
The address table contains address
information for customers, staff, and stores.
The address table primary key appears as a
foreign key in the
customer,
staff,
and
store
tables.
Columns
address_id: A surrogate primary key used to uniquely identify each address in the table.address: The first line of an address.address2: An optional second line of an address.district: The region of an address, this may be a state, province, prefecture, etc.city_id: A foreign key pointing to thecitytable.postal_code: The postal code or ZIP code of the address (where applicable).phone: The telephone number for the address.last_update: The time that the row was created or most recently updated.
The category table lists the categories
that can be assigned to a film.
The category table is joined to the
film
table by means of the
film_category
table.
Columns
category_id: A surrogate primary key used to uniquely identify each category in the table.name: The name of the category.last_update: The time that the row was created or most recently updated.
The city table contains a list of cities.
The city table is referred to by a foreign
key in the
address
table and refers to the
country
table using a foreign key.
Columns
city_id: A surrogate primary key used to uniquely identify each city in the table.city: The name of the city.country_id: A foreign key identifying the country that the city belongs to.last_update: The time that the row was created or most recently updated.
The country table contains a list of
countries.
The country table is referred to by a
foreign key in the
city
table.
Columns
country_id: A surrogate primary key used to uniquely identify each country in the table.country: The name of the country.last_update: The time that the row was created or most recently updated.
The customer table contains a list of all
customers.
The customer table is referred to in the
payment
and
rental
tables and refers to the
address
and
store
tables using foreign keys.
Columns
customer_id: A surrogate primary key used to uniquely identify each customer in the table.store_id: A foreign key identifying the customer's “home store.” Customers are not limited to renting only from this store, but this is the store they generally shop at.first_name: The customer's first name.last_name: The customer's last name.email: The customer's email address.address_id: A foreign key identifying the customer's address in theaddresstable.active: Indicates whether the customer is an active customer. Setting this toFALSEserves as an alternative to deleting a customer outright. Most queries should have aWHERE active = TRUEclause.create_date: The date the customer was added to the system. This date is automatically set using a trigger during anINSERT.last_update: The time that the row was created or most recently updated.
The film table is a list of all films
potentially in stock in the stores. The actual in-stock copies
of each film are represented in the
inventory
table.
The film table refers to the
language
table and is referred to by the
film_category,
film_actor,
and
inventory
tables.
Columns
film_id: A surrogate primary key used to uniquely identify each film in the table.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: A foreign key pointing at thelanguagetable; identifies the language of the film.original_language_id: A foreign key pointing at thelanguagetable; 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 therental_durationcolumn.length: The 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, orNC-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: The time that the row was created or most recently updated.
The film_actor table is used to support a
many-to-many relationship between films and actors. For each
actor in a given film, there will be one row in the
film_actor table listing the actor and
film.
The film_actor table refers to the
film
and
actor
tables using foreign keys.
Columns:
actor_id: A foreign key identifying the actor.film_id: A foreign key identifying the film.last_update: The time that the row was created or most recently updated.
The film_category table is used to support
a many-to-many relationship between films and categories. For
each category applied to a film, there will be one row in the
film_category table listing the category
and film.
The film_category table refers to the
film
and
category
tables using foreign keys.
Columns:
film_id: A foreign key identifying the film.category_id: A foreign key identifying the category.last_update: The time that the row was created or most recently updated.
The film_text table is the only table in
the Sakila sample database that uses the
MyISAM storage engine. This table is
provided to allow for full-text searching of the titles and
descriptions of the films listed in the
film
table.
The film_text table contains the
film_id, title and
description columns of the
film table, with the contents of the table
kept in synchrony with the film table by
means of triggers on the film table's
INSERT, UPDATE and
DELETE operations (see
Section 5.5, “Triggers”).
Columns
film_id: A surrogate primary key used to uniquely identify each film in the table.title: The title of the film.description: A short description or plot summary of the film.
The contents of the film_text table should
never be modified directly. All changes should be made to the
film table instead.
The inventory table contains one row for
each copy of a given film in a given store.
The inventory table refers to the
film
and
store
tables using foreign keys and is referred to by the
rental
table.
Columns
inventory_id: A surrogate primary key used to uniquely identify each item in inventory.film_id: A foreign key pointing to the film this item represents.store_id: A foreign key pointing to the store stocking this item.last_update: The time that the row was created or most recently updated.
The language table is a lookup table
listing the possible languages that films can have for their
language and original language values.
The language table is referred to by the
film
table.
Columns
language_id: A surrogate primary key used to uniquely identify each language.name: The English name of the language.last_update: The time that the row was created or most recently updated.
The payment table records each payment made
by a customer, with information such as the amount and the
rental being paid for (when applicable).
The payment table refers to the
customer,
rental,
and
staff
tables.
Columns
payment_id: A surrogate primary key used to uniquely identify each payment.customer_id: The customer whose balance the payment is being applied to. This is a foreign key reference to thecustomertable.staff_id: The staff member who processed the payment. This is a foreign key reference to thestafftable.rental_id: The rental that the payment is being applied to. This is optional because some payments are for outstanding fees and may not be directly related to a rental.amount: The amount of the payment.payment_date: The date the payment was processed.last_update: The time that the row was created or most recently updated.
The rental table contains one row for each
rental of each inventory item with information about who
rented what item, when it was rented, and when it was
returned.
The rental table refers to the
inventory,
customer,
and
staff
tables and is referred to by the
payment
table.
Columns
rental_id: A surrogate primary key that uniquely identifies the rental.rental_date: The date and time that the item was rented.inventory_id: The item being rented.customer_id: The customer renting the item.return_date: The date and time the item was returned.staff_id: The staff member who processed the rental.last_update: The time that the row was created or most recently updated.
The staff table lists all staff members,
including information on email address, login information, and
picture.
The staff table refers to the
store
and
address
tables using foreign keys, and is referred to by the
rental,
payment,
and
store
tables.
Columns
staff_id: A surrogate primary key that uniquely identifies the staff member.first_name: The first name of the staff member.last_name: The last name of the staff member.address_id: A foreign key to the staff member's address in theaddresstable.picture: ABLOBcontaining a photograph of the employee.email: The staff member's email address.store_id: The staff member's “home store”. The employee can work at other stores but is generally assigned to the store listed.active: Whether this is an active employee. If employees leave their rows are not deleted from this table, instead this column is set toFALSE.username: The user name used by the staff member to access the rental system.password: The password used by the staff member to access the rental system. The password should be stored as a hash using theSHA1()function.last_update: The time that the row was created or most recently updated.
The store table lists all stores in the
system. All inventory is assigned to specific stores, and
staff and customers are assigned a “home store”.
The store table refers to the
staff
and
address
tables using foreign keys and is referred to by the
staff,
customer,
and
inventory
tables.
Columns
store_id: A surrogate primary key that uniquely identifies the store.manager_staff_id: A foreign key identifying the manager of this store.address_id: A foreign key identifying the address of this store.last_update: The time that the row was created or most recently updated.
The following sections describe the views that are included with the Sakila sample database, in alphabetic order.
The actor_info view provides a list of all
actors, including the films in which they have performed,
broken down by category.
The staff_list view incorporates data from
the film, actor,
category, film_actor,
and film_category tables.
The customer_list view provides a list of
customers, with first name and last name concatenated together
and address information combined into a single view.
The customer_list view incorporates data
from the customer,
address, city, and
country tables.
The film_list view contains a formatted
view of the film table, with a
comma-separated list of the film's actors.
The film_list view incorporates data from
the film, category,
film_category, actor,
and film_actor tables.
The nicer_but_slower_film_list view
contains a formatted view of the film
table, with a comma-separated list of the film's actors.
The nicer_but_slower_film_list view differs
from the film_list view in the list of
actors. The lettercase of the actor names is adjusted so that
the first letter of each name is capitalized, rather than
having the name in all-caps.
As indicated in its name, the
nicer_but_slower_film_list view performs
additional processing and therefore takes longer to return
data than the film_list view.
The nicer_but_slower_film_list view
incorporates data from the film,
category, film_category,
actor, and film_actor
tables.
The sales_by_film_category view provides a
list of total sales, broken down by individual film category.
Because a film can be listed in multiple categories, it is not advisable to calculate aggregate sales by totalling the rows of this view.
The sales_by_film_category view
incorporates data from the category,
payment, rental,
inventory, film,
film_category, and
category tables.
The sales_by_store view provides a list of
total sales, broken down by store.
The view returns the store location, manager name, and total sales.
The sales_by_store view incorporates data
from the city, country,
payment, rental,
inventory, store,
address, and staff
tables.
The following sections describe the stored procedures included with the Sakila sample database, in alphabetic order.
All parameters listed are IN parameters
unless listed otherwise.
Description
The film_in_stock stored procedure is used
to determine whether any copies of a given film are in stock
at a given store.
Parameters
p_film_id: The ID of the film to be checked, from thefilm_idcolumn of thefilmtable.p_store_id: The ID of the store to check for, from thestore_idcolumn of thestoretable.p_film_count: AnOUTparameter that returns a count of the copies of the film in stock.
Return Values
This procedure produces a table of inventory ID numbers for
the copies of the film in stock, and returns (in the
p_film_count parameter) a count that
indicates the number of rows in that table.
Sample Usage
mysql>CALL film_in_stock(1,1,@count);+--------------+ | inventory_id | +--------------+ | 1 | | 2 | | 3 | | 4 | +--------------+ 4 rows in set (0.06 sec) Query OK, 0 rows affected (0.06 sec) mysql>SELECT @count;+--------+ | @count | +--------+ | 4 | +--------+ 1 row in set (0.00 sec)
Description
The film_not_in_stock stored procedure is
used to determine whether there are any copies of a given film
not in stock (rented out) at a given store.
Parameters
p_film_id: The ID of the film to be checked, from thefilm_idcolumn of thefilmtable.p_store_id: The ID of the store to check for, from thestore_idcolumn of thestoretable.p_film_count: AnOUTparameter that returns a count of the copies of the film not in stock.
Return Values
This procedure produces a table of inventory ID numbers for
the copies of the film not in stock, and returns (in the
p_film_count parameter) a count that
indicates the number of rows in that table.
Sample Usage
mysql>CALL film_not_in_stock(2,2,@count);+--------------+ | inventory_id | +--------------+ | 9 | +--------------+ 1 row in set (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql>SELECT @count;+--------+ | @count | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)
Description
The rewards_report stored procedure
generates a customizable list of the top customers for the
previous month.
Parameters
min_monthly_purchases: The minimum number of purchases or rentals a customer needed to make in the last month to qualify.min_dollar_amount_purchased: The minimum dollar amount a customer needed to spend in the last month to qualify.count_rewardees: AnOUTparameter that returns a count of the customers who met the qualifications specified.
Return Values
This function returns a table of customers who met the
qualifications specified. The table has the same structure as
the
customer
table. The procedure also returns (in the
count_rewardees parameter) a count that
indicates the number of rows in that table.
Sample Usage
mysql>CALL rewards_report(7,20.00,@count);... | 598 | 1 | WADE | DELVALLE | WADE.DELVALLE@sakilacustomer.org | 604 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 | | 599 | 2 | AUSTIN | CINTRON | AUSTIN.CINTRON@sakilacustomer.org | 605 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 | ... 42 rows in set (0.11 sec) Query OK, 0 rows affected (0.67 sec) mysql>SELECT @count;+--------+ | @count | +--------+ | 42 | +--------+ 1 row in set (0.00 sec)
The following sections describe the stored functions included with the Sakila sample database.
The get_customer_balance function returns
the current amount owing on a specified customer's account.
Parameters
p_customer_id: The ID of the customer to check, from thecustomer_idcolumn of thecustomertable.p_effective_date: The cutoff date for items that will be applied to the balance. Any rentals, payments, and so forth after this date are not counted.
Return Values
This function returns the amount owing on the customer's account.
Sample Usage
mysql> SELECT get_customer_balance(298,NOW());
+---------------------------------+
| get_customer_balance(298,NOW()) |
+---------------------------------+
| 22.00 |
+---------------------------------+
1 row in set (0.00 sec)
The inventory_held_by_customer function
returns the customer_id of the customer who
has rented out the specified inventory item.
Parameters
p_inventory_id: The ID of the inventory item to be checked.
Return Values
This function returns the customer_id of
the customer who is currently renting the item, or
NULL if the item is in stock.
Sample Usage
mysql>SELECT inventory_held_by_customer(8);+-------------------------------+ | inventory_held_by_customer(8) | +-------------------------------+ | NULL | +-------------------------------+ 1 row in set (0.14 sec) mysql>SELECT inventory_held_by_customer(9);+-------------------------------+ | inventory_held_by_customer(9) | +-------------------------------+ | 366 | +-------------------------------+ 1 row in set (0.00 sec)
The inventory_function function returns a
boolean value indicating whether the inventory item specified
is in stock.
Parameters
p_inventory_id: The ID of the inventory item to be checked.
Return Values
This function returns TRUE or
FALSE to indicate whether the item
specified is in stock.
Sample Usage
mysql>SELECT inventory_in_stock(9);+-----------------------+ | inventory_in_stock(9) | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.03 sec) mysql>SELECT inventory_in_stock(8);+-----------------------+ | inventory_in_stock(8) | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec)
The following sections describe the triggers in the Sakila sample database.
The customer_create_date trigger sets the
create_date column of the
customer table to the current time and date
as rows are inserted.
The payment_date trigger sets the
payment_date column of the
payment table to the current time and date
as rows are inserted.
The rental_date trigger sets the
rental_date column of the
rental table to the current time and date
as rows are inserted.
The ins_film trigger duplicates all
INSERT operations on the
film table to the
film_text table.
The upd_film trigger duplicates all
UPDATE operations on the
film table to the
film_text table.
These are a few usage examples of how to perform common operations using the Sakila sample database. While these operations are good candidates for stored procedures and views, such implementation is intentionally left as an exercise to the user.
Rent a DVD
To rent a DVD, first confirm that the given inventory item is in
stock, and then insert a row into the rental
table. After the rental table is created,
insert a row into the payment table. Depending
on business rules, you may also need to check whether the customer
has an outstanding balance before processing the rental.
mysql>SELECT INVENTORY_IN_STOCK(10);+------------------------+ | INVENTORY_IN_STOCK(10) | +------------------------+ | 1 | +------------------------+ 1 row in set (0.00 sec) mysql>INSERT INTO rental(rental_date, inventory_id, customer_id, staff_id)->VALUES(NOW(), 10, 3, 1);Query OK, 1 row affected (0.00 sec) mysql>SELECT @balance := get_customer_balance(3, NOW());+--------------------------------------------+ | @balance := get_customer_balance(3, NOW()) | +--------------------------------------------+ | 4.99 | +--------------------------------------------+ 1 row in set (0.01 sec) mysql>INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)->VALUES(3,1,LAST_INSERT_ID(), @balance, NOW());Query OK, 1 row affected (0.00 sec)
Return a DVD
To return a DVD, update the rental table and
set the return date. To do this, first identify the
rental_id to update based on the
inventory_id of the item being returned.
Depending on the situation, it may be necessary to check the
customer balance and perhaps process a payment for overdue fees by
inserting a row into the payment table.
mysql>SELECT rental_id->FROM rental->WHERE inventory_id = 10->AND customer_id = 3->AND return_date IS NULL;+-----------+ | rental_id | +-----------+ | 16050 | +-----------+ 1 row in set (0.00 sec) mysql>UPDATE rental->SET return_date = NOW()->WHERE rental_id = @rentID;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>SELECT get_customer_balance(3, NOW());+--------------------------------+ | get_customer_balance(3, NOW()) | +--------------------------------+ | 0.00 | +--------------------------------+ 1 row in set (0.09 sec)
Find Overdue DVDs
Many DVD stores produce a daily list of overdue rentals so that customers can be contacted and asked to return their overdue DVDs.
To create such a list, search the rental table
for films with a return date that is NULL and
where the rental date is further in the past than the rental
duration specified in the film table. If so,
the film is overdue and we should produce the name of the film
along with the customer name and phone number.
mysql>SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer,->address.phone, film.title->FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id->INNER JOIN address ON customer.address_id = address.address_id->INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id->INNER JOIN film ON inventory.film_id = film.film_id->WHERE rental.return_date IS NULL->AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()->LIMIT 5;+------------------+--------------+------------------+ | customer | phone | title | +------------------+--------------+------------------+ | OLVERA, DWAYNE | 62127829280 | ACADEMY DINOSAUR | | HUEY, BRANDON | 99883471275 | ACE GOLDFINGER | | BROWN, ELIZABETH | 10655648674 | AFFAIR PREJUDICE | | OWENS, CARMEN | 272234298332 | AFFAIR PREJUDICE | | HANNON, SETH | 864392582257 | AFRICAN EGG | +------------------+--------------+------------------+ 5 rows in set (0.02 sec)
The following individuals and organizations have contributed to the development of the Sakila sample database.
Roland Bouman, certification developer at MySQL AB Author. Provided valuable feedback throughout the development process, contributed sample views and stored procedures.Ronald Bradford, Developer at MySQL AB. Developed first sample application for use with the Sakila sample database.Dave Jaffe, Dell. Provided schema used in Dell whitepaper and secured permission to use parts thereof in the Sakila sample database.Giuseppe Maxia, former CTO of Stardata, now QA developer at MySQL AB. Provided valuable feedback throughout the development process, populated some of the sample data, provided some of the sample views and triggers.Jay Pipes, MySQL community advocate. Provided some of the sample stored procedures.Zak Greant, Community advocate and author. Provided advice and feedback on licensing.
In addition to the individuals mentioned above, various other individuals in MySQL AB and the community have provided feedback during the course of development.
The contents of the sakila-schema.sql and
sakila-data.sql files are licensed under the
New BSD license.
Information on the New BSD license can be found at http://www.opensource.org/licenses/bsd-license.php and http://en.wikipedia.org/wiki/BSD_License.
The additional materials included in the Sakila distribution, including this documentation, are not licensed under an open license. Use of this documentation is subject to the terms described in Section 1, “Legal Notice”.
Please Contact http://www.mysql.com/company/contact/ for more information.
When using the Sakila sample database for articles and books, it is strongly recommended that you explicitly list the version of the Sakila sample database that is used in your examples. This way readers will download the same version for their use and not encounter any differences in their results that may occur from upgrades to the data or schema.
Corrected
upd_filmtrigger definition to include changes tofilm_idvalues.Added
actor_infoview.Changed error handler for
inventory_held_by_customerfunction. Function now has an exit handler forNOT FOUNDinstead of the more cryptic1329.Added template for new BSD license to schema and data files.
Added
READS SQL DATAto the procedures and functions where appropriate to support loading on MySQL 5.1.Fixed date-range issue in the
rewards_reportprocedure (thanks Goplat).
Fixed bug in
sales_by_storeview that caused the same manager to be listed for every store.Fixed bug in
inventory_held_by_customerfunction that caused function to return multiple rows.Moved
rental_datetrigger tosakila-data.sqlfile to prevent it from interfering with data loading.
Added
film_in_stockstored procedure.Added
film_not_in_stockstored procedure.Added
inventory_help_by_customerstored function.Added
inventory__in_stockstored function.Optimized data file for loading (multiple-row
INSERTstatements, transactions). (Thanks Giuseppe)Fixed error in
paymenttable loading script that caused infinitely increasing payment amounts.
Added
sales_by_storeandsales_by_film_categoryviews, submitted by Jay Pipes.Added
rewards_reportstored procedure, submitted by Jay Pipes.Added
get_customer_balancestored procedure.Added
sakila-data.sqlfile to load data into sample database.
Changed
address.districttoVARCHAR(20).Changed
customer.first_nametoVARCHAR(45).Changed
customer.last_nametoVARCHAR(45).Changed
customer.emailtoVARCHAR(50).Added
payment.rental_idcolumn (anINT NULLcolumn).Foreign key added for
payment.rental_idtorental.rental_id.rental.rental_idadded,INT Auto_Increment, made into surrogate primary key, old primary key changed toUNIQUEkey.
All tables have a
last_updateTIMESTAMPcolumn with traditional behavior (DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP).actor_idis now aSMALLINT.address_idis now aSMALLINT.category_idis now aTINYINT.city_idis now aSMALLINT.country_idis now aSMALLINT.customer_idis now aSMALLINT.first_name,last_nameforcustomertable are nowCHARinstead ofVARCHAR.customertable now hasemail CHAR(50).create_dateoncustomertable is nowDATETIME(to accommodatelast_update TIMESTAMP).customertable has a newON INSERTtrigger that enforcescreate_datecolumn being set toNOW().film_idis nowSMALLINT.film.descriptionnow hasDEFAULT NULL.film.release_yearadded with typeYEAR.film.language_idandfilm.original_language_idadded along withlanguagetable. For foreign films that may have been subtitled.original_language_idcan beNULL,language_idisNOT NULL.film.lengthis nowSMALLINT.film.category_idcolumn removed.New table:
film_category; allows for multiple categories per film.film_text.category_idcolumn removed.inventory_idis nowMEDIUMINT.payment_idis nowSMALLINT.payment.payment_dateis nowDATETIME.Trigger added to
paymenttable to enforce thatpayment_dateis set toNOW()uponINSERT.rental.rent_dateis now rental.rental_date and is nowDATETIME.Trigger added to
rentaltable to enforce thatrental_dateis set toNOW()uponINSERT.staff_idis nowTINYINT.staff.emailadded (VARCHAR(50)).staff.usernameadded (VARCHAR(16)).store_idis nowTINYINT.film_listview updated to handle newfilm_categorytable.nicer_but_slower_film_listview updated to handle newfilm_categorytable.
