Remote database challenge

You don’t want your deployed production app talking to a database running on your laptop. This would be slow, insecure and require you to leave it turned on all the time.

Instead we can host our production database on a 3rd party service like Heroku. This is especially convenient if we’re already hosting our production server on Heroku.

Follow these instructions for setting up a free Heroku database.

Once you’re done you should have a connection string that looks something like this:

postgres://okaws:d3fa@ec2-54.eu-west-1.compute.amazonaws.com:5432/d8bvo

You can connect to the remote database from your terminal by running:

psql your_url_goes_here

Advanced SQL

Let’s practice some more advanced SQL commands. There’s a bunch of data about various FAC cohorts in init.sql. You’ll need to read this to figure out exactly what tables you’re working with.

You may have to search the internet for SQL you haven’t seen before. W3 Schools is a good resource.

There’s usually more than one way to get the right answer. If your solution is different that’s fine!

Setup

1. Download the starter files
2. Connect to your Heroku database with psql your_database_url
3. Insert the data into your DB with \i init.sql

You can check everything is set up by listing the database tables with \dt. You should see four FAC-related tables: cohorts, students, projects and students_projects.

1. Cohort locations

   List the names of all cohorts that took place in Finsbury Park.

   Expected result

   name
   14
   15
   16
   17

   Toggle answer

   SELECT name FROM cohorts WHERE location = 'Finsbury Park';

2. Student locations

   List the usernames of all students who attended FAC in Finsbury Park.

   Reveal hint

   You need to use the query from the previous question.

   Expected result

   username
   virtualdominic
   charlielafosse
   starsuit
   bobbysebolao
   albadylic
   reubengt

   Toggle answer

   SELECT username FROM students WHERE cohort_name IN (
     SELECT name FROM cohorts WHERE location = 'Finsbury Park'
   );

3. Student locations

   List the username of each student along with the location of their cohort.

   Reveal hint

   Remember you can use joins to connect two tables together and access information from both.

   Expected result

   username	location
   eliascodes	Bethnal Green
   oliverjam	Bethnal Green
   yvonne-liu	Bethnal Green
   matthewdking	Nazareth
   helenzhou6	Bethnal Green
   virtualdominic	Finsbury Park
   charlielafosse	Finsbury Park
   starsuit	Finsbury Park
   bobbysebolao	Finsbury Park
   albadylic	Finsbury Park
   reubengt	Finsbury Park

   Toggle answer

   SELECT students.username, cohorts.location FROM students
     INNER JOIN cohorts ON students.cohort_name = cohorts.name;

4. Students with projects

   List all project names with the usernames of the students who worked on them.

   Reveal hint

   Since projects-to-students is a many-to-many relationship (each project can have multiple authors, each student can have multiple projects) we can’t link them with just IDs. We need to use a separate table to keep track of which students worked on which projects.

   This is often called a join table, or junction table. You’ll need to join to this as an intermediary step to link projects to students. You don’t need to create it—it is already created in init.sql

   Expected result

   name	username
   FACX Machine	oliverjam
   FACX Machine	yvonne-liu
   Hamster Hotel	oliverjam
   Hamster Hotel	starsuit
   Agony Yaunt	starsuit
   Agony Yaunt	bobbysebolao

   Toggle answer

   SELECT projects.name, students.username FROM projects
     INNER JOIN students_projects ON projects.id = students_projects.project_id
     INNER JOIN students ON students.username = students_projects.student_username;

5. Bonus: Students with projects by location

   List all project names with the usernames of the students who worked on them, only for students who attended FAC in Finsbury Park.

   Reveal hint

   You’ve written all the queries you need in previous steps.

   Expected result

   name	username
   Hamster Hotel	starsuit
   Agony Yaunt	starsuit
   Agony Yaunt	bobbysebolao

   Toggle answer

   SELECT projects.name, students.username FROM projects
     INNER JOIN students_projects ON projects.id = students_projects.project_id
     INNER JOIN students ON students.username = students_projects.student_username
     WHERE students.username IN (
       SELECT username FROM students
         WHERE cohort_name IN (
           SELECT name FROM cohorts WHERE location = 'Finsbury Park'
         )
     );