Build Your First Database with SQLite

Design a relational database schema and write SQL queries from scratch.

Introduction

30 Second Summary

Every time you search for a song on Spotify, check your bank balance, or scroll through social media, a database is answering your question in milliseconds. Relational databases are the backbone of almost every application you use daily.

In this project, you will build a music store database from scratch using SQLite and SQL. You'll design tables, connect them with relationships, and write queries that filter, sort, and combine data across multiple tables.

What You'll Build

A fully functional music store database with 3 interconnected tables (artists, albums, songs) running on SQLite, queried through SQL scripts in VS Code.

By the end of this project, you'll have:

  • A SQLite database file with 3 related tables modeling a music store (artists, albums, songs).
  • A collection of SQL queries that filter, sort, and combine data from multiple tables.
  • Hands-on experience with the core SQL operations that 95% of database work depends on.
  • Secret Mission: Answer business questions using aggregate functions like COUNT, SUM, AVG, and GROUP BY.

Do I need any prior experience?

No prerequisites needed. This project guides you through installing every tool from scratch. All you need is a computer and curiosity.

Configure VS Code for SQL Development

Every app you use daily stores data in a database. Your music streaming service, your bank, and your social media feed all rely on SQL to organize and retrieve information.

In this step, you'll create a dedicated project folder and initialize your first SQLite database file. You'll also connect VS Code's SQLite extension to your database so you can explore it visually as you build.

In this step, get ready to:
  • Create a project folder for your music store database.
  • Initialize an empty SQLite database file.
  • Connect the SQLite extension to your database for visual exploration.
Create your project folder and database

You need a dedicated folder to keep your SQL files and database together. You'll create this folder on your Desktop so it's easy to find.

  • In VS Code, open the integrated terminal by pressing Cmd+` (macOS) or Ctrl+` (Windows).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder by running:
mkdir music-store-db
  • Confirm the folder was created by running:
ls
  • You should see music-store-db in the output.

Now open this folder in VS Code so the integrated terminal and file explorer are rooted in your project.

  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop and select the music-store-db folder.
  • Click Open (or Select Folder on some systems).

Seeing a trust dialog?

VS Code may ask "Do you trust the authors of the files in this folder?" Click Yes, I trust the authors to continue. This enables all VS Code features for your project folder.

VS Code now shows your empty project folder in the Explorer sidebar. The integrated terminal's working directory is automatically set to music-store-db.

  • Open a fresh integrated terminal by pressing Cmd+` (macOS) or Ctrl+` (Windows).
  • Create your SQLite database by running this command:
sqlite3 music_store.db

What does this command do?

sqlite3 is the SQLite command-line tool. When you give it a filename that doesn't exist yet, it creates a new empty database file with that name and opens an interactive session.

You should see the SQLite prompt (sqlite>). This means you're inside the database and can run SQL commands.

  • Exit the SQLite prompt by typing:
.quit
  • Confirm the database file was created by running:
ls
  • You should see music_store.db in the output.

Seeing 'command not found'?

This means sqlite3 is not in your system's PATH. Make sure SQLite is installed and its location is added to your PATH environment variable.

help me fix sqlite3 command not found on my system

Connect the extension to your database

The SQLite extension you have installed adds a visual explorer panel to VS Code. Connecting it to your database lets you browse tables and data without leaving the editor.

  • Press Cmd+Shift+P (macOS) or Ctrl+Shift+P (Windows) to open the Command Palette.
  • Type SQLite: Open Database and press Enter.
  • Select music_store.db from the list.
  • In the Explorer sidebar on the left, scroll down to find the SQLITE EXPLORER section.
  • You should see music_store.db listed there. It will be empty since you haven't created any tables yet.

Don't see SQLITE EXPLORER?

Make sure you ran the SQLite: Open Database command and selected your database file. The panel only appears after a database is opened.

If the Explorer sidebar itself is hidden, press Cmd+Shift+E (macOS) or Ctrl+Shift+E (Windows) to reveal it.

help me get the SQLite Explorer panel to show up in VS Code

Your SQL development environment is ready. Next up, you'll design and create three interconnected tables to hold your music store data.

Design and Create Your Tables

Your development environment is ready. Now it's time to design the structure of your music store database.

A relational database organizes data into tables, where each table represents one type of thing (artists, albums, songs). The real power comes from connecting these tables together so you can ask questions that span multiple types of data.

In this step, get ready to:
  • Write your first SQL file and create the artists table.
  • Link tables together using foreign keys.
  • Verify your schema using SQLite commands.
Create the artists table

