Oracle数据库sql语句练习【emp和dept的连表查询由浅入深】

Oracle SQL语句练习题

一、表的介绍

Oracle中有几个原始的表，我们可以以其中的三个表为例，来学习一下Oracle中SQL语句的使用。先看下这几个表的结构：

1. 雇员表（emp）

假设该表中的数据为：

2. 部门表（dept）

假设部门表中数据如下：

二、SQL语句

1. 查询20号部门的所有员工信息

select * from emp where deptno = 20;

2. 查询所有工种为CLERK的员工的工号、员工名和部门名

 select empno,ename,dname from emp,dept where job like 'CLERK' and dept.deptno=emp.deptno;

3. 查询奖金（COMM）高于工资（SAL）的员工信息

select * from emp where comm > sal;

4. 查询奖金高于工资的20%的员工信息

select * from emp where comm > (sal*0.2);

5. 查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息

select * from emp where (deptno = 10 and job like 'MANAGER') or (deptno = 20 and job like 'CLERK');

6. 查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息

select * from emp  where job not in ('MANAGER','CLERK') and sal >= 2000 ;

7. 查询有奖金的员工的不同工种

select distinct job from emp  where comm is not null;

8. 查询所有员工工资和奖金的和
   在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。

select ename,(sal+nvl(comm,0)) salcomm from emp;

9. 查询没有奖金或奖金低于100的员工信息

select * from emp where (comm is null or comm < 100) ;

10. 查询各月倒数第2天入职的员工信息
      last_day(time):返回指定日期所在月份的最后一天。

select * from emp 
	where hiredate in (select (last_day(hiredate)-1) from emp);

11. 查询员工工龄大于或等于10年的员工信息

select * from emp where (sysdate - hiredate)/365 >= 10 ;

12. 查询员工信息，要求以首字母大写的方式显示所有员工的姓名

    函数INITCAP()是将每个单词的第一个字母大写,其它字母变为小写返回。

select initcap(ename) from emp;

13. 查询员工名正好为6个字符的员工的信息
      length(string)计算string所占的字符长度。

select * from emp where length（ename）= 6 ;

14. 查询员工名字中不包含字母“S”员工,实现的方式有’not like’和’not in’两种。

select * from emp where ename not in (select ename from emp where ename like '%S%') ;

select * from emp where ename not like "%S%";

select ename,job,sal from emp where length(ename)- length(replace(ename,'S'))=0;

select ename,job,sal from emp where instr(ename,'S',1)=0;

15. 查询员工姓名的第2个字母为“M”的员工信息

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

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

select ename,job from emp where instr(ename,'M')=2;

16. 查询所有员工姓名的前3个字符
     substr，截取字符串，当第二个参数为0或1时，都是从第一位开始截取。

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

17. 查询所有员工的姓名，如果包含字母“s”，则用“S”替换

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

18. 查询员工的姓名和入职日期，并按入职日期从先到后进行排列

select ename,hiredate from emp order by hiredate asc ;

19. 显示所有的姓名、工种、工资和奖金，按工种降序排列，若工种相同则按工资升序排列

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

20. 显示所有员工的姓名、入职的年份和月份，若入职日期所在的月份排序，若月份相同则按入职的年份排序

select ename,to_char(hiredate,'yyyy')||'-'||to_char(hiredate,'mm') 
	from emp order by to_char(hiredate,'mm'),to_char(hiredate,'yyyy');

21. 查询在2月份入职的所有员工信息
      to_char是转换成字符型，Oracle特殊的地方在于字符型是可以与数值比较的。

select * from emp where to_char(hiredate,'mm') ='02' ;

22. 查询所有员工入职以来的工作期限，用“年月日”的形式表示
      floor向下取整；ceil向上取整；||字符串拼接。

select ename,floor((sysdate-hiredate)/365)||'年'
	||floor(mod((sysdate-hiredate),365)/30)||'月'
	||ceil(mod(mod((sysdate-hiredate),365),30))||'天' 
	from emp ;

23. 查询至少有一个员工的部门信息
      在员工表中，每个员工都有对应的部门号，这就意味着员工表中的部门号对应的部门都是有员工的。

select * from dept 
	where deptno in (select deptno from emp);

24. 查询工资比SMITH员工工资高的所有员工信息

–先查出SMITH工资，作为一个临时查询结果

select sal from emp where ename like 'SMITH'

select * from emp 
	where sal > (select sal from emp where ename like 'SMITH') ;

25. 查询所有员工的姓名及其直接上级的姓名
      join on是一种比较“自由”的语句，用于表连接，可以是等值连接，也可以是非等值连接。

–先创建一个临时查询的表，用以表示直接上级
select ename staname,mgr from emp
–然后再将两张表进行连接，分别查询其中的一个字段

select staname,ename 
	from (select ename staname,mgr from emp) t join emp on t.mgr = emp.empno;

select emp1.ename,emp2.ename from emp emp1 join emp emp2 on emp1.empno=emp2.mgr;

 select yg.ename,(select ld.ename from emp ld where yg.mgr=ld.empno) from emp yg;

