Question

Use ap tables

To test whether a table has been modified correctly as you do these exercises, you can write and run an appropriate SELECT statement. Or, when you’re using Oracle SQL Developer, you can click on a table name in the Connections window and then on the Data tab to display the data for all of the columns in the table. To refresh the data on this tab, click the Refresh button.

1. Write an INSERT statement that adds this row to the Invoices table:

invoice_id                 The next id in sequence (find out what this should be)
vendor_id:                 32
invoice_number:        AX-014-027
invoice_date:             8/1/2008
invoice_total:            $434.58
payment_total:          $0.00
credit_total:               $0.00
terms_id:                   2
invoice_due_date:     8/31/2008
payment_date:           null

2. Write an UPDATE statement that modifies the Vendors table. Change the default account number to 403 for each vendor that has a default account number of 400.
3. Write an UPDATE statement that modifies the Invoices table. Change the terms_id to 2 for each invoice that’s for a vendor with a default_terms_id of 2.
4. Write a DELETE statement that deletes the row that you added to the Invoices table in exercise 1.
5. After you have verified that all of the modifications for the first four exercises have been successful, rollback the changes. Then, verify that they have been rolled back.

Answer 1

NOTE:

• Execute 'commit;' command before all the queries so for exercise 5,when 'rollback'; command is executed for solution 5,it reverts all the changes till this point.

SOLUTION 1:

insert into invoice(invoice_id,vendor_id,invoice_number,invoice_date,invoice_total,payment_total,

credit_total,terms_id,invoice_due_date,payment_date) select max(invoice_id)+1,32,AX-014-027,'8/1/2008',$434.58,$0.00,$40.00,2,'8/31/2008',null from invoice;

Explanation :

• 'select max(invoice_id)+1,32,AX-014-027,'8/1/2008',$434.58,$0.00,$40.00,2,'8/31/2008',null from invoice; ' creates a temperory table and these values are inserted into invoice

SOLUTION 2:

alter table vendors alter account_number set default 403;

Explanation :

• changes the default value from 400 to 403

SOLUTION 3:

update invoice set term_id = 2 where vendor_id = 2;

SOLUTION 4:

delete from invoice order by invoice_id desc limit 1;

Explanation :

• In exercise 1,the record was inserted in the last row,so using the invoice_id it is arranged in descending order and the first record is deleted

SOLUTION 5:

rollback;

For verifying purpose execute 'desc vendors' and the default value for account_number should be 400 and not 403.