create table Inventory( I_FlowerNumber number , I_Location varchar(20), I_Condit

Question

create table Inventory( I_FlowerNumber number , I_Location varchar(20), I_Condition varchar(20), I_Search varchar(40),I_Delete varchar(20), I_Add number,I_Quantity number,PRIMARY KEY (I_FlowerNumber));

create table vendor (V_VendorID VARCHAR(20) ,V_PhoneNumber NUMBER, V_Address VARCHAR(20), V_DirectToStore VARCHAR(10), V_Status VARCHAR(5), V_LastUpdatedBy DATE, V_Password VARCHAR(10), V_LasteUpdateTime TIMESTAMP, PRIMARY KEY(V_VendorID ) );

CREATE TABLE purchase_order (

order_number int NOT NULL,

order_name char(50) NOT NULL,

customer_address char(50),

customer_zip int, customer_city char (50),

order_total varchar(50),

v_vendorID VARCHAR(20),

ITEM_NUMBER int NOT NULL,

CONSTRAINT pk_purchase_order PRIMARY KEY (order_number),

FOREIGN KEY (V_VendorID) REFERENCES vendor(V_VendorID),

FOREIGN KEY (ITEM_NUMBER) REFERENCES Inventory(I_FlowerNumber)

);

I need to answer the TWO questions below using the information above. I am having trouble getting the commands to work without getting error messages. What information do I need to enter in the parenthesis below to make the commands work?

insert into vendor(V_VendorID , V_PhoneNumber , V_Address , V_DirectToStore, V_Status , V_LastUpdatedBy, V_Password, V_LasteUpdateTime) values ('', 1, '', '', '', DATE '11/12/2016', '', CURRENT_TIMESTAMP);

insert into purchase_order (order_number, order_name, customer_address , customer_zip, customer_city , order_total) values (5, '', '', 8, '', '');

Explanation / Answer

Hi,

Ans 1- For this insert statement below are the errors I see-
insert into vendor(V_VendorID , V_PhoneNumber , V_Address , V_DirectToStore, V_Status , V_LastUpdatedBy, V_Password, V_LasteUpdateTime) values ('', 1, '', '', '', DATE '11/12/2016', '', CURRENT_TIMESTAMP);

Errors-
1. The V_VendorID is primary key of the table and hence must not be null. Need to suppy value for
2. The Date format given is incorrect. Need to provide date in proper format.

Correct statement-
insert into vendor(V_VendorID , V_PhoneNumber , V_Address , V_DirectToStore, V_Status , V_LastUpdatedBy,
V_Password, V_LasteUpdateTime) values ('123', 1, '', '', '', DATE '2016-11-12', '', CURRENT_TIMESTAMP);

Ans 2- For this insert statement, below are the errors in code-

Errors-
1. The column "order_name" has the constraint "NOT NULL" which mean that we cannot load null values for this column. Need to provide valid values for this column and it will work.

2. Columns v_vendorID VARCHAR(20),
ITEM_NUMBER int NOT NULL are the Foreign keys to this table. hence, we must not leave null for these columns.

Correct statement-
insert into purchase_order (order_number, order_name, customer_address , customer_zip, customer_city , order_total,v_vendorID,ITEM_NUMBER)
values (5, 'Happy', '', 8, '', '','123','300');

Note- Above insert will work provided we have v_vendorID=123 and ITEM_NUMBER=300 in the respective parent tables "vendor" and "Inventory"
FOREIGN KEY (V_VendorID) REFERENCES vendor(V_VendorID),

FOREIGN KEY (ITEM_NUMBER) REFERENCES Inventory(I_FlowerNumber)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.