Connor at 10:33, 11 July 2012

2012-07-11T10:33:33Z

Connor: Created page with "

Nobel Prizes: Aggregate functions

This tutorial concerns aggregate functions such as COUNT, SUM and AVG.

Exer..."

2012-07-11T09:45:47Z

Created page with " <h3>Nobel Prizes: Aggregate functions</h3> <p>This tutorial concerns aggregate functions such as <code>COUNT</code>, <code>SUM</code> and <code>AVG</code>.</p> <h2>Exer..."

New page

<h3>Nobel Prizes: Aggregate functions</h3>
<p>This tutorial concerns aggregate functions such as <code>COUNT</code>,
<code>SUM</code> and <code>AVG</code>.</p>
<h2>Exercises</h2>
<p>
Using [http://sqlzoo.net/w/index.php/Using_SUM,_Count,_MAX,_DISTINCT_and_ORDER_BY SUM, COUNT, MAX, AVG, DISTINCT and ORDER BY.]

<div class='qu'>
Show the total number of prizes awarded.
<source lang='sql' class='def'>
SELECT COUNT(winner) FROM nobel
</source>

<source lang='sql' class='ans'>
SELECT COUNT(winner) FROM nobel
</source>
</div>

<div class='qu'>
List each subject - just once
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT DISTINCT subject FROM nobel
</source>
</div>

<div class='qu'>
Show the total number of prizes awarded for Physics.
nobel('''yr, subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT COUNT(subject) FROM nobel
WHERE subject='Physics'
</source>
</div>

Using [http://sqlzoo.net/w/index.php/Using_GROUP_BY_and_HAVING. GROUP BY and HAVING.]
<div class='qu'>
For each subject show the subject and the number of prizes.
nobel('''yr,subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT subject, COUNT(winner)
FROM nobel
GROUP BY subject
</source>
</div>

<div class='qu'>
For each subject show the first year that the prize was awarded.
nobel('''yr, subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT subject, MIN(yr)
FROM nobel
GROUP BY subject
</source>
</div>

<div class='qu'>
For each subject show the number of prizes awarded in the year 2000.
nobel('''yr, subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT subject,COUNT(yr)
FROM nobel
WHERE yr=2000
GROUP BY subject
</source>
</div>

Look into [[aggregates with DISTINCT]].
<div class='qu'>
Show the number of different winners for each subject.
nobel('''yr, subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT subject,COUNT(DISTINCT winner)
FROM nobel
GROUP BY subject
</source>
</div>

<div class='qu'>
For each subject show how many years have had prizes awarded.
nobel('''yr, subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT subject,COUNT(DISTINCT yr)
FROM nobel
GROUP BY subject
</source>
</div>

Using HAVING.
<div class='qu'>
Show the years in which three prizes were given for Physics.
nobel('''yr, subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT yr
FROM nobel
WHERE subject='Physics'
GROUP BY yr
HAVING COUNT(winner)=3
</source>
</div>

<div class='qu'>
Show winners who have won more than once.
nobel('''yr, subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(yr) > 1
</source>
</div>

<div class='qu'>
Show winners who have won more than one subject.
nobel('''yr, subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject)>1
</source>
</div>

<p>GROUP BY yr, subject</p>
<div class='qu'>
Show the year and subject where 3 prizes were given. Show only years 2000 onwards.
nobel('''yr, subject, winner''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT yr,subject
FROM nobel
WHERE yr>=2000
GROUP BY yr,subject
HAVING COUNT(winner)=3
</source>
</div>

@@ Line 151: / Line 151: @@
 </div>
-<p>GROUP BY yr, subject</p>
+GROUP BY yr, subject</p>
 <div class='qu'>
 Show the year and subject where 3 prizes were given. Show only years 2000 onwards.

The nobel table can be used to practice more SUM and COUNT functions. - Revision history

Connor at 10:33, 11 July 2012

Connor: Created page with "

Nobel Prizes: Aggregate functions

Exer..."