Cinema Database Analytics — Mariano Cánovas

About this project

A cinema reservation database with eight tables and over 235 rows of data. The 22 queries below explore tickets, revenue, screening hours and voucher use through joins, aggregations and subqueries.

MySQL 8 tables · 235+ rows 22 queries JOIN · GROUP BY · subqueries

Schema

Eight tables linked by foreign keys. Tickets connect customers to screenings, screenings link movies to rooms, and the Cast table joins movies with their actors.

Customer

IdINT

NameTEXT

CityTEXT

Actor

IdINT

NameTEXT

Movie

IdINT

NameTEXT

ReleaseDATE

DurationINT

Cast

IdINT

MovieIdFK

ActorIdFK

Room

IdINT

SystemTEXT

Screening

IdINT

MovieIdFK

RoomIdFK

DateDATETIME

PriceDECIMAL

Voucher

IdINT

DiscountDECIMAL

Ticket

IdINT

CustomerIdFK

ScreeningIdFK

VoucherIdFK

AmountINT

Playground

Write any SQL query and run it against the full database, which is loaded locally in your browser. Use Cmd/Ctrl + Enter to execute.

Loading database…

Run a query to see results.

The 22 queries

Every query from the original project, with a short description. Expand any one to view the SQL, then click Run in playground to send it to the editor and execute it.

SELECT
    Name,
    Duration
FROM
    Movie
ORDER BY
    Name;

SELECT
    ROUND(AVG(Duration) / 60, 2) AS Duration
FROM
    Movie;

SELECT
    City,
    COUNT(City) AS Customers
FROM
    Customer
GROUP BY
    City
ORDER BY
    City;

SELECT
    COUNT(Id) AS Movies
FROM
    Screening
WHERE
    YEAR(Date) BETWEEN 2020 AND 2021;

SELECT
    SUM(Amount) AS Tickets
FROM
    Customer
    INNER JOIN Ticket ON Customer.Id = Ticket.CustomerId
WHERE
    Name = 'Jonas Hicks';

SELECT
    City,
    SUM(Amount) AS Tickets
FROM
    Customer
    INNER JOIN Ticket ON Customer.Id = Ticket.CustomerId
GROUP BY
    City
ORDER BY
    Tickets DESC;

SELECT
    Name,
    SUM(Amount) AS Tickets
FROM
    Ticket
    INNER JOIN Screening ON Ticket.ScreeningId = Screening.Id
    INNER JOIN Movie ON Screening.MovieId = Movie.Id
GROUP BY
    Name
ORDER BY
    Tickets DESC;

SELECT
    City,
    SUM(Amount) AS Tickets
FROM
    Customer
    INNER JOIN Ticket ON Customer.Id = Ticket.CustomerId
    INNER JOIN Screening ON Ticket.ScreeningId = Screening.Id
    INNER JOIN Movie ON Screening.MovieId = Movie.Id
WHERE
    Movie.Name = 'Licorice Pizza'
GROUP BY
    City
ORDER BY
    Tickets;

SELECT
    `System`,
    COUNT(`System`) AS Screenings
FROM
    Room
    INNER JOIN Screening ON Room.Id = Screening.RoomId
GROUP BY
    `System`
ORDER BY
    `System`;

SELECT
    `System`,
    COUNT(`System`) as Screenings
FROM
    Room
    INNER JOIN Screening ON Room.Id = Screening.RoomId
    INNER JOIN Movie ON Screening.MovieId = Movie.Id
WHERE
    Name = 'Windows'
GROUP BY
    `System`
ORDER BY
    Screenings;

SELECT
    YEAR(MIN(Date)) AS Year,
    MONTH(MIN(Date)) AS Month,
    DAY(MIN(Date)) AS Day
FROM
    Screening
    INNER JOIN Movie on Screening.MovieId = Movie.Id
WHERE
    Name = 'Replicas';

SELECT
    YEAR(Date),
    SUM(Amount) AS Tickets
FROM
    Screening
    INNER JOIN Ticket ON Screening.Id = Ticket.ScreeningId
