LogoLogo
  • FAC Curriculum
  • archive
    • Node & npm introduction
    • developer
      • Programming Paradigms
    • handbook
      • Asking for help
      • Coaching groups
      • Code review
      • Course rules
      • Debugging
      • Employment schedule and material delivered in FAC20
      • GitHub Workflow
      • Glossary or terms
      • Presentation Guidance
      • Equality, Diversity and Inclusion
      • Installation guide
      • Learning circles
      • Mentoring guidance
      • What to expect from your mentors
      • One-day projects
      • Pair programming
      • Portfolio
      • Questions for problem solving
      • Progress Logs
      • Final project report
      • Managing software projects
      • Project Presentations
      • Project roles
      • Projects
      • Retrospectives
      • Role Circles
      • Safeguarding policy
      • Technical Spikes
      • System requirements
      • Tech for Better
      • User Manuals
      • Wellbeing Support
      • project-docs
        • What makes a mentor?
        • Product Handover
        • Sprint Planning
        • Tech for Better Presentations
        • User Research & Usability Testing
    • foundation
      • full-stack
        • Learning Outcomes
        • project
      • testing
        • project
        • spikes
  • docs
    • Contributing to the curriculum
    • Curriculum intent
    • Curriculum process
  • src
    • About our curriculum
    • course
      • Code of Conduct
      • Docker
      • .NET and Umbraco
      • Getting started
      • Founders and Coders coursebook
      • KSB's
      • Mini projects
      • Revision checklist
      • Svelte
      • TypeScript
      • handbook
        • Software Developer Handbook
        • Software Foundation Handbook
      • precourse
        • Before you start the course
        • Installation List
      • syllabus
        • developer
          • app
            • learning-outcomes
            • project
            • schedule
            • spikes
          • introduction
            • learning-outcomes
            • project
            • resources
            • schedule
          • week00-pre-course
            • We'd like you to spend some time before starting the course working on useful fundamentals.
            • spikes
          • week01-project01-basics
            • Employability introduction
            • Homework
            • learning-outcomes
            • Week of September 9th
            • project
            • resources
            • schedule
            • spikes
          • week02-project02-chatbot
            • employability
            • Homework
            • learning-outcomes
            • Week of September 16th
            • project
            • resources
            • schedule
            • spikes
          • week03-project03-server
            • Learning Outcomes
            • Week of September 23th
            • The Amazin' Quizzer API Backend
            • resources
            • schedule
          • week04-project03-frontend
            • learning-outcomes
            • Week of September 30th
            • UI for Quizzer App
            • resources
            • schedule
          • week05-project03-test-deploy
            • Testing and deployment
            • Week of October 7th
            • project
            • resources
            • schedule
          • week06-project04-databases
            • learning-outcomes
            • Week of October 14th
            • project
            • Databases
            • schedule
          • week07-project04-authentication
            • Learning Outcomes
            • Week of October 21st
            • project
            • resources
            • schedule
          • week08-project04-test-deploy
            • Learning Outcomes
            • Week of October 28th
            • project
            • resources
            • schedule
          • week09-reading-week
            • Learning Outcomes
            • overview
            • Project
            • Resources
            • schedule
          • week10-project05-DOTNET-intro
            • Learning Outcomes
            • overview
            • project
            • Resources
            • schedule
          • week11-project05-DOTNET-testing
            • Testing and deployment
            • Week of November 18th
            • project
            • Resources
            • schedule
          • week12-project05-DOTNET-deploy
            • Learning Outcomes
            • Week of November 25th
            • project
            • Resources
            • schedule
            • Spikes
          • week13-TFB-design
            • Learning Outcomes
            • overview
            • Project
            • Resources
            • schedule
            • Design Week Spikes
          • week14-TFB-build
            • Learning Outcomes
            • overview
            • Project
            • DevOps Resources
            • schedule
            • Spikes
          • week15-TFB-build
            • Learning Outcomes
            • overview
            • Project
            • Resources
            • schedule
            • Spikes
          • projects
            • in-house-design
              • Learning Outcomes
              • Project
              • Resources
              • schedule
              • Design Week Spikes
        • foundation
          • Obsolete-full-stack
            • project
          • post-course
            • Homework
            • schedule
        • portfolio
          • fruit-shop
            • learning-outcomes
            • project
            • resources
          • game
            • learning-outcomes
            • project
            • resources
          • hobby-page
            • learning-outcomes
            • project
            • resources
          • movie-data
            • learning-outcomes
            • project
            • resources
          • project-gallery
            • learning-outcomes
            • project
            • resources
          • website
            • learning-outcomes
            • project
            • JavaScript
        • tfb
          • week 1
            • Introduction (45 minutes)
            • Further reading
          • week 10
            • content
            • resources
          • week 11
            • What will we be doing this week?
            • resources
          • week 12
            • What will we be doing this week?
            • Further reading
          • week 2
            • Discover (90 minutes)
            • resources
          • week 3
            • content
            • resources
          • week 4
            • Mapping the user journey (90 minutes)
            • resources
          • week 5
            • Figma Workshop 1 (90 minutes)
            • Further reading
          • week 6
            • Figma Workshop 2 (90 minutes)
            • resources
          • week 7
            • Product pitches & Selection (90 minutes)
            • resources
          • week 8
            • content
            • resources
          • week 9
            • content
            • resources
    • learn
      • DOTNET
        • Introduction to .NET
      • auth
        • Authenticating web apps
      • database
        • Persisting data with SQLite and Node
      • dotnet-two
        • Dependency injections and interfaces in .NET
      • form-validation
        • Form validation
      • react
        • Building client-side apps with React
      • server
        • HTTP servers with Node & Express
      • typescript
        • TypeScript
    • mentoring
      • design-week
        • Analysis Workshop
        • Code planning
        • Definition Workshop
        • Discovery Workshop
        • Figma introduction
        • Usability testing
        • User Research
    • resources
      • http
        • introduction
    • workshops
      • cookie-auth
        • index
      • creating-promises
        • index
      • css-layout
        • index
      • cypress-testing
        • index
      • database-testing
        • index
      • dev-tooling
        • Developer tooling
      • dom-challenge
        • index
      • dom-rendering
        • index
      • es-modules
        • index
      • express-middleware
        • Express middleware
      • first-class-functions
        • index
      • form-validation
        • index
      • functions-callbacks-async
        • Functions, callbacks, & async JavaScript
      • git-intro
        • Introduction to Git
      • git-terminal
        • Using Git in the terminal
      • git-workflow
        • Git workflow
      • github-projects
        • GitHub Projects Workflow Workshop
      • heroku-sql-challenge
        • index
      • html-forms
        • index
      • learn-a11y
        • index
        • starter-files
          • solution
            • Accessibility solution explanation
      • learn-fetch
        • index
      • learn-integration-testing
        • index
      • learn-testing
        • Learn testing in JavaScript
      • learn-unit-testing
        • index
      • node-error-handling
        • Node error-handling
      • node-express-server
        • Node and Express HTTP server
      • node-npm-intro
        • Node & npm introduction
      • node-postgres
        • Learn Postgres with Node
      • node-scripting-challenge
        • index
      • password-security
        • index
      • promise-practice
        • index
      • react-components
        • React components
      • react-fetch
        • index
      • react-forms
        • React forms
      • react-refactor-classes
        • index
      • react-state-effects
        • React state & effects
      • real-world-fetch
        • index
      • scope-challenge
        • Scope debugging challenge
      • semantic-html
        • index
      • server-side-forms
        • Server-side forms
      • session-auth
        • Session authentication
      • sql-intro
        • index
      • tdd-array-methods
        • index
Powered by GitBook
On this page
  • Advanced SQL
  • Setup
Export as PDF
  1. src
  2. workshops
  3. heroku-sql-challenge

index

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

Previousheroku-sql-challengeNexthtml-forms

Last updated 4 years ago

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 .

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. 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

instructions for setting up a free Heroku database
W3 Schools