Difference between revisions of "Nobel Quiz"

From SQLZOO
Jump to: navigation, search
(8 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
Nobel Quiz
 
Nobel Quiz
 +
<div class='ref_section' style='float:right'>
 +
<table class='db_ref'>
 +
<caption>nobel</caption>
 +
<th>yr</th>
 +
<th>subject</th>
 +
<th>winner</th>
 +
<tr>
 +
<td>1960</td>
 +
<td>Chemistry</td>
 +
<td align = 'left'>Willard F. Libby</td>
 +
</tr>
 +
<tr>
 +
<td>1960</td>
 +
<td>Literature</td>
 +
<td align = 'left'>Saint-John Perse</td>
 +
</tr>
 +
<tr>
 +
<td>1960</td>
 +
<td>Medicine</td>
 +
<td align = 'left'>Sir Frank Macfarlane Burnet</td>
 +
</tr>
 +
<tr>
 +
<td>1960</td>
 +
<td>Medicine</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>
 +
<td colspan='5'>...</td>
 +
</tr>
 +
</table>
 +
</div>
 
<quiz shuffle=none display=simple>
 
<quiz shuffle=none display=simple>
{Which of these 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%'
+
- <syntaxhighlight lang='sql'> SELECT name FROM nobel WHERE winner LIKE '%C%' AND winner LIKE '%n%' </syntaxhighlight>
- SELECT name FROM nobel WHERE winner LIKE '%C' AND winner LIKE 'n%'  
+
- <syntaxhighlight lang='sql'> SELECT name FROM nobel WHERE winner LIKE '%C' AND winner LIKE 'n%' </syntaxhighlight>
- SELECT name FROM nobel WHERE winner LIKE 'C%' AND winner LIKE '%n'
+
- <syntaxhighlight lang='sql'> SELECT name FROM nobel WHERE winner LIKE 'C%' AND winner LIKE '%n' </syntaxhighlight>
- SELECT winner FROM nobel WHERE winner LIKE '%C' AND winner LIKE 'n%'
+
- <syntaxhighlight lang='sql'> SELECT winner FROM nobel WHERE winner LIKE '%C' AND winner LIKE 'n%' </syntaxhighlight>
+ SELECT winner FROM nobel WHERE winner LIKE 'C%' AND winner LIKE '%n'
+
+ <syntaxhighlight lang='sql'> SELECT winner FROM nobel WHERE winner LIKE 'C%' AND winner LIKE '%n' </syntaxhighlight>
  
{Which of these 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
+
- <syntaxhighlight lang='sql'> SELECT COUNT(subject) FROM nobel WHERE subject = 'Chemistry' AND BETWEEN 1950 and 1960 </syntaxhighlight>
- SELECT COUNT(subject) FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN (1950, 1960)
+
- <syntaxhighlight lang='sql'> SELECT COUNT(subject) FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN (1950, 1960) </syntaxhighlight>
+ SELECT COUNT(subject) FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN 1950 and 1960
+
+ <syntaxhighlight lang='sql'> SELECT COUNT(subject) FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN 1950 and 1960 </syntaxhighlight>
- SELECT subject FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN 1950 and 1960
+
- <syntaxhighlight lang='sql'> SELECT subject FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN 1950 and 1960 </syntaxhighlight>
- SELECT subject FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN (1950, 1960)
+
- <syntaxhighlight lang='sql'> SELECT subject FROM nobel WHERE subject = 'Chemistry' AND yr BETWEEN (1950, 1960) </syntaxhighlight>
  
 +
{Pick the code that shows the amount of years where no Medicine awards were given
 +
|type="()"}
 +
- <syntaxhighlight lang='sql'> SELECT COUNT(DISTINCT yr) FROM nobel WHERE yr IN (SELECT DISTINCT yr FROM nobel WHERE subject <> 'Medicine') </syntaxhighlight>
 +
+ <syntaxhighlight lang='sql'> SELECT COUNT(DISTINCT yr) FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine') </syntaxhighlight>
 +
- <syntaxhighlight lang='sql'> SELECT DISTINCT yr FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject LIKE 'Medicine') </syntaxhighlight>
 +
- <syntaxhighlight lang='sql'> SELECT COUNT(DISTINCT yr) FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject NOT LIKE 'Medicine') </syntaxhighlight>
 +
- <syntaxhighlight lang='sql'> SELECT COUNT(yr) FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine') </syntaxhighlight>
 +
 +
{Select the result that would be obtained from the following code:
 +
<syntaxhighlight lang='sql'>>SELECT subject, winner FROM nobel WHERE winner LIKE 'Sir%' AND yr LIKE '196%'</syntaxhighlight>
 +
<table><caption>Table-A</caption><tr><td>Medicine</td><td>John Eccles</td></tr><tr><td>Medicine</td><td>Frank Macfarlane Burnet</td></tr></table>
 +
<table><caption>Table-B</caption><tr><td>Chemistry</td><td>Sir Cyril Hinshelwood</td></tr></table>
 +
<table><caption>Table-C</caption><tr><td>Medicine</td><td>Sir John Eccles</td></tr><tr><td>Medicine</td><td>Sir Frank Macfarlane Burnet</td></tr></table>
 +
<table><caption>Table-D</caption><tr><td>Medicine</td><td>John Eccles</td></tr><tr><td>Medicine</td><td>Frank Macfarlane Burnet</td></tr><tr><td>Chemistry</td><td>Willard F.Libby</td></tr></table>
 +
<table><caption>Table-E</caption><tr><td>Sir John Eccles</td></tr><tr><td>Sir Frank Macfarlane Burnet</td></tr></table>
 +
|type="()"}
 +
- Table-A
 +
- Table-B
 +
+ Table-C
 +
- Table-D
 +
- Table-E
  
{Which of these shows the amount of years where no Medicine awards were given?
+
{Select the code which would show the year when neither a Physics or Chemistry award was given
 
|type="()"}
 
|type="()"}
- SELECT COUNT(yr) FROM nobel WHERE subject NOT IN 'Medicine'
+
- <syntaxhighlight lang='sql'> SELECT yr FROM nobel WHERE subject NOT IN(SELECT yr from nobel WHERE subject IN ('Chemistry','Physics')) </syntaxhighlight>
+ SELECT COUNT(yr) FROM nobel WHERE subject NOT LIKE 'Medicine'
+
- <syntaxhighlight lang='sql'> SELECT yr FROM nobel WHERE subject NOT IN(SELECT subject from nobel WHERE subject IN ('Chemistry','Physics')) </syntaxhighlight>
- SELECT COUNT(yr) FROM nobel WHERE subject NOT 'Medicine'
+
+ <syntaxhighlight lang='sql'> SELECT yr FROM nobel WHERE yr NOT IN(SELECT yr from nobel WHERE subject IN ('Chemistry','Physics')) </syntaxhighlight>
- SELECT yr FROM nobel WHERE subject <> 'Medicine'
+
- <syntaxhighlight lang='sql'> SELECT yr FROM nobel WHERE yr NOT IN(SELECT subject from nobel WHERE subject IN ('Chemistry','Physics')) </syntaxhighlight>
- SELECT yr FROM nobel WHERE subject NOT LIKE 'Medicine'
+
- <syntaxhighlight lang='sql'> SELECT yr FROM subject WHERE yr NOT IN(SELECT yr from nobel WHERE subject IN ('Chemistry','Physics')) </syntaxhighlight>
  
{Which of these would show the year when neither a Physics or Chemistry award was given?
+
{Select the code which shows the years when a Medicine award was given but no Peace or Literature award was
 
|type="()"}
 
|type="()"}
- SELECT yr FROM nobel WHERE subject NOT IN(SELECT yr from nobel WHERE subject IN ('Chemistry','Physics'))
+
- <syntaxhighlight lang='sql'> 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') </syntaxhighlight>
- SELECT yr FROM nobel WHERE subject NOT IN(SELECT subject from nobel WHERE subject IN ('Chemistry','Physics'))
+
- <syntaxhighlight lang='sql'> SELECT DISTINCT yr FROM nobel WHERE subject='Medicine' and yr NOT IN(SELECT yr from nobel WHERE subject='Literature' AND subject='Peace') </syntaxhighlight>
+ SELECT yr FROM nobel WHERE yr NOT IN(SELECT yr from nobel WHERE subject IN ('Chemistry','Physics'))
+
+ <syntaxhighlight lang='sql'> 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') </syntaxhighlight>
- SELECT yr FROM nobel WHERE yr NOT IN(SELECT subject from nobel WHERE subject IN ('Chemistry','Physics'))
+
- <syntaxhighlight lang='sql'> SELECT DISTINCT yr FROM subject WHERE subject='Medicine' and yr NOT IN(SELECT yr from nobel WHERE subject='Literature' and subject='Peace') </syntaxhighlight>
- SELECT yr FROM subject WHERE yr NOT IN(SELECT yr from nobel WHERE subject IN ('Chemistry','Physics'))
+
- <syntaxhighlight lang='sql'> 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') </syntaxhighlight>
  
{Which of these shows the years when a Medicine award was given but no Peace or Literature award was?
+
{Pick the result that would be obtained from the following code:
 +
<syntaxhighlight lang='sql'>
 +
SELECT subject, COUNT(subject)
 +
  FROM nobel
 +
  WHERE yr ='1960'
 +
  GROUP BY subject
 +
</syntaxhighlight>
 +
<table><caption>Table-A</caption><tr><td>1</td></tr><tr><td>1</td></tr><tr><td>2</td></tr><tr><td>1</td></tr><tr><td>1</td></tr></table>
 +
<table><caption>Table-B</caption><tr><td>Chemistry</td><td>6</td></tr></table>
 +
<table><caption>Table-C</caption><tr><td>Chemistry</td><td>3</td></tr><tr><td>Literature</td><td>1</td></tr><tr><td>Medicine</td><td>2</td></tr><tr><td>Peace</td><td>0</td></tr><tr><td>Physics</td><td>2</td></tr></table>
 +
<table><caption>Table-D</caption><tr><td>Chemistry</td><td>1</td></tr><tr><td>Literature</td><td>1</td></tr><tr><td>Medicine</td><td>2</td></tr><tr><td>Peace</td><td>1</td></tr><tr><td>Physics</td><td>1</td></tr></table>
 +
<table><caption>Table-E</caption><tr><td>Chemistry</td><td>1</td></tr><tr><td>Literature</td><td>1</td></tr><tr><td>Peace</td><td>1</td></tr><tr><td>Physics</td><td>1</td></tr></table>
 
|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')
+
- Table-A
- SELECT DISTINCT yr FROM nobel WHERE subject='Medicine' and yr NOT IN(SELECT yr from nobel WHERE subject='Literature' AND subject='Peace')
+
- Table-B
+ 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')
+
- Table-C
- SELECT DISTINCT yr FROM subject WHERE subject='Medicine' and yr NOT IN(SELECT yr from nobel WHERE subject='Literature' and subject='Peace')
+
+ Table-D
- 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')
+
- Table-E
 
</quiz>
 
</quiz>
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Revision as of 10:42, 12 July 2013

Nobel Quiz

nobel
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
...

<quiz shuffle=none display=simple> {Pick the code which shows the name of winner's names beginning with C and ending in n |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 |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 |type="()"}

-
 SELECT COUNT(DISTINCT yr) FROM nobel WHERE yr IN (SELECT DISTINCT yr FROM nobel WHERE subject <> 'Medicine')
+
 SELECT COUNT(DISTINCT yr) FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')
-
 SELECT DISTINCT yr FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject LIKE 'Medicine')
-
 SELECT COUNT(DISTINCT yr) FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject NOT LIKE 'Medicine')
-
 SELECT COUNT(yr) FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = '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%'
Table-A
MedicineJohn Eccles
MedicineFrank Macfarlane Burnet
Table-B
ChemistrySir Cyril Hinshelwood
Table-C
MedicineSir John Eccles
MedicineSir Frank Macfarlane Burnet
Table-D
MedicineJohn Eccles
MedicineFrank Macfarlane Burnet
ChemistryWillard F.Libby
Table-E
Sir John Eccles
Sir Frank Macfarlane Burnet

|type="()"} - Table-A - Table-B + Table-C - Table-D - Table-E

{Select the code which would show the year when neither a Physics or Chemistry award was given |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 |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
Table-A
1
1
2
1
1
Table-B
Chemistry6
Table-C
Chemistry3
Literature1
Medicine2
Peace0
Physics2
Table-D
Chemistry1
Literature1
Medicine2
Peace1
Physics1
Table-E
Chemistry1
Literature1
Peace1
Physics1

|type="()"} - Table-A - Table-B - Table-C + Table-D - Table-E </quiz>