Advanced SQL Project Week 6
In this project, I created a database designed to manage the activity of a
cinema.
The database provides information about the movies shown, the cinema
screens, the customers who go to these movies, and the screenings that
determine in which room each movie is projected.
The model is centered around the main concept of a movie, represented by
the MOVIES table, which stores details such as the movie title, release year, and
recommended age rating.
Each movie can be shown in one or more screens, and this relationship is
managed through the SCREENINGS table, which links movies with the screens
(SCREENS) where they are played.
The SCREENS table contains information about each cinema room available
for projections, such as thr seating capacity and the floor on which it is located.
To manage the relationship between viewers and the movies they watch, the
VIEWINGS table was created. It connects the movies from MOVIES with the
customers stored in the CUSTOMERS table.
The CUSTOMERS table keeps details about each customer, including their
full name, age, email address, and phone number.
Conceptual Schema
Tables Creation
Populating the Tables
Hierarchical Queries
Query 1. For every movie screened, who were the specific customers that watched it?
WITH movie_viewing_hierarchy AS (
SELECT movie_id, title AS node_name, NULL AS parent_name, 1 AS hierarchy_level
FROM MOVIES
UNION ALL
SELECT V.movie_id, C.full_name AS node_name, [Link] AS parent_name, 2 AS
hierarchy_level
FROM VIEWINGS V
JOIN CUSTOMERS C ON V.customer_id = C.customer_id
JOIN MOVIES M ON M.movie_id = V.movie_id
)
SELECT LPAD(' ', (hierarchy_level - 1) * 4) || node_name AS Hierarchy_Output
FROM movie_viewing_hierarchy
ORDER BY movie_id, hierarchy_level;
Query 2. If organized by floor, what movie is currently running in each specific
screen?
WITH floor_screen_movie_map AS (
SELECT DISTINCT
[Link] AS level1,
S.screen_id AS level2,
[Link] AS level3
FROM SCREENS S
JOIN SCREENINGS SR ON S.screen_id = SR.screen_id
JOIN MOVIES M ON SR.movie_id = M.movie_id
)
SELECT
'Floor ' || level1 AS Floor_Number,
LPAD('Screen ' || level2, 12, ' ') AS Screen_ID,
LPAD(level3, 30, ' ') AS Movie_Title
FROM floor_screen_movie_map
ORDER BY level1, level2;
Complex Queries
Query 1. For every movie we are currently showing, list the hall ID and the number of
available seats in that hall.
SELECT [Link], S.screen_id, [Link]
FROM MOVIES M
JOIN SCREENINGS SR ON M.movie_id = SR.movie_id
JOIN SCREENS S ON S.screen_id = SR.screen_id
ORDER BY [Link], S.screen_id;
Query 2. How many movies has each customer watched?
SELECT C.full_name,
COUNT(V.movie_id) AS total_movies_watched
FROM CUSTOMERS C
JOIN VIEWINGS V ON C.customer_id = V.customer_id
GROUP BY C.full_name;
Query 3. Which customers did not go to a single movie?
SELECT full_name
FROM CUSTOMERS
WHERE customer_id NOT IN (SELECT customer_id FROM VIEWINGS);
Query 4. Select the halls in which at least 2 movies are shown.
SELECT S.screen_id, COUNT(SR.movie_id) AS total_movies_running
FROM SCREENS S
JOIN SCREENINGS SR ON S.screen_id = SR.screen_id
GROUP BY S.screen_id
HAVING COUNT(SR.movie_id) >= 2;
Query 5. Which customers watched movies that were not recommended for their age?
SELECT C.full_name, [Link], [Link], M.age_rating
FROM CUSTOMERS C
JOIN VIEWINGS V ON C.customer_id = V.customer_id
JOIN MOVIES M ON V.movie_id = M.movie_id
WHERE [Link] < M.age_rating;
Query 6. Which movies are shown in halls that have a seat capacity higher than the
average?
SELECT [Link], [Link]
FROM MOVIES M
JOIN SCREENINGS SR ON M.movie_id = SR.movie_id
JOIN SCREENS S ON SR.screen_id = S.screen_id
WHERE [Link] > (SELECT AVG(capacity) FROM SCREENS);
Analytical Functions
Query 1. Rank the movies depending on the year of release.
SELECT title, release_year,
RANK() OVER (ORDER BY release_year DESC) AS release_rank
FROM MOVIES;
Query 2. What is the age difference between a customer and the recommended age to
watch the respective movie?
SELECT C.full_name, [Link], [Link], M.age_rating,
(M.age_rating - [Link]) AS age_difference,
AVG(M.age_rating - [Link]) OVER () AS average_difference_global
FROM VIEWINGS V
JOIN CUSTOMERS C ON V.customer_id = C.customer_id
JOIN MOVIES M ON V.movie_id = M.movie_id;
Query 3. What is the average age of the customers who watched each specific movie?
SELECT [Link], C.full_name, [Link],
AVG([Link]) OVER (PARTITION BY M.movie_id) AS average_viewer_age_for_movie
FROM VIEWINGS V
JOIN CUSTOMERS C ON V.customer_id = C.customer_id
JOIN MOVIES M ON V.movie_id = M.movie_id;
Query 4. What is the age of each customer, listing the oldest customers first?
SELECT full_name, age,
DENSE_RANK() OVER (ORDER BY age DESC) AS age_position
FROM CUSTOMERS;
Query 5. What is the total number of seats as we move from one screen to the next,
and ordered by the floor level?
SELECT screen_id, floor, capacity,
SUM(capacity) OVER (ORDER BY floor, screen_id) AS cumulative_capacity
FROM SCREENS;
Query 6. How much larger or smaller is the capacity of the current screen compared
to the immediately preceding screen (when ordered by screen ID)?
SELECT screen_id,capacity,
LAG(capacity, 1, 0) OVER (ORDER BY screen_id) AS capacity_of_previous_screen,
(capacity - LAG(capacity, 1, 0) OVER (ORDER BY screen_id)) AS
difference_from_previous
FROM SCREENS
ORDER BY screen_id;