Database Reference
In-Depth Information
Exercises
Here are some exercises to practice using date and time functions and a few of the string
date values in tables. By updating data with date and time functions, you will gain a better
understanding of the potential of these functions. The
UPDATE
statement is covered in
1. Construct an SQL statement to select a list of members from the
humans
table
who live in the United Kingdom. Select first and last names, concatenating them.
Include the date they joined and when their membership expires. Use the
DATE_FORMAT()
function to format the result for each date to look like this:
Sun., Feb. 2, 1979
. Be sure to include all of the punctuations (i.e., the
comma and the periods after the abbreviations, but not at the end, and the comma).
Refer to
Table11-2
for the formatting codes.
When you're finished, execute the SQL statement to check the results are correct.
If they're not, modify the statement until you get the right results.
2. Execute the
SELECT
statement to get a list of members and their expiration dates,
ordering the results by
membership_expiration
. Then use the
UPDATE
statement to change the values in the
membership_expiration
column of the
humans
table. Use the
ADDDATE()
function to extend the membership of all
members by 1 month and 15 days, but only for those whose membership has not
yet expired as of June 30, 2014. Refer to
Table11-1
to find the interval codes you
will need. You will also need to use a string in the
WHERE
clause. When finished,
execute
SELECT
again and compare the results to the previous ones to confirm
you were successful in changing the expiration dates for the correct members.
When you've finished extending the memberships, use
DATESUB()
to change
membership_expiration
to five days less for those same members as you
did before. When that's done, execute
SELECT
again and compare the results to
the previous results.
Change the expiration date one more time, but this time use
ADD_DATE()
to
change the expiration date to 10 days less. Remember, this will require you to use a
negative value. After you've done that, execute
SELECT
again to check the res-
ults.
3. In
Adjusting to Standards and Time Zones
,
we created a new table called
bird_identification_tests
. We added one row of data to it for testing.
For this exercise, insert at least five more rows into that table. Make entries for two
other
human_id
values and a few other
bird_id
values. While doing this, as