Database Reference
In-Depth Information
Run the same SQL statement to create the user variable and the second SQL
statement for this exercise again. Notice the time it takes to execute in the results
statement. Then execute again the
SELECT
statement from the previous exercise,
the one that doesn't use a user variable. Notice how long it took to execute com-
pared to the
SELECT
statement that uses a user variable.
5. In the
humans
table, the
membership_expiration
column contains date
values. Put together a
SELECT
statement in which you determine the number of
months after the date 2014-01-01 until each member's membership will expire. If
you're not sure how to do this, refer to
Comparing Dates and Times
.
Use the
SIGN()
function in the
WHERE
clause to determine whether a membership has
expired. List only unexpired memberships. This was covered in
Eliminating Neg-
clause to exclude those members who don't have a paid membership (i.e., no ex-
piration date). Label the field as
Months to Expiration
.
6. Modify the SQL statement you created for the previous exercise. Don't exclude
expired memberships this time — but still exclude those without a paid member-
ship. Use the
CONCAT()
function to append "
- expired
" to the number of
months remaining or past due. Don't append the text if the membership hasn't ex-
pired. You'll have to use the
IF()
function to test that for the field containing the
number of months. You'll also have to use the
ABS()
function to remove the
negative sign from the value.
7. Based on the SQL statement you constructed in the previous exercises, create a
new one to determine the average number of months until expiration for all paid
members in one field, and the average number of months past expiration, based
on the date of 2014-01-01. You will need to use the
AVG()
function to calculate
these averages. Once you have that working, add fields to determine the minim-
um and maximum number of months, using
MIN()
and
MAX()
, and the
GROUP
BY
clause.
