oracle高级查询

scott用户默认表介绍

1.部门表：dept

NO名称类型描述1DEPTNONUMBER(2)表示部门编号，有两位数组成2DNAMEVARCHAR2(14)部门名称，最多由14个字符组成3LOCVARCHAR2(13)部门所在位置

2.雇员表：emp

NO名称类型描述1EMPNONUMBER(4)雇员编号2EMANEVARCHAR2(10)雇员姓名3JOBVARCHAR2(9)雇员职位4MGRNUMBER(4)雇员对应的领导编号5HIREDATEDATE雇员雇佣日期6SALNUMBER(7，2)基本工资7COMMNUMBER(7，2)奖金8DEPTNONUMBER(2)雇员所在的部门编号

3.工资等级表：salgrade

NO名称类型描述1GRADENUMBER工资等级2LOSALNUMBER此等级的最低工资3HISALNUMBER此等级的最高工资

4.工资表：bonus

NO名称类型描述1ENAMEVARCHAR2(10)雇员姓名2JOBVARCHAR2(9)雇员职位3SALNUMBER雇员工资4COMMNUMBER雇员的奖金

利用scott默认表进行如下操作：

1.列出至少有一个员工的所有部门。

select * from dept where deptno in (select deptno from emp group by deptno having count(*)>=1 )

2.列出薪金比“SMITH”多的所有员工

select ename from emp where sal>(select sal from emp where ename='SMITH')

3.列出所有员工的姓名及其直接上级的姓名。

select e.ename,m.ename as manager from emp e join emp m on e.mgr=m.empno

4.列出受雇日期早于其直接上级的所有员工

select e.*,m.hiredate as mh from emp e join emp m on e.mgr = m.empno where e.hiredate<m.hiredate

--4．列出受雇日期早于其直接上级的所有员工。

--将emp表看成两张表，一张员工表e,一张管理者表m

--本质还是表的自连接，只不过多了两个条件，mgr不可以为空，并且e.hiredate<m.hiredate

select e.*,m.hiredate as mh from emp e join emp m on e.mgr = m.empno where e.hiredate<m.hiredate

--6、列出所有“CLERK”（办事员）的姓名及其部门名称。

select ENAME,D.DNAME from emp e  join dept d on e.deptno = d.deptno where e.job='CLERK'

--7．列出最低薪金大于1500的各种工作。  

SELECT JOB FROM EMP GROUP BY JOB HAVING MIN(SAL)>1500

--8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

--9．列出薪金高于公司平均薪金的所有员工。

select * from emp where sal>(select avg(sal) from emp)

--10．列出与“SCOTT”从事相同工作的所有员工。

--11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

--12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

--13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

select count(*),avg(sal),round(avg(months_between(sysdate,hiredate)/12)) from emp group by deptno

-- 14．列出所有员工的姓名、部门名称和工资。

--15．列出所有部门的详细信息和部门人数。

select d.*,a.empnum from dept d join (select count(*) as empnum,deptno from emp group by deptno) a on d.deptno = a.deptno

--16．列出各种工作的最低工资。

--17．列出各个部门的MANAGER（经理）的最低薪金。

select MIN(SAL),DEPTNO from emp where job='MANAGER' GROUP BY DEPTNO