Assignment 2 SQL Fundamentals | Complete Solution
- AceTutor
- Rating : 33
- Grade : A+
- Questions : 0
- Solutions : 823
- Blog : 1
- Earned : $26606.70
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 |
[Solved] Assignment 2 SQL Fundamentals | Complete Solution
- This Solution has been Purchased 3 time
- Submitted On 13 Mar, 2015 12:43:00
- AceTutor
- Rating : 33
- Grade : A+
- Questions : 0
- Solutions : 823
- Blog : 1
- Earned : $26606.70
SELECT M.Mem_FName, M.Mem_LName, R.Rent_Date, DR.Detail_DueDate, DR.Detail_ReturnDate, M.Movie_Tit...