You operate a store that is sometimes open past midnight. Your point-of-sale terminals ("cash registers") use the following database:
CASHIER(CashierID,
FirstName, MiddleInit, LastName, DateHired)
TRANSACTION(TransactionID,
RegisterNumber, CashierID,
DateTimeStart, DateTimeEnd, TaxCharged)
CashierID foreign key to CASHIER
PRODUCT(SKU, Description, CurrentUnitPrice, UnitsInStock, Taxable)
TRANSACTIONDETAIL(TransactionID, SKU, Quantity, UnitPriceCharged)
TransactionID foreign key to TRANSACTION
SKU foreing key to PRODUCT
Notes:
RegisterNumber indicates with checkout lane / cash register was used to process a transaction.
Taxable is a yes/no field that contains "yes" if an item is subject to sales tax.
TaxCharged is the total sales tax charged for a transaction.
UnitPriceCharged in the TRANSACTIONDETAIL table is the unit price that the customer was charged at the time of the transaction. It can differ from CurrentUnitPrice field in the PRODUCT table because customers sometimes use coupons, and unit prices can change over time. Neither of these prices include tax, even for items subject to sales tax.
DateTimeStarted is holds the exact date and time the cashier started "ringing up" a transaction. DateTimeEnded is the exact time the transaction completed. Both are in an Access-style combined date/time format.
A sale is considered to occur and revenue is considered to be collected at the ending time of each transaction.
Note that when one specifies a date/time such as #2/29/2016#, the time segment is sent to zero, that, is the beginning of the very first second of that day. One can specify specific times within a day with syntax like #2/29/2016 9:00:00am#.
The functions Month( ), Day( ), and Year( ) can be used to extact specific information from dates (as ordinary numbers). For example, Month(#2/29/2016#) = 2, Day(#2/29/2016#) = 29, and Year(#2/29/2016#) = 2016. There are also Hour( ), Minute( ), and Second( ) functions that work similarly. The Hour( ) function uses a 24-hour scheme: for example, 10pm comes out as 22.
Write SQL statements
to implement the following queries. Fields should be displayed in the
order specified.
(a) Show the cashier first name, cashier last name, register number, and transaction start date/time for all transactions by cashiers hired since the beginning of 2016.
(b) Show the average amount of time needed to process a transaction.
(c) Show the total non-tax revenue for February 2016.
(d) Show the same information as in part (a), but also include the total amount charged for each transaction, including taxes (labeled TotalCharged).
(e) For each cashier, show the first name, last name, and total non-tax revenue collected during January 2016. This last output should be labeled FundsCollected. (Note: including tax revenue in this calculation is problematic. Why?)
(f)
For each product, show its description, the total number of units sold
in 2015 (labeled TotalUnits), and the total number of transactions (NumTransactions)
that included it in 2015.