MySQL lab
Budget: $10 – $30 USD
Deliverable: Submit the SQL queries in a docx or txt file on Blackboard.
Objective: Learn & Practice SELECT clause for the DATE functions, relation & logical operators, Special
operators (BETWEEN, IN), aggregate functions (count, min, max, avg) and numeric functions (abs, ceil,
floor)
write SQL queries that does the following: (Use payment table from the sakila database)
-- return the current date and time
-- return only the date after returning both the date & time (from the above statement)
-- return only the current date
-- return the customer id, staff id, payment date, last update and a computed column called Span of
days that is number of days between the payment date and the last update
-- return the payment date and the last update and formats the payment date in the month/date/year
format (all numbers, for example 03/17/2005) and the last update into the date-month-year format
(month is name of the month, for example, 17-March-2005). Also, use Aliases for the new formatted
date columns.
-- return the payment date, a date that is 10 days after the payment date, 2 months later than the
payment date and a year later than the payment date. Use alias for all 3 columns such as (Payment date
10days later; 2 months later and 1 year after). – Use the date_add function
-- return the customer id, staff id, amount and the rental id for all the payments where the payment
date is before July 17, 2005.
-- return all the customer id, staff id and the amount where the rental id is less than 2000
-- return all the customer id, staff id and the amount where the rental id is greater than 16000 and the
amount is less than 1.00
-- return all the details for the rental ids that may fall into the following list 2308, 7273, 15298, 9571,
8705 (hint: use IN operator) and arrange them in descending order of the amount
-- return all details from the payment table where the payment amounts fall in the range of 2.00 and
3.00 (hint: use BETWEEN operator)
-- return the total number of rows the Payment relation/table has; use an ALIAS “Total Number of Rows”
for the returned result. (hint: use COUNT function)
-- return the average, maximum and the minimum amount paid by a customer in the payment
relation/table and use ALIAS for the returned columns as “Average amount”, “MAX Amount” and “MIN
Amount” (hint: use avg, min, max functions)
-- return the rental id, amount, its nearest highest amount less than amount, nearest lowest amount
more than the amount for the rental id in the list (6163, 11177, 5441), use ALIAS wherever possible
(hint: use ceil/ceiling, floor)
Objective: Learn & Practice SELECT clause for the DATE functions, relation & logical operators, Special
operators (BETWEEN, IN), aggregate functions (count, min, max, avg) and numeric functions (abs, ceil,
floor)
write SQL queries that does the following: (Use payment table from the sakila database)
-- return the current date and time
-- return only the date after returning both the date & time (from the above statement)
-- return only the current date
-- return the customer id, staff id, payment date, last update and a computed column called Span of
days that is number of days between the payment date and the last update
-- return the payment date and the last update and formats the payment date in the month/date/year
format (all numbers, for example 03/17/2005) and the last update into the date-month-year format
(month is name of the month, for example, 17-March-2005). Also, use Aliases for the new formatted
date columns.
-- return the payment date, a date that is 10 days after the payment date, 2 months later than the
payment date and a year later than the payment date. Use alias for all 3 columns such as (Payment date
10days later; 2 months later and 1 year after). – Use the date_add function
-- return the customer id, staff id, amount and the rental id for all the payments where the payment
date is before July 17, 2005.
-- return all the customer id, staff id and the amount where the rental id is less than 2000
-- return all the customer id, staff id and the amount where the rental id is greater than 16000 and the
amount is less than 1.00
-- return all the details for the rental ids that may fall into the following list 2308, 7273, 15298, 9571,
8705 (hint: use IN operator) and arrange them in descending order of the amount
-- return all details from the payment table where the payment amounts fall in the range of 2.00 and
3.00 (hint: use BETWEEN operator)
-- return the total number of rows the Payment relation/table has; use an ALIAS “Total Number of Rows”
for the returned result. (hint: use COUNT function)
-- return the average, maximum and the minimum amount paid by a customer in the payment
relation/table and use ALIAS for the returned columns as “Average amount”, “MAX Amount” and “MIN
Amount” (hint: use avg, min, max functions)
-- return the rental id, amount, its nearest highest amount less than amount, nearest lowest amount
more than the amount for the rental id in the list (6163, 11177, 5441), use ALIAS wherever possible
(hint: use ceil/ceiling, floor)