Raf Vergauwen be6e320bb9 v1 | 2 jaren geleden | |
---|---|---|
README.md | 2 jaren geleden | |
bulb.png | 2 jaren geleden | |
question-mark.png | 2 jaren geleden |
Write an INSERT statement that adds this row to the Terms table:
Use MySQL Workbench to review the column definitions for the Terms table, and include a column list with the required columns in the INSERT statement.
Write an UPDATE statement that modifies the row you just added to the Terms table.
This statement should change the terms_description column to “Net due 125 days”.
And it should change the terms_due_days column to 125.
UPDATE
terms
SET
terms_description = "Net due 125 days",
terms_due_days = 125
WHERE
terms_id = 6;
DELETE FROM
terms
WHERE
terms_id = 6;
Write an INSERT statement that adds this row to the Invoices table:
Write this statement without using a column list.
Write an INSERT statement that adds these rows to the Invoice_Line_Items table:
line_item_description: Hard drive
invoice_sequence: 2
account_number: 527
line_item_amount: $254.35
line_item_description: Exchange Server update
Set the invoice_id column of these two rows to the invoice ID that was generated by MySQL for the invoice you added in exercise 4.
Write an UPDATE statement that modifies the invoice you added in exercise 4.
This statement should change the credit_total column so it’s 10% of the invoice_total column.
And it should change the payment_total column so the sum of the payment_total and credit_total columns are equal to the invoice_total column.
UPDATE
invoices
SET
credit_total = invoice_total * 0.1,
payment_total = invoice_total - credit_total
WHERE
invoice_id = 115;
Write an UPDATE statement that modifies the Vendors table.
Change the default account number column to 403 for the vendor with an ID of 44.
UPDATE
vendors
SET
default_account_number = 403
WHERE
vendor_id = 44;
Write an UPDATE statement that modifies the Invoices table.
Change the terms_id column to 2 for each invoice that’s for a vendor with a default_terms_id of 2.
UPDATE
invoices
SET
terms_id = 2
WHERE
vendor_id IN (
SELECT
vendor_id
FROM
vendors
WHERE
default_terms_id = 2
);
Write a DELETE statement that deletes the row that you added to the Invoices table in exercise 4.
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.)
DELETE FROM
invoice_line_items
WHERE
invoice_id = 115;
DELETE FROM invoices
WHERE
invoice_id = 115;