根据Oracle数据库scott模式下的emp表和dept表

1、题目要求：根据 Oracle 数据库 scott 模式下的 emp 表和 dept 表，完成下列操作。 （ 1 ） 查询 20 号部门的所有员工信息。select * from emp where deptno = 20;(2)查询所有工种为 CLERK勺员工的工号、员工名和部门名。select empno,ename,deptno from emp where job like 'CLERK'查询奖金(COMM )高于工资(SAL)的员工信息。select * from emp where comm > sal;查询奖金高于工资的20%的员工信息。select * fr

2、om emp where comm > (sal*;查询10号部门中工种为 MANAGER和20号部门中工种为 CLERK的员工的信息。select * from empwhere (deptno = 10 and job like 'MANAGER') or (deptno = 20 and job like 'CLERK');查询所有工种不是 MANAGER和CLERK且工资大于或等于2000的员工的详细信息。select * from empwhere job not in ('MANAGER','CLERK') an

3、d sal >= 2000 ;查询有奖金的员工的不同工种。select distinct job from emp where comm is not null;查询所有员工工资和奖金的和。select ename,(sal+nvl(comm,0) salcomm from emp; 查询没有奖金或奖金低于 100 的员工信息。select * from emp where (comm is null or comm < 100) ;查询各月倒数第 2 天入职的员工信息。select * from emp where hiredate in (select (last_day(hi

4、redate)-1) from emp); 查询员工工龄大于或等于 10年的员工信息。select * from emp where (sysdate - hiredate)/365 >= 10 ; 查询员工信息，要求以首字母大写的方式显示所有员工的姓名。select upper(substr(ename,1,1) | lower(substr(ename,2,length(ename)-1) from emp; 查询员工名正好为 6 个字符的员工的信息。select * from emp where length ( ename ) = 6 ;查询员工名字中不包含字母“S'员工

5、。select * from emp where ename not in (select ename from emp where ename like '%S%') ;select * from emp where ename not like%S%'查询员工姓名的第 2个字母为“ M ”的员工信息。select * from emp where ename like '_M%'查询所有员工姓名的前 3 个字符。select substr(ename,1,3) from emp ;查询所有员工的姓名，如果包含字母“S”，则用“ S'替换。se

6、lect replace(ename,'s','S') from emp ; 查询员工的姓名和入职日期，并按入职日期从先到后进行排列。Select ename,hiredate from emp order by hiredate aSc ; 显示所有的姓名、工种、工资和奖金，按工种降序排列，若工种相同则按工资升序排列。Select ename,job,Sal,comm from emp order by job deSc,Sal aSc ;显示所有员工的姓名、 入职的年份和月份， 若入职日期所在的月份排序， 若月份相同则按入职的 年份排序。select ena

7、me,to_char(hiredate,'yyyy')|'-'|to_char(hiredate,'mm') from emp order by to_char(hiredate,'mm'),to_char(hiredate,'yyyy');查询在 2 月份入职的所有员工信息。select * from emp where to_char(hiredate,'mm') = 2 ; 查询所有员工入职以来的工作期限，用“ *年*月* 日”的形式表示。select ename,floor(sysdate-

8、hiredate)/365)|' 年 '|floor(mod(sysdate-hiredate),365)/30)|' 月 '|cell(mod(mod(sysdate-hiredate),365),30)|' 天 ' from emp ;查询至少有一个员工的部门信息。select * from dept where deptno in (select distinct deptno from emp where mgr is not null) ; 查询工资比 SMITH 员工工资高的所有员工信息。select * from emp where

9、 sal > (select sal from emp where ename like 'SMITH') ; 查询所有员工的姓名及其直接上级的姓名。select staname,ename supname from (select ename staname,mgr from emp) t join emp on = ; 查询入职日期早于其直接上级领导的所有员工信息。select * from emp where empno in (select staempno from (select empno staempno,hiredate stahiredate,mgr

10、from emp) t join emp on = and stahiredate < hiredate) ;查询所有部门及其员工信息，包括那些没有员工的部门。select * from dept left join emp on = order by;查询所有员工及其部门信息，包括那些还不属于任何部门的员工。查询所有工种为 CLERK勺员工的姓名及其部门名称。select ename,dname from emp join dept on job like 'CLERK' and =;查询最低工资大于 2500 的各种工作。select job from (select

11、 min(sal) min_sal,job from emp group by job) where min_sal > 2500 ; 查询最低工资低于 2000 的部门及其员工信息。select * from emp where deptno in (select deptno from (select min(sal) min_sal,deptno from emp group by deptno) where min_sal < '2000') ;查询在SALES部门工作的员工的姓名信息。select ename from emp where deptno =

12、 (select deptno from dept where dname like 'SALES'); 查询工资高于公司平均工资的所有员工信息。select * from emp where sal > (select avg(sal) from emp) ;查询与 SMITH 员工从事相同工作的所有员工信息。select * from emp where job in (select job from emp where ename like 'SMITH') and ename not like 'SMITH' ;列出工资等于 30

13、号部门中某个员工工资的所有员工的姓名和工资。select ename,sal from emp where sal =any (select sal from emp where deptno = 30) ;查询工资高于 30 号部门中工作的所有员工的工资的员工姓名和工资。select ename,sal from emp where sal >all (select sal from emp where deptno = 30) ; 查询每个部门中的员工数量、平均工资和平均工作年限。select dname,count,avg_sal,avg_date from dept join (

14、select count(*) count,avg(sal) avg_sal,avg(sysdate-hiredate)/365) avg_date,deptno from emp group by deptno) t on = ; 查询从事同一种工作但不属于同一部门的员工信息。select distinct , from emp t1 join emp t2 on like and <> ; 查询各个部门的详细信息以及部门人数、部门平均工资。Select dept.*,person_num,avg_sal from dept,(select count(*) person_num

15、,avg(sal) avg_sal,deptno from emp group by deptno) t where= ;查询各种工作的最低工资。select job,min(sal) from emp group by job ; 查询各个部门中的不同工种的最高工资。select max(sal),job,deptno from emp group by deptno,job order by deptno,job 查询 10 号部门员工以及领导的信息。select * from emp where empno in (select mgr from emp where deptno=10)

16、 or deptno = 10 查询各个部门的人数及平均工资。select deptno,count(*),avg(sal) from emp group by deptno ； 查询工资为某个部门平均工资的员工信息。select * from emp where sal in (select avg(sal) avg_sal from emp group by deptno) ; 查询工资高于本部门平均工资的员工的信息。select emp.* from emp join (select deptno,avg(sal) avg_sal from emp group by deptno) t

17、on = and sal>avg_sal ;查询工资高于本部门平均工资的员工的信息及其部门的平均工资。select emp.*,avg_sal from emp join (select deptno,avg(sal) avg_sal from emp group by deptno) t on = and sal>avg_sal ;查询工资高于 20 号部门某个员工工资的员工的信息。select * from emp where sal >any(select sal from emp where deptno=20); 统计各个工种的人数与平均工资。select job,

18、count(*),avg(sal) from emp group by job ; 统计每个部门中各个工种的人数与平均工资。select deptno,job,count(*),avg(sal) from emp group by deptno,job order by deptno,job; 查询工资、奖金与 10 号部门某个员工工资、奖金都相同的员工的信息。select emp.* from emp join (select sal,comm from emp where deptno = 10) t on = and nvl,0)=nvl,0) and != 10;查询部门人数大于 5

