本文共 3935 字,大约阅读时间需要 13 分钟。
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
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;
转载地址:http://pbrji.baihongyu.com/