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:

[email protected]:~/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.

Greetings

If you are still looking for a way to load the database locally, here is how I have done it.

This is the main directory of the machine, here I have the database that I have downloaded so when I call sqlite and say “open” there is no need to give any path.

sqlite_2

This is the only thing you have to do to load the database, as you can see I made a query and it worked.

All the best.

A&E

Hi @Edelberth and thank you for the reply. Unfortunately this was not my problem. Opening .db files seems quite straight forward as you have demonstrated. What I was curious about was following @Rucha’s instructions above to this link and downloading the original database from GitHub. There you will not find .db files but rather .sql files. I was curious to know how to make use of these files.

Out of curiosity, where did you get your chinook.db file from? Did you download it from the DQ platform like I mentioned in my first post to this topic? Because I’m ultimately looking for how to get it “from the source” (ie GitHub) rather than DQ.

Hello

I have been fighting all morning with that link and I have not been able to make it work either.

I have seen that there is only one file with .db extension is the restore.db imagined that it would work following a few links but at no time I have been able to make it work.

So I decided to download the file from DQ seeing that at least it works correctly and could use SQLite “locally”, now I do not want to waste more time with this (if someone find something I :hear_with_hearing_aid:), possibly there is something wrong (according to github there are files with 9 years)…

Let’s talk.

Best regards

A&E

Thanks for trying @Edelberth! I came to the same conclusion: couldn’t make those .sql files work for me and decided if I wanted to work locally, I would just have to download the file from DQ. Still bothers me that I can’t “get if from the source” though! :angry:

Don’t bother, if it’s ok with you, I propose that when we have a moment to study it here and see what we can find out.

Is it ok with you?

Sure, I would love to figure this out! I was doing the SQL missions but after a tragic loss of a guided project, I have gone back to do the updated data visualization and storytelling missions. Once I have completed these I plan to return to learning SQL.

Greetings

I have been doing some research on the Chinnok issue and I think I am beginning to understand a little more about how databases work. :neutral_face:

The main reason why I understand that we can not install the database in the same way that we do with a .db file is that the database is prepared to work in different places hence comes that you must make use of the scripts with different extension.

As far as I have been able to load the script in mySQL workbench, my intention was to convert that I had just loaded in a .db file

…but between that I do not know about databases and that given the version that I have it seems that it does not work well, if I try to solve it I find more problems due to the dependencies in linux … that is another battle.

In short, to make it work you need the program that can read those files and in the case of sqlite apparently by default it can do it but when we do an:

.open Chinook_sqlite.sql
and
.read Chinook_sqlite.sql

a read from sqlite this error appears:

File is encrypted or is not a database message

and as far as I have arrived I have found this link:

For the moment I stop it here.

A&E

Hi @mathmike314, @Edelberth (and @jeff.henderson1986)

Lame excuse: I did not had access to DQ courses for a few months, few months back.

Real excuse: I had abandoned ship for you guys to struggle alone. Apologies.

I am not sure if this is what you guys are looking for, if yes, I hope it helps you real this time.

Step1:

I downloaded the “Chinook_Sqlite.sql” file and saved it in directory - "C:\Users\Dell\Desktop\JupyterFiles\DQ_Guided_Projects\GP8_AnalyzingCIAFactbookUsingSQL"
Turns out it got saved as “Chinook_Sqlite.sql.txt” in the same directory. Perhaps I had opened it in notepad directly or after download. So this is how I renamed it using Windows CMD prompt:

Re-Edit: Command to rename>> rename <old_file_name> <new_file_name>

Step2A:

Open Anaconda Prompt and change to directory where this .SQL file is stored. This is the place where I would also create a Jupyter Notebook. So instead of giving file name reference, I just used cd command.

Once this is done I execute the command to read and store the database structure given in the .sql file in a .db file. Basically now I will have a physical copy of the database in the location.
command: sqlite3 <database_name.db> “.read <file_name.sql>”

As of now even I am not sure about the “?” error. I tried searching any extra marks/ spaces in .sql file but didn’t work. But don’t fret on this, as the database will still get created. Have patience, this process takes time.

Step2B:

While the database is being created, in the directory we will see two files. The one highlighted here is kind of a temp file. (it will keep coming and going!)

Once done the command prompt will be back to directory.

Step3:

Now we access the new database in a jupyter notebook in the same location as database. I am just giving you the codes here. Let me know if they worked for you as well and what was the result.

# import sqlite3
import sqlite3
# connect with the created database - c
conn = sqlite3.connect("chinook.db")
# cursor object
cursor = conn.cursor()

# execute a query - listing all the tables present in database
cursor.execute("SELECT tbl_name FROM sqlite_master WHERE type='table';")
# fetch the results and save in a variable
results = cursor.fetchall()
# display Table names
print(results)

# execute another query - Count No. of rows in Album and Track tables
cursor.execute("""SELECT 'ALBUM', COUNT(*) FROM ALBUM
                  UNION
                  SELECT 'TRACK', COUNT(*) FROM TRACK;
               """)
results = cursor.fetchall()
print(results)
2 Likes

Greetings

I appreciate the post, I can tell you have spent some time in front of the computer, as soon as I have some free time I will give it the love it deserves. Let’s see if I am able to make it work.

Again, thank you very much.

A&E

1 Like