题目要求:
根据Oracle数据库scott模式下的emp表和dept表,完成下列操作。
(1)查询20号部门的所有员工信息。
select empno,ename,job,hiredate,sal from EMP where deptno=20;
(2)查询所有工种为CLERK的员工的工号、员工名和部门名。
select e.empno,e.ename,d.dname from EMP e left join dept d on e.deptno=d.deptno where e.job='CLERK';
(3)查询奖金(COMM)高于工资(SAL)的员工信息。
select * from emp where comm > sal;
(4)查询奖金高于工资的20%的员工信息。
select * from emp where comm > sal*0.2;
(5)查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。
--查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。
select * from emp where deptno=10 and job='MANAGER' UNION select * from emp where deptno=20 and job='CLERK'
(6)查询所有工种不是MANAGER和CLERK,且工资大于或等于2000的员工的详细信息。
--查询所有工种不是MANAGER和CLERK,且工资大于或等于2000的员工的详细信息。
select * from emp where job not in ('MANAGER','CLERK') and sal >= 2000
(7)查询有奖金的员工的不同工种。
--查询有奖金的员工的不同工种。
SELECT distinct job FROM EMP WHERE COMM != 0
(8)查询所有员工工资和奖金的和。
--查询所有员工工资和奖金的和。
select sum(comm)+sum(sal) as sum from emp
(9)查询没有奖金或奖金低于100的员工信息。
--查询没有奖金或奖金低于100的员工信息。
select * from emp where comm < 100 or comm is null;
(10)查询各月倒数第2天入职的员工信息。
Select * From emp Where Last_day(hiredate)-2=hiredate;
to_date('2020.06.10','yyyy.mm.dd')
(11)查询员工工龄大于或等于10年的员工信息。
--查询员工工龄大于或等于10年的员工信息。
select * from emp where extract( year from to_date('1992.06.10','yyyy.mm.dd') )- extract( year from hiredate ) > 10;
--查询员工工龄大于或等于10年的员工信息。
select * from emp where extract( year from sysdate)- extract( year from hiredate ) > 10;
(12) 查询员工信息,要求以首字母大写的方式显示所有员工的姓名。
select empno,INITCAP( SUBSTR(ename,1,1)) || LOWER(SUBSTR(ename,2,LENGTH(ename))) from emp;
(13)查询员工名正好为6个字符的员工的信息。
--查询员工名正好为6个字符的员工的信息。
select * from emp where LENGTH(ename)=6
(14)查询员工名字中不包含字母“S”员工。
-- 查询员工名字中不包含字母“S”员工。
select * from emp where instr(LOWER(ename),'s') = 0
(15)查询员工姓名的第2个字母为“M”的员工信息。
--substr是指 (截取字符串,开始位置,截取长度)
select * from emp where SUBSTR(ename,2,1)='M';
(16) 查询所有员工姓名的前3个字符。
select INITCAP(SUBSTR(ename,1,3)) from emp;
(17)查询所有员工的姓名,如果包含字母“s”,则用“S”替换。
update emp set ename = replace(ename,'s','S') where instr(ename,'s') != 0;
(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,extract( year from hiredate) as hireyear,extract( month from hiredate) as hiremonth from emp order by hiremonth asc, hireyear asc;
(21)查询在2月份入职的所有员工信息。
---- 查询在2月份入职的所有员工信息。
select * from emp where extract( month from hiredate) = 2
(22)查询所有员工入职以来的工作期限,用**年**月**日
的形式表示。
--年限 不知道怎么算
select hiredate, to_char(extract(day from numtodsinterval(sysdate - hiredate,'day')/ (365)) )||'年'||
to_char(mod(extract(day from numtodsinterval(sysdate - hiredate,'day')),12))||'月'||
to_char(extract(hour from numtodsinterval(sysdate - hiredate,'day')))||'小时'||
to_char(extract(minute from numtodsinterval(sysdate - hiredate,'day')))||'分钟'||
to_char(round(to_number (extract(second from numtodsinterval(sysdate - hiredate,'day')))))||'秒'
from emp;
(23)查询至少有一个员工的部门信息。
--查询至少有一个员工的部门信息。
select d.deptno as ddeptno,d.dname,count(e.empno) as number_of_dept from emp e left join dept d on e.deptno=d.deptno group by d.deptno,d.dname;
(24)查询工资比SMITH员工工资高的所有员工信息。
--查询工资比SMITH员工工资高的所有员工信息。
select * from emp where sal > (select sal from emp where ename='SMITH')
(25)查询所有员工的姓名及其直接上级的姓名。
--查询所有员工的姓名及其直接上级的姓名。
select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;
(26)查询入职日期早于其直接上级领导的所有员工信息。
--查询入职日期早于其直接上级领导的所有员工信息。
select e1.ename,e1.hiredate,e2.ename,e2.hiredate from emp e1 left join emp e2 on e1.mgr=e2.empno and e1.hiredate < e2.hiredate;
(27)查询所有部门及其员工信息,包括那些没有员工的部门。
--查询所有部门及其员工信息,包括那些没有员工的部门。
select * from dept d left join emp e on d.deptno=e.deptno;
(28)查询所有员工及其部门信息,包括那些还不属于任何部门的员工。
--查询所有员工及其部门信息,包括那些还不属于任何部门的员工。
select * from emp e left join dept d on e.deptno=d.deptno;
(29)查询所有工种为CLERK的员工的姓名及其部门名称。
--查询所有工种为CLERK的员工的姓名及其部门名称。
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno where e.job='CLERK';
(30)查询最低工资大于2500的各种工作。
--查询最低工资大于2500的各种工作。
select min(sal),job from emp group by job having min(sal) > 2500;
(31)查询最低工资低于2000的部门及其员工信息。
--查询最低工资低于2000的部门及其员工信息。
select * from emp where deptno in (select deptno from emp group by deptno having min(sal) < 1000);
select deptno,min(sal) from emp group by deptno;
(32)查询在SALES部门工作的员工的姓名信息。
-- 查询在SALES部门工作的员工的姓名信息。
select * from emp where deptno=( select DEPTNO from dept where dname='SALES' )
(33)查询工资高于公司平均工资的所有员工信息。
--查询工资高于公司平均工资的所有员工信息。
select * from emp where sal > (select avg(sal) from emp);
(34)查询与SMITH员工从事相同工作的所有员工信息。
-- 查询与SMITH员工从事相同工作的所有员工信息。
select * from emp where job = (select job from emp where ename='SMITH');
(35)列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。
--列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。
select ename,sal from emp where deptno=30;
(36)查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资。
--查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资。
select ename,sal from emp where sal > (select max(sal) from emp where deptno=30);
(37)查询每个部门中的员工数量、平均工资和平均工作年限。
--查询每个部门中的员工数量、平均工资和平均工作年限。
select deptno,count(empno) as number_of_emp,round(avg(sal),3) as avg_of_sal,((extract (year from sysdate)) - round(avg(extract( year from hiredate)),0)) as year_of_hireate from emp group by deptno;
(38)查询从事同一种工作但不属于同一部门的员工信息。
--查询从事同一种工作但不属于同一部门的员工信息。
select e1.empno,
e1.ename,
e1.job,
e1.mgr,
e1.hiredate,
e1.sal,
e1.comm,
e1.deptno from emp e1 left join emp e2 on e1.job=e2.job and e1.deptno!=e2.deptno
(39)查询各个部门的详细信息以及部门人数、部门平均工资。
--超帅气的sql 很棒!
select d.deptno,
d.dname,
m.number_dept,
m.avg_sal from dept d left join (select deptno,count(empno) as number_dept,round(avg(sal),2) as avg_sal from emp group by deptno) m
on d.deptno = m.deptno;
select deptno,count(empno),round(avg(sal),2) from emp group by deptno;
(40)查询各种工作的最低工资。
--查询各种工作的最低工资。
select job,min(sal) from emp group by job;
(41)查询各个部门中的不同工种的最高工资。
--查询各个部门中的不同工种的最高工资。
select deptno,job,max(sal) from emp group by deptno,job;
(42)查询10号部门员工以及领导的信息。
-- 查询10号部门员工以及领导的信息。
select * from emp e left join (select empno,mgr,ename,deptno from emp where deptno=10) m on m.mgr=e.empno;
(43)查询各个部门的人数及平均工资。
(44)查询工资为某个部门平均工资的员工信息。
(45)查询工资高于本部门平均工资的员工的信息。
(46)查询工资高于本部门平均工资的员工的信息及其部门的平均工资。
(47)查询工资高于20号部门某个员工工资的员工的信息。
(48)统计各个工种的人数与平均工资。
(49)统计每个部门中各个工种的人数与平均工资。
(50)查询工资、奖金与10 号部门某个员工工资、奖金都相同的员工的信息。
(51)查询部门人数大于5的部门的员工的信息。
(52)查询所有员工工资都大于1000的部门的信息。
(53)查询所有员工工资都大于1000的部门的信息及其员工信息。
(54)查询所有员工工资都在900~3000之间的部门的信息。
(55)查询所有工资都在900~3000之间的员工所在部门的员工信息。
(56)查询每个员工的领导所在部门的信息。
(57)查询人数最多的部门信息。
(58)查询30号部门中工资排序前3名的员工信息。
(59)查询所有员工中工资排在5~10名之间的员工信息。
(60)向emp表中插入一条记录,员工号为1357,员工名字为oracle,工资为2050元,部门号为20,入职日期为2002年5月10日。
(61)向emp表中插入一条记录,员工名字为FAN,员工号为8000,其他信息与SMITH员工的信息相同。
(62)将各部门员工的工资修改为该员工所在部门平均工资加1000。
1、查询82年员工
2、查询32年工龄的人员
3、显示员工雇佣期 6 个月后下一个星期一的日期
4、找没有上级的员工,把mgr的字段信息输出为 “boss”
5、为所有人长工资,标准是:10部门长10%;20部门长15%;30部门长20%其他部门长18%
Oracle_练习与答案
1.求部门中薪水最高的人
2.求部门平均薪水的等级
3.求部门平均的薪水等级
4.雇员中有哪些人是经理人
5.不准用组函数,求薪水的最高值
6.求平均薪水最高的部门的部门编号
组函数嵌套写法(对多可以嵌套一次,group by 只对内层函数有效)
求平均薪水最高的部门的部门名称
8.求平均薪水的等级最低的部门的部门名称
9.求部门经理人中平均薪水最低的部门名称
10.求比普通员工的最高薪水还要高的经理人名称(not in)
11.求薪水最高的前5名雇员
12.求薪水最高的第6到第10名雇(!important)
13.求最后入职的5名员工