Management Information Systems (33:136:370) 
Professor Eckstein
SQL Query Practice Questions: Point-of-Sale Terminal Records

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



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.