根據(jù)Oracle數(shù)據(jù)庫scott模式下的emp表和dept表

1、題目要求：根據(jù) Oracle 數(shù)據(jù)庫 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) ;查詢各月倒數(shù)第 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 = ; 查詢?nèi)肼毴掌谠缬谄渲苯由霞夘I(lǐng)導(dǎo)的所有員工信息。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) ; 查詢每個部門中的員工數(shù)量、平均工資和平均工作年限。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 <> ; 查詢各個部門的詳細信息以及部門人數(shù)、部門平均工資。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 號部門員工以及領(lǐng)導(dǎo)的信息。select * from emp where empno in (select mgr from emp where deptno=10)

16、 or deptno = 10 查詢各個部門的人數(shù)及平均工資。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); 統(tǒng)計各個工種的人數(shù)與平均工資。select job,

18、count(*),avg(sal) from emp group by job ; 統(tǒng)計每個部門中各個工種的人數(shù)與平均工資。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;查詢部門人數(shù)大于 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) ; 查詢每個員工的領(lǐng)導(dǎo)所在部門的信息。select * from (select , mno, mname, from emp e1 join emp e2 on = t join dept on = ;

22、 查詢?nèi)藬?shù)最多的部門信息。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