Database Reference
In-Depth Information
+--------------+--------+
| Birder | Points |
+--------------+--------+
| Elena Bokova | 4 |
| Marie Dyer | 8 |
+--------------+--------+
2. In the preceding exercises, you were asked to count the number of bird species
the members sighted from the
Galliformes
. So that the contest is more fun, in-
stead of giving one point for each bird species in that order, give a point for only
one bird species per bird family in the bird order. That means that a member
doesn't get more points for sighting the same bird species multiple times. A mem-
ber also doesn't get more points for spotting several birds in the same family. In-
stead, the member has to look through bird guides to find a species for each spe-
cies and then go looking for one from each in their area. This should make the
contest more of an adventure for the members.
To allow for the change to the contest, you will need to modify the SQL statement
you constructed at the end of the previous exercise. First, you will need to add a
DISTINCT
to the start of the column list in the outer query. You'll need to re-
move the
CONCAT()
and
GROUP BY
. When you've done that, execute the SQL
statement to make sure you have no errors. You should get a results set that shows
multiple entries for some members. Next, place the whole SQL statement inside
another SQL statement to make it a subquery. The new, outer query should in-
clude
CONCAT()
and
GROUP BY
so that it can count the single entries from
each family for each member. It should return results like this:
+--------------+--------+
| Birder | Points |
+--------------+--------+
| Elena Bokova | 1 |
| Marie Dyer | 5 |
+--------------+--------+
3. There are five families in the
Galliformes
bird order. For the contest described in
the last two exercises, the most points that a member could achieve therefore is 5.
Change the SQL statement you entered at the end of the previous exercise to list
only members who have 5 points. To do this, you will need to wrap the previous
SQL statement inside another, creating a nested query. When you execute the full
SQL statement, the results should look like this:
+------------+--------+
| Birder | Points |
+------------+--------+
