Bricec at 14:37, 20 December 2012

2012-12-20T14:37:44Z

Bricec: Created page with "

nobel
yr subject winner
1960 Chemistry
2012-12-20T14:26:31Z

Created page with " <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</t..."

New page

<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 colspan='5'>...</td>
</tr>
</table>
</div>
<h2><code>nobel</code> Nobel Laureates</h2>
<p>We continue practicing simple SQL queries on a single table.</p>
<p>This tutorial is concerned with a table of Nobel prize winners:
</p>
<pre style='width:20em;'>nobel(yr, subject, winner)</pre>

<div class="progress_panel"><div>
<div class="summary">Summary</div>
<div class="progressbarbg">
<div class="progressbar"></div>
</div>
</div></div>

<h2>Exercises</h2>
<p>Using the <code>SELECT</code> statement.</p>
<div class='qu'>
Change the query shown so that it displays Nobel prizes for 1950.
<source lang='sql' class='def'>
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1960
</source>

<source lang='sql' class='ans'>
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950
</source>
</div>

<div class='qu'>
Show who won the 1962 prize for Literature.
<source lang='sql' class='def'>
SELECT winner
FROM nobel
WHERE yr = 1960
AND subject = 'Physics'
</source>

<source lang='sql' class='ans'>
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'
</source>
</div>

<div class='qu'>
Show the year and subject that won 'Albert Einstein' his prize.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'
</source>
</div>

<div class='qu'>
Give the name of the 'Peace' winners since the year 2000, including 2000.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT winner
FROM nobel
WHERE subject = 'Peace'
AND yr >= 2000
</source>
</div>

<div class='qu'>
Show all details ('''yr''', '''subject''', '''winner''') of the Literature prize winners for 1980 to 1989 inclusive.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT yr,subject,winner
FROM nobel
WHERE subject = 'Literature'
AND yr BETWEEN 1980 AND 1989
</source>
</div>

<div class='qu'>
Show all details of the presidential winners: ('Theodore Roosevelt', 'Woodrow Wilson', 'Jed Bartlet', 'Jimmy Carter')
<source lang='sql' class='def'>
SELECT * FROM nobel
WHERE yr = 1970
AND subject IN ('Cookery',
'Chemistry',
'Literature')
</source>

<source lang='sql' class='ans'>
SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt',
'Woodrow Wilson',
'Jed Bartlet',
'Jimmy Carter')
</source>
</div>

<div class='qu'>
Show the winners with first name John
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT winner FROM nobel
WHERE winner LIKE 'John %'
</source>
</div>

<div class='qu'>
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).
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT DISTINCT yr
FROM nobel
WHERE subject='Physics' and yr NOT IN
(SELECT yr from nobel
WHERE subject='Chemistry')
</source>
</div>

<div>
<div class="lsclear">Clear your results</div>
<p><div class="quizlink">[[Nobel Quiz]]</div></p>
</div>

← Older revision		Revision as of 14:37, 20 December 2012
Line 1:		Line 1:
		+	[[Main_Page/fr\|Retour à la page principale]]

	<div class='ref_section' style='float:right'>		<div class='ref_section' style='float:right'>