-- create schema `Assignment 2`;


DROP TABLE IF EXISTS SELLS;
DROP TABLE IF EXISTS BEVERAGE;
DROP TABLE IF EXISTS STORE;

CREATE TABLE BEVERAGE
(
    code int PRIMARY KEY ,
    name VARCHAR(15),
    size VARCHAR(15)
);
CREATE TABLE STORE
(
    name  VARCHAR(20) PRIMARY KEY ,
    area  VARCHAR(20),
    phone VARCHAR(10)
);
CREATE TABLE SELLS
(
    store_name VARCHAR(15),
    code       int,
    price      int,
    PRIMARY KEY (store_name, code),
    FOREIGN KEY (store_name) REFERENCES STORE(name),
    FOREIGN KEY (code) REFERENCES BEVERAGE(code)
);

INSERT INTO STORE(name, area, phone)
VALUES ('bilka', 'Horsens', 12345678);
INSERT INTO STORE(name, area, phone)
VALUES ('føtex', 'Horsens', 22345678);
INSERT INTO STORE(name, area, phone)
VALUES ('superbrugsen', 'Vejle', 32345678);
INSERT INTO STORE(name, area, phone)
VALUES ('rema', 'Aarhus', 42345678);
INSERT INTO STORE(name, area, phone)
VALUES ('Netto', 'Randers', 52345678);

INSERT INTO BEVERAGE(code, name, size) VALUES (301, 'wine', 'medium');
INSERT INTO BEVERAGE(code, name, size) VALUES (302, 'wine', 'small');
INSERT INTO BEVERAGE(code, name, size) VALUES (303, 'coffee', 'maxi');
INSERT INTO BEVERAGE(code, name, size) VALUES (305, 'coffee', 'small');
INSERT INTO BEVERAGE(code, name, size) VALUES (306, 'lemonade', 'maxi');
INSERT INTO BEVERAGE(code, name, size) VALUES (307, 'lemonade', 'medium');
INSERT INTO BEVERAGE(code, name, size) VALUES (309, 'water', 'maxi');
INSERT INTO BEVERAGE(code, name, size) VALUES (310, 'water', 'medium');
INSERT INTO BEVERAGE(code, name, size) VALUES (311, 'water', 'small');
INSERT INTO BEVERAGE(code, name, size) VALUES (313, 'juice', 'medium');
INSERT INTO BEVERAGE(code, name, size) VALUES (314, 'juice', 'small');
INSERT INTO BEVERAGE(code, name, size) VALUES (315, 'tea', 'maxi');
INSERT INTO BEVERAGE(code, name, size) VALUES (317, 'tea', 'small');
INSERT INTO BEVERAGE(code, name, size) VALUES (318, 'milkshake', 'maxi');
INSERT INTO BEVERAGE(code, name, size) VALUES (319, 'milkshake', 'medium');
INSERT INTO BEVERAGE(code, name, size) VALUES (321, 'smoothie', 'maxi');
INSERT INTO BEVERAGE(code, name, size) VALUES (322, 'smoothie', 'medium');
INSERT INTO BEVERAGE(code, name, size) VALUES (323, 'smoothie', 'small');
INSERT INTO BEVERAGE(code, name, size) VALUES (325, 'cocoa', 'medium');
INSERT INTO BEVERAGE(code, name, size) VALUES (326, 'cocoa', 'small');
INSERT INTO BEVERAGE(code, name, size) VALUES (327, 'green tea', 'maxi');
INSERT INTO BEVERAGE(code, name, size) VALUES (329, 'green tea', 'small');
INSERT INTO BEVERAGE(code, name, size) VALUES (330, 'black tea', 'maxi');
INSERT INTO BEVERAGE(code, name, size) VALUES (331, 'black tea', 'medium');
INSERT INTO BEVERAGE(code, name, size) VALUES (333, 'orange tea', 'maxi');
INSERT INTO BEVERAGE(code, name, size) VALUES (334, 'orange tea', 'medium');
INSERT INTO BEVERAGE(code, name, size) VALUES (335, 'orange tea', 'small');
INSERT INTO SELLS(store_name, code, price) VALUES ('rema', 335, 8);
INSERT INTO SELLS(store_name, code, price) VALUES ('bilka', 306, 8);
INSERT INTO SELLS(store_name, code, price) VALUES ('Netto', 307, 228);
INSERT INTO SELLS(store_name, code, price) VALUES ('superbrugsen', 310, 268);
INSERT INTO SELLS(store_name, code, price) VALUES ('rema', 311, 45);
INSERT INTO SELLS(store_name, code, price) VALUES ('Netto', 314, 153);
INSERT INTO SELLS(store_name, code, price) VALUES ('bilka', 315, 161);
INSERT INTO SELLS(store_name, code, price) VALUES ('bilka', 318, 106);
INSERT INTO SELLS(store_name, code, price) VALUES ('rema', 319, 113);
INSERT INTO SELLS(store_name, code, price) VALUES ('rema', 321, 279);
INSERT INTO SELLS(store_name, code, price) VALUES ('superbrugsen', 322, 334);
INSERT INTO SELLS(store_name, code, price) VALUES ('rema', 334, 308);
INSERT INTO SELLS(store_name, code, price) VALUES ('bilka', 335, 72);
INSERT INTO SELLS(store_name, code, price) VALUES ('rema', 309, 8);
INSERT INTO SELLS(store_name, code, price) VALUES ('bilka', 309, 6);
INSERT INTO SELLS(store_name, code, price) VALUES ('superbrugsen', 309, 4);


INSERT INTO SELLS(store_name, code, price) VALUES ('superbrugsen', 301, 150);
INSERT INTO SELLS(store_name, code, price) VALUES ('Netto', 301, 150);
INSERT INTO SELLS(store_name, code, price) VALUES ('bilka', 301, 100);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 301, 50);


INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 302, 188);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 303, 345);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 305, 314);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 306, 314);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 307, 314);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 309, 71);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 310, 71);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 311, 71);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 313, 62);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 314, 62);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 315, 62);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 317, 88);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 318, 88);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 319, 88);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 321, 88);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 322, 88);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 323, 278);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 325, 76);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 326, 272);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 327, 95);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 329, 155);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 330, 189);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 331, 269);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 333, 111);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 334, 111);
INSERT INTO SELLS(store_name, code, price) VALUES ('føtex', 335, 111);