SQL
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 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_ Num |
Mem_ Fname |
Mem_ Lname |
Mem_Street |
Mem_City |
Mem_ State |
Mem_Zip |
Mem_ Balance |
|
102 |
Tami |
Dawson |
2632 Takli Circle |
Norene |
TN |
37136 |
11 |
|
103 |
Curt |
Knight |
4025 Cornell Court |
Flatgap |
KY |
41219 |
6 |
|
104 |
Jamal |
Melendez |
788 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 |
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