Thursday, September 9, 2021

Sample Databases for Learning MySQL and a QUIZ!!

     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

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

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

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.

from country
         join countrylanguage on
               (country.code = countrylanguage.CountryCode);