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

1、題目要求：根據(jù) Oracle 數(shù)據(jù)庫 scott 模式下的 emp 表和 dept 表，完成下列操作。 （ 1 ） 查詢 20 號(hào)部門的所有員工信息。select * from emp where deptno = 20;(2)查詢所有工種為 CLERK勺員工的工號(hào)、員工名和部門名。select empno,ename,deptno from emp where job like 'CLERK'查詢獎(jiǎng)金(COMM )高于工資(SAL)的員工信息。select * from emp where comm > sal;查詢獎(jiǎng)金高于工資的20%的員工信息。select * fr

2、om emp where comm > (sal*;查詢10號(hào)部門中工種為 MANAGER和20號(hào)部門中工種為 CLERK的員工的信息。select * from empwhere (deptno = 10 and job like 'MANAGER') or (deptno = 20 and job like 'CLERK');查詢所有工種不是 MANAGER和CLERK且工資大于或等于2000的員工的詳細(xì)信息。select * from empwhere job not in ('MANAGER','CLERK') an

3、d sal >= 2000 ;查詢有獎(jiǎng)金的員工的不同工種。select distinct job from emp where comm is not null;查詢所有員工工資和獎(jiǎng)金的和。select ename,(sal+nvl(comm,0) salcomm from emp; 查詢沒有獎(jiǎng)金或獎(jiǎng)金低于 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 個(gè)字符的員工的信息。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個(gè)字母為“ M ”的員工信息。select * from emp where ename like '_M%'查詢所有員工姓名的前 3 個(gè)字符。select substr(ename,1,3) from emp ;查詢所有員工的姓名，如果包含字母“S”，則用“ S'替換。se

6、lect replace(ename,'s','S') from emp ; 查詢員工的姓名和入職日期，并按入職日期從先到后進(jìn)行排列。Select ename,hiredate from emp order by hiredate aSc ; 顯示所有的姓名、工種、工資和獎(jiǎng)金，按工種降序排列，若工種相同則按工資升序排列。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');查詢?cè)?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 ;查詢至少有一個(gè)員工的部門信息。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') ; 查詢所有員工的姓名及其直接上級(jí)的姓名。select staname,ename supname from (select ename staname,mgr from emp) t join emp on = ; 查詢?nèi)肼毴掌谠缬谄渲苯由霞?jí)領(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') ;查詢?cè)赟ALES部門工作的員工的姓名信息。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、號(hào)部門中某個(gè)員工工資的所有員工的姓名和工資。select ename,sal from emp where sal =any (select sal from emp where deptno = 30) ;查詢工資高于 30 號(hào)部門中工作的所有員工的工資的員工姓名和工資。select ename,sal from emp where sal >all (select sal from emp where deptno = 30) ; 查詢每個(gè)部門中的員工數(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 <> ; 查詢各個(gè)部門的詳細(xì)信息以及部門人數(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 ; 查詢各個(gè)部門中的不同工種的最高工資。select max(sal),job,deptno from emp group by deptno,job order by deptno,job 查詢 10 號(hào)部門員工以及領(lǐng)導(dǎo)的信息。select * from emp where empno in (select mgr from emp where deptno=10)

16、 or deptno = 10 查詢各個(gè)部門的人數(shù)及平均工資。select deptno,count(*),avg(sal) from emp group by deptno ； 查詢工資為某個(gè)部門平均工資的員工信息。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 號(hào)部門某個(gè)員工工資的員工的信息。select * from emp where sal >any(select sal from emp where deptno=20); 統(tǒng)計(jì)各個(gè)工種的人數(shù)與平均工資。select job,

18、count(*),avg(sal) from emp group by job ; 統(tǒng)計(jì)每個(gè)部門中各個(gè)工種的人數(shù)與平均工資。select deptno,job,count(*),avg(sal) from emp group by deptno,job order by deptno,job; 查詢工資、獎(jiǎng)金與 10 號(hào)部門某個(gè)員工工資、獎(jiǎng)金都相同的員工的信息。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) ; 查詢每個(gè)員工的領(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 號(hào)部門中工資排序前 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