--1
SELECT departments.department_name FROM departments, employees, jobs WHERE
departments.department_id = employees.employee_id and jobs.job_id = employees.job_id and
jobs.max_salary > 25000
--2
SELECT SUM(e1.salary), c1.country_id FROM employees e1, countries c1, departments d1, locations l1
WHERE e1.department_id = d1.department_id
and d1.location_id = l1.location_id
and l1.country_id = c1.country_id
GROUP BY c1.country_id
--3
DECLARE @I INT
DECLARE @A INT
DECLARE @B INT
DECLARE @C INT
SET @A = 0
SET @B = 0
SET @C = 0
DECLARE @minpensja INT
DECLARE @pensja INT
DECLARE @maxpensja INT
declare kur SCROLL cursor for
select e.salary, j.min_salary, j.max_salary from employees e, jobs j
where e.job_id = j.job_id;
OPEN kur;
FETCH NEXT FROM kur INTO @pensja, @minpensja, @maxpensja;
WHILE @@FETCH_STATUS=0
    BEGIN
	IF(@pensja >= @minpensja and @pensja < @pensja*0.3 + @minpensja)
	BEGIN
		SET @A = @A+1;
	END
	IF(@pensja >= @minpensja + 0.3*@pensja and @pensja < @pensja*0.7 + @minpensja)
	BEGIN
		SET @B = @B+1;
	END
	IF(@pensja >= @minpensja + 0.7*@pensja and @pensja <= @maxpensja)
	BEGIN
		SET @C = @C+1;
	END
    FETCH NEXT FROM kur INTO @pensja, @minpensja, @maxpensja;
    END
CLOSE kur
DEALLOCATE kur
print 'a)'+CONVERT(VARCHAR(3),@A)
print 'b)'+CONVERT(VARCHAR(3),@B)
print 'c)'+CONVERT(VARCHAR(3),@C)
--4
USE HR
GO
drop procedure robinHood
GO
create proc robinHood(@idDzialu INT, @proc INT)
AS
BEGIN
DECLARE @ilu INT
SET @ilu = 0;
SET @ilu += (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = @idDzialu AND e2.salary = (
	SELECT MIN(salary) FROM employees e3 WHERE e3.job_id = e2.job_id AND e2.salary <>
	(SELECT MAX(salary) FROM employees e3 WHERE e3.job_id = e2.job_id)) --zapytanie takie jak ponizej tylko zliczajace
)

UPDATE employees SET
salary=salary*@proc/100
--jezeli uznamy ze jak mamy jedna osobe na danym stanowisku to jest ona osoba zarabiajaca najnizsza stawke
--WHERE employee_id IN
--(SELECT e2.employee_id FROM employees e2 WHERE e2.department_id = @idDzialu AND e2.salary = (
--	SELECT MIN(salary) FROM employees e3 WHERE e3.job_id = e2.job_id)
--)

-- ponizej rozumowanie przeciwne, czyli przyjmujemy ze jak na danym stanowisku jest jedna osoba to nie dostaje podwyzki
WHERE employee_id IN (
SELECT e2.employee_id FROM employees e2 WHERE e2.department_id = @idDzialu AND e2.salary = (
	SELECT MIN(salary) FROM employees e3 WHERE e3.job_id = e2.job_id) AND e2.salary <>
	(SELECT MAX(salary) FROM employees e3 WHERE e3.job_id = e2.job_id))


if(@ilu IS NOT NULL)
	print 'Podwyzszono zarobki '+CONVERT(varchar(3),@ilu)+' pracownikom.';
else
	print 'Nie ma takich pracownikow';
END

GO
GO
EXEC robinHood 10, 50
--5
DROP TRIGGER ZmianaManagera
GO
CREATE TRIGGER ZmianaManagera
ON employees
AFTER UPDATE
AS
BEGIN
	IF UPDATE(manager_id)
	BEGIN
		DECLARE @exMgr INT
		SET @exMgr = (SELECT TOP 1 d.manager_id from deleted d)
		DECLARE @Mgr INT
		set @Mgr = (SELECT TOP 1 i.manager_id from inserted i)
		IF(@exMgr IS NOT NULL)
			UPDATE employees SET salary = 0.9 * e.salary FROM employees e  WHERE e.employee_id = @exMgr
		UPDATE employees SET salary = 1.1 * e.salary FROM employees e  WHERE e.manager_id = @Mgr
	END
END