Music sample database

Artists, albums and tracks with release years and durations.

3 tables · 68 rows · 16 KB

Tables

albums 18 rows
  • album_id integer PK
  • title text
  • artist_id integer → artists
  • release_year integer
album_idtitleartist_idrelease_year
1Abbey Road11969
2Sgt. Pepper's Lonely Hearts Club Band11967
3The White Album11968
artists 10 rows
  • artist_id integer PK
  • name text
  • genre text
artist_idnamegenre
1The BeatlesRock
2Pink FloydProgressive Rock
3Michael JacksonPop
tracks 40 rows
  • track_id integer PK
  • title text
  • album_id integer → albums
  • duration_seconds integer
track_idtitlealbum_idduration_seconds
1Come Together1259
2Something1182
3Here Comes the Sun1185

How the tables connect

Try a query

SELECT a.name AS artist, COUNT(t.track_id) AS tracks,
       ROUND(SUM(t.duration_seconds) / 60.0, 1) AS minutes
FROM artists a
JOIN albums al ON al.artist_id = a.artist_id
JOIN tracks t ON t.album_id = al.album_id
GROUP BY a.name
ORDER BY minutes DESC;

Use it on your computer

The SQLite file opens with the sqlite3 command-line tool or any SQLite app:

sqlite3 music.sqlite
.tables

For MySQL, PostgreSQL or SQL Server, run the SQL script instead. It is plain CREATE TABLE and INSERT statements written for SQLite, so another database may need a small change.

8 practice questions on this database

  1. Easy Convert Seconds to Minutes Convert Track Duration to Minutes Free
  2. Easy Group Years by Decade Group Albums by Decade Free
  3. Easy Longest Item per Group Each Artist's Longest Song Free
  4. Easy UPDATE a Text Value Relabel the Genre Free
  5. Medium Rows Above Their Group Average Above-Average Track Length per Album Premium
  6. Medium Counts and Totals Across Joined Tables Most Prolific Artists Premium
  7. Medium Composite Index on Two Columns The Two-Column Index Premium
  8. Hard DELETE Using a Subquery Drop the Legacy Catalog Premium

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app

More sample databases