Question

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 your full name, section, and date submitted.

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.

22. 3 points

List the unique title ID, title name, genre name, and rating for all titles that have been rented with a fee code of ‘B’ (do not include duplicate rows). Sort by title name.

Answer 1

19) SELECT disk_ID as DiskID, title_name as Title, Rating, format_des AS Format, '$' + CONVERT(varchar(12), Fee_amt, 1) AS FROM disk where rentelID=3 ORDER BY disk_ID asc;

Here we can select DISKID title, Rating,Format and Fee where rentelID is 3. It is sorted by ORDER BY clause. To format in currency we use CONVERT().

20)SELECT rating, rating_des, count(title) FROM Disk GROUP BY Rating;

It counts the title based on the rating.count() used to count titles.

21)SELECT Rentel_ID, disk_ID, title_name, genre_name, feecode,fee_amount WHERE fee_amount>(SELECT avg(fee_amount) FROM disk) Order by title_name asc;

The nested query selects the average of all the fees amount, then it assign to the outer query condition WHERE fee_amount>avg(fee-amount). Then with this condition,selects the fields and sorted on the basis of title name.

22)SELECT distinct(titleId), title_name, genre_name, rating From Disk where fee_code='B' ORDER BY title_name asc;

With the condithin fee_code='B', selects the corresponding fields and sorted on ths basis of title_name.

Note: Here, the table names and column name are not provided. It is necessary to create queries. So in my view, i just take one table Disk with so many fields. May be these fields are not correct in your table. It make changes in your query..