Every database starts with a schema. The schema defines what tables exist and what columns each table has. You will start with the simplest table: artists.

  • In VS Code, create a new file called 01_create_tables.sql inside your music-store-db folder.
  • Paste the following SQL into the file:
-- Create the artists table
CREATE TABLE IF NOT EXISTS artists (
    artist_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT,
    genre TEXT
);

What does this code do?

  • CREATE TABLE IF NOT EXISTS creates a new table only if it does not already exist. This prevents errors if you run the file more than once.
  • artist_id INTEGER PRIMARY KEY makes artist_id a unique identifier that auto-increments for each new row.
  • NOT NULL on the name column means every artist must have a name. The database will reject any row that tries to leave this column empty.
  • country and genre are optional columns (no NOT NULL constraint).
  • Save 01_create_tables.sql.
  • Right-click anywhere inside the file editor and select SQLite: Run Query.
  • Select music_store.db from the dropdown that appears.

What is SQLite: Run Query?

This command comes from the SQLite extension you installed in Step 1. It sends your SQL code to the database file you select. You can also trigger it with Cmd+Shift+Q (macOS) or Ctrl+Shift+Q (Windows).

  • Confirm the table was created by opening your integrated terminal and running:
sqlite3 music_store.db
  • Inside the sqlite3 prompt, run:
.tables

You should see artists listed in the output.

  • Type .quit to exit sqlite3.

Don't see artists listed?

  • Make sure you saved the file before running the query. Unsaved changes are not sent to the database.
  • Check that you selected music_store.db (not a different database) when prompted.
  • Verify your SQL has no typos. A missing parenthesis or comma will silently fail.

Help me figure out why my artists table wasn't created.

Create the albums table with a foreign key

The albums table needs to know which artist each album belongs to. A foreign key creates this connection by referencing a column in another table.

SQLite does not enforce foreign keys by default. You need to enable enforcement explicitly every time you open a connection to the database.

  • At the very top of 01_create_tables.sql (above the artists table), add these lines:
-- Enable foreign key enforcement (must be run every time you open a connection)
PRAGMA foreign_keys = ON;
  • Below the artists table, add the albums table by pasting this code:
-- Create the albums table (linked to artists)
CREATE TABLE IF NOT EXISTS albums (
    album_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist_id INTEGER NOT NULL,
    release_year INTEGER,
    price REAL,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

What does this code do?

PRAGMA foreign_keys = ON tells SQLite to actually enforce foreign key relationships. Without it, you could insert an album pointing to a non-existent artist and SQLite would not complain.

FOREIGN KEY (artist_id) REFERENCES artists(artist_id) means every value in the albums artist_id column must match an existing artist_id in the artists table. This prevents orphaned data.

Why enable PRAGMA every time?

PRAGMA foreign_keys is connection-scoped. It cannot be stored inside the database file. Every time you open a new connection (including running a query from VS Code), you need it at the top of your file.

  • Save 01_create_tables.sql.
  • Right-click inside the file and select SQLite: Run Query. Select music_store.db.
  • Verify by opening sqlite3 in your terminal:
sqlite3 music_store.db
  • Run this command to see the albums table structure:
.schema albums

You should see the full CREATE TABLE statement for albums, including the FOREIGN KEY line.

  • Type .quit to exit sqlite3.

Don't see the FOREIGN KEY in .schema output?

  • Make sure the FOREIGN KEY (artist_id) REFERENCES artists(artist_id) line is inside the CREATE TABLE parentheses, after the last column definition.
  • Check that you have a comma after the price REAL line. Missing commas between column definitions cause syntax errors.

Help me fix my albums table definition.

Create the songs table with a foreign key

The songs table follows the same pattern as albums. Each song belongs to one album, connected by a foreign key.

  • Below the albums table in 01_create_tables.sql, add the songs table by pasting this code:
-- Create the songs table (linked to albums)
CREATE TABLE IF NOT EXISTS songs (
    song_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    album_id INTEGER NOT NULL,
    duration_seconds INTEGER,
    track_number INTEGER,
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

What does this code do?

  • duration_seconds INTEGER stores the song length in seconds. Using seconds instead of minutes makes calculations easier later.
  • track_number INTEGER records the order of each song on its album.
  • FOREIGN KEY (album_id) REFERENCES albums(album_id) ensures every song points to a real album. You now have a chain: songs → albums → artists.
  • Save 01_create_tables.sql.
  • Right-click inside the file and select SQLite: Run Query. Select music_store.db.
  • Verify all three tables exist by opening sqlite3:
sqlite3 music_store.db
  • Run this command:
.tables

You should see all three tables listed: albums, artists, and songs.

  • Type .quit to exit sqlite3.

Missing one of the tables?

  • Make sure every CREATE TABLE statement ends with a closing parenthesis followed by a semicolon: );.
  • Check that you ran SQLite: Run Query after adding the songs table. Adding code to the file does not run it automatically.
  • Confirm your sqlite3 session is pointing at the same music_store.db file in your music-store-db folder.

Help me figure out why one of my tables is missing.

You can also check your tables visually in the SQLITE EXPLORER panel in the VS Code sidebar. Click the refresh icon if you do not see the new tables yet.

✔️ Awesome, I've got everything!

Great. Double check you have saved 01_create_tables.sql and all three tables appear in .tables.

ⓧ I'd like to double check the full code

Here is the complete 01_create_tables.sql file. Make sure yours matches exactly:

-- Enable foreign key enforcement (must be run every time you open a connection)
PRAGMA foreign_keys = ON;

-- Create the artists table
CREATE TABLE IF NOT EXISTS artists (
    artist_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT,
    genre TEXT
);

-- Create the albums table (linked to artists)
CREATE TABLE IF NOT EXISTS albums (
    album_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist_id INTEGER NOT NULL,
    release_year INTEGER,
    price REAL,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

-- Create the songs table (linked to albums)
CREATE TABLE IF NOT EXISTS songs (
    song_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    album_id INTEGER NOT NULL,
    duration_seconds INTEGER,
    track_number INTEGER,
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

Your database schema is in place with three connected tables. Next up, you will fill these tables with real data and watch your relationships come to life.

Populate Your Database with Data

Your three tables are created and connected by foreign keys. An empty database is like a music store with shelves but no records.

In this step, you'll fill those tables using SQL's INSERT INTO statement. Each row you add must respect the relationships you defined, so albums point to real artists and songs point to real albums.

In this step, get ready to:
  • Insert 6 artists with varied genres and countries.
  • Add 8 albums linked to their respective artists.
  • Populate 18 songs distributed across your albums and test foreign key enforcement.
Insert artists into the database

Every album and song in your database will eventually point back to an artist. That makes the artists table the foundation you need to fill first.

  • Create a new file called 02_insert_data.sql in your music-store-db folder.
  • Add the following code to insert 6 artists:
PRAGMA foreign_keys = ON;

-- Insert artists
INSERT INTO artists (name, country, genre) VALUES ('Shakira', 'Colombia', 'Pop');
INSERT INTO artists (name, country, genre) VALUES ('Bad Bunny', 'Puerto Rico', 'Reggaeton');
INSERT INTO artists (name, country, genre) VALUES ('Rosalia', 'Spain', 'Flamenco Pop');
INSERT INTO artists (name, country, genre) VALUES ('Gustavo Cerati', 'Argentina', 'Rock');
INSERT INTO artists (name, country, genre) VALUES ('Natalia Lafourcade', 'Mexico', 'Folk');
INSERT INTO artists (name, country, genre) VALUES ('Calle 13', 'Puerto Rico', 'Hip Hop');

What does this code do?

PRAGMA foreign_keys = ON appears again because SQLite resets this setting every time a new connection opens. Including it at the top of every .sql file ensures foreign keys are always enforced when you run the file.

Each INSERT INTO statement adds one row. The column list (name, country, genre) tells SQLite which columns receive data. You don't need to specify artist_id because SQLite auto-generates it for INTEGER PRIMARY KEY columns.

  • Save 02_insert_data.sql.
  • Run the file by pressing Ctrl+Shift+P (Windows) or Cmd+Shift+P (macOS), typing SQLite: Run Query, and selecting music_store.db.
  • Open the integrated terminal and verify by running:
sqlite3 music_store.db "SELECT * FROM artists;"

You should see 6 rows, one for each artist you inserted, with auto-generated IDs from 1 to 6.

Not seeing any artists?

  • Make sure you saved the file before running the query. Unsaved changes won't execute.
  • Confirm you selected music_store.db when prompted. If you picked a different database, the data went there instead.
  • Check for typos in your INSERT INTO statements. Every value string must be enclosed in single quotes.

Help me troubleshoot why my INSERT statements aren't working in SQLite.

Insert albums linked to artists

Albums reference artists through the artist_id column. Each value you provide must match an existing artist_id in the artists table, or the foreign key constraint will reject it.

  • In 02_insert_data.sql, add the following album inserts below your artist inserts:
-- Insert albums
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Laundry Service', 1, 2001, 9.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('El Dorado', 1, 2017, 12.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Un Verano Sin Ti', 2, 2022, 14.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('YHLQMDLG', 2, 2020, 13.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('El Mal Querer', 3, 2018, 11.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Bocanada', 4, 1999, 10.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Fuerza Natural', 4, 2009, 11.49);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Musas', 5, 2017, 10.49);

What does this code do?

Each album row includes an artist_id that points to one of the artists you just inserted. Shakira is artist_id 1, so both 'Laundry Service' and 'El Dorado' use 1 to link back to her.

Notice that Calle 13 (artist_id 6) has no albums. This is intentional. You'll use this gap later to demonstrate the difference between INNER JOIN and LEFT JOIN.

  • Save 02_insert_data.sql.
  • Select only the album INSERT lines you just added (highlight them), then press Ctrl+Shift+P (Windows) or Cmd+Shift+P (macOS) and run SQLite: Run Query against music_store.db.
  • Verify by running in the terminal:
sqlite3 music_store.db "SELECT * FROM albums;"

You should see 8 rows with valid artist_id values ranging from 1 to 5.

Select text to run only part of a file

The SQLite extension runs whatever text you have highlighted. If nothing is selected, it runs the entire file. Since you already inserted artists, highlight only the new album lines to avoid duplicate artist rows.

Seeing a foreign key constraint error?

  • Double-check that you ran the artist inserts first. Albums reference artists, so the artists must exist before the albums.
  • If you accidentally ran the entire file again and see duplicate rows, delete the database file and re-run the full 01_create_tables.sql and 02_insert_data.sql from scratch.

Help me fix a foreign key constraint error when inserting albums.

Insert songs and test foreign key constraints

Songs are the deepest level in your data model. Each song points to an album, which in turn points to an artist. This chain of relationships is what makes a relational database powerful.

  • In 02_insert_data.sql, add the following song inserts below your album inserts:
-- Insert songs
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Whenever Wherever', 1, 197, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Underneath Your Clothes', 1, 224, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Objection', 1, 232, 3);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Chantaje', 2, 196, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Me Enamore', 2, 187, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Me Porto Bonito', 3, 178, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Titi Me Pregunto', 3, 244, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Ojitos Lindos', 3, 258, 3);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Yo Perreo Sola', 4, 172, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('La Dificil', 4, 163, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Malamente', 5, 175, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Pienso en Tu Mira', 5, 203, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Puente', 6, 298, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Bocanada', 6, 267, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Tu Sin Mi', 5, 219, 3);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Cactus', 7, 245, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Hasta la Raiz', 8, 224, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Nunca Es Suficiente', 8, 259, 2);

What does this code do?

Each song uses album_id to connect to its parent album. The songs are distributed across all 8 albums, with each album getting 2-3 tracks.

The duration_seconds column stores song length as an integer. This makes it easy to calculate totals and averages later with aggregate functions.

  • Save 02_insert_data.sql.
  • Select only the song INSERT lines, then run SQLite: Run Query against music_store.db.
  • Verify the total count by running in the terminal:
sqlite3 music_store.db "SELECT COUNT(*) FROM songs;"

You should see 18 as the result.

Now test that your foreign key constraint actually works. Try inserting a song with a non-existent album_id:

sqlite3 music_store.db "PRAGMA foreign_keys = ON; INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Fake Song', 999, 180, 1);"

SQLite rejects the insert because album 999 does not exist. This is your foreign key constraint in action, protecting data integrity.

The fake insert went through without an error?

  • Foreign key enforcement is OFF by default in SQLite. The command above includes PRAGMA foreign_keys = ON before the INSERT to enable it. Make sure both statements are in the same command.
  • If the row slipped in, remove it by running:
sqlite3 music_store.db "DELETE FROM songs WHERE title = 'Fake Song';"

Help me understand why my foreign key constraint is not working in SQLite.

✔️ Awesome, I've got everything!

Great. Double check you've saved 02_insert_data.sql and that your database has 6 artists, 8 albums, and 18 songs.

ⓧ I'd like to double check the full code

Here is the complete 02_insert_data.sql file:

PRAGMA foreign_keys = ON;

-- Insert artists
INSERT INTO artists (name, country, genre) VALUES ('Shakira', 'Colombia', 'Pop');
INSERT INTO artists (name, country, genre) VALUES ('Bad Bunny', 'Puerto Rico', 'Reggaeton');
INSERT INTO artists (name, country, genre) VALUES ('Rosalia', 'Spain', 'Flamenco Pop');
INSERT INTO artists (name, country, genre) VALUES ('Gustavo Cerati', 'Argentina', 'Rock');
INSERT INTO artists (name, country, genre) VALUES ('Natalia Lafourcade', 'Mexico', 'Folk');
INSERT INTO artists (name, country, genre) VALUES ('Calle 13', 'Puerto Rico', 'Hip Hop');

-- Insert albums
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Laundry Service', 1, 2001, 9.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('El Dorado', 1, 2017, 12.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Un Verano Sin Ti', 2, 2022, 14.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('YHLQMDLG', 2, 2020, 13.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('El Mal Querer', 3, 2018, 11.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Bocanada', 4, 1999, 10.99);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Fuerza Natural', 4, 2009, 11.49);
INSERT INTO albums (title, artist_id, release_year, price) VALUES ('Musas', 5, 2017, 10.49);

-- Insert songs
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Whenever Wherever', 1, 197, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Underneath Your Clothes', 1, 224, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Objection', 1, 232, 3);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Chantaje', 2, 196, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Me Enamore', 2, 187, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Me Porto Bonito', 3, 178, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Titi Me Pregunto', 3, 244, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Ojitos Lindos', 3, 258, 3);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Yo Perreo Sola', 4, 172, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('La Dificil', 4, 163, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Malamente', 5, 175, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Pienso en Tu Mira', 5, 203, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Puente', 6, 298, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Bocanada', 6, 267, 2);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Tu Sin Mi', 5, 219, 3);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Cactus', 7, 245, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Hasta la Raiz', 8, 224, 1);
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES ('Nunca Es Suficiente', 8, 259, 2);

Your database is stocked with real data across all three tables. Next up, you'll write queries to filter, sort, and search through this data using SELECT, WHERE, and ORDER BY.

Query and Filter Your Data

In the last step, you populated your database with artists, albums, and songs. Your tables are full of data, but data sitting in a database is only useful if you can ask questions of it.

In this step, you'll write SQL queries that retrieve exactly the data you need. You'll start with simple selections, then layer on filters, sorting, and limits to answer increasingly specific questions about your music store.

In this step, get ready to:
  • Write basic SELECT queries to retrieve data from your tables.
  • Filter results using WHERE with exact matches, patterns, and comparisons.
  • Sort and limit results using ORDER BY and LIMIT.
Write basic SELECT queries

The SELECT statement is how you read data from a database. Every question you ask your database starts with SELECT.

  • Create a new file called 03_queries.sql in your music-store-db folder.
  • Add the following queries to your new file:
-- === BASIC SELECT ===

-- Select all artists
SELECT * FROM artists;

-- Select only name and genre
SELECT name, genre FROM artists;

-- Select albums released after 2015
SELECT title, release_year FROM albums WHERE release_year > 2015;

What do these queries do?

  • SELECT * FROM artists retrieves every column and every row from the artists table. The * means "all columns."
  • SELECT name, genre FROM artists retrieves only the two columns you specify. This keeps results focused when you don't need every field.
  • The third query adds a WHERE clause to filter rows. Only albums with a release_year greater than 2015 are returned.
  • Save 03_queries.sql.
  • Right-click the file in the Explorer sidebar and select Run Query.
  • Select music_store.db when prompted.
  • You should see a results table showing all 6 artists, then a 2-column table of names and genres, then only albums released after 2015.

Not seeing any results?

  • Make sure you saved the file before running the query.
  • Check that you selected music_store.db as the target database. If you choose the wrong file, the query runs against an empty database.
  • Confirm you ran 02_insert_data.sql successfully in the previous step. Without data inserted, SELECT returns empty results.

Help me figure out why my SELECT queries return no results.

Filter with WHERE and operators

SELECT returns all rows by default. WHERE narrows results to only the rows matching a condition. You can combine multiple conditions and use pattern matching to build precise filters.

  • Add the following queries below the basic SELECT section in 03_queries.sql:
-- === FILTER WITH WHERE ===

-- Exact match: find artists from Colombia
SELECT * FROM artists WHERE country = 'Colombia';

-- Pattern matching: find artists whose name contains 'a'
SELECT * FROM artists WHERE name LIKE '%a%';

-- Multiple conditions with AND
SELECT * FROM albums WHERE release_year >= 2017 AND price < 13.00;

-- Multiple conditions with OR
SELECT * FROM artists WHERE country = 'Colombia' OR country = 'Mexico';

-- Comparison: albums cheaper than 12 dollars
SELECT title, price FROM albums WHERE price < 12.00;

What do these filters do?

  • The = operator checks for an exact match. Only Shakira is from Colombia, so that query returns 1 row.
  • LIKE '%a%' uses the % wildcard to match any characters before and after the letter "a." It finds any name containing that letter.
  • AND requires BOTH conditions to be true. OR requires only ONE to be true.
  • Comparison operators (>=, <) work on numbers just like in math.
  • Save 03_queries.sql and run the query again.
  • Check that your WHERE queries return fewer rows than SELECT * FROM artists. The Colombia query should return 1 row. The LIKE query should return multiple rows. The AND query should return albums that meet both conditions.

