Movie Mayhem DB I

Objective

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

Database

For this assignment, you will be using the Movie Mayhem database, provided with the repository as the Movie Mayhem SQL File, movie_mayhem.sql.

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.

Open Adminer at http://adminer.testopen in new window (macOS) or http://localhost/admineropen in new window (Windows)
Log in to Adminer using the root username and password
From the Adminer homepage, click on the "Import" link found on the left side of the page.
Under the "File upload" section, click the "Choose File" button. Choose the movie_mayhem.sql file found in the repository.
Click the "Execute" button.
Once the importing has been completed, select the movie_mayhem database from the DB select box.
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 in the queries.sql file.

1. Retrieve all of the columns and all of the rows from the `movies` table.

2. Retrieve the movie title (`movie_title`) and movie director (`director`) with the movie id (`movie_id`) of `3` from the `movies` table.

3. Retrieve all of the movies with the word "the" in the title (`movie_title`) from the `movies` table.

4. Retrieve all of the movies whose title starts with the word "the" and whose year is after 1990. Sort the results by year, starting with the newest movies.

5. Retrieve the first 5 movie titles (`movie_title`), directors (`director`), and years (`year`) with the headings: Movie Title, Movie Director, and Release Year sorted alphabetically by the movie title.

Movie Mayhem DB I

Objective

Database

Project Setup

1. Clone the Repository

2. Import the Movie Mayhem Database

Requirements

1. Retrieve all of the columns and all of the rows from the `movies` table.

2. Retrieve the movie title (`movie_title`) and movie director (`director`) with the movie id (`movie_id`) of `3` from the `movies` table.

3. Retrieve all of the movies with the word "the" in the title (`movie_title`) from the `movies` table.

4. Retrieve all of the movies whose title starts with the word "the" and whose year is after 1990. Sort the results by year, starting with the newest movies.

5. Retrieve the first 5 movie titles (`movie_title`), directors (`director`), and years (`year`) with the headings: Movie Title, Movie Director, and Release Year sorted alphabetically by the movie title.

6. Retrieve all the movies from the years 1982, 1999, and 2009 from the `movies` table.

7. Using a subquery, retrieve all the movie titles (`movie_title`) from the genre "Sci-Fi".

Solution

# Movie Mayhem DB I

# Objective

# Database

# Project Setup

# 1. Clone the Repository

# 2. Import the Movie Mayhem Database

# Requirements

# 1. Retrieve all of the columns and all of the rows from the movies table.

# 2. Retrieve the movie title (movie_title) and movie director (director) with the movie id (movie_id) of 3 from the movies table.

# 3. Retrieve all of the movies with the word "the" in the title (movie_title) from the movies table.

# 4. Retrieve all of the movies whose title starts with the word "the" and whose year is after 1990. Sort the results by year, starting with the newest movies.

# 5. Retrieve the first 5 movie titles (movie_title), directors (director), and years (year) with the headings: Movie Title, Movie Director, and Release Year sorted alphabetically by the movie title.

# 6. Retrieve all the movies from the years 1982, 1999, and 2009 from the movies table.

# 7. Using a subquery, retrieve all the movie titles (movie_title) from the genre "Sci-Fi".

# Solution

Movie Mayhem DB I

Objective

Database

Project Setup

1. Clone the Repository

2. Import the Movie Mayhem Database

Requirements

1. Retrieve all of the columns and all of the rows from the `movies` table.

2. Retrieve the movie title (`movie_title`) and movie director (`director`) with the movie id (`movie_id`) of `3` from the `movies` table.

3. Retrieve all of the movies with the word "the" in the title (`movie_title`) from the `movies` table.

4. Retrieve all of the movies whose title starts with the word "the" and whose year is after 1990. Sort the results by year, starting with the newest movies.

5. Retrieve the first 5 movie titles (`movie_title`), directors (`director`), and years (`year`) with the headings: Movie Title, Movie Director, and Release Year sorted alphabetically by the movie title.

6. Retrieve all the movies from the years 1982, 1999, and 2009 from the `movies` table.

7. Using a subquery, retrieve all the movie titles (`movie_title`) from the genre "Sci-Fi".

Solution