Database Reference
In-Depth Information
Exercises
Numeric functions are pretty easy, once you know what each does. You probably didn't
have any trouble following the sections on them in this chapter. Aggregate functions,
though, can be a little bothersome. Therefore, while some exercises in this section require
you to use numeric functions, most include aggregate functions. Some call for you to com-
bine numeric and aggregate functions. These should help you to retain what you learned in
this chapter. There aren't many exercises for this chapter, though, so it shouldn't take you
much time to complete all of them.
1. Construct a simple
SELECT
statement that counts the number of rows in the
birds
table where the
common_name
contains the word
Least
. Execute that to
make sure you did it correctly. Next, modify that SQL statement to count the rows
in which the
common_name
contains the word
Great
. You'll do this by using
the
LIKE
operator in the
WHERE
clause.
2. In
Calculating a Group of Values
, we covered how to group columns for counting.
Combine the two SQL statements you constructed in the previous exercise and
make one SQL statement using
GROUP BY
to produce one field in the results that
shows the number of birds with a
common_name
containing
Least
and another
field that shows the number of birds that are
Great
.
3. In some of the examples in this chapter (see
Counting Values
), we had MySQL
count the number of birds in each species and in each bird family. For this exer-
cise, you may want to refer to those examples.
Construct a
SELECT
statement to query the
birds
table, with three fields in the
results set: the name of the bird species, the number of birds in that species, and the
percentage that species represents of the total number of species. Let MySQL cal-
culate the total number of species; don't enter that value manually in the SQL
statement.
After you've successfully executed this SQL statement, modify the SQL statement
using one of the numeric functions to round to one decimal place the field that con-
tains the percentage value.
4. Do the previous exercise again, but this time create another
SELECT
statement
that retrieves only the total number of bird species. With the
SET
statement, create
a user variable to store that value taken by MySQL from the
SELECT
statement.
You may give any name you want for that variable.
Now change the
SELECT
statement you created in the previous exercise, but use
the variable you created for determining the percentage of total birds in the table.
Once you have it executed correctly, exit the
mysql
client and log back in.
