fidaahersh / db-morning-challenge Goto Github PK

Author: @shiryz

Maintainer: @shiryz

• Intro
• Basic set up
• Connect to Remote DB
  • CLI or Website
  • How to use
• Challenge 1
• Challenge 2
  • Challenge 2.I
  • Challenge 2.II
• Challenge 3

The purpose of this challenge is to connect to a remote database, construct complicated queries, learn about joins and use some subqueries.

• Fork this repo
• Clone your forked version.
• cd into the repo in your command line $ cd db-morning-challenge
• Install dependencies with $ npm install

Using a local database is ok, but only you have access to it, as it is on your PC.

To share a database between multiple developers, we can use remote databases - databases that are hosted on the cloud. We then connect to them the same way we connect to our local ones, just using a different URL - each database will have its own unique url.

Heroku has a service for hosting SQL databases - once you've create a heroku app (as you have been doing since week 4), you can then use a heroku add-on to add a database, either using the heroku command line interface (CLI) or on the website:

NOTE: We are just creating a heroku app so that we can use the heroku remote database - our code doesn't actually make a usable website

These should happen after you have cloned and ran npm i

You and your partner now both have access to the same database from the command line with psql {YOUR_COPIED_DATABASE_URL}

For each of the challenges below, write and test your queries in the command line and save them in a text editor when you're happy with them, so you can refer back to them later.

One of the tables you have created is a books table that looks like this:

your challenge is to construct a query that returns the following columns:

• book_id,
• book_name,
• max_reservation_time

AND to return only the books that can be reserved for a time greater than the average reservation time of its own library group. (hint: We're not trying to find the overall average across all libraries)

Hint: try using sub queries

You should expect to see this:

Your database contains 3 more tables:

Mentors For storing details of FAC mentors.

Posts For things that have been posted by mentors:

Likes For the likes posts have received, and the person who liked it

• Construct a query that returns the names of mentors and the number of likes each mentor got, in total, for all their posts.

  You should expect to see this:

• Construct a query that returns the location and the post id, for each of the likes. The post_id is the id of the post that has been liked and the location is the location of the mentor that did the liking.

  You should expect to see this:

Building on the queries you wrote in level 2, construct another query that returns the average number of likes per post in each location.

You should expect to see this: