UNOX Movie Booking Database
SQL Practice Questions & Solutions
LEVEL 1: Data Retrieval & Filtering Basics
Scenario 1: Movie Listing Dashboard
The operations team wants to view basic details about movies currently available in the system.
1. Retrieve all movie details from the database.
SELECT * FROM movies;
Output:
2. Show only the movie title, genre, and rating.
SELECT title, genre, rating
FROM movies;
Output:
3. Display the same results but with user-friendly column names using aliases.
SELECT title AS 'Movie Name', genre AS 'Category',
rating AS 'IMDb Rating'
FROM movies;
Output:
Scenario 2: Screen Management
The cinema manager wants to check available screens by type.
1. Retrieve all screen details.
SELECT * FROM screens;
Output:
2. Display only the screen name and capacity.
SELECT screen_name, capacity
FROM screens;
Output:
3. Retrieve screens with a capacity greater than 150.
SELECT *
FROM screens
WHERE capacity > 150;
Output:
Scenario 3: User Directory
The admin team wants to view customer contact details.
1. Retrieve all user names and their email addresses.
SELECT name, email
FROM users;
Output:
2. Use column aliases to make the output more readable.
SELECT name AS 'Customer Name', email AS 'Email Address'
FROM users;
Output:
3. List users who have provided a phone number.
SELECT *
FROM users
WHERE phone IS NOT NULL;
Output:
LEVEL 2: Advanced Filtering & Logical Operators
Scenario 1: Targeted Movie Search
The marketing team wants to identify movies to promote based on their attributes.
1. Find all movies whose titles start with the letter 'A'.
SELECT *
FROM movies
WHERE title LIKE 'A%';
Output:
2. Retrieve all movies that belong to either the 'Action' or 'Comedy' genre.
SELECT *
FROM movies
WHERE genre IN ('Action', 'Comedy');
Output:
3. Retrieve movies with ratings between 7.5 and 9.0.
SELECT *
FROM movies
WHERE rating BETWEEN 7.5 AND 9.0;
Output:
Scenario 2: Show Scheduling Check
The scheduling team wants to verify upcoming shows and avoid scheduling errors.
1. Retrieve all shows scheduled after a specific date.
SELECT *
FROM shows
WHERE show_date > '2024-01-01';
Output:
2. Find shows that are not assigned to Screen 1 or Screen 2.
SELECT *
FROM shows
WHERE screen_id NOT IN (1, 2);
Output:
3. Identify shows that have not yet been assigned a movie.
SELECT *
FROM shows
WHERE movie_id IS NULL;
Output:
Scenario 3: User Reward Analysis
The membership team wants to analyze user points and participation.
1. Retrieve users who have between 100 and 500 reward points.
SELECT *
FROM members
WHERE points BETWEEN 100 AND 500;
Output:
2. Identify users who have zero points or have not been linked to a user record.
SELECT *
FROM members
WHERE points = 0 OR user_id IS NULL;
Output:
3. Retrieve members who have earned points and have a valid email address.
SELECT m.*
FROM members m
JOIN users u ON m.user_id = u.user_id
WHERE [Link] > 0 AND [Link] IS NOT NULL;
Output:
LEVEL 3: Aggregation & Ordering
Scenario 1: Movie Ratings Summary
The analytics team wants to understand how different genres are performing.
1. Count the number of movies available in each genre.
SELECT genre, COUNT(*) AS movie_count
FROM movies
GROUP BY genre;
Output:
2. Find the average rating for each genre.
SELECT genre, ROUND(AVG(rating), 2) AS avg_rating
FROM movies
GROUP BY genre;
Output:
3. Display the genres in order of their average rating (highest first).
SELECT genre, ROUND(AVG(rating), 2) AS avg_rating
FROM movies
GROUP BY genre
ORDER BY avg_rating DESC;
Output:
Scenario 2: Ticket Sales Insights
The finance team wants to track booking and revenue patterns.
1. Count how many bookings each user has made.
SELECT user_id, COUNT(*) AS booking_count
FROM bookings
GROUP BY user_id;
Output:
2. Calculate total booking revenue per show.
SELECT show_id, SUM(amount) AS total_revenue
FROM bookings
GROUP BY show_id;
Output:
3. List shows in descending order of total revenue.
SELECT show_id, SUM(amount) AS total_revenue
FROM bookings
GROUP BY show_id
ORDER BY total_revenue DESC;
Output:
Scenario 3: Food Sales Reporting
The food & beverage team wants to know which food items are selling best.
1. Find how many times each food item was ordered.
SELECT item_id, COUNT(*) AS order_count
FROM food_orders
GROUP BY item_id;
Output:
2. Calculate the total sales amount for each food item.
SELECT item_id, SUM(amount) AS total_sales
FROM food_orders
GROUP BY item_id;
Output:
3. List food items in descending order of total sales.
SELECT item_id, SUM(amount) AS total_sales
FROM food_orders
GROUP BY item_id
ORDER BY total_sales DESC;
Output: