Build a Voice Lookup Database

Design a relational MariaDB database to catalog and search voice clips.

Introduction

30 Second Summary

You hear a voice in a movie trailer and think "I know that voice!" but you can't quite place it. A searchable database of voice clips would solve that problem instantly.

In this project, you will build a relational database from scratch using MariaDB and SQL. You'll design tables, insert voice clip data, and write queries that search by speaker name and voice characteristics.

What You'll Build

A relational database called voicelookup that stores voice clip metadata and lets you search clips by speaker name, duration, and descriptive voice traits.

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

  • A running MariaDB server installed and secured on your Fedora machine.
  • A multi-table relational database with speakers, voice clips, and linked records using foreign keys.
  • A collection of working SQL queries that search, filter, update, and delete voice data using JOINs and WHERE clauses.
  • Secret Mission: Add a many-to-many tagging system so you can search voices by multiple traits like "deep AND male AND British."

Are there any prerequisites?

None. This project includes full setup instructions for MariaDB. All you need is a Fedora 44 Linux machine and a terminal.

Before We Start

Before We Start...

Install and Secure MariaDB

Before you can store any voice data, you need a database engine running on your machine. MariaDB is an open-source relational database that speaks SQL and runs directly on your Fedora system.

In this step, you will install MariaDB from Fedora's built-in package repositories, start it as a background service, and lock down the default configuration so only you can access it.

In this step, get ready to:
  • Install MariaDB server on your Fedora machine.
  • Start the MariaDB service and set it to launch on boot.
  • Secure the installation with a root password and hardened defaults.
Install MariaDB server using dnf

Fedora 44 ships MariaDB 11.8 in its default repositories. This means you can install it with a single package manager command.

  • Check whether MariaDB is already installed by running this command:
mariadb --version

✔️ I see version 11.8 or higher

MariaDB is already installed and at the correct version. Skip ahead to the next substep to make sure the service is running.

ⓧ I see an older version

You have an older MariaDB installed from a previous Fedora release. Upgrade it to 11.8 by running:

sudo dnf upgrade mariadb-server

After the upgrade completes, continue to the next substep.

ⓧ Command not found

MariaDB is not installed yet. Install it by running this command:

sudo dnf install mariadb-server

When prompted to confirm the installation, type y and press Enter.

The download and install takes about a minute depending on your connection speed.

What is MariaDB?

MariaDB is a relational database management system. It stores data in structured tables with rows and columns, and you interact with it using SQL (Structured Query Language).

Fedora 44 includes MariaDB 11.8 as the distribution default. This is the latest Long Term Support release.

Start and enable the MariaDB service

Installing the package puts MariaDB on your machine, but it does not start running automatically on Fedora. You need to start the service and tell it to launch on boot.

  • Start MariaDB and enable it to run automatically on boot by running:
sudo systemctl enable --now mariadb

What does this command do?

The enable part tells systemd to start MariaDB every time your machine boots. The --now flag also starts it immediately so you do not have to run a separate start command.

  • Verify the service is running by checking its status:
sudo systemctl status mariadb

You should see active (running) in green text near the top of the output.

Not seeing active (running)?

Make sure you typed the command exactly as shown, including the sudo prefix. Without it, systemd cannot manage system services.

If the status says failed, check the error details in the output. A common cause is a leftover configuration file from a previous MariaDB install conflicting with the new version.

Help me troubleshoot why my MariaDB service failed to start on Fedora 44.

Run the security setup

A fresh MariaDB install has no root password, includes anonymous user accounts, and ships with a test database anyone can access. The secure installation script fixes all of this in one interactive session.

  • Lock down your MariaDB installation by running:
sudo mariadb-secure-installation

What will this script ask me?

The script walks you through a series of yes/no prompts. Here is how to answer each one:

  • Enter current password for root: press Enter (blank, since no password is set yet).
  • Switch to unix_socket authentication: type n.
  • Change the root password: type Y, then enter a password you will remember.
  • Remove anonymous users: type Y.
  • Disallow root login remotely: type Y.
  • Remove test database and access to it: type Y.
  • Reload privilege tables now: type Y.
  • Confirm you can connect to MariaDB with your new root password by running:
mysql -u root -p

Enter the root password you just set when prompted. You should see a MariaDB [(none)]> prompt, which means you are connected.

  • Exit the MariaDB prompt by typing:
EXIT;

Access denied for user root?

