EliteVideo is a startup company providing concierge DVD kiosk service in upscale
ID: 3758694 • Letter: E
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.
1.Write a query to display the movie title, movie year, and movie genre for all movies.
2.Write a query to display the movie title, movie year, and movie genre for all movies sorted by movie genre in ascending order, then sorted by movie year in descending order within genre.
3.Write a query to display the movie title, movie year, and movie cost for all movies that contain the word “hope” anywhere in the title. Sort the results in ascending order by title.
4. Write a query to display the movie title, movie year, and movie genre for all action movies.
5. Write a query to display the movie number, movie title, and movie cost for all movies with a cost greater than $40.
6. Write a query to display the movie number, movie title, movie cost, and movie genre for all movies that are either action or comedy movies and that have a cost that is less than $50. Sort the results in ascending order by genre.
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.
13.Write a query to display the movie title, movie year, and movie cost for all movies that have a cost between $44.99 and $49.99.
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.
ExercisesExplanation / Answer
17. Select min(mem_balance), max(mem_balance), avg(mem_balance) from membership m, rental r where m.mem_num=r.mem_num;
18. Select r.rent_num, rental_date, v.vid_num, movie_title, detail_duedate, detail_returndate from rental r, detailrental dr, video v, movie m where m.movie_num=v.movie_num, v.vid_num=dr.vid_num, r.rent_num=dr.rent_num where dr. detail_duedate< detail_returndate;
19. Select r.rent_num, rental_date, v.vid_num, movie_title, detail_duedate, detail_returndate, detail_fee, detail_returndate-detail_duedate as DaysPastDueDate from rental r, detailrental dr, video v, movie m where m.movie_num=v.movie_num, v.vid_num=dr.vid_num, r.rent_num=dr.rent_num where dr. detail_duedate< detail_returndate;
20. Select mem_num, mem_lname, sum(detail_fee) from membership m, rental r, detailrental dr, video v, movie mv where m.mem_num=r.mem_num, r.rent_num=dr.rent_num, dr.vid_num=v.vid_num, mv.movie_num=v.movie_num;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.