SQL Homework

Using MySQL

Assignment 4

COP3703

 

 

Instructions: Prepare and execute each of the queries listed using the "sakila" database in MySQL. Use the schema shown below. When all of your queries are complete, follow the instructions below to prepare your homework.

 

Preparing your homework for submission:

1. Type your queries into the query window of MySQL

2. Execute the queries. Scroll through the output window to be sure there are no errors.

3. Take a screen shot of your output and insert into your document for submission, along with the actual SQL code.

4. If there are no errors in your output, continue to the next step. If there are errors correct the errors in the individual query from your Notepad/Word file, then go back to Step 1.

5. Execute the queries again.

6. Make sure to copy and paste ALL queries into a Word, RTF, or PDF document put your name

at the top. This is what you will turn in for Assignment 4 in CANVAS.

 

Schema/Entities for the Sakila Database

 

 

 

 

 

 

 

Using the MySQL database, launch the MySQL Workbench program (should have been installed in Lab 2). You will see the following screen once you launch MySQL

You want to click on the root account under Local instance MySQL… If you gave it a password, when it was installed, then you will need to type in the password. You should then get the following screen:

 

 

 

The Navigator window shows you MANAGEMENT, INSTANCE, PERFORMANCE, and SCHEMAS (your databases). Select (double-click) on the sakila database. This will tell the system that you are going to be using the sakila database for the queries. Next, enter the queries in the Query1 window (to the right of the Navigator). Once you type in the query, click on the lightning bolt to run the query. The results of the query will appear in the Output window (below the Query1 window).

Visit http://dev.mysql.com/doc/workbench/en/wb-sql-editor-query-panel.html for help on using the

 

 

query editor. Take a screen shot of each query to be peformed and paste into a word/pdf/or other compatible (not Google docs) document (all of the queries only require accessing 1 table – so no joins are required). The syntax is the same for MySQL as it is for SQL Server (more or less). Format all of the queries so that the titles make sense (instead of last_name change to Last Name).

 

1. Using the actor table, select the Actors ID, and first and last name. Sort the list by actors last name and then first name. Change the column titles to Actor ID, First Name, and Last Name.

 

Hint – first line: select actor_id, first_name, last_name

 

1. Using the address table, select the Address, District, City ID, and postal code for all actors. Sort by District, and then City ID.

 

1. Using the address table, select the Address, District, City ID, and postal code for those actors that live in England.

 

1. Using the category table, select the Name and sort by name in descending order.

 

1. Using the customer table, select the First and Last name of the customers, their email address. Sort by Last name and then First name.

 

1. Using the film table, select the Title, Description, Release Year, Rental Rate, Length and Replacement Cost of all films that are longer than 100 minutes (assuming that length is in minutes). Sort by Length and then Title in descending order.

 

1. Using the payment table, select the Payment ID, Customer ID, Staff ID, Rental ID, Amount, and Payment Date of all rentals that have a payment amount of 2.99 or greater. Sort by Customer ID, and then Amount in ascending order.

 

1. Using the rental table, select Rental ID, Rental Date, Inventory ID, Customer ID, and Return Date of all rentals that were rented by customer 36. Order by Inventory ID.

 

1. Using the rental table, select Rental ID, Rental Date, Inventory ID, Customer ID, and Return Date of all rentals that were rented by customer 12, 19, and 26. Order by Customer ID, then Inventory ID.

 

1. Using the film-actor table, total all of the films that have been done by actors 8, 15, 27, 31 and 39 (you will get one number).

 

1. Using the payment table find all of the payments made by customer 7 (you will get one number).

 

1. Using the actor table, find ALL of the actors that have the first name “Ed”. List the actor id, first name, and last name in ascending order by last name.

 

1. Using the Country table, count the number of countries in the list (you will get 1 number).

 

1. Using the Film table, find out how many rentals had a rental duration of 5 or more days, and list the film’s Title, Description, and the number of days rented. Sort by title, ascending.

 

1. Using the Film category table, find out how many films had a category ID of 14 (you will get 1 number).

 

SQL HomeworkUsing MySQLAssignment 4COP3703Instructions:Prepare and execute each of the queries listed using the "sakila" databasein MySQL. Use the schema shown below. When all of your queries arecomplete, follow the instructions below to prepare your homework.Preparing your homework for submission:1.Type your queries into the query window of MySQL2.Execute the queries. Scroll through the output window to be sure there are no errors.3.Take a screen shot of your output and insert into your document for submission, along with theactual SQL code.4.If there are no errors in your output, continue to the next step. If there are errors correct theerrors in the individual query from your Notepad/Word file, then go back to Step 1.5.Execute the queries again.6.Make sure to copy and paste ALL queries into a Word, RTF, or PDF documentput yournameat the top. This is what you will turn in for Assignment 4 in CANVAS.Schema/Entities for the Sakila Database

Attachments:

• 1494579605-Lab4-SQL-Assignment-Rev-5-16.docx