Part 3 Question: Using the KCT design shown below, code SQL statements for the f
ID: 3586179 • Letter: P
Question
Part 3 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. List the costumeID, size, and description (from COSTUMETYPE) for all costumes.
B. List the costumeID ,size and description for all costumes that have “star wars” in the description.
C. The same as above, but only include costumes that have been rented at least one time on a line item.
D. The same as above, but only include costumes that have been rented in the last 30 days.
Explanation / Answer
1) List the costumeID, size, and description (from COSTUMETYPE) for all costumes.
SELECT KCT_COSTUME.CostumeID, KCT_COSTUME.Size, KCT_COSTUMETYPE.Description
FROM KCT_COSTUME
INNER JOIN KCT_COSTUMETYPE ON KCT_COSTUME.CostumeType=KCT_COSTUMETYPE.TypeID;
2) List the costumeID ,size and description for all costumes that have “star wars” in the description.
SELECT KCT_COSTUME.CostumeID, KCT_COSTUME.Size, KCT_COSTUMETYPE.Description
FROM KCT_COSTUME
INNER JOIN KCT_COSTUMETYPE ON KCT_COSTUME.CostumeType=KCT_COSTUMETYPE.TypeID AND KCT_COSTUMETYPE.Description='star wars';
3) The same as above, but only include costumes that have been rented at least one time on a line item.
SELECT KCT_COSTUME.CostumeID, KCT_COSTUME.Size, KCT_COSTUMETYPE.Description
FROM KCT_COSTUME
JOIN KCT_COSTUMETYPE ON KCT_COSTUME.CostumeType=KCT_COSTUMETYPE.TypeID
JOIN KCT_LINEITEM ON KCT_COSTUME.CostumeID=KCT_LINEITEM.CostumeID;
4) The same as above, but only include costumes that have been rented in the last 30 days.
SELECT KCT_COSTUME.CostumeID, KCT_COSTUME.Size, KCT_COSTUMETYPE.Description
FROM KCT_COSTUME
JOIN KCT_COSTUMETYPE ON KCT_COSTUME.CostumeType=KCT_COSTUMETYPE.TypeID
JOIN KCT_LINEITEM ON KCT_COSTUME.CostumeID=KCT_LINEITEM.CostumeID;
JOIN KCT_RENTAL ON KCT_LINEITEM.RentalID = KCT_RENTAL.RentalID AND KCT_RENTAL.RentalDate <= TRUNC(SYSDATE)-30;