laba_3_BD_procedures+def

--1
create or alter procedure CoopCity
@InCity varchar(30)
as
select Cooperator.Name, Surname, Cooperator.City
from Cooperator
join Company on Cooperator.City = Company.City and Company.City = @InCity
join Department on Department.Dept_id = Cooperator.Dept_id and Department.Company_id = Company.Company_id

exec [CoopCity] 'Сургут'

--2
create or alter procedure QuantCoopCateg
@InCategory varchar(30)
as
select Count(*) as 'Количество', @InCategory as 'Категория'
from Cooperator
where Cooperator.Category = @InCategory

exec [QuantCoopCateg] 'Инженер'

--3
create or alter procedure CoopSalary
@InSalary int=0
as
select Count(*) 'Количество', @InSalary 'Зарплата'
from Cooperator
where Cooperator.Salary > @InSalary

exec [CoopSalary] 80000

--4
create or alter procedure CoopCompanyName
@InCompName varchar(30)
as
select Cooperator.Name 'Имя', Cooperator.Surname 'Фамилия', @InCompName 'Название'
from Cooperator, Department, Company
where Department.Dept_id = Cooperator.Dept_id and Company.Company_id = Department.Company_id and Company.Name = @InCompName

exec [CoopCompanyName] 'ПАО АНК Башнефть'

--5
create or alter procedure IncreaseSalaryByEval
@InPercentInc int, @InStatus  decimal
AS
update Cooperator
set Salary = Salary*(100+@InPercentInc)/100
where Cooperator.Cooperator_id in (
SELECT Cooperator.Cooperator_id
FROM Cooperator, Evaluation, (SELECT Evaluation.DATE, Cooperator.Cooperator_id
                                FROM Cooperator, Evaluation
                                WHERE Cooperator.Cooperator_id = Evaluation.Cooperator_id) t
WHERE Cooperator.Cooperator_id = t.Cooperator_id AND  Evaluation.STATUS >= @InStatus
GROUP BY Cooperator.Cooperator_id,Evaluation.DATE
HAVING Evaluation.DATE = MAX(t.DATE)
)

exec [IncreaseSalaryByEval] 5,5

--6
exec sp_helptext 'IncreaseSalaryByEval'

--zashita
/*
создать процедуру которая будет уменьшать зп если у этого сотрудника последняя оценка 3
уменьшает на процент при вызове процедуры
*/

create or alter procedure decPerc
@InPerc int
as
update Cooperator
set Salary = Salary*(100-@InPerc)/100
where Cooperator.Cooperator_id in (
    SELECT Cooperator.Cooperator_id
    FROM Cooperator, Evaluation, (SELECT Evaluation.DATE, Cooperator.Cooperator_id, Evaluation.Status
                                    FROM Cooperator, Evaluation
                                    WHERE Cooperator.Cooperator_id = Evaluation.Cooperator_id) t
    WHERE Cooperator.Cooperator_id = t.Cooperator_id AND  Evaluation.STATUS = 3
    GROUP BY Cooperator.Cooperator_id,Evaluation.DATE
    HAVING Evaluation.DATE = MAX(t.DATE))

exec decPerc 10