Database Management System question:

Task: Update the INVOICE_AMOUNT to add the line items added in 3) to the INVOICE_AMOUNT field. You must do this with a query that would work regardless if there were existing INVOICE_ITEM rows for this INVOICE. Your query cannot hard code the amount.

I created the tables with a couple values and attempted to answer the question but I keep receiving erros and I cannot figure out how to correct it. Any help would be greatly appreciated!

-- Customer Table CREATE TABLE customer (cust_ID NUMERIC(4) NOT NULL, cust_name VARCHAR2(25CHAR) NOT NULL, cust_address VARCHAR2(25CHAR) NOT NULL, cust_city VARCHAR2(15CHAR), cust_state VARCHAR2(2CHAR), cust_zip NUMERIC(5), CONSTRAINT pk_customerID PRIMARY KEY (cust_ID));

-- Invoice Table CREATE TABLE invoice (invoice_ID NUMERIC(4), cust_ID NUMERIC(4), invoice_amount NUMERIC(10,4), CONSTRAINT pk_invID PRIMARY KEY (invoice_ID), CONSTRAINT fk_invID_custID FOREIGN KEY (cust_ID) REFERENCES customer(cust_ID));

-- Product Table CREATE TABLE product (product_ID VARCHAR2(7) NOT NULL, product_name VARCHAR2(255) NOT NULL, product_cost NUMERIC(5,2), CONSTRAINT pk_productID PRIMARY KEY (product_ID));

-- Invoice_Item Table CREATE TABLE invoice_item (invoice_ID NUMERIC(4), product_ID VARCHAR2(7), invoice_item_qty NUMERIC(4), invoice_item_price NUMERIC(4), CONSTRAINT pk_invoiceItems PRIMARY KEY (invoice_ID, product_ID), CONSTRAINT fk_invoiceItems_invoiceID FOREIGN KEY (invoice_ID) REFERENCES invoice (invoice_ID), CONSTRAINT fk_invoiceItems_productID FOREIGN KEY (product_ID) REFERENCES product (product_ID))

-- 1) INSERT INTO customer VALUES(222,'Dan Gerber','12 West Ave','Los Angelos','CA','21189');

-- 2) INSERT INTO invoice VALUES(0001,222,0);

-- 3) INSERT INTO product VALUES ('01','XX',5.00); INSERT INTO product VALUES ('02','YY',10.00); INSERT INTO invoice_item VALUES (0001,'01',2,5.00); INSERT INTO invoice_item VALUES (0001,'02',1,10.00);

-- 4) UPDATE invoice t1 SET t1.invoice_amount = (SELECT SUM (t2.invoice_item_qty * t2.invoice_item_price) FROM invoice t2 WHERE t2.fk_invoice_ID = 1.invoice_ID) WHERE t1.invoice_ID = (SELECT MAX(invoice_ID) FROM invoice WHERE fk_invID_custID = (SELECT MAX(cust_ID) FROM customer))