26. 查询所有部门及其员工信息（即要输出两张表的内容），包括那些没有员工的部门

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

27. 查询所有工种为CLERK的员工的姓名及其部门名称
    一个关联查询+一个条件判断。
    on 后面无论是等值连接，还是非等值连接，一般都是不同表中数据的条件判断。

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

28. 查询最低工资大于2500的各种工作
    子查询可以出现在select、from、where中。
    group by用于对结果集分组，此处如果不使用group by job，就会只求出所有工作的一个最低工资，最后不能筛选出多种工作。

--这种多重条件的，可以按步骤拆分，第一个条件是最低工资
select min(sal) min_sal,job from emp group by job
--然后将第一步骤中生成的临时字段用于条件判断

select job from (select min(sal) min_sal,job from emp group by job) t
where min_sal > 2500 ;

select job from emp group by job having min(sal) >2500;

29. 查询最低工资低于2000的部门及其员工信息【难】
     需要看出该题求的最后的完整的emp表信息。
     子查询一般在from或where中。

--第一步，按部门求最低工资
select min(sal) min_sal,deptno from emp group by deptno
--第二步，将第一步的查询结果作为临时表，然后加上<2000的条件
select deptno from 
	(select min(sal) min_sal,deptno from emp group by deptno) 
	where min_sal < '2000'
--第三步，第二步已经求出了想要的部门，在emp中查询对应的信息即可

select * from emp where deptno 
	in (select deptno 
		from (select min(sal) min_sal,deptno from emp group by deptno) 
		where min_sal < '2000') ;

select dept.dname,dept.loc,emp.* from dept join emp on dept.deptno = emp.deptno where dept.deptno in(select deptno from emp group by deptno having min(sal) < 2000);

30. 查询在SALES部门工作的员工的姓名信息

select ename,dname from emp,dept where emp.deptno = dept.deptno and dname = 'SALES';

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

31. 查询工资高于公司平均工资的所有员工信息

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

32. 查询与SMITH员工从事相同工作的所有员工信息

select * from emp 
	where job in (select job from emp where ename like 'SMITH') 
	and ename not like 'SMITH';

33. 列出工资等于30号部门中某个员工工资的所有员工的姓名和工资

--先求出30号部门中所有员工的工资
select ename,sal from emp where 
	sal = any (select sal from emp where deptno = 30) ;

34. 查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资

--先求出30号部门中所有员工的工资

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

35. 查询每个部门中的员工数量、平均工资和平均工作年限

select dname,count,avg_sal,avg_date 
	from dept join (select count(*) count,avg(sal) avg_sal,avg((sysdate-hiredate)/365) avg_date,deptno from emp group by deptno) t 
	on dept.deptno = t.deptno ;

36. 查询入职日期早于其直接上级领导的所有员工信息【难】

--第一步，创建一张临时表，其实是给自身tmp表起个别名
select empno staempno,hiredate stahiredate,mgr from emp
--第二步，将实际tmp表和临时表连接，并加入入职时间的判断

select * from emp 
	where empno in (select staempno from 
		(select empno staempno,hiredate stahiredate,mgr from emp) t 
			join emp on t.mgr = emp.empno and stahiredate < hiredate) ;

37. 查询从事同一种工作但不属于同一部门的员工信息

select distinct emp1.empno,emp1.ename,emp1.deptno  
       from emp emp1 join emp emp2 
       on emp1.job=emp2.job and emp1.deptno!=emp2.deptno;

38. 查询各个部门的详细信息以及部门人数、部门平均工资
      先拆分题目，各个部门的详细信息，即dept.*，可以直接获取。部门人数和部门平均工资，均可以从emp表中计算得出，所以可将该结果存在临时表中，然后再将dept表和临时表关联即可。

Select dept.*,person_num,avg_sal 
	from dept,(select count(*) person_num,avg(sal) avg_sal,deptno from emp group by deptno) t 
	where dept.deptno = t.deptno ;
	select dept.*,count(*) dept_num,avg(sal) acg_sal 
       from dept join emp on dept.deptno=emp.deptno
       group by deptno;

39. 查询各种工作的最低工资

select job,min(sal) from emp group by job ;

40. 查询各个部门中的不同工种的最高工资

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

41. 查询10号部门员工以及领导的信息
      此处是分两种情况进行查询的：10号部门员工和10号部门领导。

select * from emp where deptno=10 
	or empno in(select mgr from emp where deptno=10);

42. 查询各个部门的人数及平均工资

select deptno,count(*),avg(sal) from emp group by deptno ；

43. 查询工资为某个部门平均工资的员工信息。

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

44. 查询工资高于本部门平均工资的员工的信息
      先要求出各部门的平均工资，结果存到一张临时表。怎么用这个临时表呢？既不能出现在from子句中，也不能出现在where子句中，那就join on。

select emp.* from emp 
       join (select deptno,avg(sal) avg_sal from emp group by deptno) t 
       on emp.deptno=t.deptno
       where emp.sal>t.avg_sal;

45. 查询工资高于本部门平均工资的员工的信息及其部门的平均工资

select emp.*,avg_sal from emp 
	join (select deptno,avg(sal) avg_sal from emp group by deptno) t 
	on emp.deptno=t.deptno and sal>avg_sal ;

46. 查询工资高于20号部门某个员工工资的员工的信息
      any，表示与一个集合中的某个条件进行判断即可。

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

47. 统计各个工种的人数与平均工资

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

48. 统计每个部门中各个工种的人数与平均工资。

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

49. 查询工资、奖金与10 号部门某个员工工资、奖金都相同的员工的信息
      包含null值的列，要进行数值比较时，需要用到nvl方法（Oracle中特有）。
      容易忽略的条件是需要在最后判断一下部门号不为10。

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

50. 查询部门人数大于5的部门的员工的信息
      此处要和使用聚合函数后计算出来的结果进行判断，显然这是where做不到，所以用了having

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

51. 查询所有员工工资都大于1000的部门的信息
      两种思路：下面的简单些：即先找出最低公司都>1000的部门，再求出这个部门的信息。

 select * from dept where deptno in (
        select distinct deptno from emp 
               where deptno not in 
                     (select distinct deptno from emp where sal < 1000)) ;
                     
 select * from dept where deptno in (
        select distinct deptno from emp  group by deptno having min(sal) >1000) ;

52. 查询所有员工工资都大于1000的部门的信息及其员工信息【难】
      第二种思路比较简单，在上个问题的基础上，将两个表关联即可。

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;

select * from dept d left  join emp e 
	on d.deptno=e.deptno
    where e.deptno in(
    	select deptno from emp  group by deptno having min(sal)>1000);

53. 查询所有员工工资都在900~3000之间的部门的信息
      第一种思路比较简单，第二种是反向排除。

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

select * from dept 
	where deptno in (select distinct deptno from emp 
		where deptno not in (select distinct deptno from emp 
			where sal not between 900 and 3000)) ;

54. 查询所有工资都在900~3000之间的员工所在部门的员工信息
      和上一题思路类似，不过从emp表中查询数据即可。

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

select * from emp
	where deptno in (select distinct deptno from emp 
		where deptno not in (select distinct deptno from emp 
			where sal not between 900 and 3000)) ;

55. 查询每个员工的领导所在部门的信息【难】
      先让emp表和自身关联，关联时，取出需要显示的字段，比如员工姓名、编号和领导姓名、编号。然后再让这张临时表和dept表关联。

select * from 
       (select e1.empno,e1.ename,e1.mgr mno, e2.ename mname,e2.deptno 
               from emp e1 join emp e2 on e1.mgr = e2.empno) t 
       join dept on t.deptno = dept.deptno ;

56. 查询人数最多的部门信息【难，放放】

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)));

57. 查询30号部门中工资排序前3名的员工信息【难】
      Oracle中的分页要用rownum，但是rownum不能直接使用，需要先对数据进行排序，然后再嵌套使用（即要先用嵌套语句,把 rownum 生成，再使用）。

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

58. 查询所有员工中工资排在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 ) ;

59. 向emp表中插入一条记录，员工号为1357，员工名字为oracle，工资为2050元，部门号为20，入职日期为2002年5月10日

insert into emp(empno,ename,sal,deptno,hiredate) values (1357,'oracle',2050,20,to_date('2002年5月10日','yyyy"年"mm"月"dd"日"')) ;

60. 将各部门员工的工资修改为该员工所在部门平均工资加1000【难】
      先算出一个加过1000的临时表，然后将该表中的sal赋给原始表，部门编号为判断条件。

update emp b set sal=(
	select sal from (select deptno,avg(sal)+1000 sal from emp group by deptno) a 
		where a.deptno=b.deptno);

61. 查询82年员工

select emp.* from emp  where to_char(emp.hiredate, 'yy') like '82';
select emp.* from emp  where to_char(emp.hiredate,'yyyy')='1982';

62. 查询32年工龄的人员

 select round(sysdate-e.hiredate)/365, e.ename,e.hiredate 
 	from emp e where round((sysdate-e.hiredate)/365)=32;

63. 显示员工雇佣期 6 个月后下一个星期一的日期

 select next_day(add_months(e.hiredate,6),2) from emp e ;

64. 找没有上级的员工，把mgr的字段信息输出为 “boss”

select decode(e.mgr,null,'boss','中国好声音') from emp e;

65. 为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%【难】

  select decode(e.deptno,10,e.sal*1.1,20,e.sal*1.15, e.sal*1.18) 涨工资 ,e.deptno, e.sal 
  		from emp e ;

66. 求部门中薪水最高的人
      先把部门编号和部门内最高工资查出来，存在一张临时表中，然后再关联查询。

 select ename,sal,emp.deptno from emp 
join (select deptno,max(sal) max_sal from emp group by deptno) t 
on (emp.deptno = t.deptno and emp.sal = t.max_sal); 

欢迎点赞、收藏、评论加关注，比心