Question

In: Computer Science

I have attached the following script file Q1) List the full name of members, their DETAIL_DAILYLATEFEE...

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;

Solutions

Expert Solution

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 :

  • This SQL query will join the three tables membership,rental ,detailrental
  • and use the subquery to get the average of DETAIL_DAILYLATEFEE

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 :

  • This SQL query is using round(),YEAR() function and will return the result

Query result :

******************************************


Related Solutions

List customer id, customer full name (Last name, full name, state) for those customers that have...
List customer id, customer full name (Last name, full name, state) for those customers that have ordered more than once. List customers (order id, customer id, and customer last name) that had more than 2 -- products in their order. Order your result based on customer id followed by order id SQL SERVER DATABASE
All the question should be solved using IF statement Q1) List the Full Name, the city,...
All the question should be solved using IF statement Q1) List the Full Name, the city, and the state of all members, and order them by their first name in descending order (Z-A) if they are from 'KY' or by their last name in ascending order (A-Z) when they are from 'TN' state. Q2) List the total number of movies for each genre (FAMILY, ACTION, DRAMA, COMEDY). - Comment the following two lines if creating database in Mimir or Bluenose...
View the following C# Script carefully. Assume that the Script is enabled, is attached to an...
View the following C# Script carefully. Assume that the Script is enabled, is attached to an active GameObject, and addresses the appropriate namespaces first. Discuss when and how often during the game the Update() function will be executed. Discuss each line (from line 9 to 17, excluding the lines with curly brackets) to detail what the code inside the update function is doing. Be sure to outline how the variables are being used during your discussion. 1 public class Player...
View the following C# Script carefully. Assume that the Script is enabled, is attached to an...
View the following C# Script carefully. Assume that the Script is enabled, is attached to an active GameObject, and addresses the appropriate namespaces first. Discuss when and how often during the game the Update() function will be executed. Discuss each line (from line 9 to 17, excluding the lines with curly brackets) to detail what the code inside the update function is doing. Be sure to outline how the variables are being used during your discussion. 1 public class Player...
Use Vi text editor or ATOM to create a bash script file. Use the file name...
Use Vi text editor or ATOM to create a bash script file. Use the file name ayaan.sh. The script when ran it will execute the following commands all at once as script. Test your script file make sure it works. Here is the list of actions the script will do: It will create the following directory structure data2/new2/mondaynews off your home directory. inside the mondaynews, create 4 files sports.txt, baseball.txt, file1.txt and file2.txt. Make sure file1.txt and file2.txt are hidden...
Task 2.5: Write a script that will ask the user for to input a file name...
Task 2.5: Write a script that will ask the user for to input a file name and then create the file and echo to the screen that the file name inputted had been created 1. Open a new file script creafile.sh using vi editor # vi creafile.sh 2. Type the following lines #!/bin/bash echo ‘enter a file name: ‘ read FILENAME touch $FILENAME echo “$FILENAME has been created” 3. Add the execute permission 4. Run the script #./creafile.sh 5. Enter...
Create a Word file containing the following: The full name of the “test subject” The test...
Create a Word file containing the following: The full name of the “test subject” The test subject is a person, not yourself, who can say the names of the products. The best test subject is one who is adamant about being an expert who can distinguish brand A from B. You cannot be the test subject because you generate the sequence, and the test subject cannot see it for an unbiased test. Pets and babies are not allowed as they...
. The attached file contains the six variables. I have already attempted this answer and got...
. The attached file contains the six variables. I have already attempted this answer and got it wrong. Please ignore the checkmarks. Question Using the information below select all of the variables that are dichotomous (i.e., two categories). QN88 QN33 _SMOKER3 _SLEPTIM1 QN44 _RFBING5 Behavioral Risk Factor Surveillance System (BRFSS 2016) Calculated Variables https://www.cdc.gov/brfss/annual_data/2016/pdf/2016_calculated_variables_version4.pdf Youth Risk Behavior Surveillance System (YRBSS 2015) YRBS Data User's Guide https://www.cdc.gov/healthyyouth/data/yrbs/pdf/2015/2015_yrbs-data-users_guide_smy_combined.pdf
Write a brief shell script that will take in a specific file name, prompt the user...
Write a brief shell script that will take in a specific file name, prompt the user whether they would like to gzip, bzip2, or xz compress the file. Depending on response, the script then ought to compress the provided file with the corresponding method
Write a bash shell script that takes exactly one argument, a file name. If the number...
Write a bash shell script that takes exactly one argument, a file name. If the number of arguments is more or less than one, print a usage message. If the argument is not a file name, print another message. For the given file, print on the screen its content.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT