Advanced SQL Query: Finding Movies with at Least 2 Screenings in Each Screening Room

In this article, we’ll explore the concept of subqueries and how to use them to solve complex problems in SQL. We’ll break down the provided example and provide a step-by-step explanation of how to implement a query that finds movies shown at least two times in each screening room.

Understanding Subqueries

A subquery is a query nested inside another query. It’s used to retrieve data from one or more tables based on conditions specified in another query. Subqueries can be used in various contexts, including NOT EXISTS, IN, and HAVING.

In the provided example, we’re using a subquery to check if a movie has been screened at least twice in each screening room.

Breaking Down the Original Query

The original query is as follows:

SELECT * FROM movie
WHERE NOT EXISTS (
    SELECT * FROM ScreeningRoom 
    WHERE NOT EXISTS (
        SELECT * FROM Screening
        WHERE movies.movie_id=screeing.Movie_fid
        AND ScreeningRoom.ScreeningRoom_Number=Screening.Screening_id
    )
)
GROUP BY movie.title;

Let’s analyze each part of the query:

1. SELECT * FROM movie: We’re selecting all columns (*) from the movie table.
2. WHERE NOT EXISTS ...: This clause is used to check if a row in the outer query does not exist in the subquery.
3. The first subquery: SELECT * FROM ScreeningRoom WHERE NOT EXISTS ( SELECT * FROM Screening ...). We’re selecting all columns (*) from the ScreeningRoom table where there exists no row in the inner subquery that meets the conditions.
4. The inner subquery: SELECT * FROM Screening WHERE movies.movie_id=screeing.Movie_fid AND ScreeningRoom.ScreeningRoom_Number=Screening.Screening_id. We’re selecting all columns (*) from the Screening table where:
   • movies.movie_id matches the Movie_fid column in the same row of the Screening.
   • ScreeningRoom.ScreeningRoom_Number matches the Screening_id column in the same row of the Screening.

Modifying the Query to Find Movies with at Least 2 Screenings

To find movies that have been screened at least twice in each screening room, we need to modify the query to use a HAVING clause.

The modified query is as follows:

SELECT * FROM movie
WHERE NOT EXISTS (
    SELECT * FROM ScreeningRoom 
    WHERE NOT EXISTS (
        SELECT * FROM Screening
        WHERE movies.movie_id=screeing.Movie_fid
        AND ScreeningRoom.ScreeningRoom_Number=Screening.Screening_id
        HAVING COUNT(Screening.Screening_id) > 1
    )
)
GROUP BY movie.title;

Let’s analyze the changes:

• We added a HAVING clause to the inner subquery: COUNT(Screening.Screening_id) > 1. This clause counts the number of rows in the Screening table that meet the conditions and returns only those rows where the count is greater than 1.
• The outer query remains unchanged.

How it Works

When we execute the modified query, here’s what happens:

1. For each movie in the movie table, we check if there exists a row in the ScreeningRoom table that does not exist in the Screening table.
2. If such a row exists, we then check if there exists a row in the Screening table where:
   • The Movie_fid column matches the movie_id column of the same movie.
   • The ScreeningRoom.ScreeningRoom_Number column matches the Screening_id column of the same screening.
3. If such a row exists, we count the number of rows in the Screening table that meet these conditions.
4. If the count is greater than 1, it means that the movie has been screened at least twice in this screening room.
5. The outer query returns only those movies where no such screening room with multiple screenings exists.

Conclusion

In this article, we explored how to use subqueries and HAVING clauses to solve complex problems in SQL. We analyzed a provided example and modified the query to find movies that have been screened at least twice in each screening room. By understanding how these concepts work together, you can tackle more advanced SQL queries and become a proficient database administrator.

Additional Example: Finding Screening Rooms with Multiple Screenings

To further illustrate the concept, let’s consider an additional example:

SELECT * FROM ScreeningRoom
WHERE COUNT(Screening.Screening_id) > 1;

This query finds all screening rooms where at least one movie has been screened more than once. We can use a similar approach to modify this query to find movies that have been screened in multiple screening rooms.

Additional Example: Finding Movies with Multiple Screenings Across All Screening Rooms

To take it a step further, we can modify the original query to find all movies that have been screened at least twice across all screening rooms:

SELECT * FROM movie
WHERE NOT EXISTS (
    SELECT * FROM ScreeningRoom 
    WHERE NOT EXISTS (
        SELECT * FROM Screening
        WHERE movies.movie_id=screeing.Movie_fid
        AND ScreeningRoom.ScreeningRoom_Number=Screening.Screening_id
        HAVING COUNT(Screening.Screening_id) > 1
    )
)
GROUP BY movie.title
HAVING COUNT(DISTINCT Movie_id) > 1;

In this modified query, we added an additional HAVING clause to the outer query: COUNT(DISTINCT Movie_id) > 1. This clause counts the number of distinct Movie_id values in the outer query and returns only those rows where the count is greater than 1.

Last modified on 2023-11-18