BUSA 326 – Spring 2017 – Extra Credit

Due April 30th by 11:59PM

Worth 5 Points

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.

Review past videos to aid in this assignment as well as the video onMySQL Create Table.

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

1. DownloadRentalCompany.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.

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.

Failure to constructs your answers in this format will result in significant point deductions.

All of the objectives below were demonstrated in tutorials.

1. Create a table by copying the existingactor table. Name the new tableYourName_Actor. Copy the table structure and all 200 records.

2. Add a record to the newly created table with your name as the new actor.

3. Update the first name in the record that you just created in #2 with a first name ofAnna.

4. Delete only the record that you created in #2. This means that the resulting table should look just like the existingactor table in the end.

Grading:

· In addition to meeting all the specified criteria under theDirections section, you must use theBeautifyfeature in MySQL so your SQL is easily readable.

· Points will be deducted if each question is not clearly labeled within your SQL file. You will do this by commenting out the descriptive line with the pound sign as shown in theMySQL Expressions tutorial video. See example below.Your final file will have a comment, then sql statement, then comment, then sql statement, etc.

· To grade your file, I will open your.sql file in my version of MySQL and click run. If your SQL does not run, points will be deducted. Next, I will determine if your SQL was written correctly and returned the correct data results.

Complete all of Extra Credit using MySQL and turn it in to the Extra Credit Dropbox as a .sql file named “YourLastNameExtraCredit.sql” by the due date