oracle經典sql練習題

/*1、選擇在部門30中員工的所有資訊*/  
select * from scott.emp where deptno = '30'  
/*2、列出職位為（MANAGER）的員工的編號，姓名 */  
select empno, ename from scott.emp where job = 'MANAGER'  
/*3、找出獎金高於工資的員工*/  
select * from scott.emp where comm > sal   
/*4、找出每個員工獎金和工資的總和 */  
select ename, sal + nvl(comm, 0) from scott.emp   
/*5、找出部門10中的經理(MANAGER)和部門20中的普通員工(CLERK) */  
select *   
  from scott.emp   
 where deptno = '10'  
   and job = 'MANAGER'  
union   
select *   
  from scott.emp   
 where job = 'CLERK'  
   and deptno = '20'  
/*6、找出部門10中既不是經理也不是普通員工，而且工資大於等於2000的員工 */  
select *   
  from scott.emp   
 where job != 'MANAGER'  
   and job != 'CLERK'  
   and sal > 2000  
/*7、找出有獎金的員工的不同工作 */  
select distinct(job) from scott.emp where comm is not null  
/*8、找出沒有獎金或者獎金低於500的員工*/    
select *   
  from scott.emp   
 where comm is not null  
   and comm > 500  
/*9、顯示僱員姓名，根據其服務年限，將最老的僱員排在最前面 */  
select ename   
  from scott.emp   
 order by (months_between(sysdate, hiredate) / 12) desc   
    
 select ename,hiredate from scott.emp order by hiredate   
/*10、找出每個月倒數第三天受雇的員工*/  
select * from scott.emp where hiredate = last_day(hiredate) - 2  
/*11、分別用case和decode函數列出員工所在的部門，deptno=10顯示'部門10',   
 deptno=20顯示'部門20'   
 deptno=30顯示'部門30'   
 deptno=40顯示'部門40'   
 否則為'其他部門'*/  
 select ename,   
        case deptno   
          when 10 then   
           '部門10'  
          when 20 then   
           '部門20'  
          when 30 then   
           '部門30'  
          when 40 then   
           '部門40'  
          else  
           '其他部門'  
        end 工資情況   
   from scott.emp   
    
 select ename,   
        decode(deptno,   
               10,   
               '部門10',   
               20,   
               '部門20',   
               30,   
               '部門30',   
               40,   
               '部門40',   
               '其他部門') 工資情況   
   from scott.emp   
/*12、分組統計各部門下工資>500的員工的平均工資*/  
select avg(sal) from scott.emp where sal > 500 group by deptno   
/*13、統計各部門下平均工資大於500的部門*/  
select deptno from scott.emp group by deptno having avg(sal) > 500  
/*14、算出部門30中得到最多獎金的員工獎金 */  
select max(comm) from scott.emp where deptno = 30  
/*15、算出部門30中得到最多獎金的員工姓名*/  
select ename   
  from scott.emp   
 where deptno = 30  
   and comm = (select max(comm) from scott.emp where deptno = 30)   
/*16、算出每個職位的員工數和最低工資*/  
select count(ename), min(sal), job from scott.emp group by job   
/*17、列出員工表中每個部門的員工數，和部門no */  
select count(ename), deptno from scott.emp group by deptno   
/*18、得到工資大於自己部門平均工資的員工資訊*/  
select *   
  from scott.emp e   
 where sal > (select avg(sal) from scott.emp where e.deptno = deptno)   
    
  select *   
    from scott.emp e1,   
         (select avg(sal) sals, deptno from scott.emp group by deptno) e2   
   where sal > sals   
     and e1.deptno = e2.deptno   
/*19、分組統計每個部門下，每種職位的平均獎金（也要算沒獎金的人）和總工資(包括獎金) */  
select avg(nvl(comm,0)), sum(sal + nvl(comm, 0))   
  from scott.emp   
 group by deptno,job   
/*20、笛卡爾集*/  
select * from scott.emp, scott.dept   
/*21、顯示員工ID,名字，直屬主管ID，名字*/  
select empno,   
       ename,   
       mgr,   
       (select ename from scott.emp e1 where e1.empno = e2.mgr) 直屬主管名字   
  from scott.emp e2   
/*22、DEPT表按照部門跟EMP表左關聯*/  
select *   
  from scott.dept, scott.emp   
 where scott.dept.deptno = scott.emp.deptno(+)   
/*23、使用此語句重複的內容不再顯示了*/  
select distinct (job) from scott.emp   
/*24、重複的內容依然顯示 */  
select *   
  from scott.emp   
UNION ALL   
select * from scott.emp   
/*23和24題和22題是一樣的 */  
  
/*25、只顯示了兩個表中彼此重複的記錄。*/  
select *   
  from scott.dept, scott.emp   
 where scott.dept.deptno(+) = scott.emp.deptno   
/*26、只顯示了兩張表中的不同記錄*/  
select * from scott.emp union select * from scott.emp   
minus    
(select * from scott.emp intersect select * from scott.emp)   
  
(select * from scott.emp minus select * from scott.emp)   
union    
(select * from scott.emp minus select * from scott.emp)   
   表結構相同  先union 只能有 -    
/*27、列出員工表中每個部門的員工數，和部門no */  
select count(ename), deptno from scott.emp group by deptno   
/*28、列出員工表中每個部門的員工數（員工數必須大於3），和部門名稱*/    
select count(deptno),   
       deptno,   
       (select dname from scott.dept where scott.dept.deptno = e1.deptno)   
  from scott.emp e1   
 group by deptno having count(deptno)>3  
