1) Display all employees (personal number, name, profession and salary),

who earn more than the average salary for people with salaries in the grade 2.

1) select empno,ename,job,sal from EMP where

sal>(select avg(sal) from EMP,SALGRADE where sal between losal and hisal

and grade=2) order by sal;

2) How many people earn more than the minimal salary for the department located

in DALLAS.

2) select count(empno) from EMP where sal>(select min(sal) from EMP where

deptno=(select D.deptno from DEPT D where D.loc='DALLAS'));

3) For each grade find the number of people with salaries in this grade

(display the grade and the adequate number of people).

3) select grade, count(empno) from SALGRADE, EMP where

EMP.sal between SALGRADE.losal and SALGRADE.hisal group by grade;

4) Find people who earn the highest salary for their job and grade.

4) select distinct E1.empno,E1.ename,E1.sal,E1.job from EMP E1, SALGRADE S1

where E1.sal=(select max(E2.sal) from EMP E2

where E1.job=E2.job) order by E1.sal;

5) For each job find the person employed as the last one.

5) select A.job, A.ename from EMP A where A.hiredate=(select max(B.hiredate) from EMP B

where A.job=B.job);

6) For each manager find his subordinates earning the lowest salary.

Show name of manager, name and salary of subordinate.

6) select distinct A.ename, B.sal, B.ename from EMP A, EMP B

where B.mgr=A.empno order by A.ename, B.sal;

7) Find minimal yearly income for each job in each department.

7)

8) For each manager find the total yearly income earned by his subordinate.

Display name of manager and the sum.

8) select A.ename, sum(12*B.sal+NVL(B.comm,0)) from EMP A, EMP B

where B.mgr=A.empno group by A.ename;

9) Find all people working as ANALISTS and employed before FORD.

9) select ename from EMP where job='ANALYST' and hiredate<(select hiredate from EMP where ename='FORD');

10) Find number of people who don't supervise the others.

10) select count(*) from EMP M

where not exists ( select null from EMP E where E.mgr = M.empno );

---