Database Reference
In-Depth Information
Exercises
The goal of the following exercises is to give you practice assembling tables using
JOIN
and creating subqueries. In the process of doing these exercises, think about how tables and
data come together. Try to envision each table as a separate piece of paper with a list of
data on it, and how you might place them on a desk to find information on them in relation
to each other. In such a scenario, you might tend to place your left index finger at one point
on a page on the left and your right index finger on a point on another page on your right.
That's a join. Where you point on each are the join points. As you type the SQL statements
in these exercises, think of this scene and say aloud what you're doing, what you're telling
MySQL to do. It helps to better understand the joining of tables and creating of subqueries.
1. In the
birdwatchers
database, there is a table called
bird_sightings
in
which there are records of birds that members have seen in the wild. Suppose we
have a contest in which we will award a prize based on the most sightings of birds
from the order
Galliformes
. A member gets one point for each sighting of birds in
this order.
Construct an SQL statement to count the number of entries from each member.
There should be two fields in the results set: one containing the
human_id
with
Birder
as the alias; and the second field containing the number of entries with
Entries
as its alias. To accomplish this, join the
bird_sightings
table to
birds
,
bird_families
, and
bird_orders
. Remember that these tables are
in a different database. You will have to use the
COUNT()
function and a
GROUP
BY
clause. Do all of this with
JOIN
and not with subqueries. Your results should
look like the following:
+--------+---------+
| Birder | Entries |
+--------+---------+
| 19 | 1 |
| 28 | 5 |
+--------+---------+
When you have successfully constructed this SQL statement, modify it to join in
the
humans
table. In the column list, replace the field for
human_id
with the
first and last name of the member. Use the
CONCAT()
function to put them to-
gether into a single field (with a space in between the names), with the same alias.
Once you make the needed changes and execute it, the results should look like this,
but the number of names and points may be different:
