List movie genres of movies that have not yet been returned (That is, rentals that have no return date)

Do not use any outer join in your query. Each genre should be listed only once.

2) The Rental Store's management wants a report that lists all details of members who returned their rented DVDs late.

3)List the full name of members, their DETAIL_DAILYLATEFEE and the average of DETAIL_DAILYLATEFEE for allmembers 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.

4)

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

Your query should return:

Transcribed Image Text:

The DVD vidrental database contains 6 tables, and you can find the database diagram below. Following are some details about the tables in this database: • price table contains the rental fee and daily late fee for various kinds of movie collections e.g. New Release, Discount, Standard etc. • movie table contains the details of a movie such as its title, genre and the year it was released. Note that there could be multiple dvds of a movie and details of each dvd is stored in the video table. • membership table contains details of members who can rent videos rental table contains details of each rental. Note that several movies [i.e. their dvd videos) can be rented by a member on one rental. The detail of each video rented including the due date, fee and late fee for that rental is included in the detailrental table. price PRICE_CODE DECIMAL(2,0) PRICE_DESCRIPTION VARCHAR(20) PRICE_RENTFEE DECIMAL(5,2) PRICE_DAILYLATEFEE DECIMAL(5,2) Indexes membership MEM_NUM DECIMAL(8,0) MEM_FNAME VARCHAR(30) ◆MEM_LNAME VARCHAR(30) MEM_STREET VARCHAR(120) MEM_CITY VARCHAR(50) MEM_STATE CHAR(2) MEM_ZIP CHAR(5) MEM_BALANCE DECIMAL(10,2) Indexes movie MOVIE_NUM DECIMAL(8,0) MOVIE_TITLE VARCHAR(75) MOVIE YEAR DECIMAL(4,0) MOVIE_COST DECIMAL(5,2) MOVIE_GENRE VARCHAR(50) PRICE_CODE DECIMAL(2,0) Indexes VideoRental Database (Videorental_db.sql) rental RENT_NUM DECIMAL(8,0) RENT_DATE DATE MEM_NUM DECIMAL(8,0) Indexes video VID_NUM DECIMAL(8,0) VID_INDATE DATE MOVIE_NUM DECIMAL(8,0) Indexes detailrental RENT_NUM DECIMAL(8,0) VID_NUM DECIMAL(8,0) DETAIL FEE DECIMAL(5,2) DETAIL DUEDATE DATE DETAIL_RETURNDATE DATE DETAIL_DAILYLATEFEE DECIMAL(5,2) Indexes The DVD vidrental database contains 6 tables, and you can find the database diagram below. Following are some details about the tables in this database: • price table contains the rental fee and daily late fee for various kinds of movie collections e.g. New Release, Discount, Standard etc. • movie table contains the details of a movie such as its title, genre and the year it was released. Note that there could be multiple dvds of a movie and details of each dvd is stored in the video table. • membership table contains details of members who can rent videos rental table contains details of each rental. Note that several movies [i.e. their dvd videos) can be rented by a member on one rental. The detail of each video rented including the due date, fee and late fee for that rental is included in the detailrental table. price PRICE_CODE DECIMAL(2,0) PRICE_DESCRIPTION VARCHAR(20) PRICE_RENTFEE DECIMAL(5,2) PRICE_DAILYLATEFEE DECIMAL(5,2) Indexes membership MEM_NUM DECIMAL(8,0) MEM_FNAME VARCHAR(30) ◆MEM_LNAME VARCHAR(30) MEM_STREET VARCHAR(120) MEM_CITY VARCHAR(50) MEM_STATE CHAR(2) MEM_ZIP CHAR(5) MEM_BALANCE DECIMAL(10,2) Indexes movie MOVIE_NUM DECIMAL(8,0) MOVIE_TITLE VARCHAR(75) MOVIE YEAR DECIMAL(4,0) MOVIE_COST DECIMAL(5,2) MOVIE_GENRE VARCHAR(50) PRICE_CODE DECIMAL(2,0) Indexes VideoRental Database (Videorental_db.sql) rental RENT_NUM DECIMAL(8,0) RENT_DATE DATE MEM_NUM DECIMAL(8,0) Indexes video VID_NUM DECIMAL(8,0) VID_INDATE DATE MOVIE_NUM DECIMAL(8,0) Indexes detailrental RENT_NUM DECIMAL(8,0) VID_NUM DECIMAL(8,0) DETAIL FEE DECIMAL(5,2) DETAIL DUEDATE DATE DETAIL_RETURNDATE DATE DETAIL_DAILYLATEFEE DECIMAL(5,2) Indexes

Answer rating: 100% (QA)

To list movie genres of movies that have not yet been returned That is rentals that have no return d... View the full answer