Double-check that you are typing the exact password you entered during the secure installation step. Passwords are case-sensitive.

If you forgot the password already, you can re-run sudo mariadb-secure-installation to set a new one.

Help me fix an access denied error when connecting to MariaDB as root on Fedora 44.

Your database engine is installed, running, and secured. Next up, you will create your first database and start building the tables that store voice data.

Create Your Database and Speaker Table

MariaDB is installed, secured, and ready to go. Now it is time to create the actual database where your voice data will live.

A relational database organizes data into tables with defined columns. In this step, you will create a database called voicelookup and build your first table to store speaker information.

In this step, get ready to:
  • Connect to MariaDB and create the voicelookup database.
  • Create the speakers table with auto-incrementing IDs.
  • Verify the table structure with SHOW CREATE TABLE.
Connect to MariaDB and create the voicelookup database

Before you can create tables, you need a database to hold them. Think of a database as a container that groups related tables together.

  • Connect to the MariaDB server by running this command in your terminal:
mysql -u root

What does this command do?

The mysql client connects you to the MariaDB server. The -u root flag tells it to log in as the root database user. On Fedora, MariaDB uses unix socket authentication by default, so you can connect without typing a password as long as you are the system root user.

Access denied?

If you see an access denied error, try connecting with sudo mysql -u root instead. This ensures your OS user matches the unix socket authentication that MariaDB expects.

If you set a password during the security setup and MariaDB asks for it, add the -p flag: mysql -u root -p.

Help me connect to MariaDB as root on Fedora

You should see the MariaDB prompt: MariaDB [(none)]>. This means you are inside the MariaDB client and ready to run SQL statements.

  • Create the voicelookup database and switch into it by running these SQL statements:
-- Create the database
CREATE DATABASE IF NOT EXISTS voicelookup;

-- Switch to the database
USE voicelookup;

What do these statements do?

CREATE DATABASE IF NOT EXISTS voicelookup creates a new empty database called voicelookup. The IF NOT EXISTS clause prevents an error if the database already exists.

USE voicelookup tells MariaDB that all following commands should target this database. Your prompt changes to MariaDB [voicelookup]> to confirm you are inside it.

  • Confirm your prompt now shows MariaDB [voicelookup]>. This means you are working inside the correct database.

Still seeing (none) in your prompt?

Make sure each SQL statement ends with a semicolon (;). MariaDB waits for the semicolon before executing.

If you see -> instead of the main prompt, MariaDB is waiting for you to finish the statement. Type a semicolon and press Enter to execute.

Help me switch to my voicelookup database

Create the speakers table

The speakers table stores information about each voice you catalog. Every speaker gets a unique ID that MariaDB assigns automatically.

  • Create the speakers table by running this SQL statement:
-- Create speakers table
CREATE TABLE IF NOT EXISTS speakers (
    speaker_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (speaker_id)
);

What does this table do?

  • speaker_id INT NOT NULL AUTO_INCREMENT creates a unique numeric ID for each speaker. AUTO_INCREMENT means MariaDB automatically assigns the next number each time you add a row.
  • name VARCHAR(100) NOT NULL stores the speaker's name. NOT NULL means this field is required.
  • description TEXT holds a longer text description of the speaker's voice characteristics.
  • date_added DATETIME DEFAULT CURRENT_TIMESTAMP automatically records when each speaker was added to the database.
  • PRIMARY KEY (speaker_id) designates speaker_id as the primary key. Every row has a unique value in this column.

You should see Query OK, 0 rows affected confirming the table was created successfully.

Seeing a syntax error?

Check that every line inside the parentheses ends with a comma, except the last one (PRIMARY KEY (speaker_id)). A missing or extra comma is the most common cause of CREATE TABLE errors.

Also make sure you have the closing ); at the end. If your prompt shows -> on a new line, MariaDB is waiting for more input.

Help me fix my CREATE TABLE syntax error

Verify the table structure

After creating a table, it is good practice to verify that MariaDB stored it exactly as you intended.

  • Check the full table definition by running:
SHOW CREATE TABLE speakers\G

What does this command do?

SHOW CREATE TABLE displays the exact CREATE TABLE statement that MariaDB stored for your table. It includes the storage engine, character set, and all column definitions.

The \G at the end formats the output vertically instead of in a wide horizontal table. This makes it much easier to read.

  • Confirm the output shows all four columns (speaker_id, name, description, date_added) along with PRIMARY KEY and ENGINE=InnoDB in the output.

