The Sakila Database
One of the best example databases out there is the Sakila Database, which was originally created by MySQL and has been open sourced under the terms of the BSD License.
The Sakila database is a nicely normalised schema modelling a DVD rental store, featuring things like films, actors, film-actor relationships, and a central inventory table that connects films, stores, and rentals.
The database allows for nice example queries like the following one that finds the actor with most films (PostgreSQL syntax):
SELECT first_name, last_name, count(*) films FROM actor AS a JOIN film_actor AS fa USING (actor_id) GROUP BY actor_id, first_name, last_name ORDER BY films DESC LIMIT 1;
first_name last_name films -------------------------------- GINA DEGENERES 42
Or, let's calculate the cumulative revenue of all stores (PostgreSQL syntax):
SELECT payment_date, amount, sum(amount) OVER (ORDER BY payment_date) FROM ( SELECT CAST(payment_date AS DATE) AS payment_date, SUM(amount) AS amount FROM payment GROUP BY CAST(payment_date AS DATE) ) p ORDER BY payment_date;
payment_date amount sum ------------------------------------- 2005-05-24 29.92 29.92 2005-05-25 573.63 603.55 2005-05-26 754.26 1357.81 2005-05-27 685.33 2043.14 2005-05-28 804.04 2847.18 2005-05-29 648.46 3495.64 2005-05-30 628.42 4124.06 2005-05-31 700.37 4824.43 2005-06-14 57.84 4882.27 2005-06-15 1376.52 6258.79 2005-06-16 1349.76 7608.55 2005-06-17 1332.75 8941.30 ...
Want to play around with the Sakila database? We're hosting several ported versions of the original database on GitHub, including versions for:
- DB2
- MySQL
- Oracle
- PostgreSQL
- SQL Server
- SQLite
