Assignment 2 SQL Fundamentals Complete Solution

Question Details: #1320
Assignment 2 SQL Fundamentals Complete Solution

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.

 

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

 

 

 

  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

 

 

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

 

 

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

 

 

 

 

 

 

 

 

 

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

 

 

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

 

 

  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

 

 

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

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

 

 

  • Budget: $20 Ready
  • Posted by: Vikas
  • Subjects: Computer Science Painting
  • Attachments:

Solution Details: #1300
Assignment 2 SQL Fundamentals Complete Solution

SELECT M.Mem_FName, M.Mem_LName, R.Rent_Date, DR.Detail_DueDate, DR.Detail_ReturnDate, M.Movie_Tit...

Buy now with PayPal's online protection..
  • Purchased: 11 x
  • Average Rating: A+
  • Posted By: Vikas

Ratings

A+ - Thank you!

Thanks for the positive feedback!

FB Comments