Why does it show InnoDB?

InnoDB is the default storage engine in MariaDB 11.8. It supports foreign keys and transactions, which are essential for the relational design you will build in this project.

✔️ Awesome, I've got everything!

Great. Double check you see all four columns in your SHOW CREATE TABLE output before moving on.

ⓧ I'd like to double check the full code

Here is everything you should have run so far in this step:

-- Create the database
CREATE DATABASE IF NOT EXISTS voicelookup;

-- Switch to the database
USE voicelookup;

-- Create speakers table
CREATE TABLE IF NOT EXISTS speakers (
    speaker_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (speaker_id)
);

Your database is live and your first table is ready to accept data. Next up, you will create the voice_clips table and link it to speakers using a foreign key.

Create the Voice Clips Table

You have your speakers table ready to hold voice talent information.

Now you need a second table to store the actual voice clip files associated with each speaker. A single speaker can have many clips, so the two tables need a formal link between them. This link is called a foreign key, and it prevents orphan clips from pointing to speakers that don't exist.

In this step, get ready to:
  • Create the voice_clips table with a foreign key linking to speakers.
  • Insert sample speakers into the database.
  • Insert sample voice clip records that reference those speakers.
Create the voice_clips table with a foreign key

Each voice clip belongs to exactly one speaker. The voice_clips table stores the file path to each MP3 on disk rather than the audio binary itself.

Why store file paths instead of audio data?

Databases are optimized for structured data like names, numbers, and dates. Storing large binary files (like MP3s) directly in the database causes bloat, slower backups, and performance issues.

The industry best practice is to keep audio files on the filesystem and store only the path in your database. Your queries stay fast and your backups stay small.

  • Create the voice_clips table by running this SQL in your MariaDB session:
-- Create voice_clips table
CREATE TABLE IF NOT EXISTS voice_clips (
    clip_id INT NOT NULL AUTO_INCREMENT,
    speaker_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    duration_seconds INT,
    format VARCHAR(10) DEFAULT 'mp3',
    date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (clip_id),
    FOREIGN KEY (speaker_id) REFERENCES speakers(speaker_id)
);

What does this code do?

  • clip_id is the unique identifier for each voice clip, auto-generated by MariaDB.
  • speaker_id links each clip to its speaker. The FOREIGN KEY constraint ensures this value must match an existing speaker_id in the speakers table.
  • file_path stores the location of the MP3 file on disk.
  • format defaults to 'mp3' so you only need to specify it when using a different audio format.
  • Verify the table was created by running:
DESCRIBE voice_clips;

You should see six rows listing each column with its type, whether it allows NULL, its key status, default value, and any extras like auto_increment.

Seeing an error about the speakers table?

The foreign key references the speakers table, so it must already exist. If you get an error mentioning a missing referenced table, confirm you created speakers in the previous step by running SHOW TABLES;.

Also confirm you are in the correct database by running SELECT DATABASE(); to check. It should return voicelookup.

Help me fix my foreign key error when creating the voice_clips table

Insert sample speakers into the database

Before you can add voice clips, the speakers table needs data. The foreign key on voice_clips will reject any insert that references a speaker ID that doesn't exist yet.

  • Populate the speakers table with five sample records by running:
-- Insert sample speakers
INSERT INTO speakers (name, description) VALUES
    ('Morgan Freeman', 'Deep, calm, authoritative voice known for narration'),
    ('James Earl Jones', 'Deep bass voice, iconic for Darth Vader'),
    ('Scarlett Johansson', 'Distinctive husky, smooth voice'),
    ('David Attenborough', 'Warm British voice known for nature documentaries'),
    ('Dolly Parton', 'High-pitched, warm Southern American accent');

What does this code do?

This inserts five rows in a single statement using multi-row INSERT syntax. You only specify name and description because speaker_id auto-increments and date_added defaults to the current timestamp.

MariaDB assigns IDs 1 through 5 automatically in insertion order. You will reference these IDs when inserting voice clips next.

  • Confirm the speakers were inserted by running:
SELECT speaker_id, name FROM speakers;

You should see five rows with IDs 1 through 5 and the corresponding speaker names.

Seeing duplicate rows?

If you accidentally ran the INSERT statement twice, you will have 10 rows instead of 5. Fix this by clearing the table and re-inserting:

DELETE FROM speakers;
ALTER TABLE speakers AUTO_INCREMENT = 1;

