MySQL Lab # 5:
Make sure that you are using student database. DO NOT commit or rollback an operation until instructed to do so.
1. Describe the cost_table.
2. Start transaction
3. Write insert statements to place the following data into the cost_table:
|
SHIPPER |
MIN_SIZE |
MAX_SIZE |
MAX_WEIGHT |
TIME_FRAME |
COST_FACTOR |
|
US Mail |
2 |
20 |
25 |
20 |
2 |
|
Fly By Night |
10 |
100 |
150 |
5 |
10 |
|
Mail Box, Etc. |
2 |
40 |
30 |
5 |
30 |
|
Box Right |
5 |
40 |
30 |
5 |
30 |
|
Ship Aid |
5 |
40 |
30 |
5 |
30 |
4. Write a single select statement that will verify your inserts.
5. Write a delete statement to remove just the new “Ship Aid†record.
6. Write a select statement to display the distinct shippers in the cost_table to verify that the single Ship Aid record was deleted.
7. Write a statement to update the cost_factor to 5 for the US Mail record.
8. Write a select statement to verify the updated cost_factor field for the US Mail.
9. Rollback all changes to the cost_table for this transaction.
10. Write a single select statement that will verify that all changes to the cost_table have been rolled back.
11. Issue start transaction command.
12. Repeat exercise (3).
13. Issue a savepoint command. Name the savepoint ‘costs_ok’;
14. Write statements to make the following updates to cost_table (be careful with where):
1. Make max_size = 50 for US Mail and Box Right.
2. Make min_size = 100 for Fly By Night.
3. Make cost_factor = 32 for Ship Aid.
15. Write a select statement to display the shippers and each modified field to verify the changes.
16. Rollback the changes made to your savepoint.
17. Commit the changes from this transaction.
