Genre Creator Series Suggested Movie Length Price
Fantasy George Lucas Star Wars $129.99 Episode l: 136
The Phantom Menace 140
Fantasy George Lucas Star Wars $129.99 Episode II: Attack of 142
the Clones
Fantasy George Lucas Star Wars $129.99 Episode Ill: Revenge 140
of the sith
Fantasy George Lucas Star Wars $129.99 Episode IV: A New Hope 121
Family Film John Lasseter Toy Story $22.13 Toy Story 121
Family Film John Lasseter Toy Story $22.13 Toy Story 2 135
Family Film John Lasseter Toy Story $22.13 Toy Story 3 138
Fantasy John Tolkien Lord of the Rings The Lord of the Rings
The Fellowship of the ring 228
Fantasy John Tolkien Lord of the Rings The Lord of the Rings
The Two Towers 235
Fantasy John Tolkien Lord of the Rings The Lord of the Rings
The Return of the Kin 200
1) Create the tables in the schema, including all of their columns, datatypes, and constraints, and populate the tables with data. Most but not all of the data is given to you in the table below; you should also insert information for one additional movie series of your choosing. Although the data is in flattened representation below, you will of course insert the data relationally into the schema with foreign keys referencing the appropriate primary keys
A video reseller needs to know how many movies are available. Write a single query to fulfill this request.
The same video reseller needs to know the price of the most expensive and least expensive series. Write two queries that fulfill this request, and also explain how and why the SQL processor treated the suggested price for the Lord of the Rings series differently than the other suggested price values.
A film production company is considering purchasing the rights to extend a series, and needs to know the names and prices of all movie series, along with the number of movies in each series. Write a single query to fulfill this request.
The same film production company wants to create movies in a genre that has at least 7 associated movies. Write a single query to fulfill this request, making sure to list only genres that have at least 7 associated movies, along with the number of movies for the genre.
2) University wants to offer its students a movie-binge weekend by playing every movie in a series. To make sure the series is as bingeable as possible, BU wants to be sure the series will run for at least 10 hours. Write a single query that gives this information, with useful columns.
A research institution requests the names of all movie series’ creators, as well as the number of "Family Film" movies they have created (even if they created none). The institution wants the list to be ordered from most to least; the creator who created the most family films will be at the top of the list, and the one with the least will be at the bottom. Write a single query that gives this information, with useful columns.