Homework #1 - SQL

Overview

The first homework is to construct a set of SQL queries for analyzing a dataset that will be provided to you. For this, you will look into MusicBrainz data. This homework is an opportunity to: (1) learn basic and certain advanced SQL features, and (2) get familiar with using a full-featured DBMS, DuckDB.

This is a single-person project that will be completed individually (i.e., no groups).

The Database

MusicBrainz is a community-maintained, open encyclopedia of music metadata: who made a piece of music, what it was called, when and where it came out, who pressed it, and what physical or digital object it shipped on. It is the catalog that a lot of music software (tagging tools, media players, streaming back-ends) consults when it needs to know what a given track actually is.

The database you will be working with, musicbrainz-2025.duckdb, is a snapshot of the MusicBrainz core tables restricted to releases that came out in 2025. It is roughly 46K releases, 260K tracks, 445K recordings, and 128K artists. Everything else in the file — labels, areas, languages, formats — is the full reference data that those releases point at.

The download is a compressed archive. Unpack it to get musicbrainz-2025.duckdb:

tar -zxf musicbrainz-2025.tar.gz

Installing DuckDB

You need the DuckDB CLI to work on this homework. It is widely available through Homebrew and other package managers; for the exact command for your platform, follow the official instructions at https://duckdb.org/install/.

We grade with DuckDB 1.4.5 (LTS), so install that version or newer. Check what you have with:

duckdb --version

Once it is installed, you can open the database from a shell:

duckdb musicbrainz-2025.duckdb

Useful CLI commands: .tables lists the tables, DESCRIBE <table>; prints a table's columns, .mode box prints results in a readable grid, and .quit exits.

Schema

These are the relevant tables and relationships between them.

Core entity tables

TableDescriptionKey columns
artist A musician, group, orchestra, or character. id, name, sort_name, begin_date_year, end_date_year, type → artist_type, area → area, gender → gender, comment (a short disambiguation blurb)
artist_credit A reusable credit line shared by everything one or more artists made together. id, name (the fully rendered credit string), artist_count
artist_credit_name Resolves a credit into its individual artists. artist_credit → artist_credit, position (0-based order within the credit), artist → artist, name (the name as credited, which may differ from artist.name), join_phrase
release_group An album/single/EP as an abstract work. id, name, artist_credit, type → release_group_primary_type
release One concrete published edition. id, name, artist_credit, release_group, status → release_status, packaging → release_packaging, language → language, script → script, barcode
medium One physical or digital disc within a release. id, release → release, position (disc 1, disc 2, ...), format → medium_format
track One entry in a medium's track listing. id, recording → recording, medium → medium, position, number, name, artist_credit, length (milliseconds)
recording A distinct piece of recorded audio. id, name, artist_credit, length (milliseconds), video
label A record label, imprint, or distributor. id, name, begin_date_year, end_date_year, label_code, type → label_type, area → area
area A geographic entity: a country, a subdivision, a city, an island. id, name, type → area_type
TableDescription
release_country Where and when a release came out. Columns: release, country → area, date_year, date_month, date_day. A release can appear here more than once (one row per country), and the date parts may be NULL.
release_unknown_country Same date columns, for releases with a known date but no known country.
release_label Many-to-many between releases and labels, plus catalog_number.
release_group_secondary_type_join Many-to-many between release groups and secondary types (Live, Compilation, Soundtrack, ...).
country_area The subset of area.id values that are ISO countries.
language ISO language reference, used by release.language.
script ISO writing-system reference, used by release.script.
artist_type, gender, area_type, label_type, medium_format, release_status, release_packaging, release_group_primary_type, release_group_secondary_type Small lookup tables. Each has an id and a name; join to turn an integer code into a human-readable string.

Schema diagram

Each box is a table and each arrow is a foreign key pointing at the primary key it references. The dashed red arrows are the artist_credit references from point (3) above: releases, release groups, tracks, and recordings never point at an artist directly. Click the diagram to view it full size.

Entity-relationship diagram of the MusicBrainz 2025 schema

Extra

Consider these three extra notes about the structure and semantics of the MusicBrainz database that might be a source of confusion:

  1. A recording is not a track. A recording is an abstract piece of recorded audio — one performance, captured once. A track is that recording as it appears at a specific position on a specific disc of a specific release. The same recording can appear as many tracks (original album, greatest-hits compilation, deluxe reissue).
  2. A release_group is not a release. A release group is the album as a concept ("OK Computer"); a release is one concrete published edition of it (the 1997 UK CD, the 2017 vinyl reissue). The release group is where the Album/Single/EP type lives.
  3. Nothing points at an artist directly. Releases, recordings, and tracks all point at an artist_credit, which is a reusable, ordered list of artists plus the punctuation between them ("Simon & Garfunkel", "Jay-Z feat. Alicia Keys"). To get from a release to its artists you must go through artist_credit_name.

Example Queries

These three queries are not part of the assignment. They are here to show you the join paths you will need. Under each one is the output it produces on the dataset, as printed by the DuckDB CLI in .mode box.

Getting from a release to the artists who made it. Note that this goes through artist_credit_name, not straight to artist:

SELECT r.name AS release_name,
       a.name AS artist_name,
       acn.position
FROM release AS r
  JOIN artist_credit_name AS acn ON acn.artist_credit = r.artist_credit
  JOIN artist AS a ON a.id = acn.artist
WHERE r.name = 'Our Calling'
ORDER BY acn.position;

┌──────────────┬─────────────────┬──────────┐
│ release_name │   artist_name   │ position │
├──────────────┼─────────────────┼──────────┤
│ Our Calling  │ Piers Faccini   │ 0        │
│ Our Calling  │ Ballaké Sissoko │ 1        │
└──────────────┴─────────────────┴──────────┘

Turning integer codes into names. Almost every type/status column is a foreign key into a tiny lookup table:

SELECT r.name,
       rgpt.name AS primary_type,
       rs.name AS status,
       mf.name AS format
FROM release AS r
  JOIN artist_credit AS ac ON ac.id = r.artist_credit
  JOIN release_group AS rg ON rg.id = r.release_group
  JOIN release_group_primary_type AS rgpt ON rgpt.id = rg.type
  JOIN release_status AS rs ON rs.id = r.status
  JOIN medium AS m ON m.release = r.id
  JOIN medium_format AS mf ON mf.id = m.format
WHERE ac.name = 'Franz Ferdinand'
ORDER BY r.name;

┌───────────────────────────────────┬──────────────┬──────────┬───────────────┐
│               name                │ primary_type │  status  │    format     │
├───────────────────────────────────┼──────────────┼──────────┼───────────────┤
│ City Sessions (Amazon Music Live) │ EP           │ Official │ Digital Media │
│ Hooked                            │ Single       │ Official │ Digital Media │
│ Some Remixes of Hooked            │ EP           │ Official │ Digital Media │
│ You Could Have It So Much Better  │ Album        │ Official │ 12" Vinyl     │
└───────────────────────────────────┴──────────────┴──────────┴───────────────┘

Walking the full release → medium → track → recording chain. This prints the track listing of one release's first disc:

SELECT m.position AS disc,
       t.position AS track_no,
       t.name AS track_name,
       rec.length / 1000.0 AS seconds
FROM release AS r
  JOIN medium AS m ON m.release = r.id
  JOIN track AS t ON t.medium = m.id
  JOIN recording AS rec ON rec.id = t.recording
WHERE r.name = 'Our Calling' AND m.position = 1
ORDER BY t.position;

