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
 

Transcribed Image Text:

COLUMN_NAME 1 CUST_NO 2 OPERATION 3 AMOUNT 4 STATUS 5 WHEN DATA_TYPE NUMBER (4,0) CHAR (1 BYTE) NUMBER (7,0) VARCHAR2 (50 BYTE) DATE COLUMN_NAME 1 CUST_NO 2 OPERATION 3 AMOUNT 4 STATUS 5 WHEN DATA_TYPE NUMBER (4,0) CHAR (1 BYTE) NUMBER (7,0) VARCHAR2 (50 BYTE) DATE

Answer rating: 100% (QA)

1 Answer for updating the balance in custxx The PLSQL script will update the balance in custxx by using an UPDATE statement This statement will be used to modify the existing records in the custxx tab... View the full answer