[Solved]Using Sql Plus Omega Access Tables Created Project 2 Complete Following Sql Transactions L Q37167231

Using SQL*Plus on OMEGA, access the tables you created inProject 2 and complete the following SQL transactions. Log yourstatements and results by spooling your file (with echo on).Directions for creating and running SQL files are available in theAssignments and Exams page in Blackboard (in the Project 2 group offiles). All column headings must show in theirentirety. Be sure to include a cover sheet with your fullname, section, and date submitted.

1. 1 point

Add the following new row to the FeeCode table:

Fee Code         FeeAmount

     C                   2.50

2. 1 point

Change the Fee Code to ‘A’ for Disk ID191 in Rental 1.

3. 1 point

Change the Fee Code to ‘C’ for Disk ID421 in Rental 11.

4. 1 point

Update the following row inthe Rating table:

Rating            RatingDesc

PG-13             May Not be Suitable for Children Under 13

Note: This will require thatyour RatingDesc column accepts at least 45 characters. If it wasdefined with less than that, you will first need to use an altertable statement to increase the character length for thatcolumn.

5. 2 points

Add a new customer to the Customertable. The customer ID should be one more than the max customer IDin the customer table. Hint: Use a nested SELECT and the MAXfunction. The remaining attributes for the new row are asfollows:
CustFName     CustLName    ___CustPh__

     Mary              Jones             214-555-2020

6. 2 points

List the customer ID, first name, andlast name for all customers for whom no phone number exists. Sortthe output by customer ID.

7. 2 points

List the average fee for all rows inthe Fee table. Use AvgFee as the column heading.

8. 2 points

     List thecount of unique titles for which we have disks in our inventory.Use TitleCount as the column heading. Hint: Query from the disktable only.

9. 3 points

List the GenreID and Count of titlesin each genre. Use the following column headings: GenreID,TitleCount. Hint: Use a GROUP BY clause.

10. 3 points

    List rental ID,rental date, and the sum of all rental fees in rental 10. Formatthe total as currency and use the following column headings:RentalID, Date, RentalTotal.

11. A) 4 points

List the customer ID, customer firstname, customer last name, and customer count of rentals for eachcustomer. Combine the first and last name into one column. Sort byrental count in descending order, then by customer ID in ascendingorder. Use the following column headings: CustomerID, CustomerName,RentalCount. Hint: Use a GROUP BY clause.

11. B) 3 points

List all rows and all columns from theRentalDetail table; sort by Rental ID then by Disk ID, both inascending order. Use the following column headings: RentalID,DiskID, FeeCode.

12. 3 points

List the title ID, title name, andcount of each format type for each title. Sort by count indescending order. Hint: Use a GROUP BY clause.

13. 4 points

List the customer ID, customer firstname, customer last name, and customer phone number for allcustomers; sort by customer ID. Show the phone number formatted as‘(###) ###-####’ and use the following column headings:Customer_ID, First_Name, Last_Name, Phone.

14. 4 points

List the rental ID, genre ID, genrename, title ID, title name, and fee amount of the titles(s) withthe lowest fee amount in each Rental, and sort by fee amount indescending order. Show the fee amount formatted as currency, anduse the following column headings: GenreID, Genre, TitleID, Title,Fee. Hint: use a GROUP BY clause and a nested SELECT.

15. 3 points

List the genre name, title name, andrating for all titles; sort by genre then by title name.

16. 4 points

List the genre name and count oftitles in each genre. Use the following column headings: GenreName,TitleCount. Sort by title count in descending order. Hint: use aGROUP BY clause.

17. 4 points

List the RentalID, DiskID, title name,and fee amount for titles rented with a fee less than or equal to$2.50; sort by fee amount in descending order, then by title namein ascending order. Show the fee amount formatted as currency.

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 dateformatted 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 Rental3; sort by disk ID. Show the fee amount formatted as currency anduse 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 BYclause.

21. 4 points

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

22. 3 points

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

23. 4 points

List the Rental ID, Rental date, andcalculated Rental total (sum of all fee amounts) for all copiesrented in Rental 3. Show the total formatted as currency, theRental date formatted as ‘mm/dd/yy’ and use the following columnheadings: RentalID, Date, RentalTotal.

24. 4 points

List the disk ID, title ID, titlename, fee code, and fee amount for the title with thehighest-priced fee in Rental 10. Show the fee amount formatted ascurrency, and use the following column headings: DiskID, TitleID,Title, FeeCode, FeeAmt. Hint: use a nested SELECT.

25. 4 points

List the rental ID, date, customer ID,customer last name, clerk ID, and clerk last name for all rentalsbefore 9/5/17. Sort by date then by rental ID, both in ascendingorder.

26. 4 points

List the disk ID, title ID, titlename, and fee amount for all copies that have been rented at aprice higher than the average price of all fee amounts (use theaverage of the fee amounts in the Fee table). Sort by fee amount indescending order, then by disk ID in ascending order. Hint: use anested SELECT statement.

27. 4 points

List the customer ID, first name, lastname, and total (sum of all fee amounts) of all Rentals forCustomer 23. Combine the first and last name into a single columnand show the total formatted as currency. Use the following columnheadings: Customer_ID, Customer_Name, Fee_Total.

28. 4 points

List the Rental ID, Rental date,customer ID, customer first name, customer last name, and customerphone number for all Rentals on or before 9/4/17. Sort by Rentaldate then by customer ID, both in ascending order. Show the Rentaldate formatted as ‘mm/dd/yyyy’ and show the phone number formattedas ‘(###) ###-####’.

29. 4 points

List the employee ID, first name, lastname, and count of Rentals for the employee(s) who have processedmore than one rental. Sort by count in descending order, then byemployee ID in ascending order. Use the following column headings:EmployeeID, FirstName, LastName, RentalCount. Hint: use a GROUP BYclause and a HAVING clause.

30. 3 points

List the customer ID, first name, andlast name of all customers whose first or last names start with theletter ‘J.’ Sort by last name and use the following columnheadings: Customer_ID, First_Name, Last_Name.

31. 4 points

List the customer ID, first name, andlast name for any customers who do not have any rentals. Use thefollowing column headings: CustID, FirstName, LastName. Hint: use aNested Select.

Expert Answer

---

Answer to Using SQL*Plus on OMEGA, access the tables you created in Project 2 and complete the following SQL transactions. Log you… . . .