┌──────┬──────────┬─────────────────────┬─────────┐
│ disc │ track_no │     track_name      │ seconds │
├──────┼──────────┼─────────────────────┼─────────┤
│ 1    │ 1        │ One Half of a Dream │ 252.587 │
│ 1    │ 2        │ I Wanted to Belong  │ 229.533 │
│ 1    │ 3        │ If Nothing Is Real  │ 245.013 │
│ 1    │ 4        │ Mournful Moon       │ 232.306 │
│ 1    │ 5        │ Ninna nanna         │ 307.626 │
│ 1    │ 6        │ Borne on the Wind   │ 253.787 │
│ 1    │ 7        │ Go Where Your Eyes  │ 357.6   │
│ 1    │ 8        │ Shadows Are         │ 259.76  │
│ 1    │ 9        │ North and South     │ 196.706 │
│ 1    │ 10       │ By Your Hand        │ 303.347 │
└──────┴──────────┴─────────────────────┴─────────┘

Problems

Write each query in its own file named q1.sql through q10.sql. Each file must contain exactly one SQL statement.

$ mkdir submission
$ cd submission
$ touch q1.sql q2.sql [...] q10.sql

Once finished, package all *.sql files into a .zip file:

$ cd submission
$ zip -j ../submission.zip .

Important. You need to use -j with zip or the Gradescope autograder might not be able to grade your submission correctly.

Every question below fixes a complete ordering and, where relevant, a row limit, so each has exactly one correct output. Match the column order given in the question; column names do not matter.

Two conventions used throughout:

Q1 [5 pts]

Oldest record labels. Find the 10 oldest record labels. Only consider labels with a founding year (begin_date_year) of 1800 or later. Report the label name and its founding year, sorted by founding year ascending, breaking ties by label name ascending.

Output schema

label_name|founding_year

Sample output (the first row of the expected result)

John Lewis|1864

Q2 [5 pts]

Most common release languages. Find the 10 languages that the most releases are sung or spoken in. Report the language name and the number of releases in that language, sorted by that count descending, breaking ties by language name ascending.

Output schema

language_name|num_releases

Sample output (the first row of the expected result)

English|26664

Q3 [5 pts]

Widely used media formats. A release ships on one or more mediums, and every medium has a format: a CD, a slab of vinyl, a digital download. Find every format that at least 100 mediums in the dataset were released on. Report the format name and the number of mediums in that format, sorted by that count descending, breaking ties by format name ascending. Do not limit the number of rows.

Output schema

format_name|num_mediums

Sample output (the first row of the expected result)

Digital Media|43434

Q4 [10 pts]

Box sets. Find the 10 releases with the largest track listings. Report the release name, the number of distinct mediums it spans, and its total number of tracks, sorted by the total number of tracks descending, breaking ties by release name ascending.

Output schema

release_name|num_mediums|num_tracks

Sample output (the first row of the expected result)

Complete Recordings On Deutsche Grammophon|65|673

Q5 [10 pts]

Most prolific labels. Find the 10 labels that put out the most releases in 2025. Exclude the placeholder label [no label]. A release that lists the same label twice should only be counted once for that label. MusicBrainz sometimes stores what we would call one label as several rows with the same name but different id values — there are two rows named Columbia and three named BMG — so aggregate by label name, treating same-named rows as a single label. Report the label name and its number of distinct releases, sorted by that count descending, breaking ties by label name ascending.

Output schema

label_name|num_releases

Sample output (the first row of the expected result)

Columbia|117

Q6 [10 pts]

Albums that never reached America. Find the 10 countries in which the most albums were released, counting only those albums that were never released in the United States. An album is a release whose release group has the primary type Album. A release can be published in several countries at once, so an album counts here only if the United States is not among them. Remember that a country is an area of type Country, so pseudo-areas such as [Worldwide] do not count. Report the country name and the number of distinct qualifying albums released there, sorted by that count descending, breaking ties by country name ascending.

Output schema

country_name|num_albums

Sample output (the first row of the expected result)

Japan|477

Q7 [10 pts]

