Classic Cars for SQLite
If you teach an introductory course to SQL, you have quite likely come across the very useful MySQL “Classic Cars” database. This is a really well crafted teaching database, including all the common database design patterns and data types. The link contains both the SQL script to create and populate the database, and a pdf of the data model. Data models are critical to writing SQL at any level!
Historically, I have used the Turnkey Linux LAMP stack a quick and easy way to make this accessible to students. I have always run these on a local VM. Be sure to log in as root and run the updates! Others get students to load the XAMPP stack, and then import the mysqlsampledatabase.sql. I always worry that the weaker students, who need this most, will be either unable or unwilling to successfully follow all the steps in getting that stack to work, and so end up just getting further behind.
Another option I’ve wanted to try, particularly since I teach Python in my A-Level course, is to use SQLite. But the SQL dialects are slightly different. So I ran the mySQL import file through a converter msql2sqlite . There is also a nice CLI for SQLite called litecli,
You can get the script from github here .
TL;DR:
- Install python
- Install litecli (
pip install litecli) - Create a folder for the project
- Download the script to that folder
- On the first run:
litecli classicCars.db(or whatever name you want).read cclite.sql
- After that, just
litecli classicCars.db - Help is your friend.
Somewhere, years ago, I found this ERD, which has the data types and foreign keys indicated, which can be helpful as well.