INSERT INTO speakers (name, description) VALUES
    ('Morgan Freeman', 'Deep, calm, authoritative voice known for narration'),
    ('James Earl Jones', 'Deep bass voice, iconic for Darth Vader'),
    ('Scarlett Johansson', 'Distinctive husky, smooth voice'),
    ('David Attenborough', 'Warm British voice known for nature documentaries'),
    ('Dolly Parton', 'High-pitched, warm Southern American accent');

The ALTER TABLE ... AUTO_INCREMENT = 1 resets the counter so IDs start from 1 again.

Help me fix duplicate speaker rows in my MariaDB table

Insert sample voice clip records

With speakers in place, you can now add voice clips that reference them. Each clip points to a speaker via speaker_id and stores the path where the MP3 lives on your filesystem.

  • Insert eight sample voice clips by running:
-- Insert sample voice clips
INSERT INTO voice_clips (speaker_id, file_path, duration_seconds, format) VALUES
    (1, '/home/voices/morgan_freeman_narration01.mp3', 45, 'mp3'),
    (1, '/home/voices/morgan_freeman_narration02.mp3', 30, 'mp3'),
    (2, '/home/voices/james_earl_jones_vader01.mp3', 15, 'mp3'),
    (2, '/home/voices/james_earl_jones_speech01.mp3', 60, 'mp3'),
    (3, '/home/voices/scarlett_johansson_interview01.mp3', 120, 'mp3'),
    (4, '/home/voices/david_attenborough_nature01.mp3', 90, 'mp3'),
    (4, '/home/voices/david_attenborough_nature02.mp3', 55, 'mp3'),
    (5, '/home/voices/dolly_parton_interview01.mp3', 80, 'mp3');

What does this code do?

Each row references a speaker_id from the speakers table. Morgan Freeman (ID 1) and David Attenborough (ID 4) each get two clips. James Earl Jones (ID 2) also gets two clips.

The file paths follow a consistent naming convention: /home/voices/ as the base directory, then the speaker's name and a clip identifier. These files don't need to exist on disk right now. The database only stores the path string.

  • Verify the clips were inserted by running:
SELECT COUNT(*) AS total_clips FROM voice_clips;

You should see 8 as the total.

Getting a foreign key constraint error?

This means a speaker_id value in your insert doesn't match any existing speaker. Run SELECT speaker_id, name FROM speakers; to check which IDs exist.

If your speakers have IDs other than 1 through 5 (from a previous duplicate issue), you need to reset the speakers table first using the fix in the previous substep.

Help me fix a foreign key constraint error when inserting voice clips

✔️ Awesome, I've got everything!

Great. Double check you see 5 speakers and 8 clips by running SELECT COUNT(*) FROM speakers; and SELECT COUNT(*) FROM voice_clips;.

ⓧ I'd like to double check the full code

Here is every SQL statement you should have run up to this point:

-- Create the database
CREATE DATABASE IF NOT EXISTS voicelookup;

-- Switch to the database
USE voicelookup;

-- Create speakers table
CREATE TABLE IF NOT EXISTS speakers (
    speaker_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (speaker_id)
);

-- Create voice_clips table
CREATE TABLE IF NOT EXISTS voice_clips (
    clip_id INT NOT NULL AUTO_INCREMENT,
    speaker_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    duration_seconds INT,
    format VARCHAR(10) DEFAULT 'mp3',
    date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (clip_id),
    FOREIGN KEY (speaker_id) REFERENCES speakers(speaker_id)
);

-- Insert sample speakers
INSERT INTO speakers (name, description) VALUES
    ('Morgan Freeman', 'Deep, calm, authoritative voice known for narration'),
    ('James Earl Jones', 'Deep bass voice, iconic for Darth Vader'),
    ('Scarlett Johansson', 'Distinctive husky, smooth voice'),
    ('David Attenborough', 'Warm British voice known for nature documentaries'),
    ('Dolly Parton', 'High-pitched, warm Southern American accent');

