HW_SQL_3.sql

--SQL HomeWork 3. Joins
--
--Åñëè äëÿ êàêîãî-òî êåéñà íàäî ñäåëàòü äîïîëíèòåëüíóþ òàáëèöó, íàïîëíèòü å¸ äàííûìè, òî äåëàéòå )
--
--
-- 1. Âûâåñòè âñåõ ðàáîòíèêîâ ÷üè çàðïëàòû åñòü â áàçå, âìåñòå ñ çàðïëàòàìè.

select employees.id, employee_name ,monthly_salary from employee_salary 
	join employees on employee_id = employees.id  
	join salary on salary.id = salary_id;

-- 2. Âûâåñòè âñåõ ðàáîòíèêîâ ó êîòîðûõ ÇÏ ìåíüøå 2000.
select employee_id, employee_name, monthly_salary from employee_salary
	join employees on employee_id = employees.id 
	join salary on salary_id = salary.id
	where monthly_salary < 2000;

-- 3. Âûâåñòè âñå çàðïëàòíûå ïîçèöèè, íî ðàáîòíèê ïî íèì íå íàçíà÷åí. (ÇÏ åñòü, íî íå ïîíÿòíî êòî å¸ ïîëó÷àåò.)
select salary.id, monthly_salary from salary
	left join employee_salary on salary_id = salary.id
	where employee_id is null;

-- 4. Âûâåñòè âñå çàðïëàòíûå ïîçèöèè  ìåíüøå 2000 íî ðàáîòíèê ïî íèì íå íàçíà÷åí. (ÇÏ åñòü, íî íå ïîíÿòíî êòî å¸ ïîëó÷àåò.)
select salary.id, monthly_salary from salary 
	left join employee_salary on salary_id = salary.id 
	where employee_id is null and salary.monthly_salary < 2000;

-- 5. Íàéòè âñåõ ðàáîòíèêîâ êîìó íå íà÷èñëåíà ÇÏ.
select employee_name, employee_salary.salary_id from employees
	left join employee_salary on employee_id = employees.id
	where salary_id is null;

-- 6. Âûâåñòè âñåõ ðàáîòíèêîâ ñ íàçâàíèÿìè èõ äîëæíîñòè.
select * from roles_employee

select employees.id, employee_name, role_name from roles_employee 
	join employees on employee_id = employees.id 
	join roles on role_id = roles.id 

-- 7. Âûâåñòè èìåíà è äîëæíîñòü òîëüêî Java ðàçðàáîò÷èêîâ.
select employee_name, role_name from roles_employee
	join employees on employee_id = employees.id 
	join roles on role_id = roles.id 
	where role_name like '%Java%'
	
-- 8. Âûâåñòè èìåíà è äîëæíîñòü òîëüêî Python ðàçðàáîò÷èêîâ.
select employee_name, role_name from roles_employee
	join employees on employees.id = employee_id 
	join roles on roles.id = role_id 
	where role_name like '%Python developer%'
	
-- 9. Âûâåñòè èìåíà è äîëæíîñòü âñåõ QA èíæåíåðîâ.
select employee_name, role_name from roles_employee
	join employees on employee_id = employees.id 
	join roles on roles.id = role_id 
	where role_name like '%QA%'

-- 10. Âûâåñòè èìåíà è äîëæíîñòü ðó÷íûõ QA èíæåíåðîâ.
select employee_name, role_name from roles_employee
	join employees on employees.id = employee_id 
	join roles on roles.id = role_id
	where role_name like '%Manual QA%'
	
-- 11. Âûâåñòè èìåíà è äîëæíîñòü àâòîìàòèçàòîðîâ QA
select employee_name, role_name from roles_employee
	join employees on employees.id = employee_id 
	join roles on roles.id = role_id 
	where role_name like '%Automation QA%'
	
-- 12. Âûâåñòè èìåíà è çàðïëàòû Junior ñïåöèàëèñòîâ
select employee_name, role_name, monthly_salary from employee_salary 
	join employees on employee_salary.employee_id = employees.id  
	join salary on employee_salary.salary_id = salary.id
	join roles_employee on roles_employee.employee_id  = employees.id 
	join roles on roles_employee.role_id = roles.id 
	where role_name  like '%Junior%';

-- 13. Âûâåñòè èìåíà è çàðïëàòû Middle ñïåöèàëèñòîâ
select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%Middle%'
	
-- 14. Âûâåñòè èìåíà è çàðïëàòû Senior ñïåöèàëèñòîâ
select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%Senior%'
	
