Oracle练习

1、基础训练

1. 选择部门30中的所有员工：

 SELECT * FROM s_emp WHERE deptno=30;

2. 列出所有办事员(CLERK)的姓名，编号和部门编号：

SELECT ename,empno,deptno FROM s_emp WHERE UPPER(job)='CLERK';

3. 列出佣金(comm)高于薪金(sal)的员工：

 SELECT * FROM s_emp WHERE comm>sal;

4. 找出佣金（comm）高于薪金百分60的员工 ^

SELECT * FROM s_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"的员工姓名： ^
    like和not like

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 s_emp order by name desc

18. 显示员工的姓名和受雇日期，并根据其服务年限，把资料最老的员工排在第在前面：

select LAST_NAME, START_DATE , ROUND(MONTHS_BETWEEN(sysdate,START_DATE),0) "就职时间/月" from S_EMP order by "就职时间/月" desc

19. 显示所有员工的姓名，工作和薪金，按工作的降序排序，若工作相同则按薪金排序：

注意：order by 可以同时指定几个字段，在左边的先排序

select LAST_NAME , TITLE,SALARY from S_EMP order by TITLE desc,SALARY

20. 显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在的年排序，若年份相同则讲最早月份的员工排在最前面：
    ·使用TO_CHAR()函数

select LAST_NAME , to_char(START_DATE ,'yyyy') year ,to_char(START_DATE,'mm') month  from S_EMP order by year asc , month asc

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

select LAST_NAME, SALARY , ROUND(SALARY/30,0) "日薪/元" from S_EMP

22. 找出在（任何年份）的2月受聘的所有员工：

select LAST_NAME ,START_DATE,to_char(START_DATE,'mm') "月份" from S_EMP  where to_char(START_DATE,'mm') = '02'

23. 对于每个员工，显示其加入公司的天数：

select LAST_NAME, ROUND(sysdate-START_DATE) "入职时间/天"  from S_EMP where  ROUND(sysdate-START_DATE) >0 order by "入职时间/天" desc 

24. 显示姓名字段的任何位置包含“A”的所有员工姓名：

select LAST_NAME from S_EMP where LAST_NAME like '%N%'

25. 以年月的方式显示所有员工的服务年限：
    ·年：求出总共的月/12 -> 产生小数，并不能四舍五入
    ·月：对12取余

select LAST_NAME , TRUNC(MONTHS_BETWEEN(sysdate,START_DATE)/12) "服务时间/年" , TRUNC(MOD(MONTHS_BETWEEN(sysdate,START_DATE),12)) "服务时间/月" from S_EMP

基础训练总结

• 使用 like 和not like 可以搜索匹配字符串
• 单行函数既可以作用于列投影，也可以作用于where条件的判断
• 两个时间相减可以得到相差的天数
• order by 后面可以跟多个排序条件，排序优先级从左往右，先排序左边的，在排序好的基础上排序右边的
• to_char()函数可以用来格式化输出各种字符串，比如时间和￥999，999，999等格式
• LAST_DAY()函数可以得出每个月的最后一天是什么日期 ，比如 ：2021年2月的最后一天是 2021年2月28日。通过这个方法可以判断一个月有几天
• REPLACE(str,‘被替换的字符’,‘要替换的字符’) ：将指定的字符str1 换成 指定的字符str2

2、复杂查询，子查询，多表关联

26. 列出至少有三个员工的所有部门和部门信息。[!!]

select d.ID , d.NAME , count(*)  
from S_EMP e , S_DEPT d 
where e.DEPT_ID = d.ID 
group by d.ID, d.NAME 
having count(*)>=3

27. 列出薪金比“ALLEN”多的所有员工

select  LAST_NAME,SALARY from S_EMP where SALARY > (select SALARY from S_EMP where LAST_NAME = 'Ngao' )

28. 列出所有员工的姓名及其上级的姓名：

select * from s_emp e , s_emp s where e.mgr = s.empno(+)

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.hiredate<m.hiredate