Database Reference
In-Depth Information
columns appear in the output. Another change, which is cosmetic, is to provide the alias
states
to the
conservation_status
table so we could refer to the short alias later
instead of the long name.
Finally, the
ORDER BY
clause orders the output by
conservation_status_id
, be-
cause that value happens to be in the order of severity in the
conservation_status
table. We want to override the default order, which puts the most threatened species first,
so weadd the
DESC
option to put the least threatened first. We're still ordering results
secondarily by the common name of the birds, but using the actual column name this time
instead of an alias. This is because we changed the alias for the
common_name
column
from
Birds
to
Birds from Anatidae
, because all the results are in that family. We
could have used
'Birds from Anatidae'
in the
ORDER BY
clause, but that's both-
ersome to type.
Let's look at one more basic example of a
JOIN
. Suppose we wanted to get a list of mem-
bers located in Russia (i.e., where
country_id
has a value of
ru
) who have reported
sighting a bird from the
Scolopacidae
family (shore and wader birds like Sandpipers
and Curlews). Information on bird sightings is stored in the
bird_sightings
table. It
includes GPS coordinates recorded from a bird list application on the member's mobile
phone when they note the sighting. Enter this SQL statement:
SELECT CONCAT(name_first, ' ', name_last) AS Birder,
common_name AS Bird, location_gps AS 'Location of Sighting'
FROM birdwatchers.humans
JOIN birdwatchers.bird_sightings USING(human_id)
JOIN rookery.birds USING(bird_id)
JOIN rookery.bird_families USING(family_id)
WHERE country_id = 'ru'
AND bird_families.scientific_name = 'Scolopacidae'
ORDER BY Birder;
+-------------------+-------------------+---------------------------+
| Birder | Bird | Location of Sighting |
+-------------------+-------------------+---------------------------+
| Anahit Vanetsyan | Bar-tailed Godwit | 42.81958072; 133.02246094 |
| Elena Bokova | Eurasian Curlew | 51.70469364; 58.63746643 |
| Elena Bokova | Eskimo Curlew | 66.16051056; -162.7734375 |
| Katerina Smirnova | Eurasian Curlew | 42.69096856; 130.78185081 |
+-------------------+-------------------+---------------------------+
This SQL statement joins together four tables, two from the
birdwatchers
database
and two from the
birds
database. Look closely at this SQL statement and consider the
purpose of including each of those four tables. All of them were needed to assemble the
