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.
|Rank||Actor/Actress||Number of Nominations||Movies That Won Best Picture|
|1||Meryl Streep||21||"The Deer Hunter", "Kramer vs. Kramer", "Out of Africa"|
|2||Katharine Hepburn||12||"On Golden Pond"|
|3||Jack Nicholson||12||"One Flew Over the Cuckoo's Nest", "Terms of Endearment", "The Departed"|
|6||Denzel Washington||8||"Glory", "Training Day"|
|7||Marlon Brando||8||"On the Waterfront", "The Godfather"|
|8||Paul Newman||8||"The Color of Money"|
|10||Spencer Tracy||9||"It Happened One Night", "You Can't Take It with You"|
|11||Al Pacino||9||"The Godfather", "The Godfather Part II", "Scent of a Woman"|
|12||Cate Blanchett||7||"The Lord of the Rings: The Return of the King"|
|13||Robert De Niro||7||"The Godfather Part II", "Raging Bull"|
|15||Daniel Day-Lewis||6||"My Left Foot", "There Will Be Blood", "Lincoln"|
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:
- actor_id (primary key)
- nomination_id (primary key)
- actor_id (foreign key referencing actors.actor_id)
- award_id (primary key)
- movie_id (foreign key referencing movies.movie_id)
- movie_id (primary key)
- best_picture (Boolean flag indicating if the movie won Best Picture)
Here's the SQL code to generate the report:
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.