--task 2.1st,2.2nd
--select * from emp e join dept d on e.deptno = d.deptno;
--task 2.3rd (select emp name and dname for all employees in alphabetical order)
/*select e.ename, d.dname
from emp e join dept d on e.deptno = d.deptno
order by e.ename asc;*/

--task 2.4th (find all employees w/ their numbers and names of deartments they work in)
/*select e.empno, d.dname
from emp e join dept d on d.deptno = e.deptno;*/

--task 2.5th(Select employee name, job and department name for all employees who earn more than 1500)
/*select e.ename ,e.job, e.sal, d.dname
from emp e join dept d on e.deptno = d.deptno
where e.sal > 1500;*/

--task 2.6th(Get a list of employees with name, job, salary and earning class (table SALGRADE))
/*select e.ename, e.job, e.sal, s.grade
from emp e join salgrade s on e.sal between s.losal and s.hisal;*/

-- task 2.7th (Select all information for employees in 3rd earning class)
/*select e.*, s.grade
from emp e join salgrade s on e.sal between s.losal and s.hisal
where s.grade = 3;*/

--task 2.8th (Find employees who work in Dallas)

/*select e.ename
from emp e join dept d on e.deptno = d.deptno
where d.loc like 'DALLAS';*/


--task 2.9th (For each employee select his name, department name and earning class)
/*select e.ename, e.deptno, s.grade
from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;*/

--task 2.10th (Find all employees with their numbers and names of departments they work in. Includedepartments without any employees)
/*select e.empno, d.dname
from emp e right join dept d on e.deptno = d.deptno
order by d.dname asc;*/

--task 2.12th (Find employees (name, department number) from departments 20 and 30)
/*select e.ename, e.deptno
from emp e join dept d on e.deptno = d.deptno
where d.deptno = 20 or d.deptno = 30
order by d.deptno asc;

--task 2.16th (Find employees who earn less than their managers)
/*select e.ename workername, e.sal wsal, m.ename mgrname , m.sal msal
from emp e, emp m
where e.mgr = m.empno and e.sal < m.sal;*/

--10. Find all employees with their numbers and names of departments they work in. Include departments without any employees
SELECT e.empno, d.deptno, d.dname FROM emp e right join dept d ON e.deptno = d.deptno;

--11. Find all employees with their numbers and names of departments they work in. Include employees without departments
SELECT e.empno, d.deptno, d.dname FROM emp e left join dept d ON e.deptno = d.deptno;

--13. List jobs appearing in departments 10 and 30
SELECT e.job FROM emp e WHERE e.deptno = 30
INTERSECT
SELECT e.job FROM emp e WHERE e.deptno = 10;

--14.List jobs appearing in department 10 or 30 (or both)
SELECT job FROM emp WHERE deptno = 30
UNION
SELECT job FROM emp WHERE deptno = 10;

--15. List jobs appearing in department 10 but not in department 30
SELECT job FROM emp WHERE deptno = 10
MINUS
SELECT job FROM emp WHERE deptno = 30;

--17. For each employee show his name and name of the manager. Sort results by managers name
SELECT m.ename, e.ename FROM emp e, emp m WHERE m.empno = e.mgr ORDER BY m.ename;