CS 3200 – Database Design – Fontenot
Homework 03 – SQL
Regular Submission Deadline: Tuesday Feb 14 @ 11:59 to GradeScope
Extra Credit Early Submission Deadline: Sunday Feb 12 @ 11:59 to GradeScope
Name: _______________________ NUID: _______________________
Directions:
1. Download the zipped Sakila database linked > here < and save it to your 3200 folder. Unzip it.
2. Create a new datasource in DataGrip using the Sakila database file named ‘sqlite-sakila.db’.
3. For each of the 7 questions that follow, you are to produce SQL select statements. Each query you submit must successfully execute in DataGrip against the SQLite Sakila Database linked above.
For each problem, you are provided the expected output. The results of executing your query solution must match the expected output exactly, including but not limited to column names and ordering of rows in the output.
In solving each of these problems, you must rely only upon what is provided in the problem statement. You may NOT include any numerical IDs or other literal values that allow you to simplify the query. For example, if the problem statement asks for all movies that belong to the Horror category, you may not lookup the category_id for ‘horror’ and include that in the query directly. Any instances of “hard-coding” or attempting to “game the system” will result in no credit for that problem.
Each question is worth 14 points, which equates to 98 points total for the 7 problems. We will attribute the two remaining points to proper datasource creation in DataGrip, which is evidenced by you submitting queries.
Submission Info: (Updated Feb 6 2023)
You’ll submit your solutions on GradeScope. You will submit individual SQL files, one file per problem. So you will submit a p1.sql, p2.sql, p3.sql and so on. Failure to follow this file naming convention may result in no credit. It is important that you name your solution files with the proper question number based on this document before you upload to GradeScope. The numbering might be a little confusing, but Problem X on GradeScope is associated with Problem X on this document. Ignore the GradeScope numbers.
Academic Collaboration Reminder:
Remember that you may not look at, copy, capture, screenshot, or otherwise take possession of any other students’ solutions to any of these questions. Further, you may not provide your solutions in part or in whole to any other student. Doing any of the above constitutes a violation of academic honesty which could result in an F in this class and a referral to OSCCR. What is permissible? You are free and encouraged to talk to your peers about the conceptual material from the lectures or the conceptual material that is part of this assignment. You can get a round a white board and talk through the Sakila data model. You and your colleagues can work through sample SQL queries done in class or others that you dream up on the fly. I’m very confident that each of you knows where the line between collaborative learning and cheating sits. Please don’t cross that line.
1. What is the average number of actors appearing in all movies?
2. How many movies are there in each category? Order the list by category name alphabetically.
3. Which movies aren’t currently in the inventory of any of the stores? Provide a list of film titles in alphabetical order along with the name of the category that each film belongs to.
4. The company wants to start a VIP Customer Loyalty program. Any customer who has rented more than 35 movies will be invited to join. Generate a list of all customers that have rented more than 35 movies. Order the list in descending order by number of rentals.
5. Which actors or actresses have starred in movies from every category of film in our inventory? The output should give the last name and first name of these actors alphabetically.
Update Friday Feb 3, 2023: I’ve been convinced there are two interpretations of the way I stated the query. So, I’m providing an alternative output option for this problem. See below.
6. Are there any actors or actresses that have the same first name and last name? If so, give the actors’ id numbers and names.
7. Help! One of our customers just called and is looking for the name of a particular movie. This customer was talking to her next door neighbor who was raving about this movie, but can’t remember the name. The neighbor remembered that the movie was really long… over 3 hours, and they rented it sometime in the 2nd half of 2005. There was also an actor or actress in the movie with a last name similar to MacCallen or McKellen or McKallen or something like that… they couldn’t really remember. Our customer’s neighbor really only like Sci-fi and horror films. So, the movie is almost certainly in one of those two categories.