-- Collection of code snippets by Arne Vajhøj 
-- created to try various things (2003-now) 
CREATE TABLE animal_one (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    typ INTEGER NOT NULL,
    miceperday INTEGER,
    litermilkperday INTEGER
);

INSERT INTO animal_one VALUES(1,'Misse mor',1,3,NULL);
INSERT INTO animal_one VALUES(2,'Lille misser',1,0,NULL);
INSERT INTO animal_one VALUES(3,'Ko #1',2,NULL,10);
INSERT INTO animal_one VALUES(4,'Ko #2',2,NULL,10);

SELECT * FROM animal_one;

CREATE TABLE cat_two (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    miceperday INTEGER NOT NULL
);

INSERT INTO cat_two VALUES(1,'Misse mor',3);
INSERT INTO cat_two VALUES(2,'Lille misser',0);

CREATE TABLE cow_two (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    litermilkperday INTEGER NOT NULL
);

INSERT INTO cow_two VALUES(3,'Ko #1',10);
INSERT INTO cow_two VALUES(4,'Ko #2',10);

SELECT * FROM cat_two;
SELECT * FROM cow_two;

CREATE TABLE animal_three (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    typ INTEGER NOT NULL
);

INSERT INTO animal_three VALUES(1,'Misse mor',1);
INSERT INTO animal_three VALUES(2,'Lille misser',1);
INSERT INTO animal_three VALUES(3,'Ko #1',2);
INSERT INTO animal_three VALUES(4,'Ko #2',2);

CREATE TABLE cat_three (
    id INTEGER PRIMARY KEY,
    miceperday INTEGER NOT NULL
);

INSERT INTO cat_three VALUES(1,3);
INSERT INTO cat_three VALUES(2,0);

CREATE TABLE cow_three (
    id INTEGER PRIMARY KEY,
    litermilkperday INTEGER NOT NULL
);

INSERT INTO cow_three VALUES(3,10);
INSERT INTO cow_three VALUES(4,10);

SELECT * FROM animal_three JOIN cat_three ON animal_three.typ=1 AND animal_three.id=cat_three.id;
SELECT * FROM animal_three JOIN cow_three ON animal_three.typ=2 AND animal_three.id=cow_three.id;

DROP TABLE animal_one;
DROP TABLE cat_two;
DROP TABLE cow_two;
DROP TABLE animal_three;
DROP TABLE cat_three;
DROP TABLE cow_three;
