actors

id	first_name	last_name	gender
433259	William	Shatner	M
797926	Britney	Spears	F
831289	Sigourney	Weaver	F
...

movies

id	name	year	rank
112290	Fight Club	1999	8.5
209658	Meet the Parents	2000	7
210511	Memento	2000	8.7
...

roles

actor_id	movie_id	role
433259	313398	Capt. James T. Kirk
433259	407323	Sgt. T.J. Hooker
797926	342189	Herself
...

movies_genres

movie_id	genre
209658	Comedy
313398	Action
313398	Sci-Fi
...

directors

id	first_name	last_name
24758	David	Fincher
66965	Jay	Roach
72723	William	Shatner
...

movies_directors

director_id	movie_id
24758	112290
66965	209658
72723	313398
...

This assignment step is intended to strengthen your understanding of writing queries in MySQL.

Write MySQL queries for the movies database for each of the following. Write your queries in a file, queries.sql. Your filename must match exactly. You should have one query per line so that on the i^th line you have the query for the i^th question below. If a query is not possible, then your query should return NULL.

Make sure you test your queries first on IMBD_SMALL and then on IMBD.

1. The genre and number of movies of each genres.
2. The first and last names played by female actors having role 'Wonder Woman'. Display first and last name as a single column called Name.
3. Movie names that had a rank at least 9.5 and were produced in the last 10 years (2007 and later).
4. Directors and their movie names for movies with name starting with the letter "Zz".
//
5. Directors and their movie titles for movies with a rank of 9.5 or better.
6. Actors first and last names as a column Name who play themselves (ie, role is Himself).
7. The Director first and last name and average rank of movies for that director.
8. The Director first and last name with the highest average rank of movies all directors.
9. Create a view called max_roles of all actors/actresses who have had more than 300 roles.
10. Using your view, return the actor/actress first and last name for actor/actress who have played themost roles.
11. All those movie titles that are both comedies and action movies. Do NOT use IN.
12. All those movie titles that are both comedies and action movies. USE IN.
13. Some directors are actors/actresses too. Find all such director first and last names, the movies they acted in and their roles.
14. The genre of movie that appears the most in the database.
15. The names of those actors/actresses who had more than one role in the same movie (like Robin Williams in Mrs. Doubtfire).
16. The top 25 movie names (hint: look up the limit option).

This assignment step is intended to strengthen your understanding of writing create, update and insert commands.

We are going to write the code as if we are adding a new table to the database. You can test your code on the imbd_small by importing a copy to your own account. Write the following code in a new file called maintain.sql. Your filename must match exactly. You should have one command per line.

1. Create a new table that contains two columns with the first being a director_id and the second being an average rank rating.
2. Populate your table by using insert command(s) and the tables already in the database.
3. Insert a the new movie Sing that was just released. Find the appropriate information for our tables from the website. Note that for the actors/actresses just add the stars (top three) listed. Note this will also require updating some tables.

   Deliverables

   Submit the following files to MarkUs.

   • algebra.pdf
   • queries.sql
   • maintain.sql

   Please do not place a solution to this assignment online on a publicly-accessible Web site; doing so will be considered a violation of the Academic Code of Conduct.
   

   Good Luck!