-- Collection of code snippets by Arne Vajhøj 
-- (from articles on eksperten.dk / vajhoej.dk written sometime between 2004 and now) 
USE test;
CREATE TABLE customer (
    id INTEGER NOT NULL,
    name VARCHAR(32),
    phone VARCHAR(16),
    PRIMARY KEY(id)
);
CREATE TABLE customer_potential (
    id INTEGER NOT NULL,
    source VARCHAR(256),
    PRIMARY KEY(id)
);
CREATE TABLE customer_actual (
    id INTEGER NOT NULL,
    address1 VARCHAR(64),
    address2 VARCHAR(64),
    contact VARCHAR(32),
    discount INTEGER,
    PRIMARY KEY(id)
);
CREATE TABLE order_main (
    id INTEGER NOT NULL,
    customer INTEGER NOT NULL,
    status VARCHAR(16),
    PRIMARY KEY(id)
);
CREATE TABLE order_line (
    id INTEGER NOT NULL,    
    order_main INTEGER NOT NULL,
    item VARCHAR(32),
    quantity INTEGER,
    price DECIMAL(12,2),
    PRIMARY KEY(id)
);
COMMIT;
INSERT INTO customer VALUES(1, 'A company', '1111-1111');
INSERT INTO customer VALUES(2, 'B company', '2222-2222');
INSERT INTO customer VALUES(3, 'C company', '3333-3333');
INSERT INTO customer VALUES(4, 'D company', '4444-4444');
INSERT INTO customer VALUES(5, 'E company', '5555-5555');
INSERT INTO customer_potential VALUES(1, 'Sleazy information seller');
INSERT INTO customer_potential VALUES(2, 'Sleazy information seller');
INSERT INTO customer_actual VALUES(3, 'C road 3', 'C town', 'Mr. C', 10);
INSERT INTO customer_actual VALUES(4, 'D road 4', 'D town', 'Mr. D', 20);
INSERT INTO customer_actual VALUES(5, 'E road 5', 'E town', 'Mr. E', 10);
COMMIT;
INSERT INTO order_main VALUES(1, 3, 'Delivered');
INSERT INTO order_main VALUES(2, 4, 'Delivered');
INSERT INTO order_main VALUES(3, 4, 'Delivered');
INSERT INTO order_main VALUES(4, 4, 'Delivered');
INSERT INTO order_main VALUES(5, 5, 'Delivered');
INSERT INTO order_line VALUES(1, 1, 'X stuff', 1, 75.00);
INSERT INTO order_line VALUES(2, 2, 'Y stuff', 10, 40.00);
INSERT INTO order_line VALUES(3, 3, 'Y stuff', 5, 40.00);
INSERT INTO order_line VALUES(4, 4, 'Y stuff', 20, 40.00);
INSERT INTO order_line VALUES(5, 5, 'X stuff', 1, 72.00);
INSERT INTO order_line VALUES(6, 5, 'Y stuff', 1, 45.00);
INSERT INTO order_line VALUES(7, 5, 'Z stuff', 1, 90.00);
COMMIT;
CREATE TABLE abbr (
    id INTEGER NOT NULL,
    term VARCHAR(32),
    PRIMARY KEY(id)
);
INSERT INTO abbr VALUES(1, 'Internationalization');
INSERT INTO abbr VALUES(2, 'Localization');
COMMIT;
CREATE TABLE ts (
    id INTEGER NOT NULL,
    dt DATETIME,
    PRIMARY KEY (id)
);
INSERT INTO ts VALUES(1, '2023-10-01 08:00:00');
INSERT INTO ts VALUES(2, '2023-11-01 08:00:00');
INSERT INTO ts VALUES(3, '2023-12-01 08:00:00');
COMMIT;
CREATE TABLE aid (
    id INTEGER NOT NULL AUTO_INCREMENT,
    val VARCHAR(255),
    PRIMARY KEY(id)
);
COMMIT;
CREATE TABLE counter (
    id INTEGER NOT NULL,
    val INTEGER,
    PRIMARY KEY(id)
);
INSERT INTO counter VALUES(1, 0);
COMMIT;
