2
Don't use plagiarized sources. Get Your Custom Essay on
SQL
Just from $13/Page
>Assignment
3
SQL
Problem
1
through 19
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 1
0
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_ Num
|
Mem_ Fname
|
Mem_ Lname
|
Mem_Street
|
Mem_City
|
Mem_ State
|
Mem_Zip
|
Mem_ Balance
|
|
|
102
|
Tami
|
Dawson
|
2
6
32 Takli Circle
|
Norene
|
|
|
|
|
|
|
|
|
TN
|
37136
|
11
|
|
|
103
|
Curt
|
Knight
|
4
02
5
Cornell Court
|
Flatgap
|
|
|
KY
|
41219
|
6
|
|
|
104
|
Jamal
|
Melendez
|
7
8
8 East 145th Avenue
|
Quebeck
|
TN
38579
|
|
|
|
|
|
|
0
|
|
|
|
105
|
Iva
|
Mcclain
|
6045 Musket Ball Circle
|
Summit
|
KY
42783
|
|
15
|
|
106
|
Miranda
|
Parks
|
4469 Maxwell Place
|
Germantown
|
TN
38183
|
0
|
|
107
|
Rosario
|
Elliott
|
7578 Danner Avenue
|
Columbia
|
TN
38402
|
5
|
|
108
|
Mattie
|
Guy
|
4390 Evergreen Street
|
Lily
|
KY
40740
|
0
|
109
|
Clint
|
Ochoa
|
1711 Elm Street
|
Greeneville
|
TN
37745
|
10
|
|
|
110
|
Lewis
|
Rosales
|
4524 Southwind Circle
|
Counce
|
TN
38326
|
0
|
|
|
111
|
Stacy
|
Mann
|
2789 East Cook Avenue
|
Murfreesboro
|
TN
37132
|
8
|
|
112
|
Luis
|
Trujillo
|
7267 Melvin Avenue
|
Heiskell
|
TN
37754
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3
|
|
113
|
Minnie
|
Gonzales
|
6430 Vasili Drive
|
Williston
|
TN
38076
|
0
|
RENTAL
|
|
|
Rent_Num
|
Rent_Date
|
Mem_Num
|
|
|
|
|
1001
|
|
01-MAR-09
|
103
|
|
1002
|
01-MAR-09
105
|
|
|
|
1003
|
|
|
|
|
|
|
|
|
|
|
02-MAR-09
|
102
|
|
|
|
1004
|
02-MAR-09
110
|
|
|
1005
|
02-MAR-09
111
|
|
|
1006
|
02-MAR-09
107
|
|
1007
|
02-MAR-09
104
|
|
1008
|
|
|
03-MAR-09
|
105
|
|
1009
|
03-MAR-09
111
|
DETAILRENTAL
|
Rent_Num
|
|
Vid_Num
|
Detail_Fee
|
Detail_Duedate
|
Detail_Returndate
|
Detail_Dailylatefee
|
1001
|
|
|
34342
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2
|
|
|
|
|
|
|
04-MAR-09
|
02-MAR-09
|
|
|
|
|
|
|
|
|
|
|
|
|
1
|
1001
|
|
61353
|
2
04-MAR-09
03-MAR-09
1
1002
|
|
59237
|
|
|
|
|
|
|
|
|
|
|
3
.5
|
04-MAR-09
04-MAR-09
3
1003
|
|
54325
|
3.5
04-MAR-09
|
|
09-MAR-09
|
3
1003
|
|
61369
|
2
|
06-MAR-09
|
09-MAR-09
1
1003
|
|
61388
|
0
06-MAR-09
09-MAR-09
1
1004
|
|
44392
|
3.5
|
|
|
|
|
|
|
|
|
05-MAR-09
|
|
|
|
|
|
07-MAR-09
|
3
1004
|
|
34367
|
3.5
05-MAR-09
07-MAR-09
3
1004
|
|
34341
|
2
07-MAR-09
07-MAR-09
1
1005
34342
2
07-MAR-09
05-MAR-09
1
1005
|
|
44397
|
3.5
05-MAR-09
05-MAR-09
3
1006
|
|
|
34366
|
3.5
05-MAR-09
04-MAR-09
3
1006
|
|
61367
|
2
07-MAR-09
1
1007
|
|
34368
|
3.5
05-MAR-09
3
1008
|
|
34369
|
3.5
05-MAR-09
05-MAR-09
3
1009
|
|
54324
|
3.5
05-MAR-09
3
1001
34366
3.5
04-MAR-09
02-MAR-09
3
|
VIDEO
|
Vid_Num
|
Vid_Indate
|
|
Movie_Num
|
|
54321
|
|
|
18-JUN-08
|
|
|
|
1234
|
54324
18-JUN-08
1234
54325
18-JUN-08
1234
34341
|
|
22-JAN-07
|
|
|
1235
|
34342
22-JAN-07
1235
34366
02-MAR-09
|
|
|
|
1236
|
34367
02-MAR-09
1236
34368
02-MAR-09
1236
34369
02-MAR-09
1236
44392
|
|
21-OCT-08
|
|
|
|
1237
|
44397
21-OCT-08
1237
59237
|
14-FEB-09
|
1237
61388
|
25-JAN-07
|
|
1239
|
61353
|
|
28-JAN-06
|
|
|
1245
|
|
61354
|
28-JAN-06
1245
61367
|
|
30-JUL-08
|
|
|
1246
|
61369
30-JUL-08
1246
|
MOVIE
|
Movie_Num
|
Movie_Name
|
|
Movie_Year
|
Movie_Cost
|
|
|
Movie_Genre
|
|
Price_Code
|
1234
|
|
The Cesar Family
Christmas
|
|
|
|
2007
|
39.95
|
|
|
FAMILY
|
2
1235
|
|
Smokey Mountain Wildlife
|
2004
|
|
59.95
|
|
|
|
ACTION
|
1
1236
|
|
|
Richard Goodhope
|
|
|
|
2008
|
59.95
|
|
|
|
|
|
|
|
DRAMA
|
2
1237
|
|
|
Beatnik Fever
|
2007
29.95
|
|
|
|
|
|
COMEDY
|
2
|
1238
|
|
|
Constant Companion
|
2008
89.95
|
DRAMA
2
1239
|
|
|
Where Hope Dies
|
|
1998
|
25.49
|
DRAMA
3
1245
|
|
Time to Burn
|
2005
|
45.49
|
ACTION
1
1246
|
|
|
What He Doesn’t Know
|
|
2006
|
58.29
|
COMEDY
1
|
PRICE
|
Price_Code
|
Price_Description
|
|
|
Price_Rentfee
|
Price_Dailylatefee
|
1
|
|
|
Standard
|
2
1
2
|
|
|
|
|
|
|
|
|
New Release
|
3.5
3
3
|
|
|
|
|
Discount
|
1.5
|
1
|
|
|
|
|
|
|
|
|
4
|
Weekly Special
|
1
.5
|
For questions 3– 19, 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 sorted by movie genre in ascending order, then sorted by movie year in descending order within genre (result shown in Figure 2).
Figure 2 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 3).
Figure 3 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 4).
Figure 4 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 5).
Figure 5 Action movies
10. Write a query to display the movie number, movie title, movie cost, and movie genre for movies that are either action or comedy movies and movies that have a cost that is less than $50. Sort the results in ascending order by genre. (Result shown in Figure 6.)
Figure 6 Action or comedy movies costing less than $50
11. Write a query to display the movie genre and the number of movies in each genre (result shown in Figure 7).
Figure 7 Number of movies in genre
12. Write a query to display the movie genre and average cost of movies in each genre (result shown in Figure 8).
Figure 8 Average movie cost by genre
13. 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 9).
Figure 9 Rental fees for movies
|
|
Movie_Title
|
Movie_Genre
Price_Descriptioin
|
Price_Rentfee
What He Doesn’t Know
COMEDY
Standard
|
2.5
|
The Cesar Family Christmas
FAMILY
New Release
4
Richard Goodhope
DRAMA
New Release
4
Beatnik Fever
COMEDY
New Release
4
Constant Companion
DRAMA
New Release
4
Smokey Mountain Wildlife
ACTION
Discount
2
Where Hope Dies
DRAMA
Discount
2
Time to Burn
ACTION
Discount
2
14. 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 10).
Figure 10 Movies costs within a range
15. 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 11).
Figure 11 Movies with specific genres
Movie_Title
Movie_Year
Price_Desciption
|
Price_Rentfee
Movie_Genre
|
The Cesar Family
|
2007
New Release
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
16. Write a query to display the minimum balance, maximum balance, and average balance for memberships that have a rental (result shown in Figure 12).
Figure 12 Minimum, maximum, and average balances
|
MINIMUM_BALANCE
|
MAXIMUM_BALANCE
|
AVERAGE_BALANCE
|
0
15
6.43
|
17. 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 13).
Figure 13 Late video returns
18. 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 14).
Figure 14 Actual rental fees charged
19. Write a query to display the membership number, last name, and total rental fees earned from that membership (result shown in Figure 15). 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 15 Total rental fees paid by membership
