Now that you understand relationships and JOINs, try these exercises that build on the restaurants API you created in Lessons 2 and 3:

1. Design and Create a Reviews Table

Create a reviews table that connects to the restaurants you’ve been working with. Your table should include:

  • A unique ID for each review.
  • A reference to which restaurant was reviewed.
  • A reviewer name (simple text field).
  • A rating (number from 1-5).
  • An optional text comment.
  • A timestamp for when the review was created.
  • Proper foreign key constraint to restaurants with CASCADE DELETE.

Think about the data types you need and write the CREATE TABLE statement using what you’ve learned.

2. Practice JOIN Queries

Once you’ve created your reviews table and added some sample data, try these queries:

  1. Get all reviews with restaurant names: Write a JOIN query that shows review details along with the restaurant name.
  2. Count reviews for a specific restaurant: Use COUNT with a JOIN to see how many reviews a particular restaurant has.
  3. Get reviews for restaurants with a specific cuisine type: Combine JOINs with WHERE clauses to find reviews for all Italian restaurants, for example.

Extend your restaurants API with these new endpoints:

  1. GET /restaurants/:id/reviews - Get all reviews for a restaurant
    This should return reviews with restaurant information included using JOINs.
  2. POST /reviews - Create a new review
    This endpoint should validate that the restaurant exists before creating the review.

4. Test CASCADE DELETE

Practice the CASCADE DELETE concept you learnt:

  1. Add some sample reviews for different restaurants.
  2. Delete one of the restaurants using your DELETE endpoint from Lesson 3.
  3. Verify that all reviews for that restaurant were automatically deleted (CASCADE DELETE in action).
Tags: