Part 2 Question: Using the KCT design shown below, code SQL statements for the following.

KCT _CUSTOMER(CustomerID, FirstName, LastName, Street, City, State, Zip, Phone)

KCT _RENTAL(RentalID, RentalDate,NumberOfDays, RentalAmount, Deposit, Tax, TotalAmount, CustomerID, ReturnDate, AmountReturned)

KCT _LINEITEM(RentalID, CostumeID, Rate, Deposit)

KCT _COSTUMETYPE(TypeID, Description, Photo, DailyRentalRate, DepositRate, ReplacementCost)

KCT _COSTUME(CostumeID, Size, Availability, DatePurchased, CostumeType)

Question:

A. Show the SQL statement to create a view called KCT_LateRentals that shows Customer First Name, Last Name, Home Phone, RentalDate, RentalAmount, NumberOfDays, and NumberOfDaysLate (this is equal to CurrentDate-RentalDate-NumberOfDays). Only include records where CurrentDate >RentalDate plus NumberOfDays and records where the ReturnDate is null.

B. Show the SQL statement to create a view called KCT_CustomerLateFees that shows Customer.LastName,FirstName, HomePhone, and LateFee (which is equal to NumberOfDaysLate*(RentalAmount/NumberOfDays)) from the view KCT_LateRentals created above.

C. Show the SQL statement to create a view named KCT_CustomerLateTotals that uses the KCT_CustomerLateFees view, and shows Customer FirstName LastName, and HomePhone and the sum of all of their LateFees as AccountDue