【Oracle】SQL查询60题

写在前面的
有些题目是要的*，即所有信息，但是我为了理解方便，有时候会简化，比如用ename代替*
还有一点：我的代码都是按三段式走，即：
select语句占一行，
from占一行，
where占一行，
建议初学者也使用这个套路，这样思路会清晰一些。

---

Tip：手机浏览可以双击代码块，横屏查看代码；如果是电脑浏览，想要复制代码，不用选中，只要鼠标在代码块内，右侧就会出现‘复制’按钮

5）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息（ 逻辑词 and or ）

select * 
from emp
where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK'
order by deptno asc, sal asc;

8)查询所有员工工资与奖金的和（ 求和函数sum() ）

select sum(sal)+sum(comm)
from emp;

上面的两个sum()必须分开使用，否则就变成了：查询所有有奖金的员工的工资与奖金和
这个前提是要把奖金为0的设置为null，否则，奖金为0的也算作是有奖金

update emp set comm=null where comm=0;


select sum(sal+comm)
from emp;

10）查询各月倒数第三天入职的员工信息（ last_day()函数 ）

select *
from emp 
where last_day(hiredate)-2=hiredate;

11）查询工龄大于等于10年的员工信息（ sysdate、months_between()、floor()函数 ）
按月数算年份:

select ename,floor(months_between(sysdate,hiredate)/12) 下取整工龄,'年' 
from emp 
where months_between(sysdate,hiredate)/12>=10;

按天数算年份：

select ename,floor((sysdate-hiredate)/365) 下取整工龄,'年' 
from emp 
where months_between(sysdate,hiredate)/12>=10;    

12）要求以首字母大写的方式查询所有员工的信息（ substr()、upper()、lower()函数，|| 合并符 ）

substr(字符串,截取开始位置,截取长度)

首先完成第一个字母大写

select upper(substr(ename,1,1)) from emp;

然后完成剩余字母小写

select lower(substr(ename,2,length(ename))) frome emp;

合并(使用两个||进行合并)

select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) 
from emp;   

13）查询员工姓名长度为6个字符的员工信息（ length()函数 ）

select * 
from emp 
where length(ename)=6;

14）查询员工名字中不包含字母‘S’的员工名（ like，not like匹配，%表示零或多个字符，_表示单个字符 ）
注意like后面的匹配条件使用单引号，如果使用双引号会报错“标识符无效”。

select ename 
from emp 
where ename not like '%S%';

15）查询员工姓名的第二个字母为M的员工信息（ substr()函数或like匹配，推荐使用后者，简洁 ）

select * 
from emp 
where substr(ename,2,1)='M';

或者

select * 
from emp 
where ename like '_M%';

17）查询所有员工姓名，如果包含字母‘S’则用‘s’替换（ replace()函数 ）
–replace(字符串1，字符串2，字符串3)
–用字符串3替换字符串1中包含的字符串2

select replace(ename,'S','s')
from emp;

19）显示所有员工的姓名，工种，工资，按工种降序排序，若工种相同，则按工资升序排序。( 两个排序条件 )

select ename,job,sal
from emp
order by job desc,sal asc;

20)显示所有员工的姓名，入职年月，按照入职日期所在的月份排序，如果月份相同，则按年份排序。
( to_char(‘日期’,‘格式’)函数，日期转字符串，与之对应的有一个to_date(‘日期’,‘格式’)函数，在插入日期的时候用到 )

select ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'MM') month
from emp
order by month,year;

21）查询在2月份入职的所有员工信息（ to_char()函数 ）

select ename,hiredate
from emp
where to_char(hiredate,'MM')=2;

22）查询至少有一个员工的部门信息（连接，别名，count函数，group by 分类汇总，此题背景：总共有4个部门，但是只有3个部门有员工）
（难题！！能否读懂题都玄乎，关键点就在于把题目转化为SQL语言，语文功底）

select d.dname,count(empno) 
from emp e join dept d on d.deptno=e.deptno 
group by d.dname,e.deptno 
having count(empno)>1;

26）查询所有部门，及其员工信息，包括那些没有员工的部门。（外连接（左右如果用不对，结果不一样））
（这句话的意思是，有可能存在闲置的部门，造成部门多员工少，而外连接的方向总是指向数量多的那张表，以使其作为长度的标准，即dept表）
–右外连接实现

select dept.*,emp.ename
from emp
right outer join dept on emp.deptno=dept.deptno;

–左外连接实现

select dept.*,emp.ename
from dept
left outer join emp on emp.deptno=dept.deptno;

结果：15 rows selected
27）查询所有部门，及其员工信息，包括哪些不属于任何部门的员工（外连接（左右如果用不对，结果不一样））
（这句话的意思是，有可能存在闲置的员工，造成部门少员工多，而外连接的方向总是指向数量多的那张表，以使其作为长度的标准，即emp表）
–左外连接实现

select dept.*,emp.ename
from emp
left outer join dept on emp.deptno=dept.deptno;

–右外连接实现

select dept.*,emp.ename
from dept
right outer join emp on emp.deptno=dept.deptno;

结果：14 rows selected

23）查询工资比SMITH高的所有员工的信息（子查询）

select * 
from emp
where sal>(
      select sal
      from emp
      where ename='CLARK');

24）查询所有员工姓名及其直接上级姓名（别名，出现在select语句中的相关子查询：子查询用到了外部条件）

select ename,(
       select ename 
       from emp e2
       where e2.empno=e1.mgr) mgr_name
from emp e1;

25）查询入职日期早于其直接上级领导的所有员工信息（别名，出现在select和where语句中的相关子查询：子查询用到了外部条件）

select ename,hiredate,
       (select ename 
       from emp e2
       where e2.empno=e1.mgr) mgr_name,
       (select hiredate
       from emp e2
       where e2.empno=e1.mgr) mgr_hiredate      
from emp e1
where e1.hiredate<(
	   select hiredate
       from emp e2
       where e2.empno=e1.mgr) ;

28）查询所有工种为CLERK的员工姓名及部门名称
（外连接是outer而不是out，另外这个可以使用内连接，即单纯的join，但是，有可能碰到员工有姓名，工种为CLERK，但不属于任何部门，所以，使用外连接是最合适的。）

select ename,dname,job
from emp left outer join dept on emp.deptno=dept.deptno
where job='CLERK';

30）查询平均工资低于2000的部门，及其员工信息。（外连接，in语句，相关子查询）

select * 
from dept left outer join emp on dept.deptno=emp.deptno
where dept.deptno in(
      select deptno 
      from emp
      group by deptno having avg(sal)<2000);

31）查询在SALES部门工作的员工的姓名信息
简单实现（只显示姓名，子查询）：

select ename
from emp
where emp.deptno=(
      select deptno
      from dept
      where dname='SALES');

完善（显示姓名+部门名，子查询+外连接）：

select ename,dname
from emp right outer join dept on emp.deptno=dept.deptno
where emp.deptno=(
      select deptno
      from dept
      where dname='SALES');

32、查询工资高于公司平均工资的员工信息（此题有坑）
如果凭第一印象：

select *
from emp
where sal>avg(sal)

报错：
ORA-00934: 此处不允许使用分组函数

略微思考一下，使用子查询即可

select * 
from emp
where sal>(
      select avg(sal)
      from emp);

34）列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。
(换句话说，非30号部门的所有人与30号部门中的某个人的工资相等的人)
网上给出的答案，只显示30号部门的员工 ，而不显示是谁和他们想等了

select ename,sal,deptno
from emp
where sal in (
      select sal
      from emp
      where deptno=30);

我给优化的：

select ename,sal,deptno
from emp
where sal in(
select sal
from emp
where sal in (
      select sal
      from emp
      where deptno=30) and deptno<>30);

