BUSA 326 DVD Rental Company Project 4

Scenario:

You work for a DVD rental company. Your manager has asked you to pull some data on the company, its inventory, and customers. You will complete this by developing and running SQL queries.

Initialize Your Database (NOTE: If you already did this in Project 1, you do not need to do this step again):

1. Download RentalCompany.sql from Doc Sharing.

a. Run the script to create tables and data. You should not receive errors. There may be a few warnings, but that is okay.

b. To verify you created all tables, run the following SQL:

SELECT TABLE_NAME, TABLE_ROWS, TABLE_TYPE, CREATE_TIME FROM `information_schema`.`tables` WHERE `table_schema` = 'rentalcompany' AND TABLE_TYPE = 'BASE TABLE';

Your results should look like the table below. This will help verify that you properly created the schema/database and tables.

actor 200 BASE TABLE
address 603 BASE TABLE
category 16 BASE TABLE
city 600 BASE TABLE
country 109 BASE TABLE
customer 599 BASE TABLE
film 1000 BASE TABLE
film_actor 5463 BASE TABLE
film_category 1000 BASE TABLE
film_text 1000 BASE TABLE
inventory 4581 BASE TABLE
language 6 BASE TABLE
payment 16086 BASE TABLE
rental 16005 BASE TABLE
staff 2 BASE TABLE
store 2 BASE TABLE

Directions:

Note: For any UPDATE, INSERT, or DELETE statements that you construct as your answers to the questions below, you MUST provide and label the following components:

  • Initial validation section with your SELECT statement
  • Run section with transaction, commit, and rollback statements
  • Final validation section with you SELECT statement
  • You must have a semicolon after every statement so I can highlight several statements at once and run them at once.

These "sections" were discussed and demonstration in the tutorials for chapters 15, 16, and 17. Failure to construct your answers in this format will result in significant point deductions.

1. Modify film table by increasing the rental duration by 6 days for films that have the word Age in their title.

2. Modify customer table by changing the store ID to 1 and active to 0 for all customers who have Barnett as their last name. Do not specify first names in your SQL statement.

3. Add one new customer and provide data for all columns on the table for the new customer. Use your own data to enter (ex: enter your name as first and last name).

4. Create a new actor record on the actor table for Kirk James by copying the record for Russell Bacall.

5. Delete language IDs from the language table that have no films assigned to them.

Academic Honesty!
It is not our intention to break the school's academic policy. Posted solutions are meant to be used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly complete the form. Please provide a valid email address and we will get back to you within 24 hours. Payment is through PayPal, Buy me a Coffee or Cryptocurrency. We are a nonprofit organization however we need funds to keep this organization operating and to be able to complete our research and development projects.