Database Management System question: Task: Update the INVOICE_AMOUNT to add the

Question

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))

Explanation / Answer

This the corrected code for table creation :

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

This is the query :

UPDATE invoice SET invoice_amount =(SELECT sum (invoice_item_qty*invoice_item_price) from invoice_item WHERE invoice.invoice_ID=invoice_item.invoice_ID)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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