Database Reference
In-Depth Information
Exercises
Here are some exercises to practice using date and time functions and a few of the string
functions that we covered in Chapter10 . Some require you to use UPDATE to change the
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
Chapter8 .
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
Search WWH ::




Custom Search