HW3 MySQL Sakila database

DSCI 551 
HW3, 100 points
You will need to install MySQL Sakila database for this homework. You can either install the database as
described in hIps://dev.mysql.com/doc/sakila/en/; or you may follow these steps to install it on EC2.
● Download package:
○ wget hIps://downloads.mysql.com/docs/sakila-db.tar.gz
● Unzip it:
○ tar xvf sakila-db.tar.gz
● Install:
○ cd sakila-db
○ mysql -u root -p
■ source sakila-schema.sql
■ source sakila-data.sql
■ use sakila
Note that two source commands above need to be executed aZer you log into your MySQL as root.
Note that you can also download MySQL server and WorkBench from MySQL website and install them
on your laptop/PC and use them for your homework/project.
1. [70 points] Write an SQL query for each of the following quesaons.
1) Find out how many films are rated ‘PG-13’ and last between 100 and 200 minutes.
2) Find first and last names of actors whose 2nd to the last leIer of last name is ‘i’.
3) Find the atle and length of the longest films.
4) Find out how many films there are in each category. Output category name and the number of
films in the category.
5) Find ids of customers who have rented films at least 40 ames. Return the same ids only once.
6) Find first and last names of customers whose total payment exceeds $200.
7) Find first and last names of actors who have never played in films rated R.
8) Find out how many films are not available in the inventory.
9) Find out how many actors who have the same first name but a different last name with another
actor.
10) Show the first name, last name, and city of the customers whose first name is either Jamie,
Jessie, or Leslie. Order the result by first name.
Submission: a text document named sql_queries.txt that contains both the queries and their results
(copy and paste your output from mysql terminal).
2. [30 points] Write a Python script search.py that searches for customers using their first name (case
insensiave). It should return first name, last name, and city of found customers.
For example,
python3 search.py ‘john’
will find customers whose first name is john.
Libraries permiIeds: pandas, sqlalchemy, pymysql, mysql-connector-python
*Note:
1. You should already have the database sakila in mysql at this point.
2. In order to use the package mysql.connector, you'll need to set up the mysql.connector.connect() in
your code (Please refer to lecture slides for examples). If you haven't created the user 'dsci551', please
do so by following the posted slides on how to setup MySQL on EC2. AZer set up, run the command
below:
 GRANT ALL PRIVILEGES ON sakila.* TO 'dsci551'@'localhost';
Submission: search.py
Checklist for Submission :
1. DO NOT return anything we didn't ask for. For example, "please enter XXX: ___". Please no. We have
given you the EXACT output format. Please just follow them.
2. Make sure that you are able to run the code according to the execuaon format menaoned above in
the quesaons.
3. Double-check your files before submiwng them. Please use python3 to complete the homework and
try to maintain the python version as 3.7. Do not use any libraries other than the ones specified in the
handout. You can use EC2 to test your code, and python 3.7 is preinstalled on EC2.
4. You can submit it mulaple ames on D2L but only the latest aIempt will be graded.
5. You should submit TWO files to D2L this ame: A text document sql_queries.txt that contains your
answer to Q1, and the search.py for Q2.