jVLdLa7D

1create table if not exists Subdivision_type(
2subdivision_type_id int primary key generated always as identity,
3subdivision_type_name varchar(30));
4
5create table if not exists Subdivision(
6subdivision_id int primary key generated always as identity,
7subdivision_name varchar (30) not null,
8foreign key (subdivision_type_id) references Subdivision_type(subdivision_type_id),
9subdivision_type_id int not null);
10
11create table if not exists Post(
12post_id int primary key generated always as identity,
13post_name varchar (30) not null,
14foreign key (subdivision_id) references Subdivision(subdivision_id),
15subdivision_id int not null);
16
17create table if not exists Worker(
18worker_id int primary key generated always as identity,
19worker_name varchar(20) not null,
20worker_surname varchar(30) not null,
21worker_secondname varchar (25) not null,
22worker_sex varchar (10) not null,
23worker_livingplace varchar (100) not null,
24worker_birthdate date not null,
25foreign key (post_id) references Post(post_id),
26post_id int not null);
27
28insert into Subdivision_type (subdivision_type_name) values ('firts_type'), ('second_type'), ('third_type');
29select * from Subdivision_type;
30
31insert into Subdivision (subdivision_name, subdivision_type_id) values ('first_subdivision', 1),('second_subdivision', 2),('third_subdivision', 3);
32
33insert into Post (post_name, subdivision_id) values ('first_post', 1), ('second_post', 2), ('third_post', 3);
34
35insert into Worker (worker_name, worker_surname, worker_secondname, worker_sex, worker_livingplace, worker_birthdate, post_id)
36values ('Олег', 'Котов', 'Игоревич', 'Мужской', 'ул. Такая-то, д. Такой-то', '24.01.2001', 1),
37('Екатерина', 'Артамонова', 'Андреевна', 'Женский', 'ул. там-то, д. ту-та', '06.02.2001', 2),
38('Алексей', 'Щербаков', 'Дмитриевич', 'Мужской', 'ул. ядераная, д. 30', '13.12.2001', 3);
39select * from worker;
40
41select w.worker_id, w.worker_name, w.worker_surname, w.worker_secondname, post.post_name
42from worker as w
43join post on post.post_id = w.post_id
44where post.post_name = ''
45group by post.post_name