-- 1
drop function fun;
GO
Create function fun(@id int) returns int
BEGIN
return (SELECT count(*) from employees e where e.department_id=@id)
END
GO
--Wywołanie
SELECT d.department_id , dbo.fun(d.department_id) as ilosc_pracownikow
FROM departments d;

-- 2 tego nie jestem pewien
CREATE PROCEDURE podwyzka()
AS
BEGIN
Update Employees e set e.salary= e.salary + j.max_salary*0.10 from jobs j where e.job_id=j.job_id
END
GO
Exec podwyzka();
GO
Drop Procedure podwyzka
GO

-- 3
CREATE PROCEDURE  zwiększenie_płacy_dla_stanowiska( @nazwa char, @wartosc float)
AS
BEGIN
UPDATE jobs j set j.max_salary = max_salary + @wartosc where j.job_title = @nazwa;
END
GO
exec zwiększenie_płacy_dla_stanowiska('jakas nazwa', 20);
GO
drop procedure zwiększenie_płacy_dla_stanowiska
GO

-- 4 jezeli dobrze zrozumialem to cos takiego
declare @licznik int;
set @licznik=0;
declare last_name varchar(25);
declare kursor cursor for Select e.LAST_NAM FROM employees e where e.salary> (select e1.salary from employees e1 where e1.last_name = 'Kowalski';
BEGIN
open kursor;
fetch next from kursor into @last_name;
WHILE(@@FETCH_STATUS=0)
BEGIN
print 'Wiecej od Kowaslkiego zarabia ' + @last_name
set @licznik=@licznik+1;
END
print('Wiecej zarabialo ' + cast(@licznik as varchar) + ' pracownikow');
END
close kursor;
deallocate kursor;

-- 5
GO
Create table historia_zmian_prac ( data date, nr_akt int IDENTITY(1,1), stara_placa float,nowa placa float);
GO
create trigger historia_plac on employees
AFTER UPDATE
AS
BEGIN
DECLARE @old_salary float;
DECLARE @new_salary float;
select @old_salary = delted.salary form deleted;
select @new_salary = inserted.salary from inserted;
Insert into historia_zmian_prac (data,stara_placa,nowa_placa) VALUES (getDate(),@old_salary, @new_salary);
END
GO
-- test
UPDATE employees set salary=20 where employee_id=2;
Select * from historia_zmian_prac;
GO
Drop trigger wyzwalacz;
GO