Practice writing SQL queries with more complex logic. There's usually more than one way to get the right answer. If your solution is different that's fine!
Make sure you have Git and Node (v18) installed.
- Use this template, clone your copy, cd into it
- Run
npm installto install all the dependencies
There's a bunch of data about various FAC cohorts in seed.sql. You'll need to read this to figure out exactly what tables you're working with. The DB will automatically be populated with this data before each test.
Each challenge requires you to complete the corresponding statement in model.js. There are tests you can run to verify, e.g. npm run test:1.
List the names of all cohorts that took place in Finsbury Park.
List the usernames of all students who attended FAC in Finsbury Park.
Reveal hint
You need to use the query from the previous question.
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.
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
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.