SQLite Sample Database And Its Diagram (in PDF format)

Summary: in this tutorial, we first introduce you to an SQLite sample database. Then, we will give you the links to download the sample database and its diagram. At the end of the tutorial, we will show you how to connect to the sample database using the sqlite3 tool.

Introduction to the SQLite sample database

We provide you with the SQLite sample database named Chinook. The Chinook sample database is a good database for practicing with SQL, especially SQLite.

The following database diagram illustrates the Chinook database tables and their relationships.

Chinook sample database tables

The Chinook sample database has 11 tables as follows:

employees table stores employee data such as id, last name, first name, etc. It also has a field named ReportsTo to specify who reports to whom.
customers table stores customer data.
invoices & invoice_items tables: these two tables store invoice data. The invoices table stores invoice header data and the invoice_items table stores the invoice line items data.
artists table stores artist data. It is a simple table that contains the id and name.
albums table stores data about a list of tracks. Each album belongs to one artist. However, one artist may have multiple albums.
media_types table stores media types such as MPEG audio and AAC audio files.
genres table stores music types such as rock, jazz, metal, etc.
tracks table stores the data of songs. Each track belongs to one album.
playlists & playlist_track tables: playlists table stores data about playlists. Each playlist contains a list of tracks. Each track may belong to multiple playlists. The relationship between the playlists and tracks tables is many-to-many. The playlist_track table is used to reflect this relationship.

Download SQLite sample database

You can download the SQLite sample database using the following link.

Download SQLite sample database

If you want to have the database diagram for reference, you can download both black and white and color versions in PDF format.

Download the SQLite sample database diagram

Download the SQLite sample database diagram (color version)

How to connect to SQLite sample database

The sample database file is in ZIP format, therefore, you need to extract it to a directory, for example, C:\sqlite\. The name of the file is chinook.db

If you don’t have Zip software installed, you can download free Zip software such as 7-zip.

First, open the Command Prompt on Windows or a Terminal on Unix-like systems and navigate to the SQLite directory where the sqlite3.exe file is located.

Second, use the sqlite3 command to connect to the chinook sample database located in the same directory.

sqlite3 chinook.dbCode language: Shell Session (shell)

It’ll show something like this:

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite>Code language: CSS (css)

Third, show all tables in the Chinook database using the .tables command:

.tablesCode language: CSS (css)

Output:

albums          employees       invoices        playlists
artists         genres          media_types     tracks
customers       invoice_items   playlist_trackCode language: SQL (Structured Query Language) (sql)

Finally, type the .exit command to quit the sqlite3 tool:

.exitCode language: CSS (css)

Summary

In this tutorial, we have introduced you to the Chinook SQLite sample database and showed you how to connect to it using the SQLite3 tool.

Was this tutorial helpful ?