Create a table for invoices that have been paid. Name this table invoicespaid. T

Question

Create a table for invoices that have been paid. Name this table invoicespaid. The table should have columns for the VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal, AccountNo, InvoiceLineItemAmount, and InvoiceLineItemDescription. Insert into the invoicespaid table all of the records from the invoices and invoicelineitems tables that have been paid. Now, you want to mark those records that have been moved to this table. Add a column named moved to the invoices table. Write an update statement to update the records that were moved to true. All other records should be false. You need to turn in 1 .sql file that contains 1 create table statement, 1 insert statement, 1 alter table statement, 1 update statement.

Explanation / Answer

CREATE TABLE invocespaid (
VendorName varchar(15),
InvoiceNumber int(10),
InvoiceDate date,
InvoiceTotal float(10),
AccountNo int(10),
InvoiceLineItemAmount float(15),
InvoiceLineItemDescription varchar(50)
);

INSERT INTO invocespaid (VendorName,InvoiceNumber,InvoiceDate,InvoiceTotal,AccountNo,InvoiceLineItemAmount,InvoiceLineItemDescription)
SELECT VendorName,InvoiceNumber,InvoiceDate,InvoiceTotal,AccountNo,InvoiceLineItemAmount,InvoiceLineItemDescription
FROM invoices;

ALTER TABLE invoices
ADD moved BOOLEAN;

INSERT INTO invoices (moved) VALUES(FALSE);

UPDATE invoices SET moved=TRUE where InvoiceNumber in (
SELECT InvoiceNumber
FROM invoicespaid
);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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