M5q6bSFT

1USE is_invoice;
2
3DROP TABLE IF EXISTS customer, orders, products, order_products;
4
5create table customer(
6	id int  primary key,
7	name varchar(80) not null default 'Anton Hofmann',
8	address varchar(80) not null default 'address n/a'
9);
10    
11CREATE TABLE orders (
12    id INT,
13    user_id INT,
14    order_date DATE,
15    
16    PRIMARY KEY(id),
17	FOREIGN KEY (user_id) REFERENCES customer(id)
18	);
19
20CREATE TABLE products (
21	id INT,
22    descr VARCHAR(80) NOT NULL,
23    price FLOAT NOT NULL,
24    
25    PRIMARY KEY(id)
26	);
27    
28CREATE TABLE order_products (
29	pos INT NOT NULL,
30    order_id INT,
31    user_id INT,
32    product_id INT,
33    amount INT,
34    
35    FOREIGN KEY (product_id) REFERENCES products(id),
36    FOREIGN KEY (user_id) REFERENCES customer(id),
37    FOREIGN KEY (order_id) REFERENCES orders(id)
38    );
39    
40INSERT INTO customer VALUES (1, "Person 1", "Kolpinghaus");
41INSERT INTO customer VALUES (2, "Person 2", "Kolpinghaus");
42INSERT INTO customer VALUES (3, "Person 3", "Kolpinghaus");
43
44INSERT INTO products VALUES (1, "Drucker MSP45", 1000);
45INSERT INTO products VALUES (2, "Bildschirm VGA", 500);
46
47INSERT INTO orders VALUES (1, 1, "1970-01-01");
48INSERT INTO orders VALUES (2, 2, "1870-02-02");
49
50INSERT INTO order_products VALUES (1, 1, 1, 1, 5);
51INSERT INTO order_products VALUES (2, 1, 1, 2, 100);
52INSERT INTO order_products VALUES (2, 1, 1, 1, 4);
53
54-- Get Product-ID of most sold product
55
56SELECT product_id, sum(amount)
57FROM order_products
58GROUP BY product_id
59ORDER BY sum(amount) desc;