Oracle的完整练习(一)

1.选择部门30中的所有员工：
 SELECT * FROM emp WHERE deptno=30;

2.列出所有办事员(CLERK)的姓名，编号和部门编号：
 SELECT ename,empno,deptno FROM emp WHERE UPPER(job)='CLERK';

3.列出佣金(comm)高于薪金(sal)的员工：
 SELECT * FROM emp WHERE comm>sal;

4.找出佣金（comm）高于薪金百分60的员工
 SELECT * FROM emp WHERE comm>(sal*0.6);

5.找出部门10中所有经理(MANAGER) 和部门20中所有办事员（CLERK）的详细资料
 SELECT * FROM emp WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK');

6.找出部门10中所有经理，部门20中所有的办事员，既不是经理又不是办事员，但是薪金大于或等于2000的所有员工的资料：
 SELECT * FROM emp WHERE 
 (deptno=10 AND job='MANAGER') OR 
 (deptno=20 AND job='CLERK') OR 
 (job NOT IN('MANAGER','CLERK') AND sal>=2000);

7.找出收取佣金（comm）的员工的不同工作：[DISTINCT->消除重复的关键字]
 SELECT DISTINCT job  FROM emp WHERE comm IS NOT NULL;

8.找出不收取佣金或者佣金小于100的员工：
 SELECT * FROM emp WHERE comm IS NULL OR comm<100;

9.找个各月倒数第三天受雇的所有员工：
·使用LAST_DAY()函数
 SELECT * FROM emp WHERE (LAST_DAY(hiredate)-2)=hiredate;

10.找出早于12年前受雇的员工：
·注意使用MONTHS_BETWEEN(今天,雇佣日期)
 SELECT * FROM emp WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12>12;

11.按照首字母大写的方式显示员工姓名
 SELECT INITCAP(ename) FROM emp;

12.显示正好为5个字符的员工的姓名
 SELECT ename FROM emp WHERE LENGTH(ename)=5;

13.显示不带有"R"的员工姓名：
 SELECT ename FROM emp WHERE ename NOT LIKE '%R%';

14.显示所有员工姓名的前3个字符：
 SELECT SUBSTR(ename,0,3) FROM emp;

15.显示所有员工的姓名，并且用“x” 替换替换所有的 “A”；
 SELECT REPLACE(ename,'A','x') FROM emp;

16.显示满十年服务年限的员工的姓名和受雇日期：
 SELECT ename,hiredate FROM emp WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 >10;

17.显示员工的详细资料，按姓名排序:
 SELECT * FROM emp ORDER BY ename;

18.显示员工的姓名和受雇日期，并根据其服务年限，把资料最老的员工排在第在前面：
 SELECT ename,hiredate FROM emp ORDER BY hiredate;

19.显示所有员工的姓名，工作和薪金，按工作的降序排序，若工作相同则按薪金排序：
 SELECT ename,job,sal FROM emp ORDER BY job DESC,sal;

20.显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在的年排序，若年份相同则讲最早月份的员工排在最前面：
·使用TO_CHAR()函数
 SELECT ename,TO_CHAR(hiredate,'yyyy') year,TO_CHAR(hiredate,'MM') mon FROM emp ORDER BY year,mon;

21.显示在一个月为30天的情况所有员工的日薪金，并且忽略余数：
ROUND() 四舍五入
 SELECT ename,ROUND(sal/30) 日薪金 FROM emp;

22.找出在（任何年份）的2月受聘的所有员工：
 SELECT * FROM emp WHERE TO_CHAR(hiredate,'MM')=2;

23.对于每个员工，显示其加入公司的天数：
 SELECT ROUND(sysdate-hiredate)  FROM emp;

24.显示姓名字段的任何位置包含“A”的所有员工姓名：
 SELECT ename FROM emp WHERE ename LIKE '%A%';

25.以年月的方式显示所有员工的服务年限：
·年：求出总共的月/12 ->





 产生小数，并不能四舍五入
·月：对12取余 
 SELECT ename,
TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) year,
TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) mon
FROM emp;


/*-------------复杂查询，子查询，多表关联--------------*/

26.列出至少有三个员工的所有部门和部门信息。[!!]
SELECT d.*,ed.count FROM dept d,(
 SELECT deptno,COUNT(empno) count FROM emp GROUP BY deptno HAVING COUNT(empno)>3) ed
WHERE d.deptno=ed.deptno;

27.列出薪金比“ALLEN”多的所有员工
 SELECT sal FROM emp WHERE ename='ALLEN';  //子查询
SELECT * FROM emp 
WHERE sal>(SELECT sal FROM emp WHERE ename='ALLEN');

28.列出所有员工的姓名及其上级的姓名：
 SELECT e.ename 员工,m.ename 上级
 FROM emp e,emp m
 WHERE e.mgr=m.empno(+);
由于KING并没有上级，所以添加一个(+)号表示左连接

29.列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称
 SELECT e.ename,e.empno,d.dname
 FROM emp e,emp m,dept d
 WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.hiredate1500;
·在按照
SELECT e.job,COUNT(empno) 
FROM emp e
WHERE job IN(
 SELECT job FROM emp
 GROUP BY job HAVING MIN(sal)>1500
)
GROUP BY e.job;

33.列出在部门销售部工作的员工姓名，假设不知道销售部的部门编号
·根据DEPT表查询销售部的部门编号(子查询)
SELECT deptno
FROM dept
WHERE dname='SALES';
·上述为子查询
SELECT ename FROM emp
WHERE de