35）查询工资高于30号部门中工作的所有员工工资的员工信息（all操作）

select * 
from emp 
where sal > all(
      select sal 
      from emp
      where deptno=30);

36）查询每个部门中的员工数量、平均工资和平均工作年限（sysdate关键字）
按天数算年份

select deptno, count(*) "员工数量",avg(sal) "平均工资", trunc(avg((sysdate-hiredate)/365)) "平均工作年限"
from emp
group by deptno;

按月数算年份

select count(empno),avg(sal),avg(abs(trunc(months_between(SYSDATE,hiredate)/12)))--年数
from emp 
group by deptno;

37）查询各个部门的详细信息以及部门人数，部门平均工资（外连接，group by的用法）

一句SQL语句实现（使用group by，必须控制变量数量，如果有多个变量，那么就想办法把多个变量绑定到一起）

select d.deptno, d.dname, d.loc, count(e.empno),avg(e.sal)
from dept d left outer join emp e on d.deptno=e.deptno
group by d.deptno,d.dname,d.loc;

38）查询各个部门中不同工种的最高工资（即按部门分类汇总，也按工资分类汇总）

select max(sal),job,deptno
from emp 
group by job,deptno
order by deptno asc,max(sal) desc;

39）查询10号部门的员工及其领导信息
（看似简单的题干之下，隐藏着很多知识点，就下面实现来说，还有不完善的地方，因为KING没有mgr，所以就不会查到KING单独的信息性，希望有一种方式能够列出三行，KING只占左半边，右半边空着）

select a.*,b.* 
from emp a, emp b
where a.mgr=b.empno and a.deptno=10;

40）查询工资为某个部门平均工资的员工信息
（查询为空，就自己update 表名 set 列名=列值 where 条件，此题还需注意一点，就是子查询返回多列，需要用in操作，不能用等号，否则会报错： ORA-01427: 单行子查询返回多个行）

select *
from emp
where sal in (
      select avg(sal)
      from emp
      group by deptno);

上面的 in 操作符也可以用 = any 替代

select *
from emp
where sal = any (
      select avg(sal)
      from emp
      group by deptno);

41）查询工资高于本部门平均工资的员工信息（起别名的重要性）

select *
from emp e
where e.sal>(
      select avg(sal)
      from emp
      where deptno=e.deptno)
order by deptno;

42）在41题的基础上，加上部门的平均工资

一句SQL语句实现
（此题有点小难，而且也充分的提现出了别名的重要性，如果不起别名，就无法在外部调用内部的avg（sal）

select e.*,s.d_avgsal
from emp e,(select deptno,avg(sal) d_avgsal
            from emp
            group by deptno) s
where e.sal>s.d_avgsal and e.deptno=s.deptno
order by e.deptno;

43）查询工资高于20号部门某个员工工资的员工信息
（此题单纯的按照题目查询出来的结果会让旁人看不懂，因为你是某个，到底是哪一个，没有展示，当然，目的主要是练习一下 > any 的语法）

select * 
from emp
where sal > any (
      select sal
      from emp
      where deptno = 20);

44）统计各个工种的员工人数与平均工资
（此题虽说简单，但实属经典的分类汇总group by的例子）

select job,count(*),avg(sal)
from emp
group by job;

45）统计每个部门中各工种的人数与平均工资
（此题虽说简单，但实属经典的多项分类汇总group by的例子）

select count(*),job,avg(sal),deptno
from emp
group by job,deptno
order by deptno;

46）查询工资、奖金与10 号部门某个员工工资、奖金都相同的员工的信息。
（nvl（）函数，用一同一张表用三次，也是醉了！）

select e1.* 
from emp e1 join (
     select sal,comm 
     from emp e2 
     where deptno = 10) e3 on e1.sal=e3.sal 
     and 
     nvl(e1.comm,0)=nvl(e3.comm,0) 
     and 
     e1.deptno != 10;

