--task 4.1st (Find all employees with salary equal to lowest salary in company)
/*select e.ename from emp e
where e.sal=(select min(e.sal) from emp e);*/

--task 4.2nd (Find all employees that have the same job as BLAKE)
/*select e.ename, e.job from emp e
where e.job like (select e.job from emp e where e.ename like 'BLAKE');*/

--task 4.3rd (Find all employees with salary equal to lowest salary in each department) ??


--task 4.4th (Find employees with salary equal to lowest salary in departments they work in)
/*Select e.deptno, e.ename, e.sal from emp e
where e.sal = (select min(sal) from emp e2 where e.deptno = e2.deptno);*/

--task 4.5th(Using ANY, find employees with salary greater than lowest salary in department 30)
/*select e.* from emp e
where e.sal > ANY(select min(e2.sal) from emp e2 where e2.deptno =30);*/

--task 4.6th(Using ALL, find employees with salary greater than every salary in department 30)
/*select e.* from emp e
where e.sal > ALL(select e2.sal from emp e2 where e2.deptno = 30);*/

--task 4.7th(Find employees with salary greater than lowest salary in department 30 (without ANY))
/*select e.ename, e.sal from emp e
where e.sal > (select min(e2.sal) from emp e2 where e2.deptno = 30);*/

--task 4.8th(Find employees with salary greater than every salary in department 30 (without ALL))
/*select e.ename, e.sal from emp e
where e.sal > (select max(e2.sal) from emp e2 where e2.deptno = 30);*/

--task 4.9th(Find departments with average salary greater than average salary of department 30)
/*select d.deptno, avg(e.sal) from dept d join emp e on d.deptno = e.deptno
group by d.deptno
having avg(e.sal) > (select avg(e2.sal) from emp e2 where e2.deptno = 30);*/
--task 4.10th(Find job with highest average salary)
/*select e.job, avg(e.sal) from emp e
group by e.job
having avg(e.sal) > (select max(avg(e2.sal)) from emp e2 where e2.job != e.job group by e2.job);*/

--task 4.11th(Find employees with salary lower than highest salary in SALES (department))
/*select e.* from emp e
where e.sal>ALL(select e2.sal from emp e2 join dept d on e.deptno = d.deptno where d.dname like 'SALES');*/

--task 4.12th(Find employees with salary higher than average salary in their departments)
/*select e.* from emp e
where e.sal>(select avg(e2.sal) from emp e2 where e.deptno=e2.deptno);*/

--task 4.13th(Using EXISTS, find employees who have subordinates)
/*select e.* from emp e
where EXISTS(select e2.* from emp e2 where e.empno = e2.mgr);*/

--task 4.14th(Find employees whose department does not appear in DEPT table) ??

-- task 4.15th(For each department find recently hired employees. Sort by hire date)
/*select e.deptno, max(e.hiredate) from emp e
group by e.deptno;*/

--task 4.16th(Find departments with no employees (using SUBQUERY))
/*select d.deptno, count(e.empno) from dept d left join emp e on e.deptno = d.deptno
group by d.deptno
having count(e.empno) < ALL(select count(e.empno) from dept d  join emp e on e.deptno = d.deptno
group by d.deptno);*/

--task 4.17th(Write a query that returns the percentage share of the number of employees in each department)
/*select e.deptno, round(count(e.empno)/(select count(e2.empno) from emp e2)*100,2) || '%' "% of all employees" from emp e
group by e.deptno;*/

--task 5.1st(Find employees who earn the most in their departments)
/*select e.* from emp e where e.sal = (select max(e2.sal) from emp e2 where e2.deptno = e.deptno);*/

--task 5.2nd(Find employees who earn above average in their departments)
/*select e.* from emp e where e.sal >(select avg(e2.sal) from emp e2 where e2.deptno = e.deptno);*/

--task 5.3rd(Find employees with the lowest earnings in their jobs)
/*select e.* from emp e where e.sal = (select min(e2.sal) from emp e2 where e.job = e2.job);*/

--task 5.4th(Find employees who have subordinates using the EXISTS predicate)
/*select e.* from emp e where exists(select e2.* from emp e2 where e2.mgr = e.empno);*/

--task 5.5th(Find employees whose department does not appear in the DEPT table)
/*select e.ename, e.deptno from emp e where e.deptno <> ALL(select deptno from dept);*/

-- task 5.6th(Using a subquery, find the names and locations of departments without any employees)
/*select d.dname, d.loc from dept d where NOT EXISTS(select e.* from emp e where e.deptno = d.deptno);*/

--task 5.7th(Find employees earning the maximum salary in their job positions. Sort results according to decreasing earnings)
/*select e.ename,e.empno, e.sal, e.job from emp e where e.sal = (select min(e2.sal) from emp e2 where e.job = e2.job group by e.job) order by e.sal desc;*/

--task 5.8th(Find employees earning a minimum salary in their earning groups. Sort results according to descending earning groups)
/*select e.* from emp e join salgrade s on e.sal between s.losal and s.hisal
where e.sal = (select min(e2.sal) from emp e2 join salgrade s2 on e2.sal between s2.losal and s2.hisal where s.grade = s2.grade group by s2.grade); */

--task 5.9th(Identify recently hired employees for each department. Sort results by hire dates)
/*select e.* from emp e join dept d on e.deptno = d.deptno
where to_date(e.hiredate) < to_date(ALL(select min(e2.hiredate) from emp e2 join dept d2 on e2.deptno = d2.deptno where e.empno != e2.empno and e.deptno = e2.deptno group by e2.deptno));*/

--task 5.10th(Provide the name, salary and name of the department for employees whose salary exceeds
            --the average of their earning groups.)
/*select e.ename, e.sal, e.deptno from emp e join salgrade s on e.sal between s.losal and s.hisal
where e.sal > (select avg(e2.sal) from emp e2 join salgrade s2 on e2.sal between s2.losal and s2.hisal
where s.grade = s2.grade);*/

--task 5.11th(Using a subquery, find employees assigned to non-existent departments)
/*select e.* from emp e where e.deptno != ALL(select d.deptno from dept d);*/

--task 5.12th(Indicate the top three earning employees in the company. Show their names and salaries
            --(Assume, that salaries in company are unique))
/*select  e.* from emp e order by e.sal desc fetch first 3 rows only;*/