ZADANIA 6

Spoiler:

--1
SELECT e.last_name, d.department_name, l.city, j.job_title, e.salary
FROM hr.dbo.employees AS e
LEFT JOIN hr.dbo.departments AS d ON e.department_id = d.department_id
LEFT JOIN hr.dbo.locations AS l ON d.location_id = l.location_id
LEFT JOIN hr.dbo.jobs AS j ON e.job_id = j.job_id;

--2
SELECT count(*) FROM hr.dbo.employees
WHERE last_name LIKE '%n';

--3
SELECT d.department_name, l.city,
(SELECT count(*) FROM hr.dbo.employees as e WHERE d.department_id = e.department_id)
FROM hr.dbo.departments AS d, hr.dbo.locations AS l
WHERE l.location_id= d.location_id

--4
SELECT first_name, last_name, job_id, salary, hire_date
FROM hr.dbo.employees WHERE day(hire_date) < 16

--5
SELECT department_id, department_name, location_id
FROM hr.dbo.departments WHERE department_id NOT IN
(SELECT department_id FROM hr.dbo.employees WHERE department_id is not null AND job_id IN
(SELECT job_id FROM hr.dbo.jobs WHERE job_title = 'sales representative'))

--6
--a
select * from(
SELECT d.department_id, d.department_name,
(SELECT count(*) as liczba FROM hr.dbo.employees e
WHERE e.department_id =d.department_id) AS ilu
FROM hr.dbo.departments d) ww
where ww.ilu = 
(select max(aa.ilu) from(
SELECT dd.department_id, dd.department_name,
(SELECT count(*) as liczba FROM hr.dbo.employees ee
WHERE ee.department_id =dd.department_id) AS ilu
FROM hr.dbo.departments dd) aa);

--b
select * from(
SELECT d.department_id, d.department_name,
(SELECT count(*) as liczba FROM hr.dbo.employees e
WHERE e.department_id =d.department_id) AS ilu
FROM hr.dbo.departments d) ww
where ww.ilu = 
(select min(aa.ilu) from(
SELECT dd.department_id, dd.department_name,
(SELECT count(*) as liczba FROM hr.dbo.employees ee
WHERE ee.department_id =dd.department_id) AS ilu
FROM hr.dbo.departments dd) aa);

--c
select * from(
SELECT d.department_id, d.department_name,
(SELECT count(*) as liczba FROM hr.dbo.employees e
WHERE e.department_id =d.department_id) AS ilu
FROM hr.dbo.departments d) ww
where ww.ilu <3
order by ilu desc;

--7
SELECT Year(hire_date), count(*)
FROM hr.dbo.employees
GROUP BY Year(hire_date);

--8
SELECT country_name,
(SELECT count(*) FROM hr.dbo.locations AS l
WHERE l.country_id = c.country_id)
FROM hr.dbo.countries AS c;