Question

I need help with certain questions. Please.

Rating EatingDess PG-13 INSY 3304 May Not be Suitable for Children Under 13 Project 3 Due 4/29 Note: This will require that your RatineDesc column accepts at least 45 characters. If it was defined with less than that, you will first need to use an alter table statement to increase the character length for that column. Using SQL Plus on OMEGA, access the tables you created in Project 2 and complete the following SQL transactions, Log your statements and results by spooling your file (with echo on). Directions for creating and running SQL files are available in the Assignments and Exams page in Blackboard (in the Project 2 group of files). All column headings must show in their entirety. Be sure to include a cover sheet with 5. 2 points Add a new customer to the Customer table. The customer ID should be one more than the max customer ID in the customer table. Hint Use a nested SELECT and the MAX function. The remaining attributes for the new row are as follows: your full name, section, and date submitted. Mary Jones 214-555-2020 6. 2 points List the customer ID, first name, and last name for all customers for whom no phone number exists. Sort the output by customer ID. 7. 2 points List the average fee for all rows in the Fee table. Use Act as the column heading. 8. 2 points List the count of unique titles for which we have disks in our inventory. Use TitleCount as the column heading. Hnt: Query from the disk table only 9. 3 points List the GeneR and Count of titles in each genre. Use the following column headings: Genrem Titlecou Hint Use a GROUP BY clause 10. 3 points List rental ID, rental date, and the sum of all rental fees in rental 10. Format the total as cutrency and use the following column headings: ReutallD, Date, Rentalotal 1. 1 point 11. A)4 points Add the following new row to the Fee Code table: List the customer ID, customer first name, customer last name, and customer count of rentals for each customer. Combine the first and last name into one column Sort by rental count in descending order, then by customer ID in ascending order. Use the following column headings: CustomerlD, SustomerNamc. RcntalCount. Hint: Use a GROUP BY clause. Fee Code Fee Amount 2. 1 point Change the Fee Code to A' for Disk ID 191 in Rental 1. 11. B) 3 points List all rows and all columns from the RentalDetail table, sort by Rental ID then by Disk ID. both in ascending order. Use the following column headings: RentallD, DiskID 3. 1 point Change the Fee Code to C for Disk ID 421 in Rental 11 12. 3 points 4. 1 point List the title ID, title name, and count of each format type for each title. Sort by count in

18. 4 points

For each Rental, list the Rental ID, Rental date, customer ID, customer first name, customer last name, and count of disks rented; sort by Rental ID. Show the Rental date formatted as ‘mm-dd-yyyy.’ Hint: use a GROUP BY clause.

19. 4 points

List the disk ID, title name, rating, format description, and fee amount for all copies rented in Rental 3; sort by disk ID. Show the fee amount formatted as currency and use the following column headings: DiskID, Title, Rating, Format, Fee.

20. 3 points

List the rating, rating description, and count of titles for each rating. Hint: use a GROUP BY clause.

21. 4 points

List the rental ID, disk ID, title name, genre name, fee code, and fee amount for all titles that have a fee amount greater than the average fee for all Rentals (using the average of the fees for all RentalDetail rows, not the average of the fees in the fee code table). Sort by title name. Hint: use a nested SELECT.

Answer 1

If you have any doubts, please give me comment...

-- 18

SELECT R.RentalID, RentalDate, C.CustID, CustFName, CustLName, COUNT(*) countDisks

FROM Customer C, Rental R, RentalDetail RD

WHERE C.CustID = R.CustID AND R.RentalID = RD.RentalID

GROUP BY R.RentalID, RentalDate, C.CustID, CustFName, CustLName

ORDER BY R.RentalID;

-- 19

SELECT D.DiskID, Title, RatingDesc Rating, FormatDesc Format, FORMAT(FeeAmt, 'C', 'en-us') Fee

FROM Fee F, RentalDetail R, Disk D, Title T, Rating R, Format F

WHERE R.RentalID = D.RentalID AND R.FeeCode = F.FeeCode AND D.FormatCode = F.FormatCode AND D.TitleID = T.TitleID AND T.RatingID = R.RatingID AND RentalID = 3

ORDER BY D.DiskID;

-- 20

SELECT R.RatingID, RatingDesc, COUNT(*) countTitles

FROM Rating R, Title T

WHERE R.RatingID = T.RatingID

GROUP BY R.RatingID, RatingDesc;

-- 21

SELECT RentalID, DiskID, Title, GenreName, F.FeeCode, FeeAmt

FROM RentalDetail R, Disk D, Title T, Genre G, Fee F

WHERE R.DiskID = D.DiskID AND D.TitleID = T.TitleID AND T.GenreID = G.GenreID AND R.FeeCode = F.FeeCode AND FeeAmt >=(

SELECT AVG(FeeAmt)

FROM RentalDetail R, Fee F

WHERE R.FeeCode = F.FeeCode

);