Question

1) Write an INSERT statement that adds this row to the Invoices table: i

nvoice_id: The next automatically generated ID

vendor_id : 32

invoice_number: AX-014-027

invoice_date: 8/1/2014

invoice_total: $434.58

payment_total: $0.0

credit_total: $0.0

terms_id: 2

invoice_due_date: 8/31/2014

payment_date: null

Write this statement without using a column list.: Use DEFAULT to insert the automatically generated ID.

2) Write an INSERT statement that adds these rows to the Invoice_line_Items table:

invoice_sequence: 1 2

account_number: 160 527

line_item_amount: $180.23 $254.35

line_item_description: Hard drive Exchange Server update

Set the invoice_id of these two rows to the invoice ID that was generated by MySQL for the invoice you added in question 1

3) Write an UPDATE statement that modifies the invoice you added in question 1. This statement should change the credit_total column so its 10% of the invoice_total column, and it should change the payment_total column so that the sum of the payment_total and credit_total are equal to the invoice_total column.

4) Write a DELETE statement that deletes the row that you added to the Invoices table in question 1. When you execute this statement, it will produce an error since the invoice has related rows in the Invoice_Line_Items table. To fix that, precede the DELETE statement with another DELETE statement that deletes the line items for this invoice. (Remember that to code two or more statements in a script, you must end each statement with a semicolon.

Answer 1

• INSERT statement to add specified row in Invoices table

Explanation: I have specified 2 options to insert the data. This is because I am unsure if the type of columns invoice_total, payment_total, credit_total is to store decimal values only or do we need to store '$' symbol as well. You may select the right insert statement based on data type of those columns and if you want to store '$' symbol.

-- Option 1 - If the columns invoice_total, payment_total and credit_total are of type decimal
INSERT INTO Invoices 
VALUES (DEFAULT, 32, 'AX-014-027', STR_TO_DATE('8/1/2014', '%m/%d/%Y'), 434.58, 0.0, 0.0, 2, STR_TO_DATE('8/31/2014', '%m/%d/%Y'), null);

-- Option 2 - If you have to store '$' sign in columns for invoice_total, payment_total and credit_total
INSERT INTO Invoices 
VALUES (DEFAULT, 32, 'AX-014-027', STR_TO_DATE('8/1/2014', '%m/%d/%Y'), '$434.58', '$0.0', '$0.0', 2, STR_TO_DATE('8/31/2014', '%m/%d/%Y'), null);

• INSERT statement to add 2 rows to Invoice_line_Items table

Explanation: I have specified 2 options to insert the data. This is because I am unsure if the type of column line_item_amount is to store decimal values or do we need to store '$' symbol as well. You may select the right insert statement based on data type of the column and if you want to store '$' symbol.

In addition, I have written single INSERT statement to insert multiple rows. You may split them into multiple INSERT statements if you like.

Important note: LAST_INSERT_ID() function provides value of automatically generated ID. In INSERT statement on Invoices table, we have used automatically generated ID for invoice_id, the value of this will be available in function LAST_INSERT_ID()

--Option 1 - If column line_item_amount is of type decimal
INSERT INTO Invoice_line_items 
VALUES (1, 160, 180.23, 'Hard drive', LAST_INSERT_ID()),
       (2, 527, 254.35, 'Exchange Server update', LAST_INSERT_ID());

--Option 2 - If you want to store '$' symbol in column line_item_amount
INSERT INTO Invoice_line_items 
VALUES (1, 160, '$180.23', 'Hard drive', LAST_INSERT_ID()),
       (2, 527, '$254.35', 'Exchange Server update', LAST_INSERT_ID());

• UPDATE statement to modify credit_total and invoice_total

Explanation: I have specified 2 options to insert the data. This is because I am unsure if the type of columns invoice_total, payment_total, credit_total is to store decimal values only or do we need to store '$' symbol as well. You may select the right insert statement based on data type of those columns and if you want to store '$' symbol.

The second option seems more complicated as you need to remove '$' sign first, convert the remaining amount to decimal format and then concat '$' symbol back before updating the value in table.

Here we again use LAST_INSERT_ID() function to get the invoice_id added in first INSERT statement.

--Option 1 - If the columns invoice_total, payment_total and credit_total are of type decimal
UPDATE INVOICES
SET credit_total = 0.1 * invoice_total,
payment_total = invoice_total - credit_total
WHERE invoice_id = LAST_INSERT_ID();

--Option 2 - If you have to store '$' sign in columns for invoice_total, payment_total and credit_total
UPDATE INVOICES
SET credit_total = concat('$', 0.1 * convert(replace(invoice_total,'$',''),decimal(10,2))),
payment_total = concat('$', convert(replace(invoice_total,'$',''),decimal(10,2)) - convert(replace(credit_total,'$',''),decimal(10,2)))
WHERE invoice_id = LAST_INSERT_ID();

• DELETE row from from invoice_line_items and invoices tables for invoice_id added in first INSERT statement

Explanation: We first delete data from child table (Invoice_line_items) as it has rows related to data being deleted from parent table (Invoices). We use LAST_INSERT_ID() function to get the invoice_id inserted using first INSERT statement.

DELETE FROM Invoice_line_items
WHERE invoice_id = LAST_INSERT_ID();

DELETE FROM INVOICES
WHERE invoice_id = LAST_INSERT_ID();