SQL Courses: Which file in the github to upload chinook db locally on my mysql workbench app

I am retaking the intermediate SQL course and I really want to ditch the datquest online platform and do this on my compute with sql workbench. That way I can just play around and test things out better as I go along. Anyway, I have workbench and use it for other stuff but I am super new at it so when the course links to get the data in order to do this: https://github.com/lerocha/chinook-database

Here is the link for the course just for ref: https://app.dataquest.io/m/189/intermediate-joins-in-sql/1/working-with-larger-databases

I don’t follow which file to download and/or where to go from there. Would really appreciate some help here! thank you!

hi @jeff.henderson1986

Welcome back to DQ!

After you access the link, navigate to folder >> “ChinookDatabase” then “DataSources”. In the datasources folder you will all variants of Chinook database, for sqlite, postgres, oracle etc. Select the one applicable for you and download that particular database.

@Rucha I’m coming to the game a tad late but…

I followed your instructions which lead me to a *.sql file that appears to be SQL code to manually create the database as opposed to a *.db file which we have been working with so far in DQ. So the obvious question is/are: how do I use this *.sql file to make a *.db file that I can connect to the way I have so far in jupyter-notebook or how can I use this *.sql file to manually create a database locally that I can query?

For those who find this topic and are simply looking to download the ‘modified version of the database’ that’s used in the DQ mission, simply download the file via the chinook.db tab by clicking the cloud containing an arrow pointing down as shown in the image below

hi @mathmike314

Let me know if this doesn’t help you.

5 Ways to Run an SQL Script from a File in SQLite | Database.Guide

(This is based on the assumption, you are simply looking to start working with “.sql” file.)

Thank you for the link but unfortunately I was not able to get any of those methods to work with the SQL file I downloaded from github (Chinook_Sqlite.sql) I keep getting a syntax error amongst other things:

mike@TensorBook:~/Downloads$ sqlite3 test.db '.read Chinook_Sqlite.sql'
Error: near line 1: near "": syntax error
Error: near line 66: table [Album] already exists
Error: near line 216: index IFK_AlbumArtistId already exists
Error: near line 548: UNIQUE constraint failed: Album.AlbumId

And here I thought just writing complex subqueries with joins was the most difficult thing to learn about SQL! :stuck_out_tongue_winking_eye:

Hi @mathmike314

you already have the tables created in test database.

You can try creating a different database using the commands in the link or directly work on the Test db.

Is there a reason for you to work on Sqlite only, or you are trying similar process on Postgres as well? (I am not giving providing you with direct answers, as I assume you are taking the set-up as a self learning process. Let me know in case otherwise.)

Honestly, it was just a little “side mission” to see if I could make use of a *.sql file to create a *.db file that I could then access. I am so new to SQL that I just want to play around with everything I see in order to make some sense of it. However, I quickly gave up on this little endeavour and went back to the DQ missions. I haven’t even glanced at other flavours of SQL and how they differ.

1 Like

Hi @mathmike314

Oh, great! Please work through the DQ’s content at your own pace and comfortability. :+1:

As an extra learning, this website may prove useful - SQLite Tutorial for Beginners: Learn in 3 Days (guru99.com)

Happy learning. :slight_smile: Let me know if there’s anything else I can help you with.

1 Like

Thank you kindly, @Rucha! I will check out that link later today (I’ve randomly landed on that site before while Googling for answers).

I’m sure I will have more questions going forward and I appreciate all the help you provide to the community.