Assignment 2 SQL Fundamentals Complete Solution

Question: #1320

Assignment 2 SQL Fundamentals

Total points: 50

This assignment should be completed individually. For each problem, submit your SQL statement and a screen shot of the SQL results in a single Word document or pdf file. Submit the file via eLearning.

I recommend creating a new user and workspace, log in as that user and load the database script ourvideo_A2.sql (provided in this week's assignment folder).

Before you attempt to write any SQL queries, familiarize yourself with the database structure and data. I have provided a relational diagram and sample data for this database.

Write queries to address each of the problems below. Submit both the SQL statements and the screen prints of the outputs from Oracle.

OurVideo is a small movie rental company with a single store. OurVideo needs a database system to track the rental of movies to its members. OurVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie “Twist in the Wind”. “Twist in the Wind” would be one MOVIE and each copy would be a VIDEO. A rental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided in the Figure below.

OurVideo ERD

DROP TABLE DETAILRENTal;

DROP TABLE RENTal;

DROP TABLE MEMBERSHIP;

DROP TABLE VIDEO;

DROP TABLE MOVIE;

DROP TABLE PRICE;

CREATE TABLE PRICE (

PRICE_CODE NUMBER(2,0) PRIMARY KEY,

PRICE_DESCRIPTION VARCHAR2(20) NOT NULL ,

PRICE_RENTFEE NUMBER(5,2) CHECK (PRICE_RENTFEE >= 0),

PRICE_DAILYLATEFEE NUMBER(5,2) CHECK (PRICE_DAILYLATEFEE >= 0)

);

CREATE TABLE MOVIE (

MOVIE_NUM NUMBER(8,0) PRIMARY KEY,

MOVIE_TITLE VARCHAR2(75) NOT NULL,

MOVIE_YEAR NUMBER(4,0) CHECK (MOVIE_YEAR > 1900),

MOVIE_COST NUMBER(5,2),

MOVIE_GENRE VARCHAR2(50),

PRICE_CODE NUMBER(2,0) CONSTRAINT MOVIE_PRICE_CODE_FK REFERENCES PRICE

);

CREATE TABLE VIDEO (

VID_NUM NUMBER(8,0) PRIMARY KEY,

VID_INDATE DATE,

MOVIE_NUM NUMBER(8,0) CONSTRAINT VIDEO_MOVIE_NUM_FK REFERENCES MOVIE

);

CREATE TABLE MEMBERSHIP (

MEM_NUM NUMBER(8,0) PRIMARY KEY,

MEM_FNAME VARCHAR2(30) NOT NULL,

MEM_LNAME VARCHAR2(30) NOT NULL,

MEM_STREET VARCHAR2(120),

MEM_CITY VARCHAR2(50),

MEM_STATE CHAR(2),

MEM_ZIP CHAR(5),

MEM_BALANCE NUMBER(10,2)

);

CREATE TABLE RENTAL (

RENT_NUM NUMBER(8,0) ,

RENT_DATE DATE DEFAULT SYSDATE,

MEM_NUM NUMBER(8,0) ,

CONSTRAINT RENTAL_PK PRIMARY KEY (RENT_NUM),

CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP

);

CREATE TABLE DETAILRENTAL (

RENT_NUM NUMBER(8,0) NOT NULL,

VID_NUM NUMBER(8,0) NOT NULL,

DETAIL_FEE NUMBER(5,2),

DETAIL_DUEDATE DATE,

DETAIL_RETURNDATE DATE,

DETAIL_DAILYLATEFEE NUMBER(5,2),

CONSTRAINT DETAIL_RENT_VID_PK PRIMARY KEY (RENT_NUM, VID_NUM),

CONSTRAINT DETAIL_RENT_NUM_FK FOREIGN KEY (RENT_NUM) REFERENCES RENTAL,

CONSTRAINT DETAIL_VID_NUM_FK FOREIGN KEY (VID_NUM) REFERENCES VIDEO

);

/*PRICE:*/

INSERT INTO PRICE VALUES (1, 'Standard', 2, 1);

INSERT INTO PRICE VALUES (2, 'New Release', 3.5, 3);

INSERT INTO PRICE VALUES (3, 'Discount', 1.5, 1);

INSERT INTO PRICE VALUES (4, 'Weekly Special', 1, .5);

/*MOVIE:*/

INSERT INTO MOVIE VALUES (1234, 'The Cesar Family Christmas', 2009, 39.95, 'FAMILY', 2);

INSERT INTO MOVIE VALUES (1235, 'Smokey Mountain Wildlife', 2006, 59.95, 'ACTION', 1);

INSERT INTO MOVIE VALUES (1236, 'Richard Goodhope', 2010, 59.95, 'DRAMA', 2);

INSERT INTO MOVIE VALUES (1237, 'Beatnik Fever', 2009, 29.95, 'COMEDY', 2);

INSERT INTO MOVIE VALUES (1238, 'Constant Companion', 2010, 89.95, 'DRAMA', NULL);

INSERT INTO MOVIE VALUES (1239, 'Where Hope Dies', 2000, 25.49, 'DRAMA', 3);

INSERT INTO MOVIE VALUES (1245, 'Time to Burn', 2007, 45.49, 'ACTION', 1);

INSERT INTO MOVIE VALUES (1246, 'What He Doesn'|| '''' || 't Know', 2008, 58.29, 'COMEDY', 1);

/*VIDEO:*/

INSERT INTO VIDEO VALUES (34341, '01-22-09', 1235);

INSERT INTO VIDEO VALUES (34342, '01-22-09', 1235);

INSERT INTO VIDEO VALUES (34366, '03-02-11', 1236);

INSERT INTO VIDEO VALUES (34367, '03-02-11', 1236);

INSERT INTO VIDEO VALUES (34368, '03-02-11', 1236);

INSERT INTO VIDEO VALUES (34369, '03-02-11', 1236);

INSERT INTO VIDEO VALUES (44392, '10-21-10', 1237);

INSERT INTO VIDEO VALUES (44397, '10-21-10', 1237);

INSERT INTO VIDEO VALUES (54321, '05-18-10', 1234);

INSERT INTO VIDEO VALUES (54324, '05-18-10', 1234);

INSERT INTO VIDEO VALUES (54325, '05-18-10', 1234);

INSERT INTO VIDEO VALUES (59237, '02-14-11', 1237);

INSERT INTO VIDEO VALUES (61353, '01-28-08', 1245);

INSERT INTO VIDEO VALUES (61354, '01-28-08', 1245);

INSERT INTO VIDEO VALUES (61367, '07-30-10', 1246);

INSERT INTO VIDEO VALUES (61369, '07-30-10', 1246);

INSERT INTO VIDEO VALUES (61388, '01-25-09', 1239);

/*MEMBERSHIP:*/

INSERT INTO MEMBERSHIP VALUES (102, 'TAMI', 'DAWSON', '2632 TAKLI CIRCLE', 'NORENE', 'TN', '37136', 11);

INSERT INTO MEMBERSHIP VALUES (103, 'CURT', 'KNIGHT', '4025 CORNELL COURT', 'FLATGAP', 'KY', '41219', 6);

INSERT INTO MEMBERSHIP VALUES (104, 'JAMAL', 'MELENDEZ', '788 EAST 145TH AVENUE', 'QUEBECK', 'TN', '38579', 0);

INSERT INTO MEMBERSHIP VALUES (105, 'IVA', 'MCCLAIN', '6045 MUSKET BALL CIRCLE', 'SUMMIT', 'KY', '42783', 15);

INSERT INTO MEMBERSHIP VALUES (106, 'MIRANDA', 'PARKS', '4469 MAXWELL PLACE', 'GERMANTOWN', 'TN', '38183', 0);

INSERT INTO MEMBERSHIP VALUES (107, 'ROSARIO', 'ELLIOTT', '7578 DANNER AVENUE', 'COLUMBIA', 'TN', '38402', 5);

INSERT INTO MEMBERSHIP VALUES (108, 'MATTIE', 'GUY', '4390 EVERGREEN STREET', 'LILY', 'KY', '40740', 0);

INSERT INTO MEMBERSHIP VALUES (109, 'CLINT', 'OCHOA', '1711 ELM STREET', 'GREENEVILLE', 'TN', '37745', 10);

INSERT INTO MEMBERSHIP VALUES (110, 'LEWIS', 'ROSALES', '4524 SOUTHWIND CIRCLE', 'COUNCE', 'TN', '38326', 0);

INSERT INTO MEMBERSHIP VALUES (111, 'STACY', 'MANN', '2789 EAST COOK AVENUE', 'MURFREESBORO', 'TN', '37132', 8);

INSERT INTO MEMBERSHIP VALUES (112, 'LUIS', 'TRUJILLO', '7267 MELVIN AVENUE', 'HEISKELL', 'TN', '37754', 3);

INSERT INTO MEMBERSHIP VALUES (113, 'MINNIE', 'GONZALES', '6430 VASILI DRIVE', 'WILLISTON', 'TN', '38076', 0);

/*RENTAL:*/

INSERT INTO RENTAL VALUES (1001, '03-01-11', 103);

INSERT INTO RENTAL VALUES (1002, '03-01-11', 105);

INSERT INTO RENTAL VALUES (1003, '03-02-11', 102);

INSERT INTO RENTAL VALUES (1004, '03-02-11', 110);

INSERT INTO RENTAL VALUES (1005, '03-02-11', 111);

INSERT INTO RENTAL VALUES (1006, '03-02-11', 107);

INSERT INTO RENTAL VALUES (1007, '03-02-11', 104);

INSERT INTO RENTAL VALUES (1008, '03-03-11', 105);

INSERT INTO RENTAL VALUES (1009, '03-03-11', 111);

/*DETAILRENTAL:*/

INSERT INTO DETAILRENTAL VALUES (1001, 34342, 2, '03-04-11', '03-02-11', 1);

INSERT INTO DETAILRENTAL VALUES (1001, 34366, 3.5, '03-04-11', '03-02-11', 3);

INSERT INTO DETAILRENTAL VALUES (1001, 61353, 2, '03-04-11', '03-03-11', 1);

INSERT INTO DETAILRENTAL VALUES (1002, 59237, 3.5, '03-04-11', '03-04-11', 3);

INSERT INTO DETAILRENTAL VALUES (1003, 54325, 3.5, '03-04-11', '03-09-11', 3);

INSERT INTO DETAILRENTAL VALUES (1003, 61369, 2, '03-06-11', '03-09-11', 1);

INSERT INTO DETAILRENTAL VALUES (1003, 61388, 0, '03-06-11', '03-09-11', 1);

INSERT INTO DETAILRENTAL VALUES (1004, 34341, 2, '03-07-11', '03-07-11', 1);

INSERT INTO DETAILRENTAL VALUES (1004, 34367, 3.5, '03-05-11', '03-07-11', 3);

INSERT INTO DETAILRENTAL VALUES (1004, 44392, 3.5, '03-05-11', '03-07-11', 3);

INSERT INTO DETAILRENTAL VALUES (1005, 34342, 2, '03-07-11', '03-05-11', 1);

INSERT INTO DETAILRENTAL VALUES (1005, 44397, 3.5, '03-05-11', '03-05-11', 3);

INSERT INTO DETAILRENTAL VALUES (1006, 34366, 3.5, '03-05-11', '03-04-11', 3);

INSERT INTO DETAILRENTAL VALUES (1006, 61367, 2, '03-07-11', NULL, 1);

INSERT INTO DETAILRENTAL VALUES (1007, 34368, 3.5, '03-05-11', NULL, 3);

INSERT INTO DETAILRENTAL VALUES (1008, 34369, 3.5, '03-05-11', '03-05-11', 3);

INSERT INTO DETAILRENTAL VALUES (1009, 54324, 3.5, '03-05-11', NULL, 3);

Each question is worth 5 points.

Create sequences for the RENTAL and MEMBERSHIP tables. The MEMBERSHIP sequence should start with 100 and increment by 10 and the RENTAL sequence should start with 1 and increment by 1.

What is the total movie cost for each movie genre? (order the results by total cost in descending order).

Movie_Genre	Total Cost
DRAMA	$175.39
ACTION	$105.44
COMEDY	$88.24
FAMILY	$39.95

Display the rental history for Tami Dawson, display the first and last name, rent date, due date, return date, and movie title.

MEM_FNAME	MEM_LNAME	RENT_DATE	DETAIL_DUEDATE	DETAIL_RETURNDATE	MOVIE_TITLE
TAMI	DAWSON	03/02/0011	03/04/0011	03/09/0009	The Cesar Family Christmas
TAMI	DAWSON	03/02/0011	03/06/0011	03/09/0009	What He Doesn't Know
TAMI	DAWSON	03/02/0011	03/06/0011	03/09/0009	Where Hope Dies

Which members have come to the store and rented more than one video at a time? Show the members, the rent number, and the number of videos rented (only display the members who have rented more than 1 video at a time).

MEM_FNAME	MEM_LNAME	RENT_NUM	Videos Rented
LEWIS	ROSALES	1004	3
CURT	KNIGHT	1001	3
TAMI	DAWSON	1003	3
STACY	MANN	1005	2
ROSARIO	ELLIOTT	1006	2

Which movies have not been returned? Display the movie title, member name, rental date and due date.

MEM_FNAME	MEM_LNAME	RENT_DATE	DETAIL_DUEDATE	DETAIL_RETURNDATE	MOVIE_TITLE
STACY	MANN	03/03/0011	03/05/0011	-	The Cesar Family Christmas
ROSARIO	ELLIOTT	03/02/0011	03/07/0011	-	What He Doesn't Know
JAMAL	MELENDEZ	03/02/0011	03/05/0011	-	Richard Goodhope

Which movie titles have been rented out the most?

MOVIE_NUM	MOVIE_TITLE	Number of Rentals
1236	Richard Goodhope	5
1237	Beatnik Fever	3
1235	Smokey Mountain Wildlife	3
1246	What He Doesn't Know	2
1234	The Cesar Family Christmas	2
1245	Time to Burn	1
1239	Where Hope Dies	1

Modify the query in question #6 to only display the movie that has been rented the most times (Hint: you are looking for MAX Number of Rentals. You will need to use a subquery on the Having clause)

MOVIE_NUM	MOVIE_TITLE	Number of Rentals
1236	Richard Goodhope	5

Generate a list of Member ids using the first character of the first name, the first 4 characters of the last name, and the 2 characters of member state.

MEM_FNAME	MEM_LNAME	User ID
TAMI	DAWSON	TDAWSOTN
CURT	KNIGHT	CKNIGHKY
JAMAL	MELENDEZ	JMELENTN
IVA	MCCLAIN	IMCCLAKY
MIRANDA	PARKS	MPARKSTN
ROSARIO	ELLIOTT	RELLIOTN
MATTIE	GUY	MGUYKY
CLINT	OCHOA	COCHOATN
LEWIS	ROSALES	LROSALTN
STACY	MANN	SMANNTN
LUIS	TRUJILLO	LTRUJITN
MINNIE	GONZALES	MGONZATN

Show the difference between each movie’s rental fee and the min movie price and the max movie price.

MOVIE_NUM	MOVIE_TITLE	MOVIE_GENRE	PRICE_RENTFEE	MINPRICE	MinDiff	PRICE_RENTFEE	MAXPRICE	MAXDiff
1245	Time to Burn	ACTION	2	1	1	2	3.5	-1.5
1246	What He Doesn't Know	COMEDY	2	1	1	2	3.5	-1.5
1235	Smokey Mountain Wildlife	ACTION	2	1	1	2	3.5	-1.5
1236	Richard Goodhope	DRAMA	3.5	1	2.5	3.5	3.5	0
1234	The Cesar Family Christmas	FAMILY	3.5	1	2.5	3.5	3.5	0
1237	Beatnik Fever	COMEDY	3.5	1	2.5	3.5	3.5	0
1239	Where Hope Dies	DRAMA	1.5	1	.5	1.5	3.5	-2

Write a query that can be used to show how much each customer owes for returning their movie after the due date.

MEM_FNAME	MEM_LNAME	RENT_DATE	DETAIL_DUEDATE	MOVIE_TITLE	Days Late	DETAIL_FEE	Overdue Fee
TAMI	DAWSON	03/02/0011	03/04/0011	The Cesar Family Christmas	5	3.5	$17.50
LEWIS	ROSALES	03/02/0011	03/05/0011	Richard Goodhope	2	3.5	$7.00
LEWIS	ROSALES	03/02/0011	03/05/0011	Beatnik Fever	2	3.5	$7.00
TAMI	DAWSON	03/02/0011	03/06/0011	Where Hope Dies	3	0	$.00
TAMI	DAWSON	03/02/0011	03/06/0011	What He Doesn't Know	3	2	$6.00

Extra Credit:

Alter the query in #10 to display the total amount due per rental detail, per customer.

MEM_FNAME	MEM_LNAME	RENT_NUM	DETAIL_DUEDATE	Overdue Fee
TAMI	DAWSON	1003	03/06/0011	$6.00
LEWIS	ROSALES	1004	03/05/0011	$14.00
TAMI	DAWSON	1003	03/04/0011	$17.50