I frequently see requests on various web sites from MySQL novices looking for example databases with which to practice. There are several that are highly recommended and we will take a look at them over the next few weeks.
1. World Data Base -- See https://dev.mysql.com/doc/world-setup/en/
This has been THE database used in MySQL's documentation, classes, examples, and just about anything else. There are three tables -- city, country, and countrylanguage -- that are fantastic for teaching the basics such as JOINs, GROUP BY, HAVING, and other SQL commands.
1x. World X Database -- See https://dev.mysql.com/doc/world-x-setup/en/
With the advent of the X DevAPI there needed to be a test database with JSON columns and the World Database was modified. The countryinfo table was added to the inherited three so that you can practice with JSON columns and a JSON document collection.
3. Sakila -- See https://dev.mysql.com/doc/sakila/en/
Once upon a time, if you wanted to see a movie in your home you had to first go to a store filled with BETAMAX and VHS format tapes and rent them. The Sakila database is data representing the operations of such a store. This is a bigger set of data than the previous two and provides twenty three tables giving novices an opportunity to join more tables together.
4. Northwind -- Various sites
Northwind is a fictitious trading company and the sample dataset came from Microsoft. There are various version available for MySQL and they feature ten tables.
In the coming weeks I will work with various data sets hopefully showing novices how to use these test databases to expand their knowledge. And if it proves popular, there will be a quiz each week featuring one of the above.
QUIZ: How would you modify the following query on the world database to find only the official languages of each country? Bonus - list all the official languages for each country on one row.
select country.name,
countrylanguage.Language
from country
join countrylanguage on
(country.code = countrylanguage.CountryCode);
