Today, we have to say goodbye to some data.
It served its purpose, but now it is time for it to go.
How do you eliminate data using SQL, you ask?
With the DELETE statement.
You may be scared to delete data.
Perhaps you are afraid you will mess up and delete too much data!
Well, you should be afraid.
Deleting is very serious business!
So sit up straight and put on your serious face, because we are going to destroy some
data...
Today we are going to look at a table containing 10,000 songs.
We will clean up the table by removing questionable data using the DELETE command,
and then we will analyze the data for trends in music over the years.
The “song” table has 8 columns: song_id
title artist
album year_released
duration tempo, and
loudness The song_id is the primary key.
We have information about the song, including the year it was released.
The duration is the length of the song in seconds.
Tempo measures the speed of the song in beats per minute.
And lastly, we have the average loudness of the song.
Without going into the formula, the loudness is a negative number.
The closer it is to zero, the louder the song.
So a song with loudness -5 is much louder than a song with average loudness -20.
Socratica has a copy of the data available for download on Github.
In this video, we will be using PostgreSQL as our database, and the pgAdmin interface.
Let us look at the data in the table and begin the process of cleaning it up.
First, let us see how many rows there are in this table.
10,000 songs.
I knew it...
Next, let us check the first and last years covered in this table.
I am skeptical that we actually have recordings of songs from the year 0 AD.
Besides, the microphones from that time period were horrendous.
Let us look into this a little more closely.
Select the distinct values for the year_released from the table and order by year.
Execute.
It appears that 0 was used as the default value for songs with an unknown release year.
How many songs have an unknown release year?
5,320…
Over half.
We should remove these songs from our table.
To do this, we use the DELETE statement.
Write “DELETE FROM” followed by the table name.
If you were to execute this query, it would delete all the data from the table.
You need to specify which rows to delete.
You do this with a WHERE clause.
We want to delete songs with a year_released value of 0.
Before we execute this query, let us comment it out.
A comment in SQL is text that will not execute.
This is useful for writing explanations, notes, or disabling a query temporarily.
There are two ways to write a comment in SQL.
You can comment out lines one-by-one with a double-dash.
You can also comment out multiple lines by starting with a /*… and ending your comment
with a */ Notice how the query changes color to prove
that it is now a comment.
Now that we have disabled the DELETE query, let us count how many rows will be deleted.
To do this, run a SELECT COUNT and copy over the WHERE clause that you wrote for the delete
query.
Execute…
5320 rows will be deleted.
As a final sanity check, select the first 10 rows using the same WHERE clause from the
DELETE query.
If you look at the year_released column, you can see that all the values are indeed 0.
We are now ready to delete.
Get rid of our sanity check select…
Uncomment the delete query…
Drumroll… and Execute.
It appears to have worked.
Let us check.
We can count how many songs are left.
4680.
That is correct, because 10,000 - 5320 = 4680.
Next, select the distinct year_released values in this table.
Much better.
The data starts in 1926 when there was actual audio recording equipment.
Before we continue, let us look at the general form of the DELETE statement.
A delete query is written as “DELETE FROM” followed by the table name.
Next, you write a “WHERE” clause to specify which rows to delete.
This raises some important points.
A DELETE query is used to delete rows, not columns.
The WHERE clause determines which rows will be deleted.
If you do NOT have a WHERE clause, this will delete ALL rows in your table.
This is important enough to say in French.
Ceci va effacer toutes vos lignes!
We continue our quest to clean up the song table.
Another field worth checking is the “tempo” column.
Again, a good check is to inspect the minimum and maximum values.
Execute…
A song with a tempo of 0 beats per minute?
That must sound a lot like one hand clapping in a forest with nobody around…
How many songs do not have a proper tempo value?
12…
Like before, we will write a DELETE query, comment it out, and look at the data we will
be deleting.
Since there are only 12 songs with a tempo of zero, let us go ahead and look at all of
them.
Be sure to copy-and-paste your WHERE clause rather than re-type it.
You may have a type-o in your DELETE query, and you’ll want to catch it with your SELECT
test.
Execute.
Everything looks good.
There are indeed 12 rows, and they all have tempo 0.
We can now confidently execute the DELETE query.
Once more select the minimum and maximum tempo values.
We now have a believable range of values.
And a quick count shows we are down to 4,668 songs.
Next let us check the range of values for the duration column.
The songs range from 7 seconds to nearly 1600 seconds, or over 26 minutes long.
One might consider removing the overly short or long songs, but we will go ahead and leave
them.
At least there is nothing unrealistic about these durations.
And before we do an analysis, let’s check the loudness range.
OK, there is another small issue.
We expect the Loudness values to be negative, and we see there is at least one song with
a positive loudness value.
How many songs have a positive loudness?
Just one…
Let us see this song.
Here I will take care to write each part of the select query on a separate line.
Execute…
This returns the one row.
So now to delete it, we can simply replace the SELECT line with DELETE.
And execute…
Our song data now has a reasonable range of values for loudness.
Now that we have deleted the rows with problematic data, let us answer a question.
How has the tempo changed over time?
To answer this, we will select the year_released and the average tempo from the song table.
We will GROUP the rows by the year released, so this query will return the year released
and average tempo on a year-by-year basis.
In other words, this query will aggregate the data by year_released, and then return
a single row for each batch of rows.
Execute.
The averages are quite lengthy decimal values.
Let us run this query once more.
This time, we will use the ROUND function to round the average tempo to an integer.
Execute…
As you scroll through the results, you can see that after a choppy beginning, the average
tempo settles down to the 120 beats per minute range.
Here is a graph of the average tempo over time…
I wonder if there is a human psychological preference for songs of this tempo...
Today we had to say goodbye to over 5,000 songs.
If I had a byte for every database that had missing or incorrect values, then I would
be a datacenter.
But what can you do.
I will tell you what you cannot do.
You cannot get too attached to your data emotionally.
Sometimes data needs deleting.
Now go and delete yourself from the list of people who are NOT subscribed to Socratica.
That sounds bad, but I’m pretty sure it’s not...