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 namedReportsTo
to specify who reports to whom. -
customers
table stores customer data. -
invoices
&invoice_items
tables: these two tables store invoice data. Theinvoices
table stores invoice header data and theinvoice_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 theplaylists
andtracks
tables is many-to-many. Theplaylist_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.db
Code 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:
.tables
Code language: CSS (css)
Output:
albums employees invoices playlists
artists genres media_types tracks
customers invoice_items playlist_track
Code language: SQL (Structured Query Language) (sql)
Finally, type the .exit
command to quit the sqlite3 tool:
.exit
Code 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.