SHARE
TWEET

Untitled

a guest Oct 23rd, 2019 77 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE is_invoice;
  2.  
  3. DROP TABLE IF EXISTS customer, orders, products, order_products;
  4.  
  5. create 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.    
  11. CREATE 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.  
  20. CREATE TABLE products (
  21.     id INT,
  22.     descr VARCHAR(80) NOT NULL,
  23.     price FLOAT NOT NULL,
  24.    
  25.     PRIMARY KEY(id)
  26.     );
  27.    
  28. CREATE 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.    
  40. INSERT INTO customer VALUES (1, "Person 1", "Kolpinghaus");
  41. INSERT INTO customer VALUES (2, "Person 2", "Kolpinghaus");
  42. INSERT INTO customer VALUES (3, "Person 3", "Kolpinghaus");
  43.  
  44. INSERT INTO products VALUES (1, "Drucker MSP45", 1000);
  45. INSERT INTO products VALUES (2, "Bildschirm VGA", 500);
  46.  
  47. INSERT INTO orders VALUES (1, 1, "1970-01-01");
  48. INSERT INTO orders VALUES (2, 2, "1870-02-02");
  49.  
  50. INSERT INTO order_products VALUES (1, 1, 1, 1, 5);
  51. INSERT INTO order_products VALUES (2, 1, 1, 2, 100);
  52. INSERT INTO order_products VALUES (2, 1, 1, 1, 4);
  53.  
  54. -- Get Product-ID of most sold product
  55.  
  56. SELECT product_id, sum(amount)
  57. FROM order_products
  58. GROUP BY product_id
  59. ORDER BY sum(amount) desc;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top