Question: Using SQL*Plus and the database below: Need commands to do the following 1. List the rental ID, genre ID, genre name, title ID, title name,

Using SQL*Plus and the database below:

Need commands to do the following

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

2. List the genre name and count of titles in each genre. Use the following column headings: GenreName, TitleCount. Sort by title count in descending order. Hint: use a GROUP BY clause.

3. 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 name in ascending order. Show the fee amount formatted as currency.

4. 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.

5. 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.

CREATE TABLE Format_xxx (

FormatCode CHAR(1) NOT NULL,

FormatDesc VARCHAR(6) NOT NULL,

PRIMARY KEY (FormatCode)

) ;

CREATE TABLE Genre_xxx (

GenreID CHAR(2) NOT NULL,

GenreDesc VARCHAR(10) NOT NULL,

PRIMARY KEY (GenreID)

) ;

CREATE TABLE Rating_xxx (

RatingID VARCHAR(4) NOT NULL,

RatingDesc VARCHAR(22) NOT NULL,

PRIMARY KEY (RatingID)

) ;

CREATE TABLE Fee_xxx (

FeeCode CHAR(1) NOT NULL,

FeeAmt NUMBER(4,2) NOT NULL,

PRIMARY KEY (FeeCode)

) ;

CREATE TABLE Clerk_xxx (

ClerkID CHAR(2) NOT NULL,

ClerkFName VARCHAR(15) NOT NULL,

ClerkLName VARCHAR(15) NOT NULL,

PRIMARY KEY (ClerkID)

) ;

CREATE TABLE Customer_xxx (

CustID VARCHAR(5) NOT NULL,

CustFName VARCHAR(15) NOT NULL,

CustLName VARCHAR(15) NOT NULL,

CustPhone CHAR(10),

PRIMARY KEY (CustID)

) ;

CREATE TABLE Title_xxx (

TitleID VARCHAR(4) NOT NULL,

Title VARCHAR(20) NOT NULL,

RatingID VARCHAR(4) NOT NULL,

GenreID CHAR(2) NOT NULL,

PRIMARY KEY (TitleID),

FOREIGN KEY (RatingID) REFERENCES Rating_xxx,

FOREIGN KEY (GenreID) REFERENCES Genre_xxx

) ;

CREATE TABLE Rental_xxx (

RentalID NUMBER(4) NOT NULL,

RentalDate DATE NOT NULL,

CustID VARCHAR(5) NOT NULL,

ClerkID CHAR(2) NOT NULL,

PRIMARY KEY (RentalID),

FOREIGN KEY (CustID) REFERENCES Customer_xxx,

FOREIGN KEY (ClerkID) REFERENCES Clerk_xxx

) ;

CREATE TABLE Disk_xxx (

DiskID VARCHAR(4) NOT NULL,

TitleID VARCHAR(4) NOT NULL,

FormatCode CHAR(1) NOT NULL,

PRIMARY KEY (DiskID),

FOREIGN KEY (TitleID) REFERENCES Title_xxx,

FOREIGN KEY (FormatCode) REFERENCES Format_xxx

) ;

CREATE TABLE RentalDetail_xxx (

RentalID NUMBER(4) NOT NULL,

DiskID VARCHAR(4) NOT NULL,

FeeCode CHAR(1) NOT NULL,

PRIMARY KEY (RentalID, DiskID),

FOREIGN KEY (FeeCode) REFERENCES Fee_xxx

) ;

DESCRIBE Format_xxx ;

DESCRIBE Genre_xxx ;

DESCRIBE Rating_xxx ;

DESCRIBE Fee_xxx ;

DESCRIBE Clerk_xxx ;

DESCRIBE Customer_xxx ;

DESCRIBE Title_xxx ;

DESCRIBE Rental_xxx ;

DESCRIBE Disk_xxx ;

DESCRIBE RentalDetail_xxx ;

INSERT INTO Format_xxx

VALUES ('D', 'DVD');

INSERT INTO Format_xxx

VALUES ('B', 'BLURAY');

INSERT INTO Genre_xxx

VALUES ('AC', 'Action');

INSERT INTO Genre_xxx

VALUES ('CL', 'Classics');

INSERT INTO Genre_xxx

VALUES ('FM', 'Family');

INSERT INTO Genre_xxx

VALUES ('HR', 'Horror');

INSERT INTO Rating_xxx

VALUES ('R', 'Restricted Under 17');

INSERT INTO Rating_xxx

VALUES ('PG13', 'Inappropriate Under 13');

INSERT INTO Rating_xxx

VALUES ('PG', 'Parental Guidance');

INSERT INTO Rating_xxx

VALUES ('G', 'General');

INSERT INTO Fee_xxx

VALUES ('A', 3.00);

INSERT INTO Fee_xxx

VALUES ('B', 2.00);

INSERT INTO Clerk_xxx

VALUES ('15', 'Mary', 'Jones');

INSERT INTO Clerk_xxx

VALUES ('21', 'Mark', 'White');

INSERT INTO Clerk_xxx

VALUES ('30', 'Renee', 'Smith');

INSERT INTO Clerk_xxx

VALUES ('12', 'Tim', 'Shore');

INSERT INTO Clerk_xxx

VALUES ('42', 'Nicole', 'Walker');

INSERT INTO Customer_xxx

VALUES ('23', 'Jean', 'Mackey', '9725551143');

INSERT INTO Customer_xxx

VALUES ('102','Jack','Hughes','2145552014');

INSERT INTO Customer_xxx

VALUES ('154','Alicia','Moore','8175551919');

INSERT INTO Customer_xxx

VALUES ('83','Carrie','Brown', NULL);

INSERT INTO Customer_xxx

VALUES ('53','Ashleigh','Hayes','9725550628');

INSERT INTO Customer_xxx

VALUES ('68','Anthony','Smith', NULL);

INSERT INTO Customer_xxx

VALUES ('72','Shane','Wesley','8175550112');

INSERT INTO Title_xxx

VALUES ('92','Hacksaw Ridge','R','AC');

INSERT INTO Title_xxx

VALUES ('76','Wonder Woman','PG13','AC');

INSERT INTO Title_xxx

VALUES ('119','Divergent','PG13','AC');

INSERT INTO Title_xxx

VALUES ('12','Casa Blanca','PG','CL');

INSERT INTO Title_xxx

VALUES ('29','Despicable Me 2','PG','FM');

INSERT INTO Title_xxx

VALUES ('42','Frozen','PG','FM');

INSERT INTO Title_xxx

VALUES ('58','The Specialist','R','AC');

INSERT INTO Title_xxx

VALUES ('230','Lone Survivor','R','AC');

INSERT INTO Title_xxx

VALUES ('245','Rio 2','G','FM');

INSERT INTO Title_xxx

VALUES ('159','Boss Baby','PG','FM');

INSERT INTO Title_xxx

VALUES ('240','Sleepy Hollow','R','HR');

INSERT INTO Title_xxx

VALUES ('218','Gone With The Wind','PG','CL');

INSERT INTO Title_xxx

VALUES ('296','The Hurt Locker','R','AC');

INSERT INTO Title_xxx

VALUES ('35','Minions','PG','FM');

INSERT INTO Rental_xxx

VALUES (1,'02-Sep-17','23','15');

INSERT INTO Rental_xxx

VALUES (2,'02-Sep-17','102','15');

INSERT INTO Rental_xxx

VALUES (3,'02-Sep-17','154','21');

INSERT INTO Rental_xxx

VALUES (4,'03-Sep-17','83','30');

INSERT INTO Rental_xxx

VALUES (5,'03-Sep-17','23','15');

INSERT INTO Rental_xxx

VALUES (6,'04-Sep-17','83','12');

INSERT INTO Rental_xxx

VALUES (7,'04-Sep-17','154','15');

INSERT INTO Rental_xxx

VALUES (8,'05-Sep-17','53','12');

INSERT INTO Rental_xxx

VALUES (9,'05-Sep-17','68','15');

INSERT INTO Rental_xxx

VALUES (10,'05-Sep-17','23','21');

INSERT INTO Rental_xxx

VALUES (11,'05-Sep-17','72','42');

INSERT INTO Disk_xxx

VALUES ('215','92','D');

INSERT INTO Disk_xxx

VALUES ('191','76','D');

INSERT INTO Disk_xxx

