Database Reference
In-Depth Information
Exercises
Besides the SQL statements you entered on your MySQL or MariaDB server while reading
this chapter, here are a few practice exercises to further strengthen what we've covered.
They're related to creating and altering tables. We'll use these tables with the modifications
you'll make in later chapters, so make sure to complete all of the exercises here.
1. Earlier in this chapter, we created a table called
birds_details
. We created the
table with two columns:
bird_id
and
description
. We took these two
columns from the
birds
table. Our intention in creating this table was to add
columns to store a description of each bird, notes about migratory patterns, areas in
which they can be found, and other information helpful in locating each bird in the
wild. Let's add a couple of columns for capturing some of that information.
Using the
ALTER TABLE
statement, alter the
birds_details
table. In one
SQL statement, add two columns named
migrate
and
bird_feeder
, making
them both integer (
INT
) columns. These will contain values of 1 or 0 (i.e.,
Yes
or
No
). In the same SQL statement, using the
CHANGE COLUMN
clause, change the
name of the column,
description
to
bird_description
.
When you're finished altering the table, run the
SHOW CREATE TABLE
state-
ment for this table to see the results.
2. Using the
CREATE TABLE
statement, create a new reference table named,
hab-
itat_codes
. Create this table with two columns: name the first column
hab-
itat_id
and make it a primary key using
AUTO_INCREMENT
and the column
type of
INT
. Name the second column
habitat
and use the data type
VARCHAR(25)
. Enter the following SQL statement to add data to the table:
INSERT INTO
habitat_codes
(
habitat
)
VALUES
(
'Coasts'
), (
'Deserts'
), (
'Forests'
),
(
'Grasslands'
), (
'Lakes, Rivers, Ponds'
),
(
'Marshes, Swamps'
), (
'Mountains'
), (
'Oceans'
),
(
'Urban'
);
Execute a
SELECT
statement for the table to confirm that the data was entered cor-
rectly. It should look like this:
+------------+----------------------+
| habitat_id | habitat |
+------------+----------------------+
| 1 | Coasts |
| 2 | Deserts |
| 3 | Forests |
| 4 | Grasslands |
