MCQ to differentiate novices from competent practitioners:
Consider the following database table, called observations:
date species sightings ---------- ---------- ------------ 2013-01-01 woodpecker 3 2013-01-01 thrush 9 2013-02-01 woodpecker 4 2013-02-01 thrush 5 2013-03-01 woodpecker 8 2013-03-01 thrush 5
Which of the following would return the total number of sightings of woodpeckers?
a) SELECT COUNT(sightings) FROM observations GROUP BY species;
b) SELECT SUM(*) FROM observations WHERE species = ‘woodpecker’;
c) SELECT SUM(sightings) FROM observations WHERE species = ‘woodpecker’ GROUP BY species;
d) SELECT COUNT(species) FROM observations WHERE species = ‘woodpecker’ GROUP BY sightings;
Teaching Excercise:
Consider the following more complicated database table, also called observations:
date species site sightings ---------- ---------- ---------- ------------ 2013-01-01 woodpecker Saltash 7 2013-01-01 woodpecker Bude 0 2013-01-01 thrush Saltash 7 2013-01-01 thrush Bude 0 2013-02-01 woodpecker Saltash 10 2013-02-01 woodpecker Bude 4 2013-02-01 thrush Saltash 1 2013-02-01 thrush Bude 6
Complete the following SQL queries to achieve the desired result:
1) Obtain a list of dates on which woodpeckers were seen:
SELECT date FROM observations WHERE species = ‘woodpecker’ AND _____;
2) Get the total number of thrushes seen at Bude:
SELECT ___ WHERE site = ‘Bude’;
3) Get the mean number of woodpeckers seen for each day:
SELECT AVG(sightings) FROM observations _____;
4) Obtain a list of sites and total bird sightings ordered by the number of sightings, with the highest first:
SELECT site, SUM(sightings) FROM observations GROUP BY site ____;
MCQ to assess learning:
Consider the following database table, called observations:
date species sightings ---------- ---------- ------------ 2013-01-01 woodpecker 3 2013-01-01 thrush 9 2013-02-01 woodpecker 4 2013-02-01 thrush 5 2013-03-01 woodpecker 8 2013-03-01 thrush 5
Which of the following would return the number of days on which more than five thrushes were spotted?
a) SELECT COUNT() FROM observations WHERE species = ‘thrush’ AND sightings > 5;
b) SELECT * FROM observations WHERE species = ‘thrush’ AND sightings > 5;
c) SELECT SUM() FROM observations IF species = ‘thrush’ AND sightings > 5;
d) SELECT COUNT(date) FROM observations IF species = ‘thrush’ AND sightings > 5;