VALUES ('259','119','B');

INSERT INTO Disk_xxx

VALUES ('89','12','D');

INSERT INTO Disk_xxx

VALUES ('96','29','D');

INSERT INTO Disk_xxx

VALUES ('152','42','D');

INSERT INTO Disk_xxx

VALUES ('86','58','B');

INSERT INTO Disk_xxx

VALUES ('260','119','D');

INSERT INTO Disk_xxx

VALUES ('301','230','B');

INSERT INTO Disk_xxx

VALUES ('376','245','B');

INSERT INTO Disk_xxx

VALUES ('153','42','D');

INSERT INTO Disk_xxx

VALUES ('202','159','D');

INSERT INTO Disk_xxx

VALUES ('402','240','B');

INSERT INTO Disk_xxx

VALUES ('381','218','D');

INSERT INTO Disk_xxx

VALUES ('216','92','B');

INSERT INTO Disk_xxx

VALUES ('203','159','D');

INSERT INTO Disk_xxx

VALUES ('97','29','D');

INSERT INTO Disk_xxx

VALUES ('421','296','D');

INSERT INTO Disk_xxx

VALUES ('299','35','D');

INSERT INTO RentalDetail_xxx

VALUES (1,'215','A');

INSERT INTO RentalDetail_xxx

VALUES (1,'191','B');

INSERT INTO RentalDetail_xxx

VALUES (2,'259','A');

INSERT INTO RentalDetail_xxx

VALUES (3,'89','B');

INSERT INTO RentalDetail_xxx

VALUES (3,'96','B');

INSERT INTO RentalDetail_xxx

VALUES (3,'152','B');

INSERT INTO RentalDetail_xxx

VALUES (4,'86','A');

INSERT INTO RentalDetail_xxx

VALUES (5,'260','A');

INSERT INTO RentalDetail_xxx

VALUES (6,'301','A');

INSERT INTO RentalDetail_xxx

VALUES (7,'376','A');

INSERT INTO RentalDetail_xxx

VALUES (7,'153','B');

INSERT INTO RentalDetail_xxx

VALUES (8,'202','B');

INSERT INTO RentalDetail_xxx

VALUES (8,'402','A');

INSERT INTO RentalDetail_xxx

VALUES (9,'381','A');

INSERT INTO RentalDetail_xxx

VALUES (9,'216','A');

INSERT INTO RentalDetail_xxx

VALUES (10,'203','B');

INSERT INTO RentalDetail_xxx

VALUES (10,'97','A');

INSERT INTO RentalDetail_xxx

VALUES (11,'376','B');

INSERT INTO RentalDetail_xxx

VALUES (11,'421','A');

INSERT INTO RentalDetail_xxx

VALUES (11,'299','A');

SELECT * FROM Format_xxx

ORDER BY FormatCode;

SELECT * FROM Genre_xxx

ORDER BY GenreID;

SELECT * FROM Rating_xxx

ORDER BY RatingID;

SELECT * FROM Fee_xxx

ORDER BY FeeCode;

SELECT * FROM Clerk_xxx

ORDER BY ClerkID;

SELECT * FROM Customer_xxx

ORDER BY CustID;

SELECT * FROM Title_xxx

ORDER BY TitleID;

SELECT * FROM Rental_xxx

ORDER BY RentalID;

SELECT * FROM Disk_xxx

ORDER BY DiskID;

SELECT * FROM RentalDetail_xxx

ORDER BY RentalID;

UPDATE Customer_xxx

SET CustFName = 'Kerry'

WHERE CustID = '83';

UPDATE Customer_xxx

SET CustPhone = '2146881234'

WHERE CustID = '23';

INSERT INTO Customer_xxx

VALUES ('100','Amanda','Green', NULL);

INSERT INTO Genre_xxx

VALUES ('DR', 'Drama');

INSERT INTO Title_xxx

VALUES ('279','Hidalgo','PG13','DR');

INSERT INTO Disk_xxx

VALUES ('327','92','D');

INSERT INTO Disk_xxx

VALUES ('382','119','B');

INSERT INTO Disk_xxx

VALUES ('406','29','D');

INSERT INTO Rental_xxx

VALUES (12,'06-SEP-2017','53','15');

