oracle: 基于schema scott的SQL复习和练习(part I)

--sql练手by bb30.

--1.列出至少有一个员工的所有部门.
select d.DEPTNO,d.DNAME from dept d where exists (select 1 from emp e where d.deptno=e.deptno);

--2.列出薪金比 “ SMITH ” 多的所有员工.
select e.EMPNO,e.ENAME,e.SAL from emp e where e.sal > (select e.sal from emp e where ename='SMITH');
    create index ind_emp_ename on emp(ename);
    create index ind_emp_sal on emp(sal);

--3.列出所有员工的姓名及其直接上级的姓名.
select e.EMPNO,e.ENAME,e_mgr.ename as mgr_name from emp e left join emp e_mgr on e.mgr=e_mgr.empno;

--4.列出受雇日期早于其直接上级的所有员工.
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
select e.EMPNO,e.ENAME,e.hiredate,e_mgr.ename,e_mgr.hiredate from emp e inner join emp e_mgr on (e.mgr=e_mgr.empno and e.hiredate < e_mgr.hiredate);

--5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select d.deptno,d.dname,e.ename from dept d left join emp e on d.deptno=e.deptno;

--6.列出所有 “ CLERK ” （办事员）的姓名及其部门名称。
select e.empno,e.ename,e.job,d.DNAME,d.deptno from emp e  inner join dept d on (e.deptno=d.deptno and e.job='CLERK');

--7.列出最低薪金大于 1500 的各种工作。
select job from emp group by job HAVING MIN(sal) >1500;

--8. 列出在部门 “ SALES ” （销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select e.empno,e.ename from emp e where deptno=(select deptno from dept where dname='SALES');

--9.列出薪金高于公司平均薪金的所有员工
select e.empno,e.ename from emp e where sal>(select avg(sal) from emp e);

--10.列出与 “ SCOTT ” 从事相同工作的所有员工。
select e.empno,e.ename from emp e where e.job=(select job from emp where ename='SCOTT');

--11.列出与部门 30 中员工的工作相同的所有员工的姓名和薪金
select e.empno,e.ename,e.sal from emp e where job in
(select job from emp  where deptno=30) and e.deptno <>30;

--12.列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金
select e.empno,e.ename,e.sal from emp e where e.sal>
(select max(sal) from emp  where deptno=30);

--13.列出在每个部门工作的员工数量,平均工资
select d.deptno,d.dname,count(e.empno),round(avg(e.sal),2) from emp e inner join dept d on e.deptno=d.deptno group by d.deptno,d.dname;

--14.列出所有员工的姓名,部门名称和工资
select e.empno,e.ename,e.sal,d.dname from emp e inner join dept d on e.deptno=d.deptno;

--15. 列出所有部门的详细信息和部门人数
select d.DEPTNO, d.DNAME, d.LOC,count(e.ename) from dept d left join emp e on d.deptno=e.deptno group by d.DEPTNO, d.DNAME, d.LOC;

--16. 列出各种工作的最低工资。
select job,min(sal) from emp group by job;

--17.列出各个部门的 MANAGER （经理）的最低薪金。
select e_mgr.deptno,d.dname,min(e_mgr.sal)  from emp e inner join emp e_mgr on e.mgr=e_mgr.empno  inner join dept d on e_mgr.deptno=d.deptno group by e_mgr.deptno,d.dname;

--18.列出所有员工的年工资,按年薪从低到高排序
SELECT  e.empno,e.ename,12*(e.sal+nvl(e.comm,0)) as y_sal FROM emp e order by y_sal;

--19.用一条 sql 语句查询出 scott.emp 表中每个部门工资前三位的数据，显示结果如下：
select p.deptno,sum(decode(p.rn,1,sal,null)) as sal1,
sum(decode(p.rn,2,sal,null)) as sal2,
sum(decode(p.rn,3,sal,null)) as sal3
from
(select * from (select emp.deptno,emp.sal, row_number() over(partition by deptno order by sal desc) as rn from emp) where rn<=3) p
group by p.deptno order by p.deptno;

    DEPTNO       SAL1       SAL2       SAL3
---------- ---------- ---------- ----------
        10       5500       5500       5500
        20       3000       3000       2975
        30       2850       1600       1500