Database Reference
In-Depth Information
This will probably require you to use
CONCAT()
twice. Use a
WHERE
clause to
list only Warblers. Limit the results to 10 rows.
3. Construct another
SELECT
statement to list all of the common names of bird spe-
cies from the
birds
table, where the common name contains the word
Shrike
.
When you execute that statement you should see some names with a hyphen after
the word
Shrike
. Add the
REPLACE()
function to the
SELECT
statement to re-
place those hyphens with a space in the results, and then execute the SQL state-
ment again.
4. Some of the names of the birds in the results from the
SELECT
statement in the
previous exercise have more than one hyphen (e.g.,
Yellow-browed Shrike-Vireo
).
Redo that SQL statement to replace only the hyphens after the word
Shrike
(e.g.,
to look like this:
Yellow-browed Shrike Vireo
). In order to do this, use
LOCATE()
with
REPLACE()
. You will need to use
LOCATE()
twice: one
within another.
5. True Shrikes are of the
Laniidae
family. Construct another
SELECT
to select the
common bird names with the word
Shrike
, but belonging to
Laniidae
. This will
require a join to the
bird_families
table. Use one of the substring functions
like
SUBSTRING()
to extract the words before
Shrike
. To do this, you will need
to use
LOCATE()
or a similar function. Then use
CONCAT()
to display that
value extracted after
Shrike
with a comma and space in between. The results for
each field should look like this:
Shrike, Rufous-tailed
. Give the field an
alias of
Shrikes
.
6. The humans table contains entries in which the member used either all lowercase
letters or all uppercase letters to enter their first and last names (e.g.,
andy
oram
and
MICHAEL STONE
). Use
UPDATE
to change the names to title case
(i.e., the first letter capital and the rest lowercase). First experiment with
SELECT
to make sure you have the functions organized properly. Use the
UCASE()
and
LCASE()
functions to set the cases. You will need to use
SUBSTRING()
or a
similar function a few times, and
CONCAT()
a coupleof times.
