ZADANIA 5

Spoiler:

--3
SELECT last_name, job_id, hire_date FROM hr.dbo.employees
WHERE last_name in('Matos', 'Taylor')
ORDER BY last_name;

--4
SELECT last_name, hire_date,
DATEADD(dw, (9 - DATEPART(dw, DATEADD(mm, 6, hire_date))) % 7, DATEADD(mm, 6, hire_date)) AS kiedy,
FROM hr.dbo.employees;

--5
SELECT a.last_name,  a.job_id, a.department_id,b.department_name
FROM hr.dbo.employees a, HR.dbo.departments b, HR.dbo.locations c
where a.department_id=b.department_id and b.location_id=c.location_id
and c.city = 'toronto';

--6
SELECT e.last_name as nazwisko, e.department_id as ID_dzialu, w.last_name as koledzy
FROM hr.dbo.employees e, hr.dbo.employees w
WHERE e.department_id=w.department_id and e.last_name<>w.last_name
order by e.last_name;

--7
SELECT e.first_name, e.last_name
FROM HR.dbo.employees AS e, HR.dbo.employees AS w
WHERE e.manager_id=w.employee_id AND w.last_name='King';

--8
SELECT e.last_name, e.salary, e.commission_pct
FROM hr.dbo.employees e
WHERE e.commission_pct IS NOT NULL;

--9
SELECT e.first_name+ ' ' +e.last_name AS 'pracownicy'
FROM hr.dbo.employees e
WHERE e.last_name LIKE '__a%';

--10
SELECT a.first_name,a.last_name, a.department_id, a.job_id
FROM hr.dbo.employees a, HR.dbo.departments b, HR.dbo.locations c
where a.department_id=b.department_id and b.location_id=c.location_id
and c.city = 'seattle';

--11
select department_id,min(salary) FROM hr.dbo.employees where
department_id=
(select top 1 tab.department_id from
(SELECT ee.department_id, avg(ee.salary) as a FROM hr.dbo.employees ee group by ee.department_id) tab
order by tab.a desc)
group by department_id;

--12
select first_name,last_name,hire_date from hr.dbo.employees
where DATEPART(WEEKDAY,hire_date)=
(select top 1(DATEPART(WEEKDAY,hire_date))
FROM hr.dbo.employees group by DATEPART(WEEKDAY,hire_date)
order by count(DATEPART(WEEKDAY,hire_date)) desc);

--13
SELECT TOP 3 last_name, salary FROM hr.dbo.employees
ORDER BY salary DESC

--14
CREATE TABLE HR.dbo.DEPT(
  id INT NOT NULL,
  nazwa VARCHAR(25)
);
GO

ALTER TABLE HR.dbo.DEPT ADD CONSTRAINT klucz PRIMARY KEY(id) ;
GO