Section One – Aggregating Data

To practice aggregating data, you will be working with the following simplified Movie Series schema.

This schema contains basic information about various movie series and the movies that comprise them, such as the Star Wars series with its movies.

In this schema, the Movie_series table represents the overall movie series, and contains a primary key, the name of the series, foreign keys to its genre and creator, and a suggested price for the entire series. The Genre table represents the genre of a movie such as “Fantasy”, “Family Film”, and the like. It contains a primary key and the name of the genre. The Creator table represents who created the series, and contains a primary key and the name of each creator. The Movie table represents movies that comprise each movie series, and contains a primary key, a foreign key to the movie’s series, the name of the movie, and the length of the movie, in minutes.

The schema is intentionally simplified compared to what you might see in a real-world production schema. Many attributes and entities that would exist in a production database are not present. Nevertheless, there is sufficient complexity in the existing relationships and attributes to challenge you to learn various aggregation scenarios you encounter in real-world schemas.

As a reminder, for each step that requires SQL, make sure to capture a screenshot of the command and the results of its execution. Further, make sure to eliminate unneeded columns from the result set, to name your columns something user-friendly and human readable, and to format any prices as currencies.

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.

Note that the suggested price for the Lord of the Rings series is null (has no value).

2. A video reseller needs to know how many movies are available. Write a single query to fulfill this request.

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

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

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

6. Boston 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.

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

Evaluation

Your lab will be reviewed by your facilitator or instructor with the following criteria and grade breakdown.

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.