Database Reference
In-Depth Information
Exercises
In addition to logging into MySQL or MariaDB with the
mysql
client and entering the SQL
statements shown already in this chapter, here are a few exercises to get some more prac-
tice playing with the
mysql
client and to help you better understand the basics. Rather than
use generic names like
books
and
book_id
, you're asked to use more realistic names. In
that same spirit, use fairly realistic data (e.g., “John Smith” for a person's name) when en-
tering data in these exercises.
1. Log into MySQL or MariaDB using the
mysql
client and switch the default data-
base to the database,
test
. Create two tables called
contacts
and
rela-
tion_types
. For both tables, use column type
INT
for number columns and
CHAR
for character columns. Specify the maximum number of characters you want
with
CHAR
— otherwise MySQL wills set a maximum of one character, which is
not very useful. Make sure that you allow for enough characters to fit the data you
will enter later. If you want to allow characters between numbers (e.g., hyphens for
a telephone number), use
CHAR
. For the
contacts
, you will need six columns:
name
,
phone_work
,
phone_mobile
,
email
,
relation_id
. For the
re-
lation_types
table, there should be only two columns:
relation_id
and
relationship
.
When you're finished creating both tables, use the
DESCRIBE
statement to see
how they look.
2. Enter data in the two tables created in the previous exercise. Enter data in the
second table,
relation_types
first. Enter three rows of data in it. Use single-
digit, sequential numbers for the first column, but the following text for the second
column:
Family
,
Friend
,
Colleague
. Now enter data in the table named
contacts
. Enter at least five fictitious names, telephone numbers, and email ad-
dresses. For the last column,
relation_id
, enter single digits to correspond
with the
relation_id
numbers in the table,
relation_types
. Make sure
you have at least one row for each of the three potential values for
rela-
tion_id
.
3. Execute two
SELECT
statements to retrieve all of the columns of data from both
tables that you created and filled with data from the previous two exercises. Then
run a
SELECT
statement that retrieves only the person's name and email address
from the table named
contacts
.
4. Change some of the data entered in the previous exercises, using the
UPDATE
statement. If you don't remember how to do that, refer back to the examples in this
chapter on how to change data in a table. First, change someone's name or tele-
