-- 1 --
create table list (
    LastName varchar(255),
    FirstName varchar(255),
    Grade int,
    Classroom int
);
copy list
from '/home/alex/Загрузки/list.csv' WITH CSV HEADER DELIMITER ',';

alter table list
add column id bigserial Primary KEY;

create table teachers (
    LastName varchar(255),
    FirstName varchar(255),
    Classroom int
);

copy teachers
from '/home/alex/Загрузки/teachers.csv' WITH CSV HEADER DELIMITER ',';

alter table teachers
add column id bigserial Primary KEY;

-- 2 --
select teachers.FirstName firstname, teachers.LastName lastname, count(teachers.Classroom) quantity
from teachers
inner join list on list.Classroom = teachers.Classroom
group by teachers.firstname, teachers.lastname;

-- 3 --
explain
select teachers.FirstName firstname, teachers.LastName lastname, count(teachers.Classroom) classroom
from teachers
inner join list on list.Classroom = teachers.Classroom
group by teachers.firstname, teachers.lastname;

-- 4 --
-- Нормализация
select LastName, FirstName, id
into student_name
from list;

alter table student_name
add Primary KEY(id);


select Grade, id
into student_grade
from list;

alter table student_grade
add Primary KEY(id);


select Classroom, id
into student_classroom
from list;

alter table student_classroom
add Primary KEY(id);


select teachers.FirstName t_firstname, teachers.LastName t_lastname, list.id s_id
into teacher_student
from teachers
inner join list on list.Classroom = teachers.Classroom;

alter table teacher_student
add Primary KEY(s_id);

-- проверим запрос
explain
select distinct t_lastname, t_firstname, count(*)
from teacher_student
group by t_lastname, t_firstname;

-- первый cost стал ниже

-- 5 --
explain
select LastName, FirstName
from student_name
inner join student_grade on student_name.id = student_grade.id
where Grade > 3;

-- 6 --
CREATE INDEX btree ON student_grade(id);
drop index btree;