写在前面的
有些题目是要的*,即所有信息,但是我为了理解方便,有时候会简化,比如用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示意图:
.
看完如果觉得不错,留个赞再走。
.