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