MIS 3220 - Homework 4
SQL Aggregate Functions - IMDB Movies Database
Due Date: Tuesday, October 21, 2025
Student Information
Your name will be included in the downloaded file for identification purposes.
Progress
0 / 35 questions completedDatabase Schema
The imdb_movies.db database contains a single table called movies with the following columns:
CREATE TABLE movies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
poster_link TEXT,
series_title TEXT,
released_year INTEGER,
certificate TEXT,
runtime INTEGER,
genre TEXT,
imdb_rating REAL,
overview TEXT,
meta_score INTEGER,
director TEXT,
star1 TEXT,
star2 TEXT,
star3 TEXT,
star4 TEXT,
no_of_votes INTEGER,
gross INTEGER
);Note: The database contains 1,000 top-rated movies from IMDB. Some fields like gross and meta_score may contain NULL values.
Assignment Specification
For each of the following questions, write a single SQL query in the text box provided. Your answers are automatically saved as you type.
Important Guidelines:
- Each query should return only the data necessary to answer the question
- Use the exact column names specified in each question
- Use the AS keyword to rename columns as required
- Your answers are automatically saved to your browser
- Click "Download SQL File" when you're ready to submit
Section 1: Basic Aggregate Functions (Simple)
Write a SQL query to find the total number of movies in the database. Name the result column total_movies.
Write a query to find the highest IMDB rating in the database. Name the result column highest_rating.
Write a query to find the lowest IMDB rating in the database. Name the result column lowest_rating.
Write a query to calculate the average IMDB rating across all movies. Round the result to 2 decimal places. Name the result column average_rating.
Write a query to find the longest movie runtime (in minutes). Name the result column longest_movie.
Write a query to find the shortest movie runtime (in minutes). Name the result column shortest_movie.
Write a query to calculate the average runtime of all movies in the database. Round the result to 1 decimal place. Name the result column average_runtime.
Write a query to calculate the total number of votes across all movies. Name the result column total_votes.
Write a query to find the highest gross revenue earned by any movie. Name the result column highest_gross.
Write a query to calculate the average meta score of all movies. Round the result to 2 decimal places. Name the result column average_metascore.
Section 2: Aggregate Functions with WHERE Clause (Intermediate)
Write a query to count how many movies have an IMDB rating of 9.0 or higher. Name the result column highly_rated_count.
Write a query to calculate the average IMDB rating for movies released after the year 2000. Round the result to 2 decimal places. Name the result column avg_rating_after_2000.
Write a query to calculate the total gross revenue for movies with an IMDB rating of 8.5 or higher. Name the result column total_gross_top_rated.
Write a query to find the average runtime for movies that are longer than 150 minutes. Round the result to 1 decimal place. Name the result column avg_runtime_long_movies.
Write a query to find the maximum number of votes received by any movie released in the 21st century (year 2000 or later). Name the result column max_votes_21st_century.
Write a query to count how many movies were released in the 1990s (1990-1999 inclusive). Name the result column movies_in_1990s.
Write a query to find the release year of the oldest movie in the database. Name the result column oldest_movie_year.
Write a query to find the release year of the newest movie in the database. Name the result column newest_movie_year.
Write a query to calculate the average meta score for movies with an IMDB rating of 8.5 or higher. Round the result to 2 decimal places. Name the result column avg_metascore_top_rated.
Write a query to count how many movies have a runtime shorter than 90 minutes. Name the result column short_movies.
Section 3: Advanced Aggregate Functions (Challenging)
Write a query to calculate the average number of votes for movies that have an IMDB rating of 8.0 or higher AND were released in 2010 or later. Round the result to the nearest whole number (0 decimal places). Name the result column avg_votes.
Write a query to find the lowest gross revenue, making sure to exclude NULL values. Name the result column lowest_gross.
Write a query to count how many movies have gross revenue data available (not NULL). Name the result column movies_with_gross.
Write a query to calculate the average gross revenue, excluding movies without gross revenue data. Round the result to 2 decimal places. Name the result column avg_gross.
Write a query to calculate the total number of votes for movies released in 2015 or later. Name the result column total_votes_recent.
Write a query to count how many unique directors are represented in the database. Name the result column unique_directors.
Write a query to find the highest meta score in the database, excluding NULL values. Name the result column highest_metascore.
Write a query to find the lowest meta score in the database, excluding NULL values. Name the result column lowest_metascore.
Write a query to count how many "classic" movies (released before 1980) are in the database. Name the result column classic_movies.
Write a query to calculate the average runtime for modern movies (released in 2000 or later) that have a runtime shorter than 120 minutes. Round the result to 1 decimal place. Name the result column avg_runtime_modern.
Section 4: GROUP BY and HAVING (Advanced)
Write a query to find directors whose movies have earned more than $1 billion in total gross revenue. Display the director name and total gross revenue. Exclude movies with NULL gross values. Order the results by total gross in descending order. Name the result columns director and total_gross.
Write a query to analyze directors who have made 5 or more movies. For each director, show their name, total number of movies, average IMDB rating (rounded to 2 decimal places), and their highest-rated movie's rating. Order the results by average rating in descending order. Name the result columns director, total_movies, avg_rating, and highest_rating.
Write a query to find directors who have made 3 or more movies since 2010. For each director, show their name, the count of recent movies, and their average IMDB rating (rounded to 2 decimal places). Order the results by average rating in descending order. Name the result columns director, recent_movies, and avg_rating.
Write a query to analyze movie certificates (ratings like PG, R, etc.) that have 50 or more movies. For each certificate, show the certificate name, total number of movies, and the count of unique directors. Exclude movies with NULL certificates. Order the results by total movies in descending order. Name the result columns certificate, total_movies, and unique_directors.
Write a query to find "elite" directors who have made at least 4 movies AND have an average IMDB rating of 8.0 or higher. Display the director name, movie count, and average rating (rounded to 2 decimal places). Order the results by average rating in descending order. Name the result columns director, movie_count, and avg_rating.
Note: Your answers are automatically saved in your browser. Click "Download SQL File" to generate your submission file with your name and encrypted metadata.