WHERE clause returning all rows?

  • String values in SQL must be wrapped in single quotes. WHERE country = Colombia without quotes causes an error. Use WHERE country = 'Colombia'.
  • LIKE is case-insensitive in SQLite by default for ASCII characters. If you're not getting expected results, double-check your wildcard placement.

Help me debug my WHERE clause.

Sort and limit results

Filtering narrows which rows you see. Sorting controls the order they appear. Combined with LIMIT, you can answer questions like "what are the top 3 most expensive albums?"

  • Add the following queries at the bottom of 03_queries.sql:
-- === SORT AND LIMIT ===

-- Sort albums by year (newest first)
SELECT title, release_year FROM albums ORDER BY release_year DESC;

-- Sort artists alphabetically
SELECT name, country FROM artists ORDER BY name ASC;

-- Top 3 most expensive albums
SELECT title, price FROM albums ORDER BY price DESC LIMIT 3;

-- Songs longer than 4 minutes (240 seconds), sorted by duration
SELECT title, duration_seconds FROM songs
WHERE duration_seconds > 240
ORDER BY duration_seconds DESC;

What do ORDER BY and LIMIT do?

  • ORDER BY sorts results by the column you specify. DESC means descending (highest first). ASC means ascending (lowest first, or A to Z).
  • LIMIT 3 cuts the results to only the first 3 rows. Without it, you'd see all 8 albums.
  • The last query combines WHERE, ORDER BY, and LIMIT patterns. It filters songs to only those longer than 240 seconds, then sorts by duration so the longest appear first.
  • Save 03_queries.sql and run the query.
  • Check the "Top 3 most expensive albums" results. You should see Un Verano Sin Ti at the top (14.99), followed by YHLQMDLG (13.99), then El Dorado (12.99). Only 3 rows appear.

Results not in the right order?

  • Double-check you wrote DESC (descending) not ASC (ascending). For "most expensive," you want the highest price first.
  • Make sure LIMIT comes after ORDER BY in your query. SQL clauses must appear in this order: SELECT, FROM, WHERE, ORDER BY, LIMIT.

Help me fix my ORDER BY or LIMIT query.

✔️ Awesome, I've got everything!

Great. Double check you've saved 03_queries.sql with all three sections (BASIC SELECT, FILTER WITH WHERE, SORT AND LIMIT).

ⓧ I'd like to double check the full code

-- === BASIC SELECT ===

-- Select all artists
SELECT * FROM artists;

-- Select only name and genre
SELECT name, genre FROM artists;

-- Select albums released after 2015
SELECT title, release_year FROM albums WHERE release_year > 2015;

-- === FILTER WITH WHERE ===

-- Exact match: find artists from Colombia
SELECT * FROM artists WHERE country = 'Colombia';

-- Pattern matching: find artists whose name contains 'a'
SELECT * FROM artists WHERE name LIKE '%a%';

-- Multiple conditions with AND
SELECT * FROM albums WHERE release_year >= 2017 AND price < 13.00;

-- Multiple conditions with OR
SELECT * FROM artists WHERE country = 'Colombia' OR country = 'Mexico';

-- Comparison: albums cheaper than 12 dollars
SELECT title, price FROM albums WHERE price < 12.00;

-- === SORT AND LIMIT ===

-- Sort albums by year (newest first)
SELECT title, release_year FROM albums ORDER BY release_year DESC;

-- Sort artists alphabetically
SELECT name, country FROM artists ORDER BY name ASC;

-- Top 3 most expensive albums
SELECT title, price FROM albums ORDER BY price DESC LIMIT 3;

-- Songs longer than 4 minutes (240 seconds), sorted by duration
SELECT title, duration_seconds FROM songs
WHERE duration_seconds > 240
ORDER BY duration_seconds DESC;

You now have a toolkit of SQL queries that can retrieve, filter, and sort data from your music store. Next up, you'll connect data across tables using JOINs to answer questions that span artists, albums, and songs at once.

Connect Your Data with JOINs

Your queries so far have pulled data from one table at a time. That is useful, but the real power of a relational database comes from combining data across tables.

In this step, you will write JOIN queries that connect artists to their albums and songs in a single result set. This is how real applications display pages like "all songs by this artist" or "albums released this year with artist names."

In this step, get ready to:
  • Write INNER JOIN queries to combine albums with artists and songs with albums.
  • Write LEFT JOIN queries to find artists who have no albums.
  • Write a multi-table JOIN that pulls data from all three tables in one query.
