1.
Select substr(hiredate, 1, 2) H_YEAR, COUNT(*), MIN(SAL), MAX(SAL), AVG(SAL), SUM(SAL)
From emp
Group by substr(hiredate, 1, 2)
order by substr(hiredate, 1, 2);

2

select count(*) "TOTAL",
       sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
       sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
       sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
       sum(decode(to_char(hiredate,'yyyy'),'1983',1,0)) "1983",
       sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
from emp
where to_char(hiredate,'yyyy') between '1980' and '1987';
 
3

select job,
       sum(decode(deptno, 10, sal)) "Deptno 10",
       sum(decode(deptno, 20, sal)) "Deptno 20",
       sum(decode(deptno, 30, sal)) "Deptno 30",
sum(sal) "Total"
from emp
group by job
order by job;

4.
select mgr, job, sum(sal)
from emp
group by grouping sets( (mgr,job), (mgr),())
order by mgr;

5.
select deptno, avg(sal), sum(sal)
from emp
group by deptno
having deptno in
(
select deptno
from emp
group by deptno
having max(sal)>2900
)
;


6.
Select e.ename, e.job, e.sal, d.dname
From emp e, dept d
Where (e.deptno = d.deptno) and (d.loc ='NEW YORK');

7.
SELECT e.ename, e.job, d.dname, d.loc 
FROM emp e, dept d
WHERE (e.ename like '%L%') and (e.deptno = d.deptno);

8.
select e.ename, e.job, e.sal, s.grade, d.dname, d.loc
from emp e, dept d, salgrade s
where (e.deptno = d.deptno) and (e.sal between s.losal and s.hisal);

9.
select e.empno, e.ename , m.empno, m.ename
from emp e, emp m
where e.mgr = m.empno(+)
order by e.empno;

10.
select e.ename, e.hiredate, m.ename, m.hiredate
from emp e, emp m
where (e.mgr = m.empno) and (e.hiredate < m.hiredate);

11.
select ename, hiredate
from emp
where deptno = (select deptno from emp where ename = 'BLAKE');

12.
select empno, ename, job, sal, deptno
from emp
where sal > (select avg(sal) from emp)
order by sal desc;

13.
select empno, ename, sal
from emp
where deptno in (select deptno from emp where ename like '%T%')
order by empno;

14.
SELECT e.ename, e.job, e.sal 
FROM emp e, dept d
WHERE  (e.deptno = d.deptno) and (d.loc = 'DALLAS');

15.
select ename, sal
from emp 
where mgr = (select empno from emp where ename = 'KING');

16.
SELECT e.ename, e.job 
FROM emp e, dept d
WHERE  (e.deptno = d.deptno) and (d.dname = 'SALES');

17.
select *
from emp
where ename != 'FORD' and (job = (select job from emp where ename = 'FORD') or sal = (select sal from emp where ename ='FORD'));

18.
select ename, job, deptno, sal
from emp
where job = (select job from emp where ename = 'JONES') or sal >= (select sal from emp where ename ='FORD')
order by job, sal desc;

19.
select ename, job, sal
from emp
where ename not in ('SCOTT','WARD') and(sal = (select sal from emp where ename ='SCOTT') or sal = (select sal from emp where ename ='WARD'))
;

20.
select ename, job
from emp
where job in
(
select e.job
from emp e, dept d
where (e.deptno = d.deptno) and (d.loc = 'CHICAGO')
)
;

21.
select e.deptno, e.ename, e.sal
from emp e
where e.sal > (select avg(m.sal) from emp m where (e.deptno = m.deptno ) group by deptno);

22.
select *
from emp
where empno not in
(
select empno
from emp
where sal = ( select min(sal) from emp where deptno = 30)
)
;

23.
select job, ename, empno, deptno
from emp 
where empno in (select mgr from emp);

24.
select job, ename, empno, deptno
from emp 
where empno not in (select mgr from emp where mgr is not null);