EliteVideo is a startup company providing concierge DVD kiosk service in upscale

Question

EliteVideo is a startup company providing concierge DVD kiosk service in upscale neighborhoods. EliteVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have ten copies of the movie “Titanic”. “Titanic” would be one MOVIE and each copy would be a VIDEO. A rental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is shown in the figure below.

Write the SQL code to obtain the information requested in each question.

7. Write a query to display the movie number, and movie description for all movies where the movie description is a combination of the movie title, movie year and movie genre with the movie year enclosed in parentheses.

8. Write a query to display the movie genre and average cost of movies in each genre.

9.Write a query to display the movie title, movie genre, price description, and price rental fee for all movies with a price code.

10. Write a query to display the movie genre and average price rental fee for movies in each genre that have a price.

11. Write a query to display the movie title, movie year, and the movie cost divided by the price rental fee for each movie that has a price to determine the number of rentals it will take to break even on the purchase of the movie.

12.Write a query to display the movie title and movie year for all movies that have a price code.

14.Write a query to display the movie title, movie year, price description, and price rental fee for all movies that are in the genres Family, Comedy, or Drama.

15.Write a query to display the movie number, movie title, and movie year for all movies that do not have a video.

16.Write a query to display the membership number, first name, last name, and balance of the memberships that have a rental.

17.Write a query to display the minimum balance, maximum balance, and average balance for memberships that have a rental.

18.Write a query to display the rental number, rental date, video number, movie title, due date, and return date for all videos that were returned after the due date. Sort the results by rental number and movie title.

19. Write a query to display the rental number, rental date, video number, movie title, due date, return date, detail fee, and number of days past the due date that the video was returned for each video that was returned after the due date.

20. Write a query to display the membership number, last name, and total rental fees earned from that membership. The total rental fee is the sum of all of the detail fees (without the late fees) from all movies that the membership has rented.

TYPE THE ANSWER PLEASE!!! PLEASE DON'T HAND WRITE IT.

Explanation / Answer

8.

select movie_genre,avg(movie_cost) from movies;

9.

select M.movie_title, M.movie_genre, P.price_description, P.price_rentfee,P.price_code from movie M,price P where P.price_code=M.price_code;

12.

select movie_title and movie_year from movie where price_code is not null;

15.

select movie_num, movie_title, and movie_year from movie M,video V and V.movie_num=M.movie_num and V.vid_num is null;

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.