DataBase_defense(2-6)

--2
create table pl_coun(
id int primary key
)

alter table pl_coun
add id_plant int

alter table pl_coun
add constraint FK_country foreign key (id_coun) references country(id_c) on delete set null

alter table pl_coun
add constraint FK_plant foreign key (id_plant) references plant(id) on delete set null




--3
--фамилия инициалы
--дата рождения название месяца
--условие 1 или 2 курс
--вывод в 1 столбец

--select Surname+ '; ' + left(name,1) + '.' + left(Middle_name,1) + '.' + convert(varchar(20),Birthday,106) as 'Data'
select Surname+ '; ' + Substring(Name,1,1) + '.' + Substring(Middle_name,1,1) + '.' + convert(varchar(20),Birthday,106) as 'Data'
from Student
where Kurs in (1,2)


--4
--вывести для каждого студента мин маки авг оценку

select Student_Id, min(Mark) as 'min', avg(Mark) as 'avg', max(Mark) as 'max'
from Exam_mark
where Mark is not null
group by Student_Id

--5

-- новая таблица, в нее поместить компании в которых кол-во сотрудников > 2


insert into Good_Company (Full_name, Short_name, City, Coefficient)
select  Full_name, Short_name, City, Coefficient
from Company
where Company_id in (select Company_id
                     from Department
                     where Dept_id in (select Dept_id
                                       from Cooperator
                                       group by Dept_id
                                       having count(cooperator_id) > 2
                                       )
                    )

select *
from Company

select *
from Department

select *
from Cooperator

--6
--уменьшать коэф компании, если сотрудников < 3, коэф не должен становится <0

update Company
set Coefficient = Coefficient - 1
where Coefficient != 0 and Company_id in(
    select co.Company_id
    from Company co, Department dp, Cooperator cp
    where (co.Company_id = dp.Company_id and dp.Dept_id = cp.Dept_id)
    group by co.Company_id
    having count(cp.Cooperator_id) < 3
)
select*
from Company