In: Computer Science
I have attached the following script file
Q1) List the full name of members, their DETAIL_DAILYLATEFEE and
the average of DETAIL_DAILYLATEFEE for all members whose
DETAIL_DAILYLATEFEE is less than the average of all
DETAIL_DAILYLATEFEE.
Your query MUST contain a subquery.
Your result should be as shown below. Note that each member is only
listed once.
Required: AVG(DETAIL_DAILYLATEFEE)
CONCAT
DISTINCT
Restricted: GROUP BY
Q2) Management is also interested in finding out the age of
their movies (i.e. how old the movies are) in number of years
compared to the average age (in rounded number of years) of all
movies in their stock. They want a report that lists information
about the title, year, genre and how many 'Years Old' a movie is,
for all movies that are newer than the average age (in rounded
years) of all movies. (For example, if the average age of all
movies is X, list all movies with age < X)
Required:
ROUND(AVG(MOVIE_YEAR))
YEAR(NOW()) - MOVIE_YEAR
-- Comment the following two lines if creating database in Mimir
or Bluenose
create schema if not exists DVD_vidrental;
use DVD_vidrental;
DROP TABLE IF EXISTS `detailrental`;
DROP TABLE IF EXISTS `rental`;
DROP TABLE IF EXISTS `video`;
DROP TABLE IF EXISTS `movie`;
DROP TABLE IF EXISTS `price`;
DROP TABLE IF EXISTS `membership`;
CREATE TABLE `membership` (
`MEM_NUM` decimal(8,0) NOT NULL,
`MEM_FNAME` varchar(30) NOT NULL,
`MEM_LNAME` varchar(30) NOT NULL,
`MEM_STREET` varchar(120) DEFAULT NULL,
`MEM_CITY` varchar(50) DEFAULT NULL,
`MEM_STATE` char(2) DEFAULT NULL,
`MEM_ZIP` char(5) DEFAULT NULL,
`MEM_BALANCE` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`MEM_NUM`)
);
CREATE TABLE `price` (
`PRICE_CODE` decimal(2,0) NOT NULL,
`PRICE_DESCRIPTION` varchar(20) NOT NULL,
`PRICE_RENTFEE` decimal(5,2) DEFAULT NULL,
`PRICE_DAILYLATEFEE` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`PRICE_CODE`)
);
CREATE TABLE `movie` (
`MOVIE_NUM` decimal(8,0) NOT NULL,
`MOVIE_TITLE` varchar(75) NOT NULL,
`MOVIE_YEAR` decimal(4,0) DEFAULT NULL,
`MOVIE_COST` decimal(5,2) DEFAULT NULL,
`MOVIE_GENRE` varchar(50) DEFAULT NULL,
`PRICE_CODE` decimal(2,0) DEFAULT NULL,
PRIMARY KEY (`MOVIE_NUM`),
KEY `PRICE_CODE` (`PRICE_CODE`),
CONSTRAINT `movie_ibfk_1` FOREIGN KEY (`PRICE_CODE`) REFERENCES
`price` (`PRICE_CODE`)
);
CREATE TABLE `video` (
`VID_NUM` decimal(8,0) NOT NULL,
`VID_INDATE` date DEFAULT NULL,
`MOVIE_NUM` decimal(8,0) DEFAULT NULL,
PRIMARY KEY (`VID_NUM`),
KEY `MOVIE_NUM` (`MOVIE_NUM`),
CONSTRAINT `video_ibfk_1` FOREIGN KEY (`MOVIE_NUM`) REFERENCES
`movie` (`MOVIE_NUM`)
);
CREATE TABLE `rental` (
`RENT_NUM` decimal(8,0) NOT NULL,
`RENT_DATE` date DEFAULT NULL,
`MEM_NUM` decimal(8,0) DEFAULT NULL,
PRIMARY KEY (`RENT_NUM`),
KEY `MEM_NUM` (`MEM_NUM`),
CONSTRAINT `rental_ibfk_1` FOREIGN KEY (`MEM_NUM`) REFERENCES
`membership` (`MEM_NUM`)
);
CREATE TABLE `detailrental` (
`RENT_NUM` decimal(8,0) NOT NULL,
`VID_NUM` decimal(8,0) NOT NULL,
`DETAIL_FEE` decimal(5,2) DEFAULT NULL,
`DETAIL_DUEDATE` date DEFAULT NULL,
`DETAIL_RETURNDATE` date DEFAULT NULL,
`DETAIL_DAILYLATEFEE` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`RENT_NUM`,`VID_NUM`),
KEY `VID_NUM` (`VID_NUM`),
CONSTRAINT `detailrental_ibfk_1` FOREIGN KEY (`RENT_NUM`)
REFERENCES `rental` (`RENT_NUM`),
CONSTRAINT `detailrental_ibfk_2` FOREIGN KEY (`VID_NUM`) REFERENCES
`video` (`VID_NUM`)
);
START TRANSACTION;
INSERT INTO `membership` VALUES (102,'TAMI','DAWSON','2632 TAKLI
CIRCLE','NORENE','TN','37136',11.00),
(103,'CURT','KNIGHT','4025 CORNELL
COURT','FLATGAP','KY','41219',6.00),
(104,'JAMAL','MELENDEZ','788 EAST 145TH
AVENUE','QUEBECK','TN','38579',0.00),
(105,'IVA','MCCLAIN','6045 MUSKET BALL
CIRCLE','SUMMIT','KY','42783',15.00),
(106,'MIRANDA','PARKS','4469 MAXWELL
PLACE','GERMANTOWN','TN','38183',0.00),
(107,'ROSARIO','ELLIOTT','7578 DANNER
AVENUE','COLUMBIA','TN','38402',5.00),
(108,'MATTIE','GUY','4390 EVERGREEN
STREET','LILY','KY','40740',0.00),
(109,'CLINT','OCHOA','1711 ELM
STREET','GREENEVILLE','TN','37745',10.00),
(110,'LEWIS','ROSALES','4524 SOUTHWIND
CIRCLE','COUNCE','TN','38326',0.00),
(111,'STACY','MANN','2789 EAST COOK
AVENUE','MURFREESBORO','TN','37132',8.00),
(112,'LUIS','TRUJILLO','7267 MELVIN
AVENUE','HEISKELL','TN','37754',3.00),
(113,'MINNIE','GONZALES','6430 VASILI
DRIVE','WILLISTON','TN','38076',0.00);
INSERT INTO `price` VALUES (1,'Standard',3.00,1.00),
(2,'New Release',4.50,3.00),(3,'Discount',2.50,1.00),
(4,'Weekly Special',2.00,0.50);
INSERT INTO `movie` VALUES (1234,'The Cesar Family
Christmas',2014,39.95,'FAMILY',2),
(1235,'Smokey Mountain
Wildlife',2011,59.95,'ACTION',3),(1236,'Richard
Goodhope',2015,59.95,'DRAMA',2),
(1237,'Beatnik Fever',2014,29.95,'COMEDY',2),(1238,'Constant
Companion',2015,89.95,'DRAMA',NULL),
(1239,'Where Hope Dies',2005,25.49,'DRAMA',3),(1245,'Time to
Burn',2015,45.49,'ACTION',3),
(1246,'What He Doesn\'t Know',2013,58.29,'COMEDY',1);
INSERT INTO `video` VALUES
(34341,'2014-01-22',1235),(34342,'2014-01-22',1235),
(34366,'2016-03-02',1236),(34367,'2016-03-02',1236),(34368,'2016-03-02',1236),
(34369,'2016-03-02',1236),(44392,'2015-10-21',1237),(44397,'2015-10-21',1237),
(54321,'2015-06-18',1234),(54324,'2015-06-18',1234),(54325,'2015-06-18',1234),
(59237,'2016-02-14',1237),(61353,'2013-01-28',1245),(61354,'2013-01-28',1245),
(61367,'2015-07-30',1246),(61369,'2015-07-30',1246),(61388,'2014-01-25',1239);
INSERT INTO `rental` VALUES
(1001,'2016-03-01',103),(1002,'2016-03-01',105),
(1003,'2016-03-02',102),(1004,'2016-03-02',110),(1005,'2016-03-02',111),
(1006,'2016-03-02',107),(1007,'2016-03-02',104),(1008,'2016-03-03',105),(1009,'2016-03-03',111);
INSERT INTO `detailrental` VALUES
(1001,34342,2.00,'2016-03-04','2016-03-02',NULL),
(1001,34366,3.50,'2016-03-04','2016-03-02',3.00),(1001,61353,2.00,'2016-03-04','2016-03-03',1.00),
(1002,59237,3.50,'2016-03-04','2016-03-04',3.00),(1003,54325,3.50,'2016-03-04','2016-03-09',3.00),
(1003,61369,2.00,'2016-03-06','2016-03-09',1.00),(1003,61388,0.00,'2016-03-06','2016-03-09',1.00),
(1004,34341,2.00,'2016-03-07','2016-03-07',1.00),(1004,34367,3.50,'2016-03-05','2016-03-07',3.00),
(1004,44392,3.50,'2016-03-05','2016-03-07',3.00),(1005,34342,2.00,'2016-03-07','2016-03-05',1.00),
(1005,44397,3.50,'2016-03-05','2016-03-05',3.00),(1006,34366,3.50,'2016-03-05','2016-03-04',3.00),
(1006,61367,2.00,'2016-03-07',NULL,1.00),(1007,34368,3.50,'2016-03-05',NULL,3.00),
(1008,34369,3.50,'2016-03-05','2016-03-05',3.00),
(1009,54324,3.50,'2016-03-05',NULL,3.00);
COMMIT;
Question 1:
SQL query :
select distinct(concat(MEM_FNAME,' ',MEM_LNAME)) as 'Member
Name',DETAIL_DAILYLATEFEE,
(select avg(DETAIL_DAILYLATEFEE) from detailrental) as
AVG_DETAIL_DAILYLATEFEE
from membership,rental ,detailrental
where
membership.MEM_NUM=rental.MEM_NUM and
rental.RENT_NUM=detailrental.RENT_NUM and
DETAIL_DAILYLATEFEE < (select avg(DETAIL_DAILYLATEFEE) from
detailrental);
Explanation :
Query result :
******************************************
Question 2:
SQL query :
select MOVIE_TITLE,MOVIE_YEAR,MOVIE_GENRE,YEAR(NOW()) -
MOVIE_YEAR as YearsOld,
(select ROUND(AVG(YEAR(NOW()) - MOVIE_YEAR)) from movie) as
AVGMOVIEAGE
from movie
group by MOVIE_TITLE,MOVIE_YEAR,MOVIE_GENRE
having YearsOld< AVGMOVIEAGE;
Explanation :
Query result :
******************************************