> For the complete documentation index, see [llms.txt](https://founders-and-coders.gitbook.io/coursebook/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://founders-and-coders.gitbook.io/coursebook/src/workshops/heroku-sql-challenge/index.md).

# 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](https://dev.to/prisma/how-to-setup-a-free-postgresql-database-on-heroku-1dc1).

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](https://www.w3schools.com/sql/) 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 |