oracle高级查询

scott用户默认表介绍

1.部门表：dept

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

2.雇员表：emp

NO	名称	类型	描述
1	EMPNO	NUMBER(4)	雇员编号
2	EMANE	VARCHAR2(10)	雇员姓名
3	JOB	VARCHAR2(9)	雇员职位
4	MGR	NUMBER(4)	雇员对应的领导编号
5	HIREDATE	DATE	雇员雇佣日期
6	SAL	NUMBER(7，2)	基本工资
7	COMM	NUMBER(7，2)	奖金
8	DEPTNO	NUMBER(2)	雇员所在的部门编号

3.工资等级表：salgrade

NO	名称	类型	描述
1	GRADE	NUMBER	工资等级
2	LOSAL	NUMBER	此等级的最低工资
3	HISAL	NUMBER	此等级的最高工资

4.工资表：bonus

NO	名称	类型	描述
1	ENAME	VARCHAR2(10)	雇员姓名
2	JOB	VARCHAR2(9)	雇员职位
3	SAL	NUMBER	雇员工资
4	COMM	NUMBER	雇员的奖金

利用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