Oscar-winning actors' nominations & movies.

Here is a list of 15 Oscar-winning actors and actresses, ranked by the number of nominations they've received (as of my last training data in September 2021). Please note that the list could change after 2021 as more award ceremonies are held and new nominations and wins are given out.

The list is not exhaustive, and the number of nominations is only accurate up to September 2021. Some actors and actresses may have films that won Best Picture but are not included in the "Movies That Won Best Picture" column due to the limitation of listing a maximum of three movies.

Certainly! I'll provide you with a sample SQL code based on hypothetical table structures.

Let's assume the tables have the following columns:

1. actors:
   • actor_id (primary key)
   • actor_name
2. nominations:
   • nomination_id (primary key)
   • actor_id (foreign key referencing actors.actor_id)
3. awards:
   • award_id (primary key)
   • movie_id (foreign key referencing movies.movie_id)
4. movies:
   • movie_id (primary key)
   • movie_name
   • best_picture (Boolean flag indicating if the movie won Best Picture)

Here's the SQL code to generate the report:

sqlCopy code
WITH RankedNominations AS (
    SELECT
        a.actor_name,
        COUNT(n.nomination_id) AS total_nominations,
        STRING_AGG(m.movie_name, ', ') AS best_picture_movies
    FROM 
        actors a
    JOIN
        nominations n ON a.actor_id = n.actor_id
    LEFT JOIN 
        movies m ON m.best_picture = TRUE AND m.movie_id IN (
            SELECT award_id FROM awards WHERE movie_id = m.movie_id
        )
    GROUP BY
        a.actor_name
    ORDER BY
        total_nominations DESC, a.actor_name
    LIMIT 15
)

SELECT
    ROW_NUMBER() OVER(ORDER BY total_nominations DESC) AS rank,
    actor_name,
    total_nominations,
    best_picture_movies
FROM 
    RankedNominations;

This code will first aggregate the nominations and Best Picture movies for each actor in a CTE (Common Table Expression) named RankedNominations. Then, it uses the ROW_NUMBER() function to assign ranks and selects the top 15 actors by total nominations.

You might need to adjust the table or column names based on your actual database schema.