| MTM6405

Movie Mayhem DB II

Objective

For this assignment, you will demonstrate your skills and understanding of SQL Queries and the SELECT, INSERT, UPDATE, and DELETE statements by creating queries to retrieve and manipulate data from the Movie Mayhem database.

Project Setup

1. Clone the Repository

Clone the repository from GitHub and use the provided file to complete the assignment.

2. Import the Movie Mayhem Database

To import the database, using phpMyAdmin, complete the following instructions.

  1. Open Adminer at http://adminer.testopen in new window (macOS) or http://localhost/admineropen in new window (Windows)
  2. Log in to Adminer using the root username and password
  3. From the Adminer homepage, click on the "Import" link found on the left side of the page.
  4. Under the "File upload" section, click the "Choose File" button. Choose the movie_mayhem.sql file found in the repository.
  5. Click the "Execute" button.
  6. Once the importing has been completed, the movie_mayhem database should be listed in the DB select box.
  7. Once the importing has been completed, select the movie_mayhem database from the DB select box.
  8. With the movie_mayhem database selected, click on the "SQL command" link on the left side of the page. From this page, you can execute custom SQL commands.

Requirements

Create ONE query for each of the tasks below. Save the queries to the queries.sql file.

1. Add a movie to the movies table with the following data:

movie_titledirectoryeargenre_id
'WALL-E''Brad Bird'20082

2. Using a subquery, add a movie to the movies table with the following data:

movie_titledirectoryeargenre_title
'Inside Out''Peter Doctor'2014'Family'

3. Update the year (year) to 2015 for the movie with the title (movie_title) 'Inside Out'.

4. Using a subquery, update the director (director) to 'Peter Doctor' and the genre to 'Romance' for the movie with the title (movie_title) 'WALL-E'.

5. Remove all the movies from the director (director) 'Peter Doctor'.

6. Using an INNER JOIN, retrieve the movie title (movie_title), year (year), and genre title (genre_title) for all the movies in the movies table.

7. Retrieve the total number of movies in the movies table with the heading Number of Movies.

8. Retrieve the oldest movie year in the movies table in the genre 'Fantasy'.

9. Retrieve the number of movies for each genre title. The headings should be Genre and Number of Movies.

10. Retrieve a list of directors that have more than 1 movie in the movies table.

Solution