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	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