USA: +1(669)289-8683 
Sign in
SQL/MS ACCESS 30 Questions. Some are long and some are short
Assignment 3: SQL Practice
Problem 1 through 30
Note:
• Please be loyal to the data model provided; in the meantime make necessary decisions (e.g., on appropriate
data types etc.). Document and justify your decisions, whenever necessary.
• Please submit a single file that includes answers to all questions.
• When you answer question, you can save your SQL code in a document in case you need to repeat
• After you create database, please save one copy the database in case you make mistakes
RentalAVideo is a movie rental store. It needs a database system to track the rental of movies to its members.
RentalAVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10
copies of the movie “Gone with the Wind”. “Gone with the Wind” 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 RDM is provided below.
RentalAVideo ERD (Figure 1)
1. Write the SQL code to create the table structures for the entities shown in Figure 1. The
structures should contain the specified attributes. Use data types that would be appropriate for
the data that will need to be stored in each attribute. Enforce primary key and foreign key
constraints as indicated by the ERD.
2. The following tables provide a very small portion of the data that will be kept in the database.
This data needs to be inserted into the database for testing purposes. Write the INSERT
commands necessary to place the following data in the tables that were created in problem 1.
Note: For each table, you can write insert statements for 2 rows. If it works, you can copy these
data to Excel file. Then, you can import the Excel file to MS Access using the method provided
in Access tutorial slide # 10. But please make sure to create primary key for each table and
relationships between tables shown in Figure 1 after you import data
MEMBERSHIP
Mem_ Mem_ Mem_
Num Fname Lname
102 Tami
Dawson
103 Curt
Knight
104 Jamal
Melendez
Mem_Street
Mem_City
2632 Takli Circle
4025 Cornell Court
788 East 145th Avenue
Norene
Flatgap
Quebeck
Mem_
State
TN
KY
TN
Mem_
Mem_Zip Balance
37136
41219
38579
11
6
0
105 Iva
106 Miranda
107 Rosario
108 Mattie
109 Clint
110 Lewis
111 Stacy
112 Luis
113 Minnie
Mcclain
Parks
Elliott
Guy
Ochoa
Rosales
Mann
Trujillo
Gonzales
RENTAL
Rent_Num Rent_Date
1001 01-MAR-09
1002 01-MAR-09
1003 02-MAR-09
1004 02-MAR-09
1005 02-MAR-09
1006 02-MAR-09
1007 02-MAR-09
1008 03-MAR-09
1009 03-MAR-09
6045 Musket Ball Circle
4469 Maxwell Place
7578 Danner Avenue
4390 Evergreen Street
1711 Elm Street
4524 Southwind Circle
2789 East Cook Avenue
7267 Melvin Avenue
6430 Vasili Drive
Summit
Germantown
Columbia
Lily
Greeneville
Counce
Murfreesboro
Heiskell
Williston
KY
TN
TN
KY
TN
TN
TN
TN
TN
42783
38183
38402
40740
37745
38326
37132
37754
38076
15
0
5
0
10
0
8
3
0
Mem_Num
103
105
102
110
111
107
104
105
111
DETAILRENTAL
Rent_Num Vid_Num Detail_Fee Detail_Duedate
1001
34342
2 04-MAR-09
1001
61353
2 04-MAR-09
1002
59237
3.5 04-MAR-09
1003
54325
3.5 04-MAR-09
1003
61369
2 06-MAR-09
1003
61388
0 06-MAR-09
1004
44392
3.5 05-MAR-09
1004
34367
3.5 05-MAR-09
1004
34341
2 07-MAR-09
1005
34342
2 07-MAR-09
1005
44397
3.5 05-MAR-09
1006
34366
3.5 05-MAR-09
1006
61367
2 07-MAR-09
1007
34368
3.5 05-MAR-09
1008
34369
3.5 05-MAR-09
1009
54324
3.5 05-MAR-09
1001
34366
3.5 04-MAR-09
Detail_Returndate
02-MAR-09
03-MAR-09
04-MAR-09
09-MAR-09
09-MAR-09
09-MAR-09
07-MAR-09
07-MAR-09
07-MAR-09
05-MAR-09
05-MAR-09
04-MAR-09
05-MAR-09
02-MAR-09
Detail_Dailylatefee
1
1
3
3
1
1
3
3
1
1
3
3
1
3
3
3
3
VIDEO
Vid_Num Vid_Indate
54321 18-JUN-08
54324 18-JUN-08
54325 18-JUN-08
34341 22-JAN-07
34342 22-JAN-07
34366 02-MAR-09
34367 02-MAR-09
34368 02-MAR-09
34369 02-MAR-09
44392 21-OCT-08
44397 21-OCT-08
59237 14-FEB-09
61388 25-JAN-07
61353 28-JAN-06
61354 28-JAN-06
61367 30-JUL-08
61369 30-JUL-08
Movie_Num
1234
1234
1234
1235
1235
1236
1236
1236
1236
1237
1237
1237
1239
1245
1245
1246
1246
MOVIE
Movie_Num Movie_Name
The Cesar Family
1234 Christmas
1235 Smokey Mountain Wildlife
1236 Richard Goodhope
1237 Beatnik Fever
1238 Constant Companion
1239 Where Hope Dies
1245 Time to Burn
1246 What He Doesn’t Know
PRICE
Price_Code Price_Description
1 Standard
2 New Release
3 Discount
4 Weekly Special
Movie_Year Movie_Cost Movie_Genre
Price_Rentfee
2
3.5
1.5
1
Price_Code
2007
39.95 FAMILY
2
2004
2008
2007
2008
1998
2005
2006
59.95 ACTION
59.95 DRAMA
29.95 COMEDY
89.95 DRAMA
25.49 DRAMA
45.49 ACTION
58.29 COMEDY
1
2
2
2
3
1
1
Price_Dailylatefee
1
3
1
.5
For questions 3– 31, use the tables that were created in Problem 1 and the data that was loaded into those tables in
Problem 2.
3. Write the SQL command to change the movie year for movie number 1245 to 2006.
4. Write the SQL command to change the price code for all Action movies to price code 3.
5. Write a single SQL command to increase all price rental fee values by $0.50.
6. Write a query to display the movie title, movie year, and movie genre for all movies (result shown in
Figure 2).
Figure 2 All Movies
7. Write a query to display the movie year, movie title, and movie cost sorted by movie year in descending
order (result shown in Figure 3)
Figure 3 Movies by year
8. 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 4).
Figure 4 Movies with multicolumn sort
9. 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 5).
Figure 5 Movies starting with R
10. 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 6).
Figure 6 Movies with “Hope” in the title
11. Write a query to display the movie title, movie year, and movie genre for all action movies (result shown
in Figure 7).
Figure 7 Action movies
12. 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 8).
P8 Movies costing less than $40
13. Write a query to display the movie number, movie title, movie cost, and movie genre for movies that are
either action or comedy movies or movies that have a cost that is less than $50. Sort the results in ascending
order by genre. (Result shown in Figure 9.)
Figure 9 Action or comedy movies costing less than $50
14. Write a query to display the movie genre and the number of movies in each genre (result shown in
Figure 10).
Figure 10 Number of movies in genre
15. Write a query to display the average cost of all of the movies (result shown in Figure 11).
Figure 11 Average movie cost
16. Write a query to display the movie genre and average cost of movies in each genre (result shown in
Figure 12).
Figure 12 Average movie cost by genre
17. 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 13).
Figure 13 Rental fees for movies
Movie_Title
What He Doesn’t Know
The Cesar Family Christmas
Richard Goodhope
Beatnik Fever
Constant Companion
Smokey Mountain Wildlife
Where Hope Dies
Time to Burn
Movie_Genre
COMEDY
FAMILY
DRAMA
COMEDY
DRAMA
ACTION
DRAMA
ACTION
Price_Descriptioin
Standard
New Release
New Release
New Release
New Release
Discount
Discount
Discount
Price_Rentfee
2.5
4
4
4
4
2
2
2
18. 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 14).
Figure 14 Average rental fee by genre
Movie_Genre
AVERAGE_RENTAL_FEE
FAMILY
4
COMEDY
3.25
ACTION
2
DRAMA
3.33
19. 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 15).
Figure 15 Breakeven rentals
MOVIE_TITLE
What He Doesn’t Know
Richard Goodhope
The Cesar Family
Beatnik Fever
Constant Companion
Where Hope Dies
Time to Burn
Smokey Mountain Wildlife
MOVIE_YEAR BREAKEVEN_RENTALS
2006
23.32
2008
14.99
2007
9.99
2007
7.49
2008
22.49
1998
12.75
2006
22.75
2004
29.98
20. Write a query to display the movie title and movie year for all movies that have a price code (result shown
in Figure v 16).
Figure 16 Movies with a price
MOVIE_TITLE
MOVIE_YEAR
Beatnik Fever
2007
Constant Companion
2008
Richard Goodhope
2008
Smokey Mountain Wildlife
2004
Time to Burn
2006
What He Doesn’t Know
2006
Where Hope Dies
1998
The Cesar Family Christmas
2007
21. 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 17).
Figure 17 Movies costs within a range
22. 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 18).
Figure 18 Movies with specific genres
Movie_Title
The Cesar Family
Movie_Year
2007
Price_Desciption
New Release
Price_Rentfee
Movie_Genre
4
FAMILY
Richard Goodhope
2008
New Release
4
DRAMA
Beatnik Fever
2007
New Release
4
COMEDY
Constant Companion
2008
New Release
4
DRAMA
Where Hope Dies
1998
Discount
2
DRAMA
What He Doesn’t Know
2006
Standard
2.5
COMEDY
23. 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 19).
Figure 19 Movies without videos
24. 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 20).
Figure 20 Balances of memberships with rentals
25. Write a query to display the minimum balance, maximum balance, and average balance for memberships
that have a rental (result shown in Figure 21).
Figure 21 Minimum, maximum, and average balances
MINIMUM_BALANCE
MAXIMUM_BALANCE
0
AVERAGE_BALANCE
15
6.43
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 22).
Figure 22 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 23.)
Figure 23 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 24).
Figure 24 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 25). 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 25 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 26). 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 26 Movie difference from genre average
