TinyVideo needs a database system to track the rental of movies to its members.

need helpA 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 ERD is provided in Figure P7.65.Figure P7.65 TinyVideo ERD 65.Write the SQL code to create the table structures for the entities shown in Figure P7.65. The structures should contain the attributes specified in the ERD. 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. 66.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 65. Remark: Based on the referential integrity constraints, you should be able to identify a correct sequence in which to insert the data into the tables. The order listed in the problem will not work because it shows inserting rows into DETAILRENTAL before the corresponding rows have been inserted into VIDEO. The key point is that due to referential integrity constraints, the rows must be inserted in the table contributing its PK as a FK before the related rows can be inserted into the table containing the FK. MEMBERSHIP Mem_NumMem_FnameMem_LnameMem_StreetMem_CityMem_StateMem_ZipMem_Balance102TamiDawson2632 Takli CircleNoreneTN3713611103CurtKnight4025 Cornell CourtFlatgapKY412196104JamalMelendez788 East 145th AvenueQuebeckTN385790105IvaMcclain6045 Musket Ball CircleSummitKY4278315106MirandaParks4469 Maxwell PlaceGermantownTN381830107RosarioElliott7578 Danner AvenueColumbiaTN384025108MattieGuy4390 Evergreen StreetLilyKY407400109ClintOchoa1711 Elm StreetGreenevilleTN3774510110LewisRosales4524 Southwind CircleCounceTN383260111StacyMann2789 East Cook AvenueMurfreesboroTN371328112LuisTrujillo7267 Melvin AvenueHeiskellTN377543113MinnieGonzales6430 Vasili DriveWillistonTN380760RENTALRent_NumRent_DateMem_Num100101-MAR-09103100201-MAR-09105100302-MAR-09102100402-MAR-09110100502-MAR-09111100602-MAR-09107100702-MAR-09104100803-MAR-09105100903-MAR-09111DETAILRENTALRent_NumVid_NumDetail_FeeDetail_DuedateDetail_ReturndateDetail_Dailylatefee100134342204-MAR-0902-MAR-091100161353204-MAR-0903-MAR-0911002592373.504-MAR-0904-MAR-0931003543253.504-MAR-0909-MAR-093100361369206-MAR-0909-MAR-091100361388006-MAR-0909-MAR-0911004443923.505-MAR-0907-MAR-0931004343673.505-MAR-0907-MAR-093100434341207-MAR-0907-MAR-091100534342207-MAR-0905-MAR-0911005443973.505-MAR-0905-MAR-0931006343663.505-MAR-0904-MAR-093100661367207-MAR-09 11007343683.505-MAR-09 31008343693.505-MAR-0905-MAR-0931009543243.505-MAR-09 31001343663.504-MAR-0902-MAR-093VIDEOVid_NumVid_IndateMovie_Num5432118-JUN-0812345432418-JUN-0812345432518-JUN-0812343434122-JAN-0712353434222-JAN-0712353436602-MAR-0912363436702-MAR-0912363436802-MAR-0912363436902-MAR-0912364439221-OCT-0812374439721-OCT-0812375923714-FEB-0912376138825-JAN-0712396135328-JAN-0612456135428-JAN-0612456136730-JUL-0812466136930-JUL-081246MOVIEMovie_NumMovie_NameMovie_YearMovie_CostMovie_GenrePrice_Code1234The Cesar Family Christmas200739.95FAMILY21235Smokey Mountain Wildlife200459.95ACTION11236Richard Goodhope200859.95DRAMA21237Beatnik Fever200729.95COMEDY21238Constant Companion200889.95DRAMA21239Where Hope Dies199825.49DRAMA31245Time to Burn200545.49ACTION11246What He Doesn’t Know200658.29COMEDY1PRICEPrice_CodePrice_DescriptionPrice_RentfeePrice_Dailylatefee1Standard212New Release3.533Discount1.514Weekly Special1.5For questions 67 – 98, use the tables that were created in Problem 65 and the data that was loaded into those tables in Problem 66.67.Write the SQL command to save the rows inserted in Problem 66.68.Write the SQL command to change the movie year for movie number 1245 to 2008.UPDATE Movie SET MOVIE_YEAR = 2008 WHERE MOVIE_NUM = 1245;69.Write the SQL command to change the price code for all Action movies to price code 3.UPDATE Movie SET PRICE_CODE = 3 WHERE MOVIE_GENRE = ‘Action’;70.Write a single SQL command to increase all price rental fee values by $0.50.UPDATE Price SET PRICE_CODE = PRICE_CODE + ‘0.50’;71.Write the SQL command to save the changes made to the PRICE and MOVIE tables in Problems 67 – 70.72.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 73.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 74.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 75.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 76.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 77.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 78.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 79.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 80.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 81.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 82.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 83.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 84.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 85.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 86.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 87.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 88.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 89.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 90.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 91.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 92.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 93.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 94.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

Leave a Reply