Write INNER JOIN queries to combine tables

An INNER JOIN combines rows from two tables where a matching value exists in both. If a row in one table has no match in the other, it is excluded from the results entirely.

  • Create a new file called 04_joins.sql in your music-store-db folder.
  • Add the PRAGMA statement and your first INNER JOIN queries by copying this code into the file:
PRAGMA foreign_keys = ON;

-- === INNER JOIN ===

-- Albums with their artist names
SELECT albums.title AS album_title, artists.name AS artist_name, albums.release_year
FROM albums
INNER JOIN artists ON albums.artist_id = artists.artist_id;

-- Songs with their album titles
SELECT songs.title AS song_title, albums.title AS album_title
FROM songs
INNER JOIN albums ON songs.album_id = albums.album_id;

What does this code do?

The first query starts from the albums table and joins it to artists wherever albums.artist_id matches artists.artist_id. The result is a combined row showing the album title, artist name, and release year side by side.

The AS keyword gives columns a friendlier name in the output. Since both albums and songs have a column called title, the aliases prevent confusion in results.

  • Save 04_joins.sql.
  • Right-click the file and select Run Query, then choose music_store.db.
  • You should see results showing combined columns from both tables. The first query shows album titles next to artist names. The second shows song titles next to album titles.

Seeing an error about ambiguous columns?

When two tables share a column name (like title), you must prefix it with the table name (e.g., albums.title). Check that every column reference includes its table prefix.

Also confirm the ON clause uses the correct column pair: albums.artist_id = artists.artist_id for the first query and songs.album_id = albums.album_id for the second.

Why isn't my INNER JOIN returning results?

Write LEFT JOIN queries to find artists without albums

An INNER JOIN only shows rows where both tables have a match. A LEFT JOIN keeps every row from the left table, filling in NULL where no match exists on the right side.

Your database has one artist (Calle 13) with no albums. A LEFT JOIN is how you find these "orphan" rows.

  • Add the following LEFT JOIN queries to the bottom of 04_joins.sql:
-- === LEFT JOIN ===

-- All artists and their albums (including artists with no albums)
SELECT artists.name, albums.title
FROM artists
LEFT JOIN albums ON artists.artist_id = albums.artist_id;

-- Find artists with NO albums
SELECT artists.name
FROM artists
LEFT JOIN albums ON artists.artist_id = albums.artist_id
WHERE albums.album_id IS NULL;

What does this code do?

The first query lists every artist alongside their album titles. Artists with no albums still appear, but with NULL in the title column.

The second query uses WHERE albums.album_id IS NULL to filter down to only artists with no matching album. This pattern is a standard way to find "missing" relationships in your data.

  • Save 04_joins.sql and run the query against music_store.db.
  • The first LEFT JOIN should show all 6 artists. Calle 13 appears with NULL in the album title column.
  • The second query should return only one row: Calle 13.

Why use LEFT JOIN instead of INNER JOIN here?

An INNER JOIN would have hidden Calle 13 entirely because they have no albums to match on. LEFT JOIN guarantees every row from the "left" table (artists) appears in the output, whether or not a match exists on the right.

This is useful in real apps for finding incomplete records. For example, customers who have never placed an order, or products with no reviews.

LEFT JOIN returning the same results as INNER JOIN?

Make sure the table order is correct. The "left" table (the one that keeps all rows) comes before the LEFT JOIN keyword. In this case, artists must come in the FROM clause, not albums.

Why is my LEFT JOIN not showing NULL rows?

Write a multi-table JOIN to pull data from all three tables

You can chain multiple JOINs in a single query. This lets you start from songs, connect to albums, then connect to artists, all in one result set.

  • Add the following multi-table JOIN queries to the bottom of 04_joins.sql:
-- === MULTI-TABLE JOIN ===

-- Songs with full context: song, album, artist, and year
SELECT
    songs.title AS song,
    albums.title AS album,
    artists.name AS artist,
    albums.release_year AS year
FROM songs
INNER JOIN albums ON songs.album_id = albums.album_id
INNER JOIN artists ON albums.artist_id = artists.artist_id;

-- Filter the multi-table join: only Reggaeton songs
SELECT
    songs.title AS song,
    albums.title AS album,
    artists.name AS artist
FROM songs
INNER JOIN albums ON songs.album_id = albums.album_id
INNER JOIN artists ON albums.artist_id = artists.artist_id
WHERE artists.genre = 'Reggaeton';

What does this code do?

The first query chains two JOINs: songs connects to albums (via album_id), then albums connects to artists (via artist_id). The result shows every song alongside its album name, artist name, and release year.

The second query adds a WHERE clause to filter the joined result. Even though genre lives in the artists table, you can filter on it because the JOIN made all columns available.

  • Save 04_joins.sql and run the query.
  • The first multi-table query should return all 18 songs, each showing the song title, album title, artist name, and year in one row.
  • The filtered query should return only songs by Bad Bunny (the Reggaeton artist), which includes tracks from Un Verano Sin Ti and YHLQMDLG.

Getting fewer rows than expected?

Check that both ON clauses reference the correct foreign key columns. The first JOIN connects songs.album_id to albums.album_id. The second connects albums.artist_id to artists.artist_id. Swapping the order or using the wrong column silently drops rows.

Why is my multi-table JOIN missing rows?

✔️ Awesome, I've got everything!

Great. Double check you have saved 04_joins.sql and that all your queries ran without errors.

ⓧ I'd like to double check the full code

Here is the complete 04_joins.sql file. Compare it against yours to spot any differences.

PRAGMA foreign_keys = ON;

-- === INNER JOIN ===

-- Albums with their artist names
SELECT albums.title AS album_title, artists.name AS artist_name, albums.release_year
FROM albums
INNER JOIN artists ON albums.artist_id = artists.artist_id;

-- Songs with their album titles
SELECT songs.title AS song_title, albums.title AS album_title
FROM songs
INNER JOIN albums ON songs.album_id = albums.album_id;

-- === LEFT JOIN ===

-- All artists and their albums (including artists with no albums)
SELECT artists.name, albums.title
FROM artists
LEFT JOIN albums ON artists.artist_id = albums.artist_id;

-- Find artists with NO albums
SELECT artists.name
FROM artists
LEFT JOIN albums ON artists.artist_id = albums.artist_id
WHERE albums.album_id IS NULL;

-- === MULTI-TABLE JOIN ===

-- Songs with full context: song, album, artist, and year
SELECT
    songs.title AS song,
    albums.title AS album,
    artists.name AS artist,
    albums.release_year AS year
FROM songs
INNER JOIN albums ON songs.album_id = albums.album_id
INNER JOIN artists ON albums.artist_id = artists.artist_id;

-- Filter the multi-table join: only Reggaeton songs
SELECT
    songs.title AS song,
    albums.title AS album,
    artists.name AS artist
FROM songs
INNER JOIN albums ON songs.album_id = albums.album_id
INNER JOIN artists ON albums.artist_id = artists.artist_id
WHERE artists.genre = 'Reggaeton';

Secret mission

Answer Business Questions with Aggregates

Your music store database is full of data. But what if a store manager asks: "Which artist has the most albums?" or "What's the average album price in each genre?" These questions require aggregate functions, a powerful SQL feature that lets you summarize groups of rows into single answers.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • The music-store-db project folder (contains music_store.db and all .sql files).
  • The VS Code SQLite extension by alexcvzz.

Keep everything running

No action needed. Choose this if you're still actively building or plan to revisit your queries soon.

  • Your music_store.db database stays on disk and costs nothing.
  • Your SQL files remain ready to run whenever you open the folder again in VS Code.
  • The SQLite extension stays installed and available for future SQL projects.

Pause - I'll come back to this later

Close VS Code to free up memory. Your project folder and database file remain on disk for when you return.

  • Close VS Code.
  • Your music-store-db folder stays on your Desktop with all files intact.
  • When you're ready to resume, open VS Code and select File > Open Folder to reopen music-store-db.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your database and SQL files.

  • Delete the project folder by running this command in your terminal:

macOS

rm -rf ~/Desktop/music-store-db

Windows

rmdir /s /q %USERPROFILE%\Desktop\music-store-db
  • Uninstall the SQLite extension in VS Code by opening the Extensions panel (Ctrl+Shift+X), finding SQLite by alexcvzz, and clicking Uninstall.

Nice Work!

Nice Work!

You just built a fully functional music store database from scratch. You now have hands-on experience with the core SQL operations that power virtually every application on the internet.

Here's what you accomplished:

  • Designed a relational schema with 3 interconnected tables, enforcing data integrity through PRIMARY KEY, NOT NULL, and FOREIGN KEY constraints.
  • Queried and filtered data using SELECT, WHERE, LIKE, ORDER BY, and LIMIT to extract exactly the data you needed.
  • Combined data across multiple tables using INNER JOIN and LEFT JOIN to answer questions that span relationships.
  • Secret Mission: Answered business questions using aggregate functions (COUNT, SUM, AVG) with GROUP BY and HAVING.

Ready to quiz yourself?