data_mondrian_shop.sql

create database shop;

 -- --------------------------
-- department
-- ---------------------------

create table department
(
	id serial not null,
	name varchar(100) not null,
	city varchar(100) not null
);

create unique index department_id_uindex
	on department (id);

create unique index department_name_uindex
	on department (name);

alter table department
	add constraint department_pk
		primary key (id);


 -- --------------------------
-- visits
-- ---------------------------

create table visits
(
	id serial not null,
	department_id int not null
		constraint visits_department_id_fk
			references department
				on update restrict on delete restrict,
	day date not null,
	visitors int not null
);

create unique index visits_id_uindex
	on visits (id);

alter table visits
	add constraint visits_pk
		primary key (id);


 -- --------------------------
-- sales
-- ---------------------------

create table sales
(
	id serial not null,
	department_id int not null
		constraint sales_department_id_fk
			references department
				on update restrict on delete restrict,
	day date not null,
	client_surname varchar(30),
	seller_surname varchar(30)
);

create unique index sales_id_uindex
	on sales (id);

alter table sales
	add constraint sales_pk
		primary key (id);
		
-- ---------------------------
-- sales - VIEW
-- ---------------------------
		
create view public.sales_dnia as
	select count(id) as "sales", department_id
    from sales
    group by department_id;

-- ---------------------------
-- DANE
-- ---------------------------	
		
insert into department (id, name, city) values (1, 'BKDS Warsaw', 'Warszawa');
insert into department (id, name, city) values (2, 'BKDS Warsaw 2', 'Warszawa');
insert into department (id, name, city) values (3, 'BKDS Warsaw 3', 'Warszawa');
insert into department (id, name, city) values (4, 'BKDS Cracow', 'Kraków');
insert into department (id, name, city) values (5, 'BKDS Cracow 2', 'Kraków');
insert into department (id, name, city) values (6, 'BKDS Gdansk', 'Gdańsk');

insert into sales (id, department_id, client_surname, seller_surname, day) values (1, 4, 'Woodgate', 'Baulch', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (2, 6, 'Carson', 'Vaughn', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (3, 4, 'Horsefield', 'Baulch', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (4, 3, 'Meynell', 'Dimmock', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (5, 3, 'Kyd', 'Dimmock', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (6, 1, 'Cavilla', 'Kalker', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (7, 3, 'Stouther', 'Hadye', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (8, 4, 'Benedek', 'Baulch', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (9, 5, 'Spurgin', 'Ricart', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (10, 6, 'Brymner', 'Farren', '2020-08-01');
insert into sales (id, department_id, client_surname, seller_surname, day) values (11, 4, 'Vakhrushev', 'Baniard', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (12, 5, 'Avesque', 'Barszczewski', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (13, 3, 'Rowdell', 'Dimmock', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (14, 6, 'Ledley', 'Crowe', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (15, 1, 'Stanyan', 'Kalker', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (16, 4, 'Thominga', 'Baniard', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (17, 5, 'Schiersch', 'Ricart', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (18, 3, 'Sobtka', 'Ghidini', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (19, 2, 'Sleaford', 'Benzie', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (20, 6, 'Twallin', 'Farren', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (21, 2, 'Gosling', 'Benzie', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (22, 4, 'Butrimovich', 'Baulch', '2020-08-02');
insert into sales (id, department_id, client_surname, seller_surname, day) values (23, 2, 'Paolacci', 'Enns', '2020-08-03');
insert into sales (id, department_id, client_surname, seller_surname, day) values (24, 3, 'Hewkin', 'Hadye', '2020-08-03');
insert into sales (id, department_id, client_surname, seller_surname, day) values (25, 2, 'Wrotchford', 'Campes', '2020-08-03');
insert into sales (id, department_id, client_surname, seller_surname, day) values (26, 1, 'Steuart', 'Kalker', '2020-08-03');
insert into sales (id, department_id, client_surname, seller_surname, day) values (27, 4, 'Gremain', 'Baulch', '2020-08-03');
insert into sales (id, department_id, client_surname, seller_surname, day) values (28, 6, 'Skokoe', 'Farren', '2020-08-03');
insert into sales (id, department_id, client_surname, seller_surname, day) values (29, 1, 'Burnup', 'Kalker', '2020-08-03');
insert into sales (id, department_id, client_surname, seller_surname, day) values (30, 6, 'Castaneda', 'Chapiro', '2020-08-03');

insert into visits (id, department_id, day, visitors) values (1, 1, '2020-08-01', 23);
insert into visits (id, department_id, day, visitors) values (2, 2, '2020-08-01', 7);
insert into visits (id, department_id, day, visitors) values (3, 3, '2020-08-01', 17);
insert into visits (id, department_id, day, visitors) values (4, 4, '2020-08-01', 8);
insert into visits (id, department_id, day, visitors) values (5, 5, '2020-08-01', 5);
insert into visits (id, department_id, day, visitors) values (6, 6, '2020-08-01', 8);
insert into visits (id, department_id, day, visitors) values (7, 1, '2020-08-02', 9);
insert into visits (id, department_id, day, visitors) values (8, 2, '2020-08-02', 13);
insert into visits (id, department_id, day, visitors) values (9, 3, '2020-08-02', 19);
insert into visits (id, department_id, day, visitors) values (10, 4, '2020-08-02', 8);
insert into visits (id, department_id, day, visitors) values (11, 5, '2020-08-02', 14);
insert into visits (id, department_id, day, visitors) values (12, 6, '2020-08-02', 20);
insert into visits (id, department_id, day, visitors) values (13, 1, '2020-08-03', 14);
insert into visits (id, department_id, day, visitors) values (14, 2, '2020-08-03', 12);
insert into visits (id, department_id, day, visitors) values (15, 3, '2020-08-03', 16);
insert into visits (id, department_id, day, visitors) values (16, 4, '2020-08-03', 21);
insert into visits (id, department_id, day, visitors) values (17, 5, '2020-08-03', 7);
insert into visits (id, department_id, day, visitors) values (18, 6, '2020-08-03', 13);