Part I.

The ERD of movie rental database is provided below. The database is provided on Blackboard under assignment 1 link with the name of Movie_Rental.rtf. The first 20 questions are worth 2.5 each and last 10 questions are worth 5 marks each. Run the script and then run the queries after solving in the questions below.

1. Write the SQL command to change the movie year for movie number 1245 to 2008.

2. Write the SQL command to change the price code for all Action movies to price code 3.

3. Write a single SQL command to increase all price rental fee values by $0.50.

4. Write a query to display the movie title, movie year, and movie genre for all movies (result shown in Figure P7.72).

Figure P7.72 All Movies

5. 

   

5. Write a query to display the movie year, movie title, and movie cost sorted by movie year in descending order (result shown in Figure P7.73).

Figure P7.73 Movies by year

6. 

   

   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 (result shown in Figure P7.74).

Figure P7.74 Movies with multicolumn sort

7. Write a query to display the movie number, movie title, and price code for all movies with a title that starts with the letter R (result shown in Figure P7.75).

Figure P7.75 Movies starting with R

8. 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 (result shown in figure P7.76).

Figure P7.76 Movies with Hope in the title

9. Write a query to display the movie title, movie year, and movie genre for all action movies (result shown in Figure P7.77).

Figure P7.77 Action movies

10. Write a query to display the movie number, movie title, and movie cost for all movies with a cost greater than $40 (result shown in Figure P7.78).

P7.78 Movies costing more than $40

11. 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. (Result shown in Figure P7.79.)

Figure P7.79 Action or comedy movies costing less than $50

12. 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 (result shown in Figure P7.80).

Figure P7.80 Movies with concatenated descriptions

13. Write a query to display the movie genre and the number of movies in each genre (result shown in Figure P7.81).

Figure P7.81 Number of movies in genre

14. Write a query to display the average cost of all of the movies (result shown in Figure P7.82).

Figure P7.82 Average movie cost

15. Write a query to display the movie genre and average cost of movies in each genre (result shown in Figure P7.83).

Figure P7.83 Average movie cost by genre

16. Write a query to display the movie title, movie genre, price description, and price rental fee for all movies with a price code (result shown in Figure P7.84).

Figure P7.84 Rental fees for movies

17. Write a query to display the movie genre and average price rental fee for movies in each genre that have a price (result shown in Figure P7.85).

Figure P7.85 Average rental fee by genre

18. 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 (result shown in Figure P7.86).

Figure P7.86 Breakeven rentals

19. Write a query to display the movie title and movie year for all movies that have a price code (result shown in Figure P7.87).

P7.87 Movies with a price

20. 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 (result shown in Figure P7.88).

Figure P7.88 Movies costs within a range

21. 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 (result shown in Figure P7.89).

Figure P7.89 Movies with specific genres

22. Write a query to display the movie number, movie title, and movie year for all movies that do not have a video (result shown in Figure P7.90).

Figure P7.90 Movies without videos

23. Write a query to display the membership number, first name, last name, and balance of the memberships that have a rental (result shown in Figure P7.91).

Figure P7.91 Balances of memberships with rentals

24. Write a query to display the minimum balance, maximum balance, and average balance for memberships that have a rental (result shown in Figure P7.92).

Figure P7.92 Minimum, maximum, and average balances

25. Write a query to display the membership name (concatenate the first name and last name with a space between them into a single column), membership address (concatenate the street, city, state, and zip codes into a single column with spaces (result shown in Figure P7.93).

Figure P7.93 Concatenated membership data

26. 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 (result shown in Figure P7.94).

Figure P7.94 Late video returns

27. 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. Sort the results by rental number and movie title. (Result shown in Figure P7.95.)

Figure P7.95 Number of days late

28. Write a query to display the rental number, rental date, movie title, and detail fee for each movie that was returned on or before the due date (result shown in Figure P7.96).

Figure P7.96 Actual rental fees charged

29. Write a query to display the membership number, last name, and total rental fees earned from that membership (result shown in Figure P7.97). 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.

Figure P7.97 Total rental fees paid by membership

30. Write a query to display the movie number, movie genre, average movie cost of movies in that genre, movie cost of that individual movie, and the percentage difference between the average movie cost and the individual movie cost (result shown in Figure P7.98).

Note: the percentage difference is calculated as the cost of the individual movie minus the average cost of movies in that genre, divided by the average cost of movies in that genre multiplied by 100. For example, if the average cost of movies in the Family genre is $25, if a given Family movie cost $26, then the calculation would be ((26 25) / 25 * 100), which would work out to be 4.00%. This indicates that this movie costs 4% more than the average Family movie.

Figure P7.98 Movie difference from genre average