Oracle exercise 1

由于该练习网上看到的基本是多表连接，但如果是大型数据库，多表连接的效率会低于子查询，于是增加了子查询的代码，已测试通过。。。

oracle自带表：emp,salgrade,dept

平台：Oracle11G

--1.列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。
<oracle>
select d.deptno, d.dname, count(e.empno), min(sal), max(sal), avg(sal)
from emp e,dept d
where e.deptno = d.deptno(+)
group by d.deptno, d.dname
having count(e.empno) > 1;
<SQL1999>
select d.deptno, d.dname, count_empno, t.min_sal, t.max_sal, t.avg_sal
from dept d 
join (
      select deptno, count(empno) count_empno, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal 
      from emp 
      group by deptno 
      having count(empno) > 1) t
on (d.deptno = t.deptno);

--2.列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
<oracle>
select e.empno, e.ename, d.dname, m.ename
from emp e, dept d, emp m
where e.sal > all (select sal from emp where ename in ('SMITH','ALLEN'))
    and e.deptno = d.deptno
    and e.mgr = m.empno(+);
<SQL1999>
select temp1.empno, temp1.ename, d.dname, temp1.mgrname
from dept d
join (
    select temp.empno, temp.deptno, temp.ename, m.ename mgrname
    from emp m
    right join (
        select empno,deptno, ename, mgr
        from emp
        where sal > all (
            select sal
            from emp
            where ename in ('SMITH', 'ALLEN'))) temp
    on (temp.mgr = m.empno)) temp1
on (temp1.deptno = d.deptno);

--3.列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
<oracle>
select e.empno, e.ename, m.empno, m.ename, (m.sal + nvl(m.comm, 0)) * 12 income
from emp e, emp m
where e.mgr = m.empno(+)
order by income desc;
<SQL1999>
select t.empno, t.ename, m.empno, m.ename, (m.sal + nvl(m.comm, 0)) * 12 income
from emp m
right join (
    select empno, ename, mgr
    from emp) t
on (t.mgr = m.empno)
order by income desc;

--4.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
<oracle>
select e.empno, e.ename, d.dname, d.loc,temp.count_num
from emp e, emp m, dept d, (
    select deptno, count(deptno) count_num 
    from emp 
    group by deptno) temp
where e.mgr = m.empno(+) 
    and e.hiredate < m.hiredate
    and e.deptno = d.deptno
    and e.deptno = temp.deptno;
<SQL1999>
select t2.empno, t2.ename, t2.dname, t2.loc, t1.count_deptno
from (
    select deptno, count(deptno) count_deptno 
    from emp
    group by deptno) t1
join (
    select temp.empno, temp.ename, d.dname, d.loc, d.deptno
    from dept d
    join (
        select e.empno, e.ename, e.deptno
        from emp e
        join emp m
        on (e.mgr = m.empno and e.hiredate < m.hiredate)) temp
    on (temp.deptno = d.deptno)) t2
on (t1.deptno = t2.deptno);


--5.列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。
<oracle>
select d.deptno, d.dname, d.loc, count(e.empno), avg(e.sal)
from dept d,emp e
where d.deptno = e.deptno(+)
group by d.deptno, d.dname, d.loc;
<SQL1999>
select d.dname, temp.count_empno, temp.avg_sal
from dept d
left join (
    select deptno, count(empno) count_empno, avg(sal) avg_sal
    from emp
    group by deptno) temp
on (d.deptno = temp.deptno);

--6.列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。
<oracle>
select e.ename, d.dname, temp.count_num, s.grade
from emp e, dept d, (select deptno, count(deptno) count_num from emp group by deptno) temp, salgrade s
where job = 'CLERK'
    and e.deptno = d.deptno
    and e.deptno = temp.deptno
    and e.sal between s.losal and s.hisal;
<SQL1999>
select t4.ename, t4.dname, t4.count_deptno, s.grade
from salgrade s
join (
    select t3.ename, t3.dname, t2.count_deptno, t3.sal
    from (
        select deptno, count(deptno) count_deptno
        from emp
        group by deptno) t2
    join (
        select t1.ename, d.dname, d.deptno, t1.sal
        from dept d
        join (
            select ename, deptno, sal
            from emp
            where job = 'CLERK') t1
        on (d.deptno = t1.deptno)) t3
    on (t2.deptno = t3.deptno)) t4
