SQL-complete-tutorial

1
USE sql_invoices;
2

3
-- inputing where client_id is a single number
4
UPDATE invoices
5
SET 
6
	payment_total = invoice_total * 0.5,
7
    payment_date = due_date 
8
WHERE client_id =
9
			(SELECT client_id
10
            FROM clients
11
            WHERE name = 'Myworks');
12

13
-- inputing where client_id is multiple numbers
14
UPDATE invoices
15
SET 
16
	payment_total = invoice_total * 0.5,
17
    payment_date = due_date 
18
WHERE client_id IN
19
            (SELECT client_id
20
            FROM clients
21
            WHERE state IN ('CA','NY'));
22
            
23
-- find the gold customers and add comments for them
24

25
USE sql_store;
26

27
UPDATE orders
28
SET comments = 'Gold customer'
29
WHERE customer_id IN
30
			(SELECT DISTINCT c.customer_id
31
			FROM orders o
32
			JOIN customers c
33
				USING (customer_id)
34
			WHERE c.points > 3000);
35
            
36
UPDATE orders
37
SET comments = 'Gold customer'
38
WHERE customer_id IN
39
			(SELECT customer_id
40
			FROM customers
41
			WHERE points > 3000);
42