Database Reference
In-Depth Information
scientific_name: Strix omanensis
english_name: Omani Owl
order: Strigiformes
family: Strigidae (Owls)
It's now loading well. The scientific and common names are in the correct columns, along
with the other columns that we want. We're ready to move to the nextstep.
Selecting Imported Data
Now that wehave properly loaded the data from the Cornell data text file into the
clem-
ents_list_import
table, we can use the
INSERT INTO...SELECT
statementto
copy the data we want to the
birds
table. We're learning and experimenting, so let's cre-
ate a table identical to the
birds
table to insert the data from the
clem-
ents_list_import
table. Execute the following on your server:
CREATE TABLE
rookery
.
birds_new
LIKE
rookery
.
birds
;
Now let's select the rows we want from
clements_list_import
and insert them in-
to
birds_new
. Execute this on your server:
INSERT INTO
birds_new
(
scientific_name
,
common_name
,
family_id
)
SELECT
clements
.
scientific_name
,
english_name
,
bird_families
.
family_id
FROM
clements_list_import
AS
clements
JOIN
bird_families
ON
bird_families
.
scientific_name
=
SUBSTRING
(
family
,
1
,
LOCATE
(
' ('
,
family
) )
WHERE
change_type
=
'new species'
;
In this SQL statement, we're inserting only two columns from the
clem-
ents_list_import
table (i.e.,
scientific_name
and
english_name
). We're
joining the
clements_list_import
table to the
bird_families
table to get the
family_id
. To determine the
family_id
, we have to join on the name of the family.
This is included in the
family
column of the
clements_list_import
table, but it
has extra text in parentheses — common names for some of the birds in the family. So
we're usingthe
SUBSTRING()
and the
LOCATE()
functionsto get all of the text from
the start of the string until it finds a space followed by an open parenthesis, as in
Stri-
gidae (Owls)
. In the
WHERE
clause here, we're selecting any
change_type
that
has a value of
new species
.