47）查询部门人数大于5的部门的员工的信息。
（这个难在where子句中不能出现分组函数count(),否则会报错：ORA-00934: 此处不允许使用分组函数 ）

select * 
from emp
where deptno in (
      select deptno
      from emp
      group by deptno having count(*)>5);

48）查询所有员工工资都大于1000的部门的信息。
(反向思维，两层where语句的子查询，in和not in混用，dept和emp混用)

select * 
from dept 
where deptno in (
      select distinct deptno 
      from emp 
      where deptno not in (
            select distinct deptno 
            from emp 
            where sal < 1000)) ;

49）查询所有员工工资都大于1000的部门的信息及其员工信息。
（一层from语句的子查询，一层where语句的子查询）

select * 
from emp join dept on dept.deptno in (
     select distinct deptno 
     from emp 
     where deptno not in (
           select distinct deptno 
           from emp 
           where sal < 1000)) 
           and
           dept.deptno=emp.deptno;

50）查询所有员工工资都在900-3000的部门信息。
（查询部门信息就找dept表，查询条件是依据部门号，这是唯一一个和emp表有联系的列，从emp表中查询部门号，查询条件是按部门号进行分类汇总，分类汇总的条件是工资都在900-3000）

select * 
from dept 
where deptno in(
     select deptno 
     from emp 
     group by deptno having min(sal)>900 and max(sal)<3000);

51）查询所有工资在900-3000的员工所在部门的员工信息
（与上一题只是外部查询表换成了emp，这两道题一定要头脑清醒，否则容易被绕晕）

select * 
from emp 
where deptno in(
     select deptno 
     from emp 
     group by deptno having min(sal)>900 and max(sal)<3000);

52）查询人数最多的部门信息
（三层四个子查询，你终于还是出现了，然而你的题目是如此的简洁！！！叹为观止。）

select * 
from dept
where deptno in(
      select deptno 
      from (
           select count(*) count,deptno 
           from emp 
           group by deptno)
      where count in(
            select max(count)
            from (
                 select count(*) count ,deptno 
                 from emp 
                 group by deptno)));

53）查询30号部门中工资排序前3名的员工信息。

select * 
from(
       select sal,deptno 
       from emp 
       where deptno=30 
       order by sal desc) e
where rownum<4;

54）查询所有员工中工资排在5~10名之间的员工信息。

select *
from emp 
where empno in (
      select empno 
      from (
           select empno,rownum num 
           from (
                select empno,sal 
                from emp 
                order by sal desc)) 
      where num between 5 and 10 ) 
order by sal desc;

55）向emp表中插入一条记录
员工号1357，员工姓名oracle，工资2050，部门号20，入职日期为2002年5月10日
（处理空缺列值的方法）
NULL方法：

insert into emp values(1357,'oracle',NULL,NULL,to_date('2002-05-10','yyyy-mm-dd'),2050,NULL,20);

指定列名称方法：

insert into emp(empno,ename,hiredate,sal,deptno) values(2357,'oracle',to_date('2002-05-10','yyyy-mm-dd'),2050,20);

56）向emp表中插入一条记录,员工名字为FAN,员工号为8000,其他信息与SMITH员工的信息相
（此题告诉人们，别学完这一节之后脑子里只有select开头的SQL语句了，其实还有好多开头的SQL语句，而且select语句也可以用于其他语句中充当子句。）

insert into emp select 8000,'FAN',job,mgr,hiredate,sal,comm,deptno
                from emp
                where ename='SMITH';

57）将各部门员工的工资修改为该员工所在部门平均工资加1000。
（此操作过后会修改所有人的工资，而且会使工资数字变得毫无意义，如果想撤销操作，可以使用rollback命令。）

update emp e set sal=1000+(select avg(sal) from emp where deptno=e.deptno);

操作结果及rollback示意图：

.

看完如果觉得不错，留个赞再走。

.