Challenge 15 Megathread

For any and all questions relating to Challenge 15 :point_down: post away!

Hi. I can’t seem to find the sex column in the database, is it only me or anyone else have the same problem?

You can find it in the ‘members’ table. e.g. SELECT * FROM members

2 Likes

FYI there are TWO tables you can access data from: ‘peaks’ and ‘members’.

peaks-has data about mountain heights in meters and feet.
members - has data about the genders of the mountaineers.

You can go “SELECT stuff FROM members” or you can go “SELECT stuff FROM peaks”.
Substituting * for stuff will select the whole row, while you can specify certain column names from the examples if you wish.

Also, no words need to be uppercase or lowercase, it’s just a convention that makes reading SQL easier.

To the tutorial people/lighthouse labs: Not sure why you didn’t provide a database schema in the tutorial, it seems to have been an oversight.

13 Likes

Thank you !!! found it. Best of luck.

Useful code for reviewing database name:
cur = conn.cursor()
cur.execute(“SELECT name FROM sqlite_master WHERE type = ‘table’”).fetchall()

After you get the name of database, follow the tutorial to check each dataframe. Good luck.

3 Likes

How do we get a list of the tables in the database if we don’t know them? Did I miss this in the tutorial?

3 Likes

There isn’t a mention of how to get a list of the tables from the database, but you can infer that PEAKS and MEMBERS are the tables of interest from the tutorial examples.

Yes, my challenge is finished for today but I’m still looking to learn how to do this. Perhaps the moderators from Lighthouse Labs can answer this.

2 Likes

After searching online, I found how to identify the tables inside a database:
cur = conn.cursor()
cur.execute(“SELECT name FROM sqlite_master WHERE type = ‘table’”).fetchall()
This prints the names of the tables; hope it helps!
PS there are three tables in this database (peaks, members, and exped)

5 Likes

Morning!
If anyone knows how to export *.db files, please post how to do it. My searches have not been successful so far.
Thanks in advance!

1 Like

Hi @Mayra!

What do you mean by export? If you mean “how to download from LHL’s jupyter notebook”, you can do this:

from IPython.display import FileLink
FileLink("FILENAME.extension")

Replace “FILENAME.extension” with the file name. E.g. for this challenge replace with “himalayas.db”.

2 Likes

In Postgres, you just have to use

postgres = # \dt

But why do you need this?

Thank you @Mayra, that works perfectly! It’s not very intuitive but I’ll go with it!

A slightly more intuitive approach might be this:

query = "SELECT name FROM sqlite_master WHERE type='table'"
pd.read_sql(query, conn)

It’s more similar to the rest of the code in the challenge, but not very different from Mayra’s example.

Edit:
It seems that sqlite_master is a table with info about the other tables. So if you did the below, you would see what’s in sqlite_master, and notice a column called table that includes the names of all other tables in the database:

query = "SELECT * FROM sqlite_master"
pd.read_sql(query, conn)
2 Likes

Apologies for repeating what you had already posted (how to find tables involved).

You are welcome, and I agree: definitely not intuitive! But I need to point out that @amaturecoder had already found the answer; should have just quoted him instead of reporting the same thing.

Just some feedback on today’s challenge - I feel like it could have been explained a little bit better (e.g. the “*” in the examples, names of the databases). I guess the good thing is that this makes us look for the answers on the web/interact in the forum, but some more guidance would have been helpful for someone who had no idea what SQL is like me.

Edit: I forgot to thank everyone for their contributions here, I finally understood the challenge after going through this thread!

3 Likes

Hi! Thank you for your reply. I am interesting in downloading the data to work in my “personal space”. You gave us a great solution in Day 12’s post, but my online search did not hit a specific case for *.db. I was not sure if we needed to use anything similar to the steps you used there (any base64/HTML encode/decode). It is definitely easier to work outside of the “small window” we have in the challenge environment! Have a good day.

1 Like

Look at the examples, you find the clue.