Programming Question

Using MySQL SELECT commands answer the questions listed below using the Tiny Videoschema used in your previous assignment. Submit via the D2L drop box the final SQL script for
each problem.
1. Write a query to display the columns listed below. The query should list each customer
once, whether they have rented a movie or not. Where the customer has rented a movie it
should show only the first rental date. The output should be sorted by membership
number.
2. Write a query to display the columns listed below. The query should list each customer
who has not rented a move. The output should be sorted by membership number.
3. Write a query to display the columns listed below. The query should list each customer
where the total detail rental fee is greater than twice the overall average detail rental fee.
The output should be sorted by membership number. (Hint: A HAVING clause will be
needed)
4. Write a query to display the columns listed below. The query should list each customer in
which the video rental is overdue. The Days_Overdue column should calculate the
number of days overdue based upon the due date and return date. The output should be
sorted by membership number and then by the detail due date.
5. Write a query to display the columns listed below. For each customer the query should
show the current system date, the current day (when you do the problem the date and day
will be different), the number of characters in the member last name, the last date the
customer rented a video and how many total videos the person rented.
This assignment uses the same schema as the previous assignment so no need to create it again.
But if you have lost it, here is where you can find it: Tiny Video
Please submit one .sql file containing all queries.
/* Database Systems, 9th Ed., Coronel/MOrris/Rob */
/* Type of SQL : MySQL */
CREATE SCHEMA IF NOT EXISTS TINY_VIDEO;
USE TINY_VIDEO;
DROP TABLE IF EXISTS detail_rental;
DROP TABLE IF EXISTS rental;
DROP TABLE IF EXISTS membership;
DROP TABLE IF EXISTS video;
DROP TABLE IF EXISTS movie;
DROP TABLE IF EXISTS price;
/*Create table price*/
CREATE TABLE price
(price_id INTEGER PRIMARY KEY AUTO_INCREMENT,
description VARCHAR(20) NOT NULL,
rental_fee DECIMAL(5,2),
daily_late_fee DECIMAL(5,2));
/*Insert data into price*/
INSERT INTO price VALUES(1,’Standard’,2.5,1);
INSERT INTO price VALUES(2,’New Release’,4.0,3);
INSERT INTO price VALUES(3,’Discount’,2.0,1);
INSERT INTO price VALUES(4,’Weekly Special’,1.5,.5);
/*Create table movie*/
CREATE TABLE movie
(movie_id INTEGER PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(75) NOT NULL,
year_released INTEGER,
cost DECIMAL(5,2),
genre VARCHAR(50),
price_id INTEGER,
FOREIGN KEY(price_id) REFERENCES price(price_id));
/*Insert data into movie*/
INSERT INTO movie VALUES(1234,’The Cesar Family Christmas’,2007,39.95,’FAMILY’,2);
INSERT INTO movie VALUES(1235,’Smokey Mountain Wildlife’,2004,59.95,’ACTION’,3);
INSERT INTO movie VALUES(1236,’Richard Goodhope’,2008,59.95,’DRAMA’,2);
INSERT INTO movie VALUES(1237,’Beatnik Fever’,2007,29.95,’COMEDY’,2);
INSERT INTO movie VALUES(1238,’Constant Companion’,2008,89.95,’DRAMA’,NULL);
INSERT INTO movie VALUES(1239,’Where Hope Dies’,1998,25.49,’DRAMA’,3);
INSERT INTO movie VALUES(1245,’Time to Burn’,2006,45.49,’ACTION’,3);
INSERT INTO movie VALUES(1246,’What He Doesn”t Know’,2006,58.29,’COMEDY’,1);
/*Create table video*/
CREATE TABLE video
(video_id INTEGER PRIMARY KEY AUTO_INCREMENT,
purchase_date DATE,
movie_id INTEGER,
FOREIGN KEY(movie_id) REFERENCES movie(movie_id));
/*Insert data into video*/
INSERT INTO video VALUES(54321,’2008-06-18′,1234);
INSERT INTO video VALUES(54324,’2008-06-18′,1234);
INSERT INTO video VALUES(54325,’2008-06-18′,1234);
INSERT INTO video VALUES(34341,’2007-01-22′,1235);
INSERT INTO video VALUES(34342,’2007-01-22′,1235);
INSERT INTO video VALUES(34366,’2009-03-02′,1236);
INSERT INTO video VALUES(34367,’2009-03-02′,1236);
INSERT INTO video VALUES(34368,’2009-03-02′,1236);
INSERT INTO video VALUES(34369,’2009-03-02′,1236);
INSERT INTO video VALUES(44392,’2008-10-21′,1237);
INSERT INTO video VALUES(44397,’2008-10-21′,1237);
INSERT INTO video VALUES(59237,’2009-02-14′,1237);
INSERT INTO video VALUES(61388,’2007-01-25′,1239);
INSERT INTO video VALUES(61353,’2006-01-28′,1245);
INSERT INTO video VALUES(61354,’2006-01-28′,1245);
INSERT INTO video VALUES(61367,’2008-07-30′,1246);
INSERT INTO video VALUES(61369,’2008-07-30′,1246);
/*Create table membership*/
CREATE TABLE membership
(membership_id INTEGER PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
street VARCHAR(120),
city VARCHAR(50),
state VARCHAR(2),
zip VARCHAR(5),
balance DECIMAL(10,2));
/*Insert data into 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’,’Greenville’,’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’,’124 6th Street West’,’Williston’,’ND’,’58801′,0);
/*Create table rental*/
CREATE TABLE rental
(rental_id INTEGER PRIMARY KEY AUTO_INCREMENT,
rental_date DATE,
membership_id INTEGER,
FOREIGN KEY(membership_id) REFERENCES membership(membership_id));
/*Insert data into rental*/
INSERT INTO rental VALUES(1001,’2009-03-01′,103);
INSERT INTO rental VALUES(1002,’2009-03-01′,105);
INSERT INTO rental VALUES(1003,’2009-03-02′,102);
INSERT INTO rental VALUES(1004,’2009-03-02′,110);
INSERT INTO rental VALUES(1005,’2009-03-02′,111);
INSERT INTO rental VALUES(1006,’2009-03-02′,107);
INSERT INTO rental VALUES(1007,’2009-03-02′,104);
INSERT INTO rental VALUES(1008,’2009-03-03′,105);
INSERT INTO rental VALUES(1009,’2009-03-03′,111);
/*Create table detailrental*/
CREATE TABLE detail_rental
(rental_id INTEGER,
video_id INTEGER,
fee DECIMAL(5,2),
due_date DATE,
return_date DATE,
daily_late_fee DECIMAL(5,2),
PRIMARY KEY(rental_id, video_id),
FOREIGN KEY(rental_id) REFERENCES rental(rental_id),
FOREIGN KEY(video_id) REFERENCES video(video_id));
/*Insert data into dailyrental*/
INSERT INTO detail_rental VALUES(1001,34342,2,’2009-03-04′,’2009-03-02′,1);
INSERT INTO detail_rental VALUES(1001,61353,2,’2009-03-04′,’2009-03-03′,1);
INSERT INTO detail_rental VALUES(1002,59237,3.5,’2009-03-04′,’2009-03-04′,3);
INSERT INTO detail_rental VALUES(1003,54325,3.5,’2009-03-04′,’2009-03-09′,3);
INSERT INTO detail_rental VALUES(1003,61369,2,’2009-03-06′,’2009-03-09′,1);
INSERT INTO detail_rental VALUES(1003,61388,0,’2009-03-06′,’2009-03-09′,1);
INSERT INTO detail_rental VALUES(1004,44392,3.5,’2009-03-05′,’2009-03-07′,3);
INSERT INTO detail_rental VALUES(1004,34367,3.5,’2009-03-05′,’2009-03-07′,3);
INSERT INTO detail_rental VALUES(1004,34341,2,’2009-03-07′,’2009-03-07′,1);
INSERT INTO detail_rental VALUES(1005,34342,2,’2009-03-07′,’2009-03-05′,1);
INSERT INTO detail_rental VALUES(1005,44397,3.5,’2009-03-05′,’2009-03-05′,3);
INSERT INTO detail_rental VALUES(1006,34366,3.5,’2009-03-05′,’2009-03-04′,3);
INSERT INTO detail_rental VALUES(1006,61367,2,’2009-03-07′,NULL,1);
INSERT INTO detail_rental VALUES(1007,34368,3.5,’2009-03-05′,NULL,3);
INSERT INTO detail_rental VALUES(1008,34369,3.5,’2009-03-05′,’2009-03-05′,3);
INSERT INTO detail_rental VALUES(1009,54324,3.5,’2009-03-05′,NULL,3);
INSERT INTO detail_rental VALUES(1001,34366,3.5,’2009-03-04′,’2009-03-02′,3);