-- 15. Âûâåñòè çàðïëàòû Java ðàçðàáîò÷èêîâ
select monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%Java developer%'
	
-- 16. Âûâåñòè çàðïëàòû Python ðàçðàáîò÷èêîâ
select monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%Python developer%'	
	
-- 17. Âûâåñòè èìåíà è çàðïëàòû Junior Python ðàçðàáîò÷èêîâ
select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%Junior Python%'	
	
-- 18. Âûâåñòè èìåíà è çàðïëàòû Middle JS ðàçðàáîò÷èêîâ
select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%Middle JavaScript%'	
	
-- 19. Âûâåñòè èìåíà è çàðïëàòû Senior Java ðàçðàáîò÷èêîâ
select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%Senior Java developer%'		
	
select * from roles
	where role_name like '%QA%'

-- 20. Âûâåñòè çàðïëàòû Junior QA èíæåíåðîâ
select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where roles.id in (10, 18)	
	
-- 21. Âûâåñòè ñðåäíþþ çàðïëàòó âñåõ Junior ñïåöèàëèñòîâ 
	--ï. 12 äëÿ ïðîâåðêè
select AVG(monthly_salary) from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%Junior%'
	
-- 22. Âûâåñòè ñóììó çàðïëàò JS ðàçðàáîò÷èêîâ
select sum(monthly_salary) from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%JavaScript%'	
	
-- 23. Âûâåñòè ìèíèìàëüíóþ ÇÏ QA èíæåíåðîâ
select min(monthly_salary) from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%QA%'		
	
-- 24. Âûâåñòè ìàêñèìàëüíóþ ÇÏ QA èíæåíåðîâ
select max(monthly_salary) from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where role_name like '%QA%'		
	
-- 25. Âûâåñòè êîëè÷åñòâî QA èíæåíåðîâ
select count(*) from roles_employee 
	join roles on roles.id = role_id
	where role_name like '%QA%';
	
-- 26. Âûâåñòè êîëè÷åñòâî Middle ñïåöèàëèñòîâ.
select count(*) from roles_employee
	join roles on roles.id = role_id 
	where role_name like '%Middle%'
	
-- 27. Âûâåñòè êîëè÷åñòâî ðàçðàáîò÷èêîâ
select count(*) from roles_employee re 
	join roles r on r.id = re.role_id 
	where role_name like '%developer%'
	
-- 28. Âûâåñòè ôîíä (ñóììó) çàðïëàòû ðàçðàáîò÷èêîâ.
select  sum(monthly_salary) from employee_salary 
	join salary on employee_salary.salary_id = salary.id 
	join roles_employee  on roles_employee.employee_id = employee_salary.employee_id 
	join roles on roles.id =roles_employee.role_id 
	where roles.role_name like '%developer';

-- 29. Âûâåñòè èìåíà, äîëæíîñòè è ÇÏ âñåõ ñïåöèàëèñòîâ ïî âîçðàñòàíèþ
select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	order by monthly_salary;
			
-- 30. Âûâåñòè èìåíà, äîëæíîñòè è ÇÏ âñåõ ñïåöèàëèñòîâ ïî âîçðàñòàíèþ ó ñïåöèàëèñòîâ ó êîòîðûõ ÇÏ îò 1700 äî 2300
select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where monthly_salary > 1700 and monthly_salary < 2300
	order by monthly_salary;

-- 31. Âûâåñòè èìåíà, äîëæíîñòè è ÇÏ âñåõ ñïåöèàëèñòîâ ïî âîçðàñòàíèþ ó ñïåöèàëèñòîâ ó êîòîðûõ ÇÏ ìåíüøå 2300
select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where monthly_salary < 2300
	order by monthly_salary;

-- 32. Âûâåñòè èìåíà, äîëæíîñòè è ÇÏ âñåõ ñïåöèàëèñòîâ ïî âîçðàñòàíèþ ó ñïåöèàëèñòîâ ó êîòîðûõ ÇÏ ðàâíà 1100, 1500, 2000

select employee_name, role_name, monthly_salary from employee_salary
	join employees on employees.id = employee_salary.id 
	join salary on salary.id = employee_salary.salary_id 
	join roles_employee on roles_employee.employee_id = employees.id 
	join roles on roles.id = roles_employee.role_id 
	where (monthly_salary = 1100) or (monthly_salary = 1500) or (monthly_salary = 2000)
	order by monthly_salary;