# Nobel Quiz

[Link](https://sqlzoo.net/wiki/Nobel_Quiz)

| yr   | subject    | winner                      |
| ---- | ---------- | --------------------------- |
| 1960 | Chemistry  | Willard F. Libby            |
| 1960 | Literature | Saint-John Perse            |
| 1960 | Medicine   | Sir Frank Macfarlane Burnet |
| 1960 | Medicine   | Peter Medawar               |
| 1960 | Physics    | Donald A. Glaser            |
| 1960 | Peace      | Albert Lutuli               |
| ...  |            |                             |

1. Pick the code which shows the name of winner's names beginning with **C** and ending in **n**

```sql
SELECT winner FROM nobel
 WHERE winner LIKE 'C%' and winner LIKE '%n'
```

2. Select the code that shows how many Chemistry awards were given between 1950 and 1960

```sql
 SELECT COUNT(subject) FROM nobel
 WHERE subject = 'Chemistry'
   AND yr BETWEEN 1950 and 1960
```

3. Pick the code that shows the amount of years where no Medicine awards were given

```sql
SELECT COUNT(DISTINCT yr) FROM nobel
 WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')
```

4\. Select the result that would be obtained from the following code: 

```sql
SELECT subject, winner FROM nobel WHERE winner LIKE 'Sir%' AND yr LIKE '196%'
```

| Medicine | Sir John Eccles             |
| -------- | --------------------------- |
| Medicine | Sir Frank Macfarlane Burnet |

5. Select the code which would show the year when neither a Physics or Chemistry award was given

```sql
SELECT yr FROM nobel
 WHERE yr NOT IN (SELECT yr 
                   FROM nobel
                   WHERE subject IN ('Chemistry','Physics'))
```

6. Select the code which shows the years when a Medicine award was given but no Peace or Literature award was

```sql
SELECT DISTINCT yr FROM nobel
 WHERE subject = 'Medicine'
 AND yr NOT IN (SELECT yr FROM nobel WHERE subject = 'Peace')
 AND yr NOT IN (SELECT yr FROM nobel WHERE subject = 'Literature')
```

```sql
-- better version
SELECT DISTINCT yr 
FROM nobel 
WHERE subject = 'Medicine' 
AND yr NOT IN (SELECT yr 
                FROM nobel 
                WHERE subject IN ('Peace','Literature'))
```

7\. Pick the result that would be obtained from the following code: 

```sql
 SELECT subject, COUNT(subject) 
   FROM nobel 
  WHERE yr ='1960' 
  GROUP BY subject
```

| Chemistry  | 1 |
| ---------- | - |
| Literature | 1 |
| Medicine   | 2 |
| Peace      | 1 |
| Physics    | 1 |