on (t4.sal between s.losal and s.hisal);

--7.列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资
<oracle>
select temp.job, temp.count_empno, d.dname, d.loc, temp1.avg_sal
from emp e, (
    select e.job, count(e.empno) count_empno
    from emp e
    group by job
    having min(e.sal) > 1500
    ) temp, dept d, (
    select deptno, avg(sal) avg_sal 
    from emp 
    group by deptno
    ) temp1
where e.job = temp.job and e.deptno = d.deptno and e.deptno = temp1.deptno;
<SQL1999>
select t3.job, t3.count_empno, t3.dname, t3.loc, t4.avg_sal
from (
    select deptno, avg(sal) avg_sal 
    from emp 
    group by deptno) t4
join (
    select t2.job, t2.count_empno, d.dname, d.loc, d.deptno
    from dept d
    join (
        select e.deptno, t1.job, t1.count_empno
        from emp e
        join (
            select job, count(empno) count_empno
            from emp
            group by job
            having min(sal) >1500) t1
        on (e.job = t1.job)) t2
    on (d.deptno = t2.deptno)) t3
on (t4.deptno = t3.deptno);

--8.列出在部门“SALES”（销售部）工作的员工姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。
<oracle>
select e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where e.deptno = d.deptno and d.dname = 'SALES';
<SQL1999>
select e.ename, e.sal, e.hiredate, d.dname
from emp e
join dept d
on (e.deptno = d.deptno)
where d.dname = 'SALES';

--9.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。
<oracle>
select e.ename, e.sal, d.dname, m.ename, s.grade
from emp e, dept d, emp m, salgrade s
where e.sal > (select avg(sal) from emp)
    and e.deptno = d.deptno
    and e.mgr = m.empno(+)
    and e.sal between s.losal and s.hisal;
<SQL1999>
select t3.ename, t3.dname, t3.mname, t3.sal, s.grade
from salgrade s
join (
    select t2.ename, t2.dname, t2.deptno, m.ename mname, t2.sal
    from emp m
    right join (
        select t1.ename, d.dname, d.deptno, t1.mgr, t1.sal
        from dept d
        join (
            select ename, deptno, mgr, sal
            from emp
            where sal > (select avg(sal) from emp)) t1
        on (d.deptno = t1.deptno)) t2
    on (m.empno = t2.mgr)) t3
on (t3.sal between s.losal and s.hisal);

--10.列出“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
<oracle>
select e.empno, e.ename, e.job, d.dname, temp.count_empno
from emp e, dept d, (select deptno, count(empno) count_empno from emp grouP by deptno) temp
where e.job = (select job from emp where ename = 'SCOTT')
    and e.ename <> 'SCOTT'
    and e.deptno = d.deptno
    and e.deptno = temp.deptno;
<SQL1999>
select t2.ename, t2.dname, t3.count_empno
from (
    select deptno, count(empno) count_empno
    from emp
    group by deptno) t3
join (
    select t1.ename, d.dname, t1.deptno
    from dept d
    join (
        select e.ename, e.deptno
        from emp e
        where job = (
            select job
            from emp
            where ename = 'SCOTT')
            and ename <> 'SCOTT') t1
    on (d.deptno = t1.deptno)) t2
on (t2.deptno = t3.deptno);

--11.列出公司各个工资等级雇员的数量、平均工资
<oracle>
select s.grade, s.losal, s.hisal, count(e.empno), avg(e.sal)
from emp e, salgrade s
where e.sal between s.losal and s.hisal
group by s.grade, s.losal, s.hisal;
<SQL1999>
select s.grade, s.losal, s.hisal, count(e.empno), avg(e.sal)
from emp e
join salgrade s
on (e.sal between s.losal and s.hisal)
group by s.grade, s.losal, s.hisal;

--12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
<oracle>
select e.empno, e.ename, e.sal, d.dname
from emp e, dept d
where sal > all (select sal from emp where deptno = 30)
    and e.deptno = d.deptno;
<SQL1999>
select t.empno, t.ename, t.sal, d.dname
from dept d
join (
    select empno, ename, sal, deptno
    from emp
    where sal > all (
        select sal
        from emp
        where deptno = 30)) t
on (d.deptno = t.deptno);

--13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
<oracle>
select d.deptno, d.dname, d.loc, count(e.empno), avg(e.sal), 
    avg(months_between(sysdate, e.hiredate) / 12) year
from emp e, dept d
where e.deptno(+) = d.deptno
group by d.deptno, d.dname, d.loc;
<SQL1999>
select d.deptno, d.dname, d.loc, count(e.empno), avg(e.sal), avg(months_between(sysdate, hiredate) / 12) year
from emp e
right join dept d
on (e.deptno = d.deptno)
group by d.deptno, d.dname, d.loc;

--14.列出所有员工的姓名、部门名称和工资
<oracle>
select e.ename, d.dname, e.sal
from emp e, dept d
where e.deptno = d.deptno;
<SQL1999>
select e.ename, d.dname, e.sal
from emp e
join dept d
on (e.deptno = d.deptno);

--15.列出所有部门的详细信息和部门人数。
<oracle>
select d.deptno, d.dname, d.loc, count(e.empno)
from emp e, dept d
where e.deptno(+) = d.deptno
group by d.deptno, d.dname, d.loc;
<SQL1999>
select d.deptno, d.dname, d.loc, count(e.empno) count_empno
from emp e
right join dept d
on (e.deptno = d.deptno)
group by d.deptno, d.dname, d.loc;

--16.列出各种工作的最低工资及从事此工作的雇员姓名。
<oracle>
select e.ename, e.job, e.sal
from emp e, (
    select job, min(sal) min_sal
    from emp 
    group by job) temp
where e.job = temp.job 
    and e.sal = temp.min_sal;
<SQL1999>
select e.ename, t.job, e.sal
from emp e
join (
    select job, min(sal) min_sal
    from emp 
    group by job) t
on (e.job = t.job)
where e.sal = t.min_sal;
<SQL1999>

--17.列出各个部门的MANAGER（经理）的最低薪金、姓名、部门名称、部门人数。
<oracle>
select e.ename, e.sal, d.dname, res.count_empno
from emp e, (
    select deptno, min(sal) min_sal
    from emp
    where job = 'MANAGER'
    group by deptno) temp, dept d, (
    select deptno, count(empno) count_empno
    from emp 
    group by deptno) res
where e.deptno = temp.deptno
    and e.sal = temp.min_sal
    and e.job = 'MANAGER'
    and e.deptno = d.deptno
    and e.deptno = res.deptno;
<SQL1999>
select t3.ename, t3.min_sal, t3.dname, t4.count_empno
from (
    select deptno, count(empno) count_empno
    from emp 
    group by deptno) t4
join (
    select t2.ename, t2.min_sal, d.dname, d.deptno
    from dept d
    join (
        select e.ename, t1.min_sal, e.deptno
        from emp e
        join (
            select deptno,min(sal) min_sal
            from emp
            where job = 'MANAGER'
            group by deptno) t1
        on (e.deptno = t1.deptno and e.sal = t1.min_sal)) t2
    on (d.deptno = t2.deptno)) t3
on (t4.deptno = t3.deptno);

--18.列出所有员工的年工资，所在部门名称，按年薪从低到高排序。
<oracle>
select e.ename, (e.sal + e.comm) * 12 income, d.dname
from emp e, dept d
where e.deptno = d.deptno
order by income asc;
<SQL1999>
select e.ename, (e.sal + e.comm) * 12 income, d.dname
from emp e
join dept d
on (e.deptno = d.deptno)
order by income asc;

--19.查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000。
<oracle>
select distinct m.ename, d.dname, m .sal
from emp e, emp m, dept d
where e.mgr = m.empno
    and m.deptno = d.deptno
    and m.sal > 3000
<SQL1999>
select distinct t.ename, d.dname, t.sal
from dept d
join (
    select m.empno, m.ename, m.deptno, m.sal
    from emp e
    join emp m
    on (e.mgr = m.empno)) t
on (d.deptno = t.deptno)
where sal > 3000;

--20.求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数。
<oracle>
select d.dname, sum(e.sal), count(e.empno)
from emp e, dept d
where e.deptno(+) = d.deptno and d.dname like '%S%'
group by d.dname
<SQL1999>
select d.dname, sum(e.sal), count(empno)
from emp e
right join dept d
on (e.deptno = d.deptno)
where d.dname like '%S%'
group by d.dname;

--21.给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，依次类推。
<oracle>
update emp set
    sal = (1+deptno/100) * sal
where months_between(sysdate, hiredate) / 12 > 30
    or to_char(hiredate, ‘yyyy’) = 1987;