index

Practice deploying a PostgreSQL database to Heroku, plus some advanced SQL commands.

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:[e-mail]: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

2. Student locations

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

   Expected result

   username
   virtualdominic
   charlielafosse
   starsuit
   bobbysebolao
   albadylic
   reubengt

3. Student locations

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

   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

4. Students with projects

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

   Expected result

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

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.

   Expected result

   name	username
   Hamster Hotel	starsuit
   Agony Yaunt	starsuit
   Agony Yaunt	bobbysebolao