-- base RH -------

-- pour la reproductibilité
SELECT setseed(0);

-- clean
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dep;

-- schema
CREATE TABLE dep(
    depname text PRIMARY KEY,
    parent text NULL REFERENCES dep(depname)
);


CREATE TABLE emp(
    depname text NOT NULL REFERENCES dep(depname),
    empno int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    salary int NOT NULL
);

-- dataset, toy sample
INSERT INTO dep VALUES
    ('direction', NULL),
    ('production', 'direction'),
    ('personnel', 'direction'),
    ('sales', 'direction'),
    ('develop', 'production'),
    ('maintenance', 'production'),
    ('team 1', 'develop'),
    ('team 2', 'develop');


INSERT INTO emp(depname, empno, salary) VALUES
    ('develop'   , 11, 5200),
    ('develop'   ,  7, 4200),
    ('develop'   ,  9, 4500),
    ('develop'   ,  8, 6000),
    ('develop'   , 10, 5200),
    ('personnel' ,  5, 3500),
    ('personnel' ,  2, 3900),
    ('sales'     ,  3, 4800),
    ('sales'     ,  1, 5000),
    ('sales'     ,  4, 4800),
    ('sales'     ,  12, 4200);

VACUUM ANALYZE emp, dep;