SQL-complete-tutorial

1
/* If the view doesn't have the following words:
2
-- DISTINCT
3
-- Aggregate Functions (MIN, MAX, SUM)
4
-- GROUP BY / HAVING
5
-- UNION
6
we consider it as an updateable view */
7

8
CREATE OR REPLACE VIEW invoices_with_balance AS
9
SELECT
10
	invoice_id,
11
    number,
12
    client_id,
13
    invoice_total,
14
    payment_total,
15
    invoice_total - payment_total AS balance,
16
    invoice_date,
17
    due_date,
18
    payment_date
19
FROM invoices
20
WHERE (invoice_total - payment_total) > 0;
21

22
DELETE FROM invoices_with_balance
23
WHERE invoice_id = 1;
24

25
UPDATE invoices_with_balance
26
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY)
27
WHERE invoice_id = 2;
28