MySQL Exercise: Music Database

Let’s create a small database to store information about songs such as the artist and album.

Creating the Database

First, we’ll want to create a new database called music and start using it.

CREATE DATABASE music;
USE music;

Creating the Tables

For our first table, let’s create one that holds a list of artists. We’ll set the following requirements:

Name of artist cannot be null
Name of artist must be unique
Each artist will be assigned an id automatically

CREATE TABLE artists (                                                     
    id int NOT NULL AUTO_INCREMENT,
    name varchar(40) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY(name)
);

For our second table, let’s create one that holds a list of albums. We’ll set the following requirements:

Name of album cannot be null
Name of album may repeat (i.e. Multiple artists can have albums named the same thing)
Artist field cannot be null
Artist field must match an id in the artists table
Each album will be assigned an id automatically

CREATE TABLE albums (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(40) NOT NULL,
    artist int NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(artist) REFERENCES artists(id)
);

For our last table, let’s create one that holds a list of songs. We’ll set the following requirements:

Name of the song cannot be null
Album field cannot be null
Album field must match an id in the album table
Each song will be assigned an id automatically

CREATE TABLE songs (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(40) NOT NULL,
    album int NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(album) REFERENCES albums(id)
);

Configuring the Schema

Let’s confirm our schema. We should have three tables in the music database: albums, artists, and songs.

SHOW TABLES;
+-----------------+
| Tables_in_music |
+-----------------+
| albums          |
| artists         |
| songs           |
+-----------------+

The artists table should be able to take the name of an artist and create an ID for said artist.

DESCRIBE artists;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

The albums table should be able to take the name of an album and create an ID for said album. The table should also be linked to the artists table via artists.id.

DESCRIBE songs;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | NO   |     | NULL    |                |
| album | int(11)     | NO   | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

Adding Data

Adding Artists

Let’s add a few artists to our database.

INSERT INTO artists (name) VALUES ('Ellie Goulding');
INSERT INTO artists (name) VALUES ('Florence + The Machine');
INSERT INTO artists (name) VALUES ('Ed Sheeran');

Adding Albums

Now that we have our artists added, let’s see which ID each artist was linked to. For example, Ellie Goulding was given the ID of 1.

SELECT * FROM artists;
+----+------------------------+
| id | name                   |
+----+------------------------+
|  3 | Ed Sheeran             |
|  1 | Ellie Goulding         |
|  2 | Florence + The Machine |
+----+------------------------+

Now that we know Ellie’s ID, let’s go ahead and add an album of hers called Lights. As you can see, we said name=Lights and artist=1 (ID of Ellie Goulding = 1).

INSERT INTO albums (name, artist) VALUES ('Lights', 1);
INSERT INTO albums (name, artist) VALUES ('Lungs', 2);
INSERT INTO albums (name, artist) VALUES ('x(Deluxe Edition)', 3);

Adding Songs

Now that we have our albums added, let’s print their IDs. This will be helpful later on when we add songs to each album.

SELECT * FROM albums;
+----+-------------------+--------+
| id | name              | artist |
+----+-------------------+--------+
|  1 | Lights            |      1 |
|  2 | Lungs             |      2 |
|  3 | x(Deluxe Edition) |      3 |
+----+-------------------+--------+

Printing the names of the albums was cool but I don’t remember which artist is which when it comes to their ID. Let’s join the albums and artists tables and make it easier to read.

SELECT albums.id AS 'Album ID',
    albums.name AS 'Album Name',
    artists.name AS 'Artist Name'
    FROM albums
    JOIN artists ON (artists.id = albums.artist);
+----------+-------------------+------------------------+
| Album ID | Album Name        | Artist Name            |
+----------+-------------------+------------------------+
|        1 | Lights            | Ellie Goulding         |
|        2 | Lungs             | Florence + The Machine |
|        3 | x(Deluxe Edition) | Ed Sheeran             |
+----------+-------------------+------------------------+

Much better! We can now see that Ellie Goulding has an album called Lights and the ID of said album is 1. Let’s add some songs to Ed Sheeran’s x(Deluxe Edition) album. We’re setting name=Bloodstream and album=3 (ID of Ed Sheeran’s album x(Deluxe Edition) = 3.

INSERT INTO songs (name, album) VALUES ('Bloodstream', 3);
INSERT INTO songs (name, album) VALUES ("Don't", 3);

Let’s add some songs to Florence + The Machines’s Lungs album.

INSERT INTO songs (name, album) VALUES ('Drumming Song', 2);
INSERT INTO songs (name, album) VALUES ('Blinding', 2);

Lastly, let’s add some songs to Ellie Goulding’s Lights album.

INSERT INTO songs (name, album) VALUES ('Lights - Single Version', 1);
INSERT INTO songs (name, album) VALUES ('Starry Eyed', 1);

**Note:** The songs table id column should be 1, 2, 3, 4, 5, 6 instead of 1, 1, 2, 2, 3, 3.

Great! Now that we have added all of our data, let’s print a list of our songs.

SELECT name FROM songs;
+-------------------------+
| name                    |
+-------------------------+
| Bloodstream             |
| Don't                   |
| Drumming Song           |
| Blinding                |
| Lights - Single Version |
| Starry Eyed             |
+-------------------------+

Wouldn’t it be nice to see which albums these songs were on?

SELECT songs.name AS 'Song Name',
    albums.name AS 'Album Name'
    FROM songs
    JOIN albums ON (albums.id = songs.album);
+-------------------------+-------------------+
| Song Name               | Album Name        |
+-------------------------+-------------------+
| Bloodstream             | x(Deluxe Edition) |
| Don't                   | x(Deluxe Edition) |
| Drumming Song           | Lungs             |
| Blinding                | Lungs             |
| Lights - Single Version | Lights            |
| Starry Eyed             | Lights            |
+-------------------------+-------------------+

I don’t remember which artist made which album. Let’s show the artist alongside the album name and the song name.

SELECT songs.name AS 'Song Name',
    albums.name AS 'Album Name',
    artists.name AS 'Artist Name'
    FROM songs
    JOIN albums ON (albums.id = songs.album)
    JOIN artists ON (artists.id = albums.artist);
+-------------------------+-------------------+------------------------+
| Song Name               | Album Name        | Artist Name            |
+-------------------------+-------------------+------------------------+
| Bloodstream             | x(Deluxe Edition) | Ed Sheeran             |
| Don't                   | x(Deluxe Edition) | Ed Sheeran             |
| Drumming Song           | Lungs             | Florence + The Machine |
| Blinding                | Lungs             | Florence + The Machine |
| Lights - Single Version | Lights            | Ellie Goulding         |
| Starry Eyed             | Lights            | Ellie Goulding         |
+-------------------------+-------------------+------------------------+