Ê·ÉÏ×îÈ«OracleÊý¾Ý¿â»ù±¾²Ù×÷Á·Ï°Ìâ(º¬´ð°¸) ÏÂÔØ±¾ÎÄ

20.Áгö×îµÍнˮ´óÓÚ1500µÄ¸÷ÖÖ¹¤×÷¡£

select job from emp group by job having min(sal)>1500;

21.ÏÔʾËùÓÐÔ±¹¤µÄÐÕÃûºÍ¼ÓÈ빫˾µÄÄê·ÝºÍÔ·ݣ¬½«Ô±¹¤ÈëÖ°ÄêÔ´ӵ͵½¸ßÅÅÐò¡£

Select ename,to_char(hiredate,'yyyy') as year,to_char(hiredate,'mm') as months from emp order by months;

22.ÏÔʾÔÚÒ»¸öÔÂΪ30ÌìµÄÇé¿öÏÂËùÓÐÔ±¹¤µÄÈÕнˮ£¬È¡Õû¡£ select trunc((nvl(comm,0)+sal)/30,0) as ÈÕн from emp; ½âÊÍ:nvl£¨comm,0£©Èç¹ûÊÇnullÔòת»»Îª0£¬Èç¹û²»ÊÇnull£¬Õâ±£³ÖÔ­Öµ¡£

23.ÕÒ³öÔÚ£¨ÈκÎÄê·ÝµÄ£©2ÔÂÊÜÆ¸µÄËùÓÐÔ±¹¤¡£ select * from emp where to_char(hiredate,'mm')=2;

24.¶ÔÓÚÿ¸öÔ±¹¤£¬ÏÔʾÆä¼ÓÈ빫˾µÄÌìÊý¡£ select (sysdate-hiredate) as ÌìÊý from emp;

25.ÁгöÓÐÔ±¹¤µÄ²¿ÃÅ£¬Í¬Ê±ÁгöÄÇЩûÓÐÔ±¹¤µÄ²¿ÃÅ¡£

26.ÁгöÖÁÉÙÓÐÒ»¸öÔ±¹¤µÄËùÓв¿ÃÅ¡£

5

select deptno from emp group by deptno having count(empno)>1; 27.ÁгöËùÓÐÔ±¹¤µÄÔ±¹¤ÐÕÃû¡¢²¿ÃÅÃû³ÆºÍнˮ¡£ select ename,deptno,sal from emp;

28.Áгöнˮ±È¡°SMITH¡±¶àµÄËùÓÐÔ±¹¤¡£

select ename,sal from emp where sal>(select sal from emp where ename='SMITH');

29.ÁгöËùÓÐÔ±¹¤µÄÐÕÃû¼°ÆäÖ±½ÓÉϼ¶µÄÐÕÃû¡£

select t1.empno,t1.ename,t1.mgr,t2.ename from emp t1 left join emp t2 on t1.mgr=t2.empno;

30.ÁгöÊܹÍÈÕÆÚÔçÓÚÆäÖ±½ÓÉϼ¶µÄËùÓÐÔ±¹¤¡£

select t1.empno as Ô±¹¤±àºÅ,t1.ename as Ö°Ô±,t1.mgr as ¾­Àí±àºÅ,t2.ename as ¾­Àí,t1.hiredate as Ö°Ô±Èëְʱ¼ä,t2.hiredate as ¾­ÀíÈëְʱ¼ä from emp t1 left join emp t2 on t1.mgr=t2.empno where t2.hiredate>t1.hiredate;

31.Áгö²¿ÃÅÃû³ÆºÍÕâЩ²¿ÃŵÄÔ±¹¤ÐÅÏ¢£¬Í¬Ê±ÁгöûÓÐÔ±¹¤µÄ²¿ÃÅ¡£ select e.*,d.deptno,d.dname from dept d left join emp e on e.deptno=d.deptno;

6

32.ÁгöËùÓС°CLERK¡±£¨°ìÊÂÔ±£©µÄÐÕÃû¼°Æä²¿ÃÅÃû³Æ¡£ select

e.ename,d.dname

from

emp

e,dept

d

where

d.deptno=e.deptno and e.job='CLERK';

33.ÁгöÔÚ²¿ÃÅ¡°SALES¡±£¨ÏúÊÛ²¿£©¹¤×÷µÄÔ±¹¤µÄÐÕÃû£¬¼Ù¶¨²»ÖªµÀÏúÊÛ²¿µÄ²¿ÃűàºÅ¡£

select ename from emp where deptno=(select deptno from dept where dname='SALES');

34.Áгöнˮ¸ßÓÚ¹«Ë¾Æ½¾ùнˮµÄËùÓÐÔ±¹¤¡£

select ename,sal from emp where sal>(select avg(sal) from emp);

35.ÁгöÓë¡°SCOTT¡±´ÓÊÂÏàͬ¹¤×÷µÄËùÓÐÔ±¹¤¡£

select ename,job from emp where job=(select job from emp where ename='SCOTT');

36.ÁгöнˮµÈÓÚ²¿ÃÅ30ÖÐÔ±¹¤µÄнˮµÄËùÓÐÔ±¹¤µÄÐÕÃûºÍнˮ¡£ (¸öÈËÀí½âΪ²éѯÆäËü²¿ÃÅнˮ¸ÕºÃµÈÓÚ²¿ÃÅ30ÖÐÈκÎÒ»¸öÔ±¹¤µÄнˮ)

select ename,sal from emp where sal in (select sal from emp where deptno=30) and deptno!=30;

7

37.Áгöнˮ¸ßÓÚÔÚ²¿ÃÅ30¹¤×÷µÄËùÓÐÔ±¹¤µÄнˮµÄÔ±¹¤ÐÕÃûºÍнˮ¡£

select sal,ename from emp where sal>(select max(sal) from emp where deptno=30);

38.ÁгöÔÚÿ¸ö²¿Ãʤ×÷µÄÔ±¹¤ÊýÁ¿¡¢Æ½¾ù¹¤×ÊºÍÆ½¾ù·þÎñÆÚÏÞ¡£ select count(deptno) as ²¿ÃÅÔ±¹¤ÊýÁ¿,avg(sal) as ƽ¾ù¹¤×Ê,avg(sysdate-hiredate) as ƽ¾ù·þÎñÄêÏÞ from emp group by deptno;

39.Áгö´ÓÊÂͬһÖÖ¹¤×÷µ«ÊôÓÚ²»Í¬²¿ÃŵÄÔ±¹¤µÄÒ»ÖÖ×éºÏ¡£ select distinct t1.deptno,t1.job from emp t1,emp t2 where t1.deptno != t2.deptno and t1.job =t2.job order by t1.job;

40.ÁгöËùÓв¿ÃŵÄÏêϸÐÅÏ¢ºÍ²¿ÃÅÈËÊý¡£

select d.deptno,d.dname,d.loc,e.²¿ÃÅÈËÊý from dept d,(select count(deptno) as ²¿ÃÅÈËÊý,deptno from emp group by deptno) e where d.deptno=e.deptno;

41.Áгöнˮˮƽ´¦ÓÚµÚËÄλµÄÔ±¹¤¡£ select

ename,empno,sal,

ÅÅ

Ãû

from

(select

ename,empno,sal,dense_rank() over(order by sal desc) as ÅÅÃû

8