abdou / classroom
Back to MIS 3220

SQL Lesson 19: Comprehensive Review

Test your mastery of SQL with comprehensive challenges covering all concepts learned.

Loading Database...

SQL Mastery Challenge

This final lesson brings together everything you've learned through a comprehensive set of challenges that test your mastery of SQL concepts across all areas covered in the tutorial.

Database Schema Overview

You'll be working with a comprehensive movie database containing 5 interconnected tables with detailed Pixar movie data:

movies

Complete information about 14 Pixar movies (1995-2013)

id: INTEGER PRIMARY KEY
title: TEXT (movie title)
director: TEXT (director name)
year: INTEGER (release year)
length_minutes: INTEGER (runtime)
genre: TEXT (Animation)
rating: TEXT (G, PG ratings)
budget: INTEGER (production cost)
language: TEXT (default: English)

boxoffice

Financial performance data for each movie

movie_id: INTEGER PRIMARY KEY, FK
rating: REAL (IMDb rating 6.4-8.5)
domestic_sales: INTEGER (US box office)
international_sales: INTEGER (worldwide)
opening_weekend: INTEGER (opening weekend)

directors

Information about 7 Pixar directors

id: INTEGER PRIMARY KEY
name: TEXT (director name)
birth_year: INTEGER (birth year)
nationality: TEXT (American)
awards_count: INTEGER (major awards)

studios

Production and distribution companies

id: INTEGER PRIMARY KEY
name: TEXT (studio name)
founded_year: INTEGER (establishment year)
headquarters: TEXT (location)

movie_studios

Many-to-many relationship between movies and studios

movie_id: INTEGER FK (movies.id)
studio_id: INTEGER FK (studios.id)
role: TEXT (Production/Distribution)

Sample Data: Includes movies from Toy Story (1995) to Monsters University (2013), with complete financial data, director information, and studio relationships. Perfect for testing complex JOINs, aggregations, and analytical queries.

Challenge Guidelines

  • Each challenge tests different SQL concepts and difficulty levels
  • Use the category filter to focus on specific skill areas
  • Solutions demonstrate best practices and efficient query patterns
  • Take your time to understand the relationships between tables
  • Don’t hesitate to explore the data before attempting challenges

Skills Covered

Basic Queries
Aggregates
Joins & Calculations
Complex Joins
Advanced Queries
Advanced Calculations

Query Editor

Run a query to see results

SQL Mastery Challenge

1
Easy
Basic Queries
Find all movies released after 2005 with an IMDb rating above 8.0, showing title, year, and IMDb rating.
Hint: Use WHERE clause with multiple conditions and specify table aliases for columns
2
Easy
Aggregates
Calculate the total domestic and international sales for all Pixar movies, showing the grand totals.
3
Medium
Aggregates
Find the average movie length by decade (1990s, 2000s, 2010s), showing decade and average length.
4
Medium
Aggregates
List all directors who have directed more than 2 movies, showing director name and movie count, ordered by movie count descending.
5
Medium
Joins & Calculations
Find movies that made more internationally than domestically, showing title, domestic sales, international sales, and the difference.
6
Hard
Complex Joins
Create a comprehensive report showing each studio's total movies, average rating, and total revenue (domestic + international).
7
Hard
Advanced Queries
Find the most profitable movie per year (highest total revenue), showing year, title, director, and total revenue.
8
Hard
Advanced Calculations
Calculate the return on investment (ROI) for each movie as (total_revenue - budget) / budget * 100, showing only movies with ROI > 200%.

Complete all challenges to master SQL!

Progress: 0 / 8 challenges completed
Overall Progress0 of 8 challenges completed
Master all challenges to complete your SQL journey!