It’s 2023 and you’ve decided you want to beef up your resume with a tech skill that’s proven and lucrative. You’ve decided, NOW is the time to learn SQL. I want to show you how to make this more enjoyable by using your own data.


More fun examples, using your own data to learn SQL, build REST APIs, etc.

Untappd || iTunes || Twitter || Strava || Netflix || Spotify


But wait, why not use the time honored classic examples such as EMPLOYEES and DEPARTMENTS?

It’s not JUST because it’s boring, but mostly because it’s very boring.

It’s much better to learn with data YOU understand

It’s very easy to write a valid SQL statement and get data back. And you will be tempted to think, ‘got it!’ But, it’s also VERY easy to write a SQL statement that’s not really asking what you THINK it’s asking.

For THIS reason, I recommend you try to learn with data that you PERSONALLY have experience with. Your gut will know when the answers you’re looking at, don’t make sense or feel right.

Not a Spotify subscriber?

That’s OK! Your ITUNES library can be exported as CSV. You can download your personal data from Twitter or Strava. Find something that has meaning to YOU, and use that raw data. Hopefully it’s in either a JSON or CSV format.

Step 0: Obtain or Create a Database

I suggest an Always Free Oracle Database for the following reasons:

  • it’s free
  • Oracle takes care of it for you
  • you can have a working database in several minutes
  • you can do most of everything you need without having to install any software
  • I work for Oracle (surprise!)

No, it’s really free. Always.

Watch this video tutorial from one of our biggest advocates, Layla. She makes it look easy, but it’s also pretty easy.

Less than 4 minutes! You’ve got 4 minutes, right?

Go here to get started with Oracle Cloud Infrastructure (OCI) and create your first database.

Step 1: Request your Spotify data, wait, & download

If you have an account, go here. Scroll to the bottom, and click the ‘Request Data’ button. Acknowledge, respond to the email confirmation, and wait about a week.

You’ll get a notification that it’s available.

Download it, save to your machine.

Find the ‘StreamingHistory0.json’ file in the MyData directory of that archive/ZIP download.

Don’t freak out, my musical tastes are even worse than they initially appear.

Step 2: Login to Database Actions/SQL Developer Web

Your Always Free Database account in the Oracle Cloud comes with a console. Layla showed you this, but if you go to cloud.oracle.com, and move to the Autonomous Database page, you should see something like this –

Just click that button (works best in Chrome)

You should end up in a screen that looks like this –

It’s empty because you haven’t created anything yet. Your database is sad and empty.

Step 3a: Import the Spotify data!

That JSON file I asked you to find? You’re going to need it now.

Click the button with the 3 dots on it. In the biz, we call that a ‘meatballs’ button. Sersiously.

That’s going to prompt you for a file. Point to that JSON file.

Then you’re going to get a screen that looks like this –

You should see your personal data here, except it won’t look like JSON anymore.

If you don’t see your data here, something is wrong, like you picked the wrong file or your data is something we can’t read or recognize.

It should be good though, the Spotify JSON format/schema is SUPER simple. So keeping in mind this is working, click ‘Next.’

Step 3b: DESIGN your new table

It needs a name! By default it will use the name of the file to name the table, but change it to something that means something to you.

You can simply click in that box and type what you want. Don’t use spaces or weird characters.

We’re also going to make some changes to the COLUMN definitions.

The default will work, but they could be better.

{
    "endTime" : "2022-01-16 01:09",
    "artistName" : "Megadeth",
    "trackName" : "A Tout Le Monde - Remastered 2004",
    "msPlayed" : 262133
},

Our data is pretty simple. That first JSON attribute is called “endTime”, and it’s value is a date and time. We call this a TEMPORAL type, and in Oracle it’s always best to use TEMPORAL data types to store this kind of data.

So I’m going to choose change this from ‘VARCHAR2’ to ‘DATE’

I’m also going to change the name of the colum.

Tip: Use good object names, column names, and column data types! If you don’t, your life will be that much harder EVERY SINGLE TIME you go to write your SQL.

That’s the bare minimum we need to do. You can keep clicking through the dialog until you get to the end and click ‘Finish.’

Here’s the whole process, start to finish.

Step 4: Write your first SQL statement!

You can now type and run a SQL statement to interact with your first table. Now, I could teach you how to write a SQL SELECT, or I could cheat and let the tool do it for you.

So let’s cheat.

Grab the table on the left, and drag it into the editor.

It will ask what kind of SQL you want.

Choose ‘Select.’

You’ll now see your SQL, ready to be executed.

The text will differ depending on how you’ve named your table and columns.

Step 5: Execute your first SQL statement!

Just click the first green play button. You can also use your keyboard, on Windows simply do ‘ctrl+enter’ or if you’re on a Mac, use ‘cmd+enter’ to execute your SQL.

The results are shown in a spreadsheet-looking kind of grid below your SQL code.
SELECT
    PLAYED,
    ARTISTNAME,
    TRACKNAME,
    DURATION
FROM
    ADMIN.SPOTIFY_STREAMS_2022;

Breaking down the SQL

SELECT PLAYED, ARTISTNAME, TRACKNAME, DURATION

These are the columns I want to come back from my query.

FROM ADMIN.SPOTIFY_STREAMS_2022

This is the SCHEMA and TABLE from which the columns (and the data) can be found. In Oracle, SCHEMA is synonymous with ‘USER’ – so our user account here is ‘ADMIN’ and the tablename is the SPOTIFY_STREAMS_2022.

You could get lazy and simply run this instead and get the same results –

SELECT *
  FROM SPOTIFY_STREAMS_2022;

The asterisk, is often referred to as ‘star’ as in ‘SELECT STAR FROM’ – it’s basically shorthand for ALL of the columns in a table or view.

We’re logged in as the ADMIN user, so we don’t have to include it in the FROM clause.

Browsing our data

I can scroll down the list of songs until you get bored, or you can ask the tool to tell you how many records are in your table. To do the latter, right-click in the grid, and select ‘Count Rows…’

My table has more than 7,000 records in it, yours will be however many songs you listened to over the past year.

This isn’t a lot of data, but it’s more than enough to come up with some fun queries.

If we right click again, we can also choose to download the data back out as…JSON or CSV.

But we just spent all that time getting the data INTO the database, so let’s stop here.

Summary

We grabbed our personal data from Spotify. Well, we requested it and waited. And then while we were waiting we went and created ourselves an Always Free Oracle Cloud Autonomous Database.

Then we loaded our JSON data to a new able.

And we ended things by asking the web tool to write a SQL SELECT statement for us, and we ran it!

If you liked this post, and playing with your Spotify data, I’ve got one more for you! We’ll show how to do some sorting, add functions to our SELECTs, and do a GROUP BY!

Part II: Answering some questions about our data with SQL

Looking for some good resources for learning SQL? See Chris post over on Follow the Coffee!

Chris is a great follow if you also use Python to play with data in your Oracle Database.
thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

1 Comment

  1. Avatar

    Hello again Jeff. Thanks also for this post (following on from commenting about your Netflix post). I will definitely be trying this out. I tinker a bit with PHP and have played around with Spotify’s API (sorry for the plug – here you go: https://jimpix.co.uk/m/ – though I realise there are many other setups like https://www.statsforspotify.com and https://discoverquickly.com). However – it’s not possible to download your streaming history using the API so I will be trying out your method ASAP. Thanks again 🙂

Write A Comment