Production Relational SchemaIntermediate

Movie Database

Movies, actors, genres, ratings and user reviews.

7 Tables25K+ RecordsJoinsGROUP BYAnalytics

Interactive SQL Playground & Query Runner

In-Browser Sandbox

Run realistic queries against the Movie Database database. Write custom SQL or solve high-yield interview problems.

Interview Challenge:

easy

Top Rated Movies with Average Critic Scores

Calculate average review ratings and total review counts per movie. Return title, release_year, review_count, and avg_score.

JOINAVGGROUP BY

query.sql

Press Ctrl + Enter to execute

Entity Relationship (ER) & Foreign Key Architecture

Referential integrity mappings and table cardinalities

5 Key Relations

1:NFeatures Cast

movies

film_id

movie_cast

film_id

1:NActs In

actors

performer_id

movie_cast

performer_id

1:NTagged With

movies

film_id

film_genres

film_id

1:NClassifies

genres

genre_id

film_genres

genre_id

1:NReviewed By

movies

film_id

reviews

film_id

Relational Tables & Column Data Types

Field definitions, types, and primary keys for query construction

7 tables total

movies

4 cols

PKfilm_id

INT

title

VARCHAR(120)

release_year

INT

runtime_min

INT

actors

2 cols

PKperformer_id

INT

stage_name

VARCHAR(100)

movie_cast

3 cols

PKfilm_id

INT

PKperformer_id

INT

billed_as

VARCHAR(80)

genres

2 cols

PKgenre_id

INT

genre_name

VARCHAR(60)

film_genres

2 cols

PKfilm_id

INT

PKgenre_id

INT

reviews

5 cols

PKrating_id

INT

PKfilm_id

INT

viewer_alias

VARCHAR(60)

score

DECIMAL(3,1)

rated_on

DATE

studios

2 cols

PKstudio_id

INT

studio_name

VARCHAR(100)

Runnable DDL Setup & Seed Script

Run directly in MySQL Workbench, pgAdmin, DBeaver, or terminal

CREATE DATABASE IF NOT EXISTS studyflow_movies;
USE studyflow_movies;

CREATE TABLE movies (
  film_id INT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  release_year INT,
  runtime_min INT
);

CREATE TABLE actors (
  performer_id INT PRIMARY KEY,
  stage_name VARCHAR(100) NOT NULL
);

CREATE TABLE movie_cast (
  film_id INT,
  performer_id INT,
  billed_as VARCHAR(80),
  PRIMARY KEY (film_id, performer_id),
  FOREIGN KEY (film_id) REFERENCES movies(film_id),
  FOREIGN KEY (performer_id) REFERENCES actors(performer_id)
);

CREATE TABLE genres (
  genre_id INT PRIMARY KEY,
  genre_name VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE film_genres (
  film_id INT,
  genre_id INT,
  PRIMARY KEY (film_id, genre_id),
  FOREIGN KEY (film_id) REFERENCES movies(film_id),
  FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

CREATE TABLE reviews (
  rating_id INT PRIMARY KEY,
  film_id INT,
  viewer_alias VARCHAR(60),
  score DECIMAL(3,1),
  rated_on DATE,
  FOREIGN KEY (film_id) REFERENCES movies(film_id)
);

CREATE TABLE studios (
  studio_id INT PRIMARY KEY,
  studio_name VARCHAR(100) NOT NULL
);

INSERT INTO movies VALUES
(1,'Signal at Dawn',2021,118),(2,'Paper Satellites',2023,104),(3,'The Last Monsoon',2022,132),
(4,'Quiet Orbit',2024,110),(5,'Northbound',2020,126);

INSERT INTO actors VALUES
(1,'Ari Vale'),(2,'Mina Ray'),(3,'Dev Shah'),(4,'Nora Sen'),(5,'Kabir Roy');

INSERT INTO movie_cast VALUES
(1,1,'Lead'),(1,2,'Lead'),(2,3,'Lead'),(2,4,'Supporting'),
(3,2,'Lead'),(3,5,'Lead'),(4,1,'Lead'),(5,3,'Lead');

INSERT INTO genres VALUES
(1,'Drama'),(2,'Sci-Fi'),(3,'Thriller'),(4,'Comedy');

INSERT INTO film_genres VALUES
(1,1),(1,3),(2,2),(2,4),(3,1),(3,3),(4,2),(5,1);

INSERT INTO reviews VALUES
(1,1,'viewer_01',8.4,'2026-01-03'),(2,1,'viewer_02',9.0,'2026-01-08'),
(3,2,'viewer_03',7.8,'2026-02-02'),(4,3,'viewer_04',8.7,'2026-02-15'),
(5,4,'viewer_05',9.2,'2026-03-10'),(6,5,'viewer_06',7.9,'2026-03-12');

INSERT INTO studios VALUES
(1,'Northstar Pictures'),(2,'Blue Frame'),(3,'Orbit Works');