1) For each grade find number of people who earn salary in this grade.
1) select count(ename),grade from emp,salgrade where sal>=losal and sal<=hisal group by grade;
2) Find people who earn the highest salary for their job and grade.
2) 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;
3) Show names, jobs and hire dates for all people employed in NEW YORK in 1984.
3) select ename, job, hiredate from emp e, dept d
where e.deptno=d.deptno and e.hiredate like('1984')
and d.loc='NEW YORK';
4) Find all employees (personal number, name, profession and salary), who
earn more than the average salary for people with salaries in the second
grade. Display results order by salaries descending.
4) select empno,ename,job,sal from emp e where e.sal>(select avg(sal) from emp a, salgrade
where grade=2 and a.sal>=losal and a.sal<=hisal) order by sal desc;
5) How many people earn more than the minimal salary for the department
located in DALLAS.
5) select count(empno) from EMP where sal>(select min(sal) from EMP where
deptno=(select D.deptno from DEPT D where D.loc='DALLAS'));
6) Find the number of people who don't supervises the others.
6) select count(*) from EMP M
where not exists ( select null from EMP E where E.mgr = M.empno );
7) For each manager find the total monthly income earned by his
subordinates. Display name of manager and the sum.
7) select A.ename, sum(B.sal) from EMP A, EMP B
where B.mgr=A.empno group by A.ename;
8) Find all people working as ANALISTS and employed before FORD.
8) select ename from emp where job='ANALYST'
and hiredate < (select hiredate from emp where ename='FORD');
9) Find all jobs which appear in department 10 but not in department 20.
9) select job from emp where deptno=10 MINUS
(select job from EMP where deptno=20) order by job;
10) For each job in each grade find the minimal yearly income.
Order results by incomes descending.
10) select min(12*E.sal+NVL(comm,0)),job,grade from EMP E, SALGRADE S where
E.sal between S.losal and S.hisal group by E.job, S.grade
order by min(12*E.sal+NVL(E.comm,0)) desc;