19、的部门的员工的信息。select * from emp where deptno in (select deptno from emp group by deptno having count(*)>5); 查询所有员工工资都大于 1000 的部门的信息。select * from dept where deptno in (select distinct deptno distinct deptno from emp where sal < 1000) ;查询所有员工工资都大于 1000 的部门的信息及其员工信息。fromempwheredeptnonotin(selectsel

20、ect * from emp join dept on in (select distinct deptno distinct deptno from emp where sal < 1000) and =; 查询所有员工工资都在 9003000 之间的部门的信息。fromempwheredeptnonotin(selectselect * from dept where deptno in (select distinct deptnofromempwheredeptnonotin(selectdistinct deptno from emp where sal not between

21、 900 and 3000) ; 查询所有工资都在 9003000 之间的员工所在部门的员工信息。select * from emp where deptno in (select distinct deptno fromempwheredeptnonotin(selectdistinct deptno from emp where sal not between 900 and 3000) ; 查询每个员工的领导所在部门的信息。select * from (select , mno, mname, from emp e1 join emp e2 on = t join dept on = ;

22、 查询人数最多的部门信息。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);查询 30 号部门中工资排序前 3 名的员工信息。select * from emp where empno in (select empno from (select empno,sal from emp where deptno=30 order by sal desc) where rownum < 4) ;查询所有员工中工资排在 510 名之间的员工信息。select * from emp where empno in (select empno from (select empno,rownum num from (select empno,sal from emp order