/*29、找出工資比jones多的員工*/  
select *   
  from scott.emp   
 where sal > (select sal from scott.emp where ename = 'JONES')   
/*30、列出所有員工的姓名和其上級的姓名 */  
select ename,   
       (select ename from scott.emp e1 where e1.empno = e2.mgr) 上級的姓名   
  from scott.emp e2   
/*31、以職位分組，找出平均工資最高的兩種職位 */  
select job   
  from scott.emp   
 group by job   
having avg(sal) in (select max(sal) from scott.emp group by job )   
  
select job   
  from (select job, avg(sal)   
          from scott.emp   
         group by job   
         order by avg(sal) desc)   
 where rownum <= 2  
    
 最大的：   
 select max(max_sal)   
   from (select job, avg(sal) max_sal from scott.emp group by job)   
/*32、尋找出不在部門20，且比部門20中任何一個人工資都高的員工姓名、部門名稱*/  
  
select ename, dname   
  from scott.emp e1, scott.dept e2   
 where e1.deptno = e2.deptno   
   and e1.deptno <> 20  
   and sal > (select max(sal) from scott.emp where deptno = '20')   
              
/*33、得到平均工資大於2000的工作職種 */  
select job from scott.emp group by job having avg(sal) > 2000  
/*34、分部門得到工資大於2000的所有員工的平均工資，並且平均工資還要大於2500 */  
select avg(sal)   
  from scott.emp   
 where sal > 2000  
 group by deptno   
having avg(sal) > 2500  
/*35、得到每個月工資總數最少的那個部門的部門編號，部門名稱，部門位置 */  
select deptno, dname, loc   
  from scott.dept   
 where deptno in (select deptno   
                   from scott.emp   
                  group by deptno   
                 having sum(sal) = (select min(sum(sal))   
                                     from scott.emp   
                                    group by deptno))   
  
select * from scott.dept   
/*36、分部門得到平均工資等級為2級（等級表）的部門編號 */  
select deptno   
  from scott.emp   
 group by deptno   
having avg(sal) between (select losal from scott.salgrade where grade = 2) and (select hisal   
                                                                                  from scott.salgrade   
                                                                               where grade = 2)   
                                                                                  
select avg(sal) from scott.emp group by deptno   
select * from scott.salgrade   
/*37、尋找出部門10和部門20中，工資最高第3名到工資第5名的員工的員工名字，部門名字，部門位置*/  
select a.ename, dname, loc   
  from (select *   
          from (select rownum rn, deptno, empno, sal, ename   
                  from (select deptno, empno, sal, ename   
                          from scott.emp   
                         where deptno in (10, 20)   
                           and rownum <= 5  
                         order by sal desc))   
         where rn between 3 and 5) a,   
       scott.dept b   
 where a.deptno = b.deptno   
              
              
select deptno, ename   
  from (select empno, deptno, ename   
          from (select rownum rn, deptno, empno, sal, ename   
                  from (select deptno, empno, sal, ename   
                          from scott.emp   
                         where deptno in (10, 20)   
                         order by sal desc))   
         where rn between 3 and 5)   
     
  
/*38、尋找出收入（工資加上獎金），下級比自己上級還高的員工編號，員工名字，員工收入*/  
select empno, ename, sal + nvl(comm, 0)   
  from scott.emp e1   
 where sal + nvl(comm, 0) >   
       (select sal + nvl(comm, 0) from scott.emp where empno = e1.mgr)   
  
select * from scott.emp   
select ename, sal + nvl(comm, 0) from scott.emp   
/*39、尋找出職位和'MARTIN' 或者'SMITH'一樣的員工的平均工資 */  
select avg(sal)   
  from scott.emp   
 where job in (select job   
                 from scott.emp   
                where ename = 'MARTIN'  
                   or ename = 'SMITH')   
/*40、尋找出不屬於任何部門的員工 */  
select * from scott.emp where deptno  is null  
select * from scott.emp where deptno not in (select deptno from scott.emp)   
/*41、按部門統計員工數，查處員工數最多的部門的第二名到第五名（列出部門名字，部門位置）*/  
select dname, loc   
  from (select *   
          from (select rownum rn, deptno   
                  from (select deptno, count(*)   
                          from scott.emp   
                         group by deptno   
                         order by count(*) desc))   
         where rn between 2 and 5) a,   
       scott.dept b   
 where a.deptno = b.deptno   
     
  select count(*) from scott.emp group by deptno   
/*42、查詢出king所在部門的部門號\部門名稱\部門人數 （多種方法）*/  
select sc.deptno, dname, count(*)   
  from scott.emp sc, scott.dept de   
 where sc.deptno = ((select deptno from scott.emp where ename = 'KING'))   
   and de.deptno = sc.deptno   
 group by sc.deptno, dname   
  
  
/*43、查詢出king所在部門的工作年限最大的員工名字*/    
select *   
  from scott.emp   
 where hiredate =   
       (select min(hiredate)   
          from scott.emp   
         where deptno in (select deptno from scott.emp where ename = 'KING'))   
   and deptno = (select deptno from scott.emp where ename = 'KING')   
/*44、查詢出工資成本最高的部門的部門號和部門名稱 */  
select deptno, dname   
  from scott.dept   
 where deptno = (select deptno   
                   from scott.emp   
                  group by deptno   
                 having sum(sal) = (select max(sum(sal))   
                                     from scott.emp   
                                    group by deptno))   
  
select * from scott.emp for update  

  轉載請標明出處 http://blog.csdn.net/shimiso 

技術交流群:173711587