Instructions In simplified form, you are going to be producing a script. The script will create 2
Question:
Instructions
In simplified form, you are going to be producing a script. The script will create 2 tables, load the 2 tables with data and then using PL/SQL it will process those 2 tables and with 2 SQL statements produce the resulting output for the 2 processed tables.
The script must run completely and accurately to produce the proper output. A script that does not run properly will be considered incomplete and needs to be resubmitted and be complete. No marks for resubmission. Get it right the first time, please.
The script will create 2 tables
Table custxx where xx is replaced with your Oracle id
Table transxx where xx is replaced with your Oracle id
CUSTXX
Into custxx you will copy 2 columns from your customers table, cust_no and phone_no, but the new columns will be called cust_no, and balance. Only copy customers with cust_no up to 1050.
TRANSXX
This table needs to be created and contains the following columns
It is loaded with the following data:
The status column will be left blank or NULL
1001 | u | 213 | 2022-10-29 |
1002 | u | 1002 | 2022-10-29 |
1003 | u | 333 | 2022-10-30 |
1004 | u | 4000 | 2022-10-30 |
1002 | u | 2000 | 2022-10-31 |
1006 | u | 600 | 2022-10-31 |
1010 | i | 213 | 2022-10-29 |
1011 | i | 1002 | 2022-10-29 |
1100 | i | 333 | 2022-10-30 |
1012 | d | 2022-10-30 | |
1013 | i | 2000 | 2022-10-31 |
1014 | x | 600 | 2022-10-31 |
In the operation column
U is for update. The update changes the balance to balance to this new value. On an insert, if the account already exists, an update is done. On an update, if the account does not exist, it is created by an insert.
D is for delete. On a delete, if the row does not exist, no action is taken.
I is for increasing the balance if it is positive or decreasing if negative
Anything else is an incorrect operation code.
Your PL/SQL will run the TRANSXX against the CUSTXX.
1. It will update the balance in custxx
2. It will put an appropriate message in the status column in transxx
There will be an SQL statement at the end of the script to show the new values in custxx and transxx
On an insert, if the account already exists, an update is done instead. On an update, if the account does not exist, it is created by an insert. On a delete, if the row does not exist, then only the status will indicate it did not exist. Example: Delete: ID does not exist
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill