Difference between revisions of "SELECT from Nobel Tutorial"

From SQLZOO
Jump to: navigation, search
 
(11 intermediate revisions by 3 users not shown)
Line 37: Line 37:
 
   <pre style='width:20em;'>nobel(yr, subject, winner)</pre>
 
   <pre style='width:20em;'>nobel(yr, subject, winner)</pre>
  
<div class="summary">Summary</div>
+
<div class="progress_panel"><div>
 
+
  <div class="summary">Summary</div>
<div class="progressbarbg">
+
  <div class="progressbarbg">
  <div class="progressbar"></div>
+
      <div class="progressbar"></div>
</div>
+
  </div>
 +
</div></div>
  
 
<h2>Exercises</h2>
 
<h2>Exercises</h2>
Line 160: Line 161:
 
</div>
 
</div>
  
[[Nobel Quiz]]
+
<div>
 +
<div class="lsclear">Clear your results</div>
 +
<p><div class="quizlink">[[Nobel Quiz]]</div></p>
 +
</div>

Latest revision as of 11:29, 14 February 2014

nobel
yr subject winner
1960 Chemistry Willard F. Libby
1960 Literature Saint-John Perse
1960 Medicine Sir Frank Macfarlane Burnet
1960 Medicine Peter Medawar
...

nobel Nobel Laureates

We continue practicing simple SQL queries on a single table.

This tutorial is concerned with a table of Nobel prize winners:

nobel(yr, subject, winner)
Summary

Exercises

Using the SELECT statement.

Change the query shown so that it displays Nobel prizes for 1950.

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1960
SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

Show who won the 1962 prize for Literature.

SELECT winner
  FROM nobel
 WHERE yr = 1960
   AND subject = 'Physics'
SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'

Show the year and subject that won 'Albert Einstein' his prize.

 
SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'


Give the name of the 'Peace' winners since the year 2000, including 2000.

 
SELECT winner
FROM nobel
WHERE subject = 'Peace'
AND yr >= 2000

Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.

 
SELECT yr,subject,winner
FROM nobel
WHERE subject = 'Literature'
AND yr BETWEEN 1980 AND 1989

Show all details of the presidential winners: ('Theodore Roosevelt', 'Woodrow Wilson', 'Jed Bartlet', 'Jimmy Carter')

SELECT * FROM nobel
 WHERE yr = 1970
  AND subject IN ('Cookery',
                  'Chemistry',
                  'Literature')
SELECT * FROM nobel
 WHERE winner IN ('Theodore Roosevelt', 
                  'Woodrow Wilson', 
                  'Jed Bartlet', 
                  'Jimmy Carter')

Show the winners with first name John

 
SELECT winner FROM nobel
  WHERE winner LIKE 'John %'

In which years was the Physics prize awarded but no Chemistry prize. (WARNING - this question is way too hard for this level, you will need to use sub queries or joins).

 
SELECT DISTINCT yr 
FROM nobel 
WHERE subject='Physics' AND yr NOT IN
(SELECT yr FROM nobel 
WHERE subject='Chemistry')
Clear your results

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense