# Oscar Nominees Table

Table: **oscar\_nominees**

**Schema:**

|          |         |
| -------- | ------- |
| year     | int     |
| category | varchar |
| nominee  | varchar |
| movie    | varchar |
| winner   | bool    |
| id       | int     |

**Preview:**

<table><thead><tr><th>year</th><th width="158">category</th><th>nominee</th><th width="169">movie</th><th>winner</th><th>id</th></tr></thead><tbody><tr><td>2010</td><td>actor in a leading role</td><td>James Franco</td><td>127 Hours</td><td>FALSE</td><td>648</td></tr><tr><td>2003</td><td>actor in a supporting role</td><td>Benicio Del Toro</td><td>21 Grams</td><td>FALSE</td><td>139</td></tr><tr><td>2003</td><td>actress in a leading role</td><td>Naomi Watts</td><td>21 Grams</td><td>FALSE</td><td>1119</td></tr><tr><td>2001</td><td>actress in a supporting role</td><td>Jennifer Connelly</td><td>A Beautiful Mind</td><td>TRUE</td><td>710</td></tr><tr><td>2001</td><td>actor in a leading role</td><td>Russell Crowe</td><td>A Beautiful Mind</td><td>FALSE</td><td>1315</td></tr></tbody></table>

## MEDIUM

## Find the nominee who has won the most Oscars

Find the nominee who has won the most Oscars. Output the nominee's name alongside the result.

{% embed url="<https://platform.stratascratch.com/coding/9750-find-the-nominee-who-has-won-the-most-oscars?code_type=1>" %}

```sql
SELECT nominee, count(id) AS oscar_won
FROM oscar_nominees
WHERE winner = TRUE
GROUP BY  1
HAVING count(id) = (SELECT COUNT(ID) 
                FROM oscar_nominees
                WHERE winner = TRUE 
                GROUP BY nominee 
                ORDER BY COUNT(ID) DESC 
                LIMIT 1) -- to include all winners 
ORDER BY count(id) DESC;
```

## HARD

## Find the genre of the person with the most number of oscar winnings

Find the genre of the person with the most number of oscar winnings. If there are more than one person with the same number of oscar wins, return the first one in alphabetic order based on their name. Use the names as keys when joining the tables.

{% embed url="<https://platform.stratascratch.com/coding/10171-find-the-genre-of-the-person-with-the-most-number-of-oscar-winnings?code_type=1>" %}

```sql
SELECT t1.*, n.genre
SELECT o.nominee, count(o.id) AS oscar_won
FROM oscar_nominees o
WHERE winner = TRUE
GROUP BY 1
ORDER BY count(id) DESC, nominee
LIMIT 1) t1
LEFT JOIN nominee_information n
ON t1.id = n.id
;

```