Expert answer:MySQL lab data manipulation using a student databa
Solved by verified expert: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 transaction3. Write insert statements to place the following data into the cost_table:SHIPPERMIN_SIZEMAX_SIZEMAX_WEIGHTTIME_FRAMECOST_FACTORUS Mail22025202Fly By Night10100150510Mail Box, Etc.24030530Box Right54030530Ship Aid540305304. 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.
mysql_lab_5.pdf
sqlab5.zip
Unformatted Attachment Preview
MySQL Lab # 5
Objectives
To understand and use start transaction, commit and rollback, and to learn and use the
basic syntax of MySQL Data Manipulation Language (DML), to insert, update and delete
data from existing tables.
PART I – Transaction Control with Commit, Rollback and Savepoint
One of the first things a new MySQL user must understand is that data added to, modified
in, or deleted from a table are saved immediately. MySQL allows users to make a series
of data-changing statements together as one logical unit of work. This unit of work,
referred to as a transaction, begins with the start transaction command and ends when the
transaction is either committed or rolled back. Because of MySQL’s transaction control
logic, a user may make numerous changes to the data in tables. If the user is satisfied
with the changes made, the data can be saved in the database. Alternatively, if a problem
is discovered with the changes made, all changes made can be discarded. Either
alternative ends the transaction.
Start Transaction
When the user issues a Start Transaction command, it is a signal to the database that all
changes to the database that will be made after the Start Transaction command are
temporary. The syntax is:
start transaction;
When executed, it starts a new transaction.
COMMIT
When the user issues a commit statement, it is a signal to the database that all changes
made by the user during the current transaction contain no mistakes, and that the user is
ready to save the work in the database. The syntax is:
commit;
When executed, the commit statement ends the current transaction.
ROLLBACK
When the user issues a rollback statement, it is a signal to the database that all changes
made by the user during the current transaction should be discarded. The syntax is:
rollback;
You may be asking, “Can changes be rolled back after a commit?”. The answer is no.
SAVEPOINT
Occasionally, a user working with long transactions may wish to designate the statements
completed to that point as being correct, but to continue working on the transaction
without pausing to commit. To do so, the user would issue a savepoint command:
savepoint ok_so_far;
which consists of the ‘savepoint’ keyword, followed by a space and a user-selected name
for the savepoint. It may be useful for you to think of a savepoint as a kind of bookmark
that you can set as a placeholder.
The use of commit, rollback and savepoint is illustrated in the following example.
start transaction;
update shipments
set ship_cost = 10.95
where to_city = “Salt Lake City”;
savepoint ok_so_far
update shipments
set ship_cost = 14.95;
rollback to savepoint ok_so_far;
commit;
PART II – The Data Manipulation Language
The Data Manipulation Language (DML) allows data in a table to be modified or
manipulated. There are three principal elements of DML: insert, update and
delete. In addition, the select keyword, is classified as an element of DML.
INSERT
It is important to recognize that when an MySQL table is created, there are no data in the
table at all. Data have to be inserted into the table. You will recognize the following
output as being the result of a describe of the shipment_items table:
+————-+————-+——+—–+———+——-+
| Field
| Type
| Null | Key | Default | Extra |
+————-+————-+——+—–+———+——-+
| item_id
| double(8,0) | NO
| PRI | NULL
|
|
| shipment_id | double(8,0) | NO
|
| NULL
|
|
| quantity
| double(4,0) | NO
|
| NULL
|
|
| description | varchar(40) | YES |
| NULL
|
|
| height
| double(6,2) | YES |
| NULL
|
|
| width
| double(6,2) | YES |
| NULL
|
|
| weight
| double(6,2) | YES |
| NULL
|
|
| ship_cost
| double(7,2) | YES |
| NULL
|
|
+————-+————-+——+—–+———+——-+
8 rows in set (0.14 sec)
There are two ways to insert data into an MySQL table. The first way does not require
the columns of the table to be explicitly mentioned in the insert statement, but does
require that a value be inserted for each column. Note also that each value inserted must
be of the correct data type (refer to the describe output). So, let’s insert a new record into
the table:
insert into shipment_items values
(30, 20, 3, “Our Best New Widget”, 20, 30, 20, 12.50 );
Notice the keywords insert into followed by the table_name, which is followed by the
keyword values. There are 8 data elements enclosed by parentheses in the example, one
data element for each column in the table. Notice also that each data element is of the
correct datatype (numbers and a text string). The data are entered by MySQL into the
table in the order listed in the command. For example, item_id is set to 30, shipment_id
is set to 20, quantity is set to 3, etc.
The second way of inserting data into a table requires that each column in the table be
explicitly mentioned in the insert statement, but allows for missing data in certain
circumstances. Note that the circumstances under which missing data are tolerated by
MySQL are limited to columns having yes in Null Column. No NULL is an integrity
constraint requiring data to be entered in each field that is so declared.
The following example illustrates the second way to insert data into a table. Note that
only a portion of the columns are represented in the example, but that all the No NULL
columns have appropriate data inserts:
insert into shipment_items
(item_id, shipment_id, quantity, description, weight,
ship_cost)
values (31, 20, 3, “Our Other New Widget”, 20, 12.50 );
Also note that the columns to be INSERTed are listed in parentheses immediately after
the table_name (i.e., before the values keyword). And, finally, note that no data were
entered for the height and width fields for this record. As long as the there is no NO
NULL constraint on the field, and the columns into which data will be placed are
explicitly named in the statement, this will work fine. Data for the missing fields can
always be added later with the update command.
UPDATE
The update command is used to modify or change existing data in a table. If you insert
data into a table, commit the change, and discover later that there in an error with your
INSERTed data, you can always update the data. Assume that the shipping cost for “Our
Other New Widget”, INSERTed in the above section is found to be in error. We can fix it
with update. First we need to get the item_id for our inserted data. This can be done with
the following select command:
select item_id, description
from shipment_items
where description = “Our Best New Widget”;
In my case shipment_id is 30. In your case it might have a different value. Now we can
use the update command to update the cost:
update shipment_items
set ship_cost = 12.75
where item_id = 30;
The update command works with the set keyword to modify the value of the indicated
field. However, and this is extremely important, the where clause is needed to limit the
effect of the update command. The update command will work without the where clause,
but if where is not specified the update command will modify all ship_cost fields to the
value specified!
Is there any way we can undo this?
DELETE
The delete command works with the from keyword, and can also be limited with the
where clause. Delete removes an entire record (row) from the database. Delet is not used
to remove a value from a single field (if you wish to remove a single value, update that
field and set its value to NULL). The following example illustrates the use of DELETE:
delete from shipment_items
where ship_cost = 12.75;
The above statement will remove the entire row wherever ship_cost = 12.75. Is that
acceptable? Perhaps it is given our present shipment_items table. However, assuming
we want to DELETE the row we INSERTed and UPDATed in the above sections, a safer
and better approach would be to specify a where parameter unique to the specific record
(row). Setting the where clause to the value of the primary key for the record will ensure
that only that record is affected. The primary key uses the unique and No NULL
integrity constraints (again, more later). On that basis, a better delet statement would be:
delete from shipment_items
where item_id = 30;
By the way, it was mentioned above that where limits delete just as it does update. Will
delete work without a WHERE clause? Yes it will, but:
delete from shipment_items;
without a where clause will delete every row from the table!!!
PART III – Problems
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. Open text editor. 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
16. the changes.
17. Rollback the changes made to your savepoint.
18. Commit the changes from this transaction.
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades