a. Find all movies with a cost less than or equal to the average moVies cost b. Write a query to display all the movies that have video copies c. Write a query to display the membership number, first name, last name, and balance of the memberships that have a rental on March-2009. d. Create a NewMember table with only using the membership name (Fname and Lname) and membership city. e. List movie title and movie year for movies that have a movie cost that is greater than all individual movie costs for movies with price description "weekly Special".
a. Find all movies with a cost less than or equal to the average moVies cost b. Write a query to display all the movies that have video copies c. Write a query to display the membership number, first name, last name, and balance of the memberships that have a rental on March-2009. d. Create a NewMember table with only using the membership name (Fname and Lname) and membership city. e. List movie title and movie year for movies that have a movie cost that is greater than all individual movie costs for movies with price description "weekly Special".
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
Related questions
Question
PLZ help with the following Using oracle sql command
Use subqueires for the following;
CREATE TABLE MEMBERSHIP (
MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEMNUM_PK PRIMARY KEY,
MEM_FNAME VARCHAR(30) NOT NULL,
MEM_LNAME VARCHAR(30) NOT NULL,
MEM_STREET VARCHAR(30),
MEM_CITY VARCHAR(10),
MEM_STATE CHAR(2),
MEM_ZIP CHAR(5),
MEM_BALANCE NUMBER(2)
);
CREATE TABLE RENTAL (
RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENTNUM_PK PRIMARY KEY,
RENT_DATE DATE,
MEM_NUM CHAR(3),
CONSTRAINT RENTAL_MEMNUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP
);
CREATE TABLE PRICE (
PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICECODE_PK PRIMARY KEY,
PRICE_DESC VARCHAR(20),
PRICE_RENTFEE NUMBER (3, 1),
PRICE_DAILYATFEE NUMBER(3, 1)
);
CREATE TABLE MOVIE (
MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIENUM_PK PRIMARY KEY,
MOVIE_NAME VARCHAR(30) NOT NULL,
MOVIE_YEAR CHAR(4),
MOVIE_COST NUMBER(5, 2),
MOVIE_GENRE VARCHAR(15),
PRICE_CODE CHAR(1),
CONSTRAINT MOVIE_PRICECODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE
);
CREATE TABLE VIDEO (
VID_NUM CHAR(5) CONSTRAINT VIDEO_VIDEONUM_PK PRIMARY KEY,
VID_INDATE DATE,
MOVIE_NUM CHAR(4),
CONSTRAINT VIDEO_MOVIENUM_FK FOREIGN KEY (MOVIE_NUM) REFERENCES MOVIE
);
CREATE TABLE DETAILRENTAL (
RENT_NUM CHAR(4),
VID_NUM CHAR(5),
DETAIL_FEE NUMBER(3, 1),
DETAIL_DUEDATE DATE,
DETAIL_RETURNDATE DATE,
DETAIL_DALYLATEFEE NUMBER(2),
CONSTRAINT DETAILRENTAL_RENTVID_PK PRIMARY KEY(RENT_NUM, VID_NUM),
CONSTRAINT DETAILRENTAL_RENT_FK FOREIGN KEY(RENT_NUM) REFERENCES RENTAL,
CONSTRAINT DETAILRENTAL_VID_FK FOREIGN KEY(VID_NUM) REFERENCES VIDEO
);
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', '38402', 5);
INSERT INTO RENTAL VALUES('1001', TO_DATE('01-MAR-09', 'DD-MM-YY'), '103');
INSERT INTO RENTAL VALUES('1002', TO_DATE('01-MAR-09', 'DD-MM-YY'), '105');
INSERT INTO RENTAL VALUES('1003', TO_DATE('02-MAR-09', 'DD-MM-YY'), '102');
INSERT INTO PRICE VALUES('1', 'Standard', 2.0, 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, 0.5);
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', '2');
INSERT INTO MOVIE VALUES('1239', 'Where Hope Dies', 1998, 25.49, 'DRAMA', '1');
INSERT INTO MOVIE VALUES('1245', 'Time to Burn', 2005, 45.49, 'ACTION', '3');
INSERT INTO MOVIE VALUES('1246', 'What He Doesnt Know', 2006, 58.29, 'COMEDY', '1');
INSERT INTO VIDEO VALUES('54321', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');
INSERT INTO VIDEO VALUES('54324', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');
INSERT INTO VIDEO VALUES('54325', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');
INSERT INTO VIDEO VALUES('34341', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');
INSERT INTO VIDEO VALUES('34342', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');
INSERT INTO VIDEO VALUES('34366', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('34367', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('34368', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('34369', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('44392', TO_DATE('21-OCT-08', 'DD-MM-YY'), '1237');
INSERT INTO DETAILRENTAL VALUES('1001', '34342', 2, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('02-MAR-09', 'DD-MM-YY'), 1);
INSERT INTO DETAILRENTAL VALUES('1001', '54321', 2, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('03-MAR-09', 'DD-MM-YY'), 1);
INSERT INTO DETAILRENTAL VALUES('1002', '54321', 3.5, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('04-MAR-09', 'DD-MM-YY'), 3);
INSERT INTO DETAILRENTAL VALUES('1003', '54325', 3.5, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('09-MAR-09', 'DD-MM-YY'), 3);
INSERT INTO DETAILRENTAL VALUES('1003', '54321', 2, TO_DATE('06-MAR-09', 'DD-MM-YY'), TO_DATE('09-MAR-09', 'DD-MM-YY'), 1);
INSERT INTO DETAILRENTAL VALUES('1003', '44392', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 3);
INSERT INTO DETAILRENTAL VALUES('1003', '34367', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 3);
I
Transcribed Image Text:a. Find all movies with a cost less than or equal to the average movies cost
b. Write a query to display all the movies that have video copies
c. Write a query to display the membership number, first name, last name,
and balance of the memberships that have a rental on March-2009.
d. Create a NewMember table with only using the membership name (Fname
and Lname) and membership city.
e. List movie title and movie year for movies that have a movie cost that
is greater than all individual movie costs for movies with price
description "weekly Special".
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 5 images
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY