Database Reference
In-Depth Information
Exercises
Exercises follow for you to practice using the
UPDATE
and
DELETE
statements. If you
haven't already, download the
rookery
and the
birdwatchers
databases from the
MySQL Resources site
). This will give you some good-sized tables on which to practice
these SQL statements.
a copies of the
humans
and the
prize_winners
tables. Name the new tables
humans_copy
and
prize_winners_copy
. Once you've created the copies,
use the
SELECT
statement to view all of the rows in both of the new tables. You
should see the same values as are contained in the original tables.
2. After you've done the previous exercise, use the
SELECT
statement to select all of
the members from Austria in the
humans
table. You'll need to use a
WHERE
clause for that SQL statement. The
country_id
for Austria is
au
. If you have
problems, fix the SQL statement until you get it right.
Next, using the same
WHERE
clause from the
SELECT
statement, construct an
UPDATE
statement to change the value of the
membership_type
column for
Austrian members to
premium
. In the same
UPDATE
statement, set the value of the
membership_expiration
to one year from the date you execute the SQL
statement. You will need to use the
CURDATE()
function inside the
DATE_ADD()
function. The
DATE_ADD()
function was shown in an example
guments to it, nothing to go inside its parentheses. Both functions are covered in
the date manually (e.g., '
2014-11-03'
for November 3, 2014; include the quote
marks). Use the
SELECT
statement to check the results when you're done.
3. Using the
DELETE
statement, delete the rows associated with the member named
Barry Pilson
from the
humans
and
prize_winners
tables. This was ex-
plained, along with an example showing how to do it, in
Deleting in Multiple
both tables to make sure you deleted both rows.
4. Using the
DELETE
statement, delete all of the rows in the
humans
table. Then de-
lete all of the rows of data in the
prize_winners
tables. Use the
SELECT
state-
ment to confirm that both tables are empty.
Now copy all of the data from the
humans_copy
and
prize_winners_copy
tables to the
humans
and
prize_winners
tables. Do this with the
INSERT...SELECT
statement (covered in
Inserting Data from Another Table
).