GROUP BY
    YEAR(Date)
ORDER BY
    YEAR(Date);

SELECT
    SUM(Amount) AS Tickets
FROM
    Ticket
    INNER JOIN Screening ON Ticket.ScreeningId = Screening.Id
    INNER JOIN Movie ON Screening.MovieId = Movie.Id
    INNER JOIN Cast ON Movie.Id = Cast.MovieId
    INNER JOIN Actor ON Cast.ActorId = Actor.Id
WHERE
    Actor.Name = 'Keanu Reeves';

SELECT
    RoomId AS Room,
    COUNT(DISTINCT(MovieId)) AS Movies
FROM
    Screening
    INNER JOIN Room ON Screening.RoomId = Room.Id
GROUP BY
    RoomId
ORDER BY
    RoomId;

SELECT
    Movie.Name,
    SUM(Amount) AS Tickets
FROM
    Ticket
    INNER JOIN Screening ON Ticket.ScreeningId = Screening.Id
    INNER JOIN Movie ON Screening.MovieId = Movie.Id
WHERE Movie.Id IN (
    SELECT
        Movie.Id
    FROM
        Movie
        INNER JOIN Cast ON Movie.Id = Cast.MovieId
        INNER JOIN Actor on Cast.ActorId = Actor.Id
    WHERE
        Actor.Name = 'Sean Penn')
GROUP BY
    Movie.Name
ORDER BY
    Movie.Name;

SELECT
    Name,
    DATEDIFF(MIN(Date), `Release`) AS Difference
FROM
    Movie
    INNER JOIN Screening ON Movie.Id = Screening.MovieId
GROUP BY
    Movie.Id
ORDER BY
    Name;

SELECT
    Name,
    ROUND(COUNT(Screening.Id) * Duration / 60.0, 2) AS Hours
FROM
    Screening
    INNER JOIN Movie ON Screening.MovieId = Movie.Id
GROUP BY
    MovieId
ORDER BY
    Hours;

SELECT
    Name,
    ROUND(COUNT(IF(`System` = 'Dolby Cinema', Screening.Id, NULL)) * Duration / 60.0, 2) AS `Dolby Cinema`,
    ROUND(COUNT(IF(`System` = 'IMAX', Screening.Id, NULL)) * Duration / 60.0, 2) AS `IMAX`
FROM
    Screening
    INNER JOIN Movie ON Screening.MovieId = Movie.Id
    INNER JOIN Room ON Screening.RoomId = Room.Id
GROUP BY
    MovieId
ORDER BY
    Name;

SELECT
    ROUND(COUNT(Ticket.VoucherId) * 1.0 / COUNT(Voucher.Id), 2) AS Redeemed
FROM
    Ticket
    RIGHT JOIN Voucher ON Ticket.VoucherId = Voucher.Id;

SELECT
    YEAR(Screening.Date) AS Year,
    ROUND(SUM(Ticket.Amount * Screening.Price * Voucher.Discount), 2) AS Discount
FROM
    Ticket
    INNER JOIN Screening ON Ticket.ScreeningId = Screening.Id
    INNER JOIN Voucher ON Ticket.VoucherId = Voucher.Id
GROUP BY
    YEAR(Screening.Date);

SELECT
    YEAR(Date) AS Year,
    ROUND(SUM(Amount * Price * (1 - IF(Discount IS NULL, 0, Discount))), 2) AS Revenue
FROM
    Ticket
    INNER JOIN Screening ON Ticket.ScreeningId = Screening.Id
    LEFT JOIN Voucher ON Ticket.VoucherId = Voucher.Id
GROUP BY
    YEAR(Date)
ORDER BY
    YEAR(Date);

SELECT
    Year,
    SUM(Regular) AS Count
FROM
    (SELECT
        YEAR(Date) AS Year,
        SUM(Amount) >= 4 AS Regular
    FROM
        Ticket
        INNER JOIN Screening ON Ticket.ScreeningId = Screening.Id
    GROUP BY
        YEAR(Date),
        CustomerId) AS Temp
GROUP BY
    Year
ORDER BY
    Year;