Database Reference
In-Depth Information
Exercises
String functions are very necessary to developing databases in MySQL and MariaDB. You
need to know them well. To become an expert, you need to practice using them, so be sure
to complete all of the following exercises.
1. One of the most commonly used string functions is
CONCAT()
. Construct a
SELECT
statement to query the
humans
table. Use the
CONCAT()
function to
merge together values from the
name_first
column with the
name_last
column. Use the
SPACE()
function to put a space between them in the results.
Give that field an alias of
Full Name
— and remember to put quotes around this
alias, as it contains a space. Limit the results to four people. Execute it to be sure it
has no errors.
Add a
WHERE
clause to that
SELECT
statement. For the condition of the
WHERE
clause, copy the
CONCAT()
you just assembled. List rows where the name is in a
set of the following names: Lexi Hollar, Michael Zabalaoui, and Rusty Johnson.
After you successfully execute the
SELECT
with that
WHERE
clause, add an
ORDER BY
clause to sort the data based on the concatenated name. Do it without
using
CONCAT()
.
2. Construct a
SELECT
statement that selects, from the
birds
table, the
com-
mon_name
and the
scientific_name
. Use a string function to change the
scientific_name
to all lowercase letters. Use the
CONCAT()
function to put
them into one field, with a space after the common name, followed by the scientif-
ic name in parentheses — for example,
African Desert Warbler (sylvia deserti)
.
Don't use the
SPACE()
function. Instead, put the spaces and parentheses within
single quote marks within the
CONCAT()
. Give the resulting field an alias of
Bird Species
. Limit the results to 10 rows.
After you've successfully executed that SQL statement, modify that statement to
join in the
bird_families
and the
bird_orders
tables. The
JOIN
state-
ment was covered extensively in
Unifying Results
. Then add the
scientif-
ic_name
columns from both of these tables to the fields returned.
Execute this modified statement to make sure your joins are correct. When they
are, move the
scientific_name
columns for the two additional tables into the
CONCAT()
. Using the
RPAD()
function, put dots after the bird species name, be-
fore the bird family and the bird order names. The results for a field will look like
this:
Speckled Warbler (pyrrholaemus
sagittatus)...Acanthizidae...Passeriformes