Session players. Some artists appear all over the recordings in this dataset yet are never credited on the front of a release — conductors, backing ensembles, and featured performers. Find the 10 such artists who are credited on the most distinct recordings but are credited on zero releases. Report the artist name and the number of distinct recordings they are credited on, sorted by that count descending, breaking ties by artist name ascending.

Output schema

artist_name|num_recordings

Sample output (the first row of the expected result)

GrlCHEXXIT!!|394

Q8 [15 pts]

Top labels per market. First identify the 5 countries with the most distinct releases (ties broken by country name ascending). Then, for each of those 5 countries, find the 3 labels with the most distinct releases in that country, excluding [no label]; break ties between labels by label name ascending. As in Q5, aggregate labels by name, so rows that share a name count as a single label. Report the country name, the label name, and that label's number of distinct releases in that country. Sort by country name ascending, then by release count descending, then by label name ascending.

Output schema

country_name|label_name|num_releases

Sample output (the first row of the expected result)

France|Messe Basse Production|10

Q9 [15 pts]

Track-for-track Runtime The artist Harrison Gordon put out exactly one release in this dataset, a six-track EP called Spring Break!. Somewhere else in the dataset, on some other release entirely, there is a track sitting at the same position in the tracklist that runs for almost exactly the same number of milliseconds.

For each of the six track positions on Spring Break!, find the track that (a) sits at that same position on its own medium, (b) belongs to some release other than Spring Break!, and (c) has a known length that is as close as possible to the length of the Spring Break! track at that position. Report the position, the Spring Break! track name, the name of the release the match came from, the matching track name, the matching track's length in milliseconds, and the absolute difference between the two lengths. Sort by position ascending, then by release name ascending, then by matching track name ascending. If two or more tracks tie for closest at a given position, report all of them.

Output schema

position|track_name|match_release_name|match_track_name|match_length_ms|length_difference

Sample output (the first row of the expected result)

1|the Greatest Song Ever Written|This One's Gonna Hurt|Intro|43111|11

Q10 [15 pts]

Proximity to Hot Mulligan. The #1 hot new band, Hot Mulligan, have collaborated with a number of other artists in 2025. We want to know how far these connections go.

Say that two distinct artists are collaborators if they both appear in the same artist credit (that is, both are listed in artist_credit_name for the same artist_credit). This relation defines a graph on artists. An artist's degree of separation from Hot Mulligan is the length of the shortest chain of collaborators leading back to them: a direct collaborator is 1, a collaborator of a direct collaborator is 2, and so on.

Find every artist within 4 degrees of separation of the artist named Hot Mulligan. Report the artist name and their degree of separation. Do not include Hot Mulligan themselves, and report each artist exactly once, at their smallest degree — an artist reachable in both 1 and 3 hops is a 1. Sort by degree of separation ascending, breaking ties by artist name ascending. Hint: you cannot write imperative code for this question, but WITH RECURSIVE may be useful.

Output schema

artist_name|degree_of_separation

Sample output (the first row of the expected result)

Cory Castro|1

Grading Rubric

Each submission will be graded based on whether the SQL queries fetch the expected sets of tuples from the database. Only one statement is allowed in each SQL query. Note that your SQL queries will be auto-graded by comparing their outputs (i.e. tuple sets) to the correct outputs. For your queries, the order of the output columns is important; their names are not.

Late Policy

See the late policy in the syllabus.

Submission

We use the Autograder from Gradescope for grading in order to provide you with immediate feedback. After completing the homework, you can submit your compressed folder submission.zip (only one file) to Gradescope:

Important: Use the Gradescope course code announced on Piazza.

We will be comparing the output files using a function similar to diff. You can submit your answers as many times as you like.

AI Policy

You are not permitted to use coding agents or LLMs on this or any homework assignment. The penalty for violating this policy may include an Academic Integrity Violation (AIV) and/or a grade adjustment.

Collaboration Policy

WARNING: All of the code for this project must be your own. You may not copy source code from other students or other sources that you find on the web. Plagiarism will not be tolerated. See CMU's Policy on Academic Integrity for additional information.