Oracle常用SQL查询（常见题目）

Oracle数据库对于开发人员来说重点内容是DML，即针对数据库的增删改查，以及更新删除操作。

所有的查询结构都是在Oracle实例scott用户下进行的，该用户下有四张表,分别是emp,dept,salgrade,bonus。

实例：

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

SELECT dname FROM deptWHERE deptno IN(SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(deptno) >= 1);

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

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH');

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

SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr = m.empno;

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

SELECT e.ename FROM emp e,emp m WHERE e.mgr=m.empno AND e.hiredate<m.hiredate;--多表查询SELECT e.ename FROM emp e WHERE e.hiredate<(SELECT hiredate FROM emp m WHERE e.mgr=m.empno)；--子查询

5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT d.dname,e.* FROM emp e, dept d WHERE e.deptno(+) = d.deptno;

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

SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno AND e.job = 'CLERK';

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

SELECT DISTINCT job FROM emp GROUP BY job HAVING MIN(sal) > 1500;

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

SELECT * FROM emp e WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='SALES');--方法一SELECT * FROM emp e,(SELECT deptno FROM dept WHERE dname='SALES') tmp WHERE e.deptno = tmp.deptno;--方法二

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

SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp); 

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

SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT';

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

SELECT ename,sal FROM emp WHERE sal IN(SELECT sal FROM emp WHERE deptno = 30);       --AND deptno<>30;

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

SELECT ename,sal FROM emp WHERE sal >ALL(SELECT sal FROM emp WHERE deptno = 30);

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

SELECT deptno,COUNT(1),trunc(AVG(sal)),trunc(AVG(months_between(SYSDATE,hiredate)/12))FROM empGROUP BY deptno;

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

SELECT e.ename,d.dname,e.sal FROM emp e,dept d WHERE e.deptno = d.deptno;

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

SELECT COUNT(e.deptno),d.dname,d.loc,d.deptno FROM emp e, dept d WHERE e.deptno(+) = d.deptno GROUP BY d.dname,d.loc,d.deptno

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

SELECT min(sal),job FROM emp GROUP BY job; 

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

--SELECT * FROM emp;SELECT deptno,MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno;

18.列出所有员工的年工资，按年薪从低到高排序。

SELECT ename,(sal+NVL(comm,0))*12 income FROM emp ORDER BY income ASC;

–19.查出某个员工的所有上级主管，并要求出这些主管中的薪水超过3000 (用一条sql语句解决)

SELECT e.ename 雇员姓名,m.ename 领导姓名 FROM emp e,emp m WHERE e.mgr = m.empno AND m.sal > 3000;

20.求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数
要求：部门工资合计要 大于 5000，并且按照部门的人数排序。

--function1SELECT COUNT(e.empno),NVL(SUM(sal),0),d.deptno FROM emp e,dept d WHERE d.deptno IN (SELECT deptno FROM dept WHERE dname LIKE '%S%')      AND e.deptno(+) = d.deptnoGROUP BY d.deptnoHAVING SUM(e.sal)>5000;--function2SELECT d.deptno,NVL(SUM(sal),0),COUNT(empno)FROM emp e,dept dWHERE e.deptno(+)=d.deptno AND d.dname LIKE '%S%'GROUP BY d.deptno ;

21.给任职日期超过10年的人加薪10%

UPDATE myemp SET sal = sal*1.1 WHERE months_between(SYSDATE,hiredate)/12 > 10;--SELECT * FROM myemp;

22、列出员工任职的年、月、日

SELECT          TRUNC(months_between(SYSDATE,hiredate)/12) 年,         TRUNC(MOD(months_between(SYSDATE,hiredate),12)) 月,         TRUNC(SYSDATE - add_months(hiredate,TRUNC(months_between(SYSDATE,hiredate)))) 日FROM emp;

–23、列出员工中薪水最低，任职日期最长的员工。

SELECT * FROM emp WHERE (sal,hiredate)=(SELECT MIN(sal),MIN(hiredate) FROM emp);

24.列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数，平均工资。

SELECT e.empno,e.ename,e.job,d.dname,tmp.avgsal,tmp.countFROM emp e,dept d,(SELECT deptno,AVG(sal) avgsal,COUNT(*) COUNT FROM emp GROUP BY deptno) tmpWHERE e.deptno = d.deptno      AND d.deptno = tmp.deptno      AND e.job=(SELECT job FROM emp WHERE ename='SCOTT')      AND e.ename<>'SCOTT';

25.列出薪金高于在部门30工作的所有员工的薪金以及高于公司平均工资的的员工姓名和薪金、部门名称、部门平均工资、个人工资等级。

SELECT e.empno,e.ename,tmp.avgsal,s.gradeFROM emp e,dept d,salgrade s,(SELECT deptno dno,AVG(sal) avgsal                                      FROM emp GROUP BY deptno) tmpWHERE sal>ALL(SELECT sal FROM emp WHERE deptno = 30)      AND sal>(SELECT AVG(sal) FROM emp)      AND e.deptno = tmp.dno      AND e.deptno = d.deptno      AND e.sal BETWEEN s.losal AND s.hisal;