Difference between revisions of "Nobel Quiz"
From SQLZOO
| Line 25: | Line 25: | ||
<td>Medicine</td> | <td>Medicine</td> | ||
<td align = 'left'>Peter Medawar</td> | <td align = 'left'>Peter Medawar</td> | ||
| + | </tr> | ||
| + | <tr> | ||
| + | <td>1960</td> | ||
| + | <td>Physics</td> | ||
| + | <td align = 'left'>Donald A. Glaser</td> | ||
| + | </tr> | ||
| + | <tr> | ||
| + | <td>1960</td> | ||
| + | <td>Peace</td> | ||
| + | <td align = 'left'>Albert Lutuli</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
| Line 34: | Line 44: | ||
{Pick the code which shows the name of winner's names beginning with C and ending in n | {Pick the code which shows the name of winner's names beginning with C and ending in n | ||
|type="()"} | |type="()"} | ||
| − | - | + | - SELECT name FROM nobel WHERE winner LIKE '%C%' AND winner LIKE '%n%' |
| − | - | + | - SELECT name FROM nobel WHERE winner LIKE '%C' AND winner LIKE 'n%' |
| − | - | + | - SELECT name FROM nobel WHERE winner LIKE 'C%' AND winner LIKE '%n' |
| − | - | + | - SELECT winner FROM nobel WHERE winner LIKE '%C' AND winner LIKE 'n%' |
| − | + | + | + SELECT winner FROM nobel WHERE winner LIKE 'C%' AND winner LIKE '%n' |
{Select the code that shows how many Chemistry awards were given between 1950 and 1960 | {Select the code that shows how many Chemistry awards were given between 1950 and 1960 | ||
|type="()"} | |type="()"} | ||
| − | - | + | - SELECT COUNT(subject) FROM nobel WHERE subject = 'Chemistry' AND BETWEEN 1950 and 1960 |
| − | - | + | - SELECT COUNT(subject) FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN (1950, 1960) |
| − | + | + | + SELECT COUNT(subject) FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN 1950 and 1960 |
| − | - | + | - SELECT subject FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN 1950 and 1960 |
| − | - | + | - SELECT subject FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN (1950, 1960) |
| − | + | ||
{Pick the code that shows the amount of years where no Medicine awards were given | {Pick the code that shows the amount of years where no Medicine awards were given | ||
|type="()"} | |type="()"} | ||
| − | - | + | - SELECT COUNT(yr) FROM nobel WHERE subject NOT IN 'Medicine' |
| − | + | + | + SELECT COUNT(yr) FROM nobel WHERE subject NOT LIKE 'Medicine' |
| − | - | + | - SELECT COUNT(yr) FROM nobel WHERE subject NOT 'Medicine' |
| − | - | + | - SELECT yr FROM nobel WHERE subject <> 'Medicine' |
| − | - | + | - SELECT yr FROM nobel WHERE subject NOT LIKE 'Medicine' |
| + | |||
| + | +{Select the result that would be obtained from the following code: SELECT subject, winner FROM nobel WHERE winner LIKE 'Sir%' AND yr LIKE '196%' | ||
| + | |type="()"} | ||
| + | - Medicine, John Eccles, Medicine, Frank Macfarlane Burnet | ||
| + | - Chemistry, Sir Cyril Hinshelwood | ||
| + | + Medicine, Sir John Eccles, Medicine, Sir Frank Macfarlane Burnet | ||
| + | - Medicine, Sir John Eccles, Medicine, Sir Frank Macfarlane Burnet, Chemistry, Willard F.Libby | ||
| + | - Sir John Eccles, Sir Frank Macfarlane Burnet | ||
{Select the code which would show the year when neither a Physics or Chemistry award was given | {Select the code which would show the year when neither a Physics or Chemistry award was given | ||
|type="()"} | |type="()"} | ||
| − | - | + | - SELECT yr FROM nobel WHERE subject NOT IN(SELECT yr from nobel WHERE subject IN ('Chemistry','Physics')) |
| − | - | + | - SELECT yr FROM nobel WHERE subject NOT IN(SELECT subject from nobel WHERE subject IN ('Chemistry','Physics')) |
| − | + | + | + SELECT yr FROM nobel WHERE yr NOT IN(SELECT yr from nobel WHERE subject IN ('Chemistry','Physics')) |
| − | - | + | - SELECT yr FROM nobel WHERE yr NOT IN(SELECT subject from nobel WHERE subject IN ('Chemistry','Physics')) |
| − | - | + | - SELECT yr FROM subject WHERE yr NOT IN(SELECT yr from nobel WHERE subject IN ('Chemistry','Physics')) |
{Select the code which shows the years when a Medicine award was given but no Peace or Literature award was | {Select the code which shows the years when a Medicine award was given but no Peace or Literature award was | ||
|type="()"} | |type="()"} | ||
| − | - | + | - SELECT DISTINCT yr FROM nobel WHERE subject='Medicine' and subject NOT IN(SELECT yr from nobel WHERE subject='Literature') and yr NOT IN (SELECT yr FROM nobel WHERE subject='Peace') |
| − | - | + | - SELECT DISTINCT yr FROM nobel WHERE subject='Medicine' and yr NOT IN(SELECT yr from nobel WHERE subject='Literature' AND subject='Peace') |
| − | + | + | + SELECT DISTINCT yr FROM nobel WHERE subject='Medicine' and yr NOT IN(SELECT yr from nobel WHERE subject='Literature') and yr NOT IN (SELECT yr FROM nobel WHERE subject='Peace') |
| − | - | + | - SELECT DISTINCT yr FROM subject WHERE subject='Medicine' and yr NOT IN(SELECT yr from nobel WHERE subject='Literature' and subject='Peace') |
| − | - | + | - SELECT DISTINCT yr FROM subject WHERE subject='Medicine' and yr NOT IN(SELECT yr from nobel WHERE subject='Literature') and yr NOT IN (SELECT yr FROM nobel WHERE subject='Peace') |
| + | |||
| + | {Pick the result that would be obtained from the following code: SELECT subject, COUNT(subject) FROM nobel WHERE yr ='1960' GROUP BY subject | ||
| + | |type="()"} | ||
| + | - 1, 1, 2, 1, 1 | ||
| + | - Chemistry, 6 | ||
| + | - Chemistry, 3, Literature, 1, Medicine, 2, Peace, 0, Physics, 2 | ||
| + | + Chemistry, 1, Literature, 1, Medicine, 2, Peace, 1, Physics, 1 | ||
| + | - Chemistry, 1, Literature, 1, Peace, 1, Physics, 1 | ||
</quiz> | </quiz> | ||
[[Category:Quizzes]] | [[Category:Quizzes]] | ||
Revision as of 10:31, 6 August 2012
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 | ||
| ... | ||||