-- Insert sample voice clips
INSERT INTO voice_clips (speaker_id, file_path, duration_seconds, format) VALUES
    (1, '/home/voices/morgan_freeman_narration01.mp3', 45, 'mp3'),
    (1, '/home/voices/morgan_freeman_narration02.mp3', 30, 'mp3'),
    (2, '/home/voices/james_earl_jones_vader01.mp3', 15, 'mp3'),
    (2, '/home/voices/james_earl_jones_speech01.mp3', 60, 'mp3'),
    (3, '/home/voices/scarlett_johansson_interview01.mp3', 120, 'mp3'),
    (4, '/home/voices/david_attenborough_nature01.mp3', 90, 'mp3'),
    (4, '/home/voices/david_attenborough_nature02.mp3', 55, 'mp3'),
    (5, '/home/voices/dolly_parton_interview01.mp3', 80, 'mp3');

Your database now has structure and real data. Next up, you will write queries that search across both tables using JOINs and filters to find exactly the voice you are looking for.

Insert Data and Query Your Database

Your database has speakers and voice clips stored across two related tables. The real power of a relational database comes from pulling that data back out in useful combinations.

In this step, you'll write SQL queries that search across tables, filter by name, and count clips per speaker. These are the same query patterns you'd use to power a voice lookup search feature.

In this step, get ready to:
  • Verify your inserted data using SELECT statements.
  • Write a JOIN query to combine speaker and clip data.
  • Filter and sort results with WHERE, ORDER BY, and LIKE.
Verify your inserted data

Before writing complex queries, confirm that your data is in place. A simple SELECT * returns every row and column from a table.

  • In your MariaDB session, view all speakers by running this query:
SELECT * FROM speakers;

You should see a table with 5 rows listing Morgan Freeman, James Earl Jones, Scarlett Johansson, David Attenborough, and Dolly Parton.

  • Now check your voice clips by running:
SELECT * FROM voice_clips;

You should see 8 rows, each with a speaker_id linking it back to the speakers table.

What is multi-row INSERT?

When you inserted your data in the previous steps, you used multi-row INSERT statements. Instead of writing one INSERT per row, you listed multiple value sets separated by commas after a single VALUES keyword.

This is more efficient than individual inserts because MariaDB processes them as a single operation. It also keeps your SQL file shorter and easier to read.

Find clips by speaker name using JOIN

Your voice clips store a speaker_id number, but you want to search by name. A JOIN combines rows from two tables based on a shared column, letting you pull the speaker name alongside their clip data.

  • Find all of Morgan Freeman's voice clips by running this JOIN query:
-- Find all clips by a specific speaker
SELECT s.name, vc.file_path, vc.duration_seconds
FROM voice_clips vc
JOIN speakers s ON vc.speaker_id = s.speaker_id
WHERE s.name = 'Morgan Freeman';

What does this query do?

  • FROM voice_clips vc starts with the voice_clips table and gives it a short alias vc so you don't have to type the full name each time.
  • JOIN speakers s ON vc.speaker_id = s.speaker_id connects each clip to its speaker by matching the speaker_id column in both tables.
  • WHERE s.name = 'Morgan Freeman' filters the results to only show clips belonging to that speaker.

You should see 2 rows showing Morgan Freeman's file paths and durations (45 seconds and 30 seconds).

Seeing 'Empty set'?

  • Check that the name in your WHERE clause matches exactly, including capitalization. SQL string comparisons are case-sensitive by default.
  • Make sure you're using the voicelookup database. Run SELECT DATABASE(); to confirm.
  • Why is my JOIN returning empty results?
Filter and sort results with WHERE, ORDER BY, and LIKE

Real voice lookup searches need more than exact name matches. You'll often want to count how many clips each speaker has, or search for speakers when you only remember part of their name.

  • Count how many clips each speaker has by running this GROUP BY query:
-- Find all speakers and their clip count
SELECT s.name, COUNT(vc.clip_id) AS total_clips
FROM speakers s
LEFT JOIN voice_clips vc ON s.speaker_id = vc.speaker_id
GROUP BY s.name
ORDER BY total_clips DESC;

What does this query do?

  • COUNT(vc.clip_id) AS total_clips counts how many clips exist for each speaker and labels the result column total_clips.
  • LEFT JOIN includes speakers even if they have zero clips. A regular JOIN would hide them.
  • GROUP BY s.name groups all the rows by speaker so the count is calculated per person.
  • ORDER BY total_clips DESC sorts the results so the speaker with the most clips appears first.

You should see all 5 speakers listed with their clip counts. Morgan Freeman and James Earl Jones each have 2 clips. David Attenborough also has 2. Scarlett Johansson and Dolly Parton each have 1.

Getting a syntax error?

  • Make sure you have a semicolon at the end of your query. MariaDB won't execute until it sees one.
  • Check that GROUP BY comes after your JOIN and before ORDER BY. SQL clause order matters.
  • Help me fix my GROUP BY query in MariaDB.
  • Search for a speaker when you only know part of their name by running this LIKE query:
-- Search speakers by partial name match
SELECT name, description
FROM speakers
WHERE name LIKE '%Jones%';

What does LIKE do?

The LIKE keyword lets you match patterns instead of exact values. The % symbol is a wildcard that matches any number of characters.

So '%Jones%' matches any name containing "Jones" anywhere in it. This is exactly how a search box in your future voice lookup app would work behind the scenes.

You should see 1 row returned showing James Earl Jones and his description.

LIKE query returning nothing?

  • Make sure you have the % wildcards on both sides of your search term inside the quotes.
  • Check that your search string uses single quotes, not double quotes. MariaDB uses single quotes for string values.
  • Help me debug my LIKE query in MariaDB.

✔️ Awesome, I've got everything!

Great. All three query patterns are working in your database.

ⓧ I'd like to double check the full code

Here is everything your setup.sql should contain at this point, including the queries you just ran.

-- Create the database
CREATE DATABASE IF NOT EXISTS voicelookup;

-- Switch to the database
USE voicelookup;

-- Create speakers table
CREATE TABLE IF NOT EXISTS speakers (
    speaker_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (speaker_id)
);

-- Create voice_clips table
CREATE TABLE IF NOT EXISTS voice_clips (
    clip_id INT NOT NULL AUTO_INCREMENT,
    speaker_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    duration_seconds INT,
    format VARCHAR(10) DEFAULT 'mp3',
    date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (clip_id),
    FOREIGN KEY (speaker_id) REFERENCES speakers(speaker_id)
);

-- Insert sample speakers
INSERT INTO speakers (name, description) VALUES
    ('Morgan Freeman', 'Deep, calm, authoritative voice known for narration'),
    ('James Earl Jones', 'Deep bass voice, iconic for Darth Vader'),
    ('Scarlett Johansson', 'Distinctive husky, smooth voice'),
    ('David Attenborough', 'Warm British voice known for nature documentaries'),
    ('Dolly Parton', 'High-pitched, warm Southern American accent');

-- Insert sample voice clips
INSERT INTO voice_clips (speaker_id, file_path, duration_seconds, format) VALUES
    (1, '/home/voices/morgan_freeman_narration01.mp3', 45, 'mp3'),
    (1, '/home/voices/morgan_freeman_narration02.mp3', 30, 'mp3'),
    (2, '/home/voices/james_earl_jones_vader01.mp3', 15, 'mp3'),
    (2, '/home/voices/james_earl_jones_speech01.mp3', 60, 'mp3'),
    (3, '/home/voices/scarlett_johansson_interview01.mp3', 120, 'mp3'),
    (4, '/home/voices/david_attenborough_nature01.mp3', 90, 'mp3'),
    (4, '/home/voices/david_attenborough_nature02.mp3', 55, 'mp3'),
    (5, '/home/voices/dolly_parton_interview01.mp3', 80, 'mp3');

-- Query: Find all clips by a specific speaker
SELECT s.name, vc.file_path, vc.duration_seconds
FROM voice_clips vc
JOIN speakers s ON vc.speaker_id = s.speaker_id
WHERE s.name = 'Morgan Freeman';

-- Query: Find all speakers and their clip count
SELECT s.name, COUNT(vc.clip_id) AS total_clips
FROM speakers s
LEFT JOIN voice_clips vc ON s.speaker_id = vc.speaker_id
GROUP BY s.name
ORDER BY total_clips DESC;

-- Query: Search speakers by partial name match
SELECT name, description
FROM speakers
WHERE name LIKE '%Jones%';

You can now search your voice database by name, count clips per speaker, and do fuzzy searches. Next up, you'll learn how to update and delete records when your data needs to change.

Update, Delete, and Manage Records

Your database has 5 speakers and 8 voice clips, all queryable with JOINs and filters. That's a solid foundation for searching voice data.

Real databases need ongoing maintenance. Descriptions get outdated, files move to new locations, and old clips need removal. In this step, you'll learn to modify and delete records safely using UPDATE and DELETE statements.

In this step, get ready to:
  • Update a speaker's description using UPDATE and WHERE.
  • Delete a voice clip record safely.
  • Verify your changes with SELECT COUNT(*).
Update an existing record

The UPDATE statement changes existing data in a table. The WHERE clause targets only the row you want to modify.

  • Update James Earl Jones's description to include his role as Mufasa by running this query in your MariaDB session:
-- Update a record
UPDATE speakers
SET description = 'Deep bass voice, iconic for Darth Vader and Mufasa'
WHERE name = 'James Earl Jones';

What does this query do?

  • UPDATE speakers tells MariaDB which table to modify.
  • SET description = '...' specifies the new value for the column.
  • WHERE name = 'James Earl Jones' ensures only that one row is affected. Without WHERE, every row in the table would be overwritten.
  • Confirm the update worked by running this SELECT query:
SELECT name, description FROM speakers WHERE name = 'James Earl Jones';

You should see the description now reads Deep bass voice, iconic for Darth Vader and Mufasa.

Description didn't change?

Make sure you typed the name exactly as it was inserted. Database names on Linux are case-sensitive, and string comparisons in WHERE clauses must match the stored value precisely.

Check that your query ended with a semicolon. Without it, MariaDB waits for more input instead of executing.

Help me debug my UPDATE statement

Delete a voice clip record safely

DELETE removes rows permanently. There is no undo. The WHERE clause is critical here because a DELETE without WHERE erases every row in the table.

  • Delete the second Morgan Freeman clip (clip_id 2) by running:
-- Delete a clip
DELETE FROM voice_clips
WHERE clip_id = 2;

Why use clip_id instead of file_path?

The primary key (clip_id) uniquely identifies exactly one row. Using it in your WHERE clause guarantees you'll never accidentally delete multiple records.

File paths could theoretically match multiple rows if you made a typo in the comparison. Primary keys are always the safest target for DELETE operations.

Seeing a foreign key error?

If the clip had entries in another table referencing it (like tags), MariaDB's foreign key constraints would block the delete. At this point your voice_clips table has no dependent tables, so the delete should succeed.

Help me fix my DELETE statement

Verify your data with COUNT

After modifying data, always verify the result. SELECT COUNT(*) gives you a quick row count without listing every record.

  • Confirm the deletion reduced your clip count by running:
-- Verify count
SELECT COUNT(*) AS total_clips FROM voice_clips;

You should see 7 in the total_clips column. You started with 8 clips and deleted one.

What is AS doing here?

AS total_clips creates an alias for the result column. Without it, MariaDB would label the column COUNT(*) which is harder to read. Aliases make query output clearer for both you and any application reading the results.

  • Now add the UPDATE, DELETE, and COUNT queries to your setup.sql file. Open setup.sql in a text editor and append these lines after your existing queries:
-- Update a record
UPDATE speakers
SET description = 'Deep bass voice, iconic for Darth Vader and Mufasa'
WHERE name = 'James Earl Jones';

-- Delete a clip
DELETE FROM voice_clips
WHERE clip_id = 2;

-- Verify count
SELECT COUNT(*) AS total_clips FROM voice_clips;

What does this code do?

This block documents the three management operations you just ran interactively. Saving them to setup.sql means you have a complete record of every SQL statement used in this project.

  • Save setup.sql.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file.

ⓧ I'd like to double check the full code

-- Create the database
CREATE DATABASE IF NOT EXISTS voicelookup;

-- Switch to the database
USE voicelookup;

-- Create speakers table
CREATE TABLE IF NOT EXISTS speakers (
    speaker_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (speaker_id)
);

-- Create voice_clips table
CREATE TABLE IF NOT EXISTS voice_clips (
    clip_id INT NOT NULL AUTO_INCREMENT,
    speaker_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    duration_seconds INT,
    format VARCHAR(10) DEFAULT 'mp3',
    date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (clip_id),
    FOREIGN KEY (speaker_id) REFERENCES speakers(speaker_id)
);

-- Insert sample speakers
INSERT INTO speakers (name, description) VALUES
    ('Morgan Freeman', 'Deep, calm, authoritative voice known for narration'),
    ('James Earl Jones', 'Deep bass voice, iconic for Darth Vader'),
    ('Scarlett Johansson', 'Distinctive husky, smooth voice'),
    ('David Attenborough', 'Warm British voice known for nature documentaries'),
    ('Dolly Parton', 'High-pitched, warm Southern American accent');

-- Insert sample voice clips
INSERT INTO voice_clips (speaker_id, file_path, duration_seconds, format) VALUES
    (1, '/home/voices/morgan_freeman_narration01.mp3', 45, 'mp3'),
    (1, '/home/voices/morgan_freeman_narration02.mp3', 30, 'mp3'),
    (2, '/home/voices/james_earl_jones_vader01.mp3', 15, 'mp3'),
    (2, '/home/voices/james_earl_jones_speech01.mp3', 60, 'mp3'),
    (3, '/home/voices/scarlett_johansson_interview01.mp3', 120, 'mp3'),
    (4, '/home/voices/david_attenborough_nature01.mp3', 90, 'mp3'),
    (4, '/home/voices/david_attenborough_nature02.mp3', 55, 'mp3'),
    (5, '/home/voices/dolly_parton_interview01.mp3', 80, 'mp3');

-- Query: Find all clips by a specific speaker
SELECT s.name, vc.file_path, vc.duration_seconds
FROM voice_clips vc
JOIN speakers s ON vc.speaker_id = s.speaker_id
WHERE s.name = 'Morgan Freeman';

-- Query: Find all speakers and their clip count
SELECT s.name, COUNT(vc.clip_id) AS total_clips
FROM speakers s
LEFT JOIN voice_clips vc ON s.speaker_id = vc.speaker_id
GROUP BY s.name
ORDER BY total_clips DESC;

-- Query: Search speakers by partial name match
SELECT name, description
FROM speakers
WHERE name LIKE '%Jones%';

-- Update a record
UPDATE speakers
SET description = 'Deep bass voice, iconic for Darth Vader and Mufasa'
WHERE name = 'James Earl Jones';

-- Delete a clip
DELETE FROM voice_clips
WHERE clip_id = 2;

-- Verify count
SELECT COUNT(*) AS total_clips FROM voice_clips;

Secret mission

Build a Tag-Based Voice Search System

What if you could search your voice database by multiple characteristics at once? In this mission, you'll build a many-to-many tagging system that lets you find voices matching "deep AND male AND British" in a single query.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • MariaDB server (running as a systemd service).
  • The voicelookup database (4 tables: speakers, voice_clips, tags, clip_tags).
  • SQL files: setup.sql and tags_system.sql.

Keep everything running

No action needed. Choose this if you're still actively building on top of your voice lookup database or plan to add a REST API layer next.

  • MariaDB continues running in the background as a service.
  • Your voicelookup database and all tables remain intact and queryable.
  • Your SQL files stay wherever you created them.

Pause - I'll come back to this later

Stop the MariaDB service to free up memory. Your database and files stay on disk so you can pick up where you left off.

  • Stop the MariaDB service by running:
sudo systemctl stop mariadb
  • Disable MariaDB from starting automatically on boot by running:
sudo systemctl disable mariadb

When you're ready to resume, start the service again with:

sudo systemctl enable --now mariadb

Your database and all data will be exactly as you left them.

Delete - I don't want to use this again

Remove all project resources completely. This deletes your database, uninstalls MariaDB, and removes your SQL files.

  • Drop the database from within MariaDB. Connect first by running:
mysql -u root -p
  • Enter your root password when prompted, then run this SQL command:
DROP DATABASE voicelookup;
  • Exit the MariaDB client:
EXIT;
  • Stop and disable the MariaDB service:
sudo systemctl stop mariadb
sudo systemctl disable mariadb
  • Remove the MariaDB server package:
sudo dnf remove mariadb-server
  • Confirm the removal when prompted by typing y and pressing Enter.
  • Delete your SQL files by running:
rm setup.sql tags_system.sql
  • Optionally, remove the data directory where MariaDB stored database files:
sudo rm -rf /var/lib/mysql

Your machine is now clean of all project resources.

Nice Work!

Nice Work!

You just built your first relational database from scratch. You now have a fully functional voice lookup database running on your machine that you designed, populated, and queried using pure SQL.

Here's what you accomplished:

  • Installed and secured MariaDB on your Fedora machine, including setting a root password and locking down the default installation.
  • Designed a multi-table relational schema with foreign keys linking speakers to voice clips, using the industry-standard file-path storage pattern for media files.
  • Wrote SQL to CREATE tables, INSERT data, query with JOINs, UPDATE, and DELETE records to manage your voice clip catalog.
  • Built a many-to-many tagging system using a junction table, enabling advanced multi-tag searches with GROUP BY and HAVING COUNT to find voices matching multiple characteristics simultaneously.

Ready to quiz yourself?