You want to create a new table in the USER_DTAB tablespace. The table named CH07
ID: 3716390 • Letter: Y
Question
You want to create a new table in the USER_DTAB tablespace. The table named CH07GOLD stores a history of the price of gold. A new row is added to the table approximately every two hours. Write the CREATE TABLE command for the table. Your command should address these points:
- The columns are named PRICE, PRICE_DATETIME, and TIME_BETWEEN.
- The PRICE can contain fractions of a penny as small as thousandths of a penny. The price is always under 1000 dollars.
- The TIME_BETWEEN column stores the number of days (up to 99 days), hours, minutes, and seconds (to the hundreth of a second) between the record and the previous record.
- Because the table is always recieving inserted data and is never updated, you want to minimize the storage space saved for updates.
- Assume that the average row length is 20 bytes, the table gets an average of 12 inserted records per day, and you want the table to be created with enough storage space for approximately 6 months.
Explanation / Answer
The create table command has been proovided under.It has three columns as required.The price column
will accept floating point values of price in dollars.It can also be a numeric with scale and precision
that is required. The PRICE_DATETIME column is a column with datatype date and finally the TIME_BETWEEN
column has been made a column with datatype TIMESTAMP. It stores year, month, day, hour, minute, and second values.
CREATE TABLE CH07GOLD
(
PRICE FLOAT,
PRICE_DATETIME DATE
TIME_BETWEEN TIMESTAMP,
CONSTRAINT CHKPRICERANGE CHECK(PRICE<1000)
);
The storage space functionality should be implemented by creating triggers which automatically inserts
data into the table if the insertion of data into the table is done after or before a specific
event occurs. The trigger thus allows olny the insertion of data
and saves a huge time and storage space issue for the insertion of data because the trigger fires only
for the task that is intended to do.
Example of a sample trigger:
CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
INSERT INTO CH07GOLD VALUES(125,'2000-05-13','2000-05-13 09:26:50.12')
EXCEPTION
WHEN ...
-- exception handling
END;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.