INSERT INTO Rental_xxx

VALUES (13,'07-SEP-2017','100','12');

UPDATE RentalDetail_xxx

SET FeeCode = 'B'

WHERE RentalID = 1;

INSERT INTO RentalDetail_xxx

VALUES (12,'376','A');

INSERT INTO RentalDetail_xxx

VALUES (12,'406','B');

INSERT INTO RentalDetail_xxx

VALUES (13,'382','A');

INSERT INTO RentalDetail_xxx

VALUES (13,'215','B');

INSERT INTO RentalDetail_xxx

VALUES (13,'327','A');

SELECT * FROM Format_xxx

ORDER BY FormatCode;

SELECT * FROM Genre_xxx

ORDER BY GenreID;

SELECT * FROM Rating_xxx

ORDER BY RatingID;

SELECT * FROM Fee_xxx

ORDER BY FeeCode;

SELECT * FROM Clerk_xxx

ORDER BY ClerkID;

SELECT * FROM Customer_xxx

ORDER BY CustID;

SELECT * FROM Title_xxx

ORDER BY TitleID;

SELECT * FROM Rental_xxx

ORDER BY RentalID;

SELECT * FROM Disk_xxx

ORDER BY DiskID;

SELECT * FROM RentalDetail_xxx

ORDER BY RentalID;

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Q:

Please write queries based on the following requirements using Premier Database. Premier Database can be downloaded from Blackboard. You are required to submit SQL query code. You do NOT need to...

Q:

Question 2: SQL-SELECT queries (20 marks) This question is based on the View Ridge Gallery database you have been using in the labs. See the textbook for background to the case and the table...

Q:

Part I: Copy and paste the Data Definition Commands in the file "Sailor Query DDL and Data" to create the database and tables for the boat reservation system Part II: Populating Tables 1. copy and...

Q:

PLEASE HELP :) You must have completed all of the steps found in the Oracle 11g Launch document. The queries for this assignment use the 'EliteVideo' Database tables you created in the last module....

Q:

The music database consists of 9 tables (where keys are underlined): album_country country, album_id) album_genre (album_id, genre_id) album track (album_id, position, sang_id) albums (album id,...

Q:

***Pleace reach out to me at 859-398-9677 for SQL Script. Thanks*** Chapter 7 Homework To create MSU Human Resources database, use MSU_HR_MySQL script provided. Before answering the questions, be...

Q:

Chapter 7 Homework To create MSU Human Resources database, use MSU_HR_MySQL script provided. Before answering the questions, be familiar with the database structure by referring to the database...

Q:

Here is the script given: Looking for the answer to the Advanced Challenge Quetion. CREATE TABLE warehouses (wh_id NUMBER(2), location VARCHAR(12) ); INSERT INTO warehouses VALUES (10, 'Boston');...

Q:

1 . Colonial Adventure Tours database currently is designed as below 5 tables ( primary keys are boxed ) : Guide ( GuideNum , LastName, FirstName, Address, City, State, PostalCode, PhoneNum, HireDate...

Q:

1 Goals and Overview. 1. To use a text file for output and later for input. 2. To use exceptions and write an exception handler that does more than abort execution. 3. To use a static class variable....

Q:

5. Suppose that a = a + ib, where a, b R are fixed. By integrating the function eat over an interval [0, T] and equating real parts, show that (a + b?) | eat cos bt dt = ea" (a cos bT + b sin bT) a.

Q:

What is the effect of heat gain and heat loss on the entropy of the fluid during Rayleigh flow?

Q:

7 - 6 Indicate if each transaction and event is ( 1 ) a source of cash, ( 2 ) a use of cash, and / or ( 3 ) an adjustment leading to a source or use of cash ( assume an indirect format ) . List also...

Q:

Suppose we need to deliver 500 gal/min of ethanol from a storage tank to a process unit 300 ft away. The storage tank is horizontal and on concrete supports such that the tank bottom is 4 ft above...

Q:

What is the basis for Security Concerns in Cloud Computing?

Q:

Should Needs and GAP Analyses be equally applied in terms of effort when off-theshelf System Solutions being acquired versus building a custom system using Vendors or internal Programming Staff?

Q:

Describe the three main Cloud Computing Environments.