--PostgreSQL 9.6
--'\\' is a delimiter

select version() as postgresql_version;

set datestyle to 'DMY';

create table region(
    id_reg integer,
    name_reg varchar(80)
    );

create table prov(
    id_pr integer,
    id_reg integer,
    name_pr varchar(80)
    );

create table depot(
    id_pr integer,
    id_depot integer,
    name_depot varchar(80),
    t_amount integer,
    t_volume float(53)
    );

--    Нафтобаза: код області, код нафтобази, назва нафтобази, кількість резервуарів, об’єм
--резервуарів;


create table flow(
    id_depot integer,
    flow float,
    f_date date
    );

-- Облік: код нафтобази, кількість завезених нафтопродуктів, дата занесення даних

ALTER TABLE region ADD PRIMARY KEY(id_reg);
ALTER TABLE prov ADD PRIMARY KEY(id_pr);
ALTER TABLE depot ADD PRIMARY KEY(id_depot);

ALTER TABLE prov ADD CONSTRAINT cs_prov FOREIGN
KEY(id_reg) REFERENCES region(id_reg);

INSERT INTO region (id_reg,name_reg) VALUES(1,'Днiпровсько-Донецька нафтогазоносна область');
INSERT INTO region (id_reg,name_reg) VALUES(2,'Передкарпатська нафтогазоносна область');
INSERT INTO region (id_reg,name_reg) VALUES(3,'Причорноморсько-Кримська газонафтоносна впадина');

INSERT INTO prov (id_pr,id_reg,name_pr) VALUES(1,2,'Iвано-Франкiвська');
INSERT INTO prov (id_pr,id_reg,name_pr) VALUES(2,2,'Закарпатська');
INSERT INTO prov (id_pr,id_reg,name_pr) VALUES(3,2,'Чернiвецька');
INSERT INTO prov (id_pr,id_reg,name_pr) VALUES(4,2,'Львiвська');
INSERT INTO prov (id_pr,id_reg,name_pr) VALUES(5,1,'Луганська');
INSERT INTO prov (id_pr,id_reg,name_pr) VALUES(6,1,'Харкiвська');
INSERT INTO prov (id_pr,id_reg,name_pr) VALUES(7,3,'Одеська');
INSERT INTO prov (id_pr,id_reg,name_pr) VALUES(8,3,'Кримська АР');

INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(1,1,'Снятин НБ',28,93053);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(2,2,'Майдан НБ',36,9479);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(2,3,'Чоп НБ',37,16936);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(3,4,'Вижниця НБ',210,18114);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(3,5,'Чернівці НБ',16,16902);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(4,6,'Ходорів НБ',110,37190);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(4,7,'Яворів НБ',320,172652);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(5,8,'Мілове НБ',44,3066);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(6,9,'Есхар НБ',156,15675);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(7,10,'Сарата НБ',40,15610);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(7,11,'Городнє НБ',25,7626);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(7,12,'Берізка НБ',333,12755);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(8,13,'Бахчи НБ',25,7053);
INSERT INTO depot(id_pr, id_depot, name_depot, t_amount, t_volume)
VALUES(8,14,'Судак НБ',33,10527);

INSERT INTO flow(id_depot,flow,f_date)
VALUES(1,188200,'12/08/1997');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(2,80060,'09/06/1995');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(3,77432,'23/08/1991');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(4,30400,'20/12/1995');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(5,138150,'23/11/1998');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(6,87641,'15/12/1990');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(6,2,'18/06/1990');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(7,63380,'28/07/1990');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(8,28700,'17/03/1993');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(9,24711,'25/11/1997');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(10,60400,'15/12/1990');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(10,44400,'08/03/1992');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(11,28800,'06/07/1998');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(12,24400,'02/04/1998');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(13,41300,'12/02/1999');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(14,52440,'19/10/1991');
INSERT INTO flow(id_depot,flow,f_date)
VALUES(13,49900,'11/12/1991');

SELECT * FROM region;

SELECT * FROM prov;

SELECT * FROM depot;

SELECT * FROM flow;

SELECT name_reg, name_pr FROM prov,region WHERE prov.id_reg = region.id_reg;

SELECT name_depot, name_pr, t_amount, t_volume FROM depot,prov WHERE prov.id_pr = depot.id_pr;

SELECT name_depot, flow, f_date FROM depot,flow WHERE flow.id_depot=depot.id_depot;