oracleÁ·Ï°Ì⣨mldnÊÓƵ£©Ò»

1,Áгö×îµÍн½ð´óÓÚ1500µÄ¸÷ÖÖ¹¤×÷¼°´ÓÊ´˹¤×÷µÄÈ«²¿¹ÍÔ±ÈËÊý¡£
select job,count(empno) from emp
where job in(select job from emp group by job having min(sal)>1500)
group by job;
2£¬Áгöн½ð¸ßÓÚ¹«Ë¾Æ½¾ùн½ðµÄËùÓÐÔ±¹¤ËùÔÚ²¿ÃÅ£¬Éϼ¶Áìµ¼£¬¹«Ë¾µÄ¹¤×ʵȼ¶¡£
select e.ename,d.dname,m.ename,e.sal,sa.grade
from emp e,emp m,dept d,(select avg(sal) avg from emp) ed,salgrade sa
where e.mgr=m.empno(+) and e.deptno=d.deptno and e.sal >ed.avg and (e.sal between losal and hisal);
3£¬ÁгöÓëscott´ÓÊÂÏàͬ¹¤×÷µÄËùÓÐÔ±¹¤¼°²¿ÃÅÃû³Æ¡£
select e.ename,d.dname from emp e,dept d,(select job from emp where ename='SCOTT') ed
where e.job=ed.job and e.deptno=d.deptno and ename!='SCOTT';
4,ÁгöËùÓв¿ÃŵÄÏêϸÐÅÏ¢ºÍ²¿ÃÅÈËÊý¡£
select d.*,nvl(ed.cou,0)
from dept d,(select deptno,count(empno) cou from emp group by deptno) ed
where d.deptno=ed.deptno(+);
5,Áгö¸÷ÖÖ¹¤×÷µÄ×îµÍ¹¤×ʼ°´ÓÊ´˹¤×÷µÄ¹ÍÔ±ÐÕÃû(δÄܽ«Ïàͬ¹¤×ʵÄÈËÅųý£©¡£
select e.ename,e.job,e.sal
from emp e,(select min(sal) min from emp group by job) ed
where e.sal=ed.min;
6,Áгö¸÷¸ö²¿ÃŵÄmanager£¨¾­Àí£©µÄ×îµÍн½ð(²éѯ³öµÄÊǸ÷¸ö²¿Ãŵľ­ÀíµÄ¹¤×Ê£¬ÒòΪÿ¸ö²¿ÃÅÖ»ÓÐÒ»¸ö¾­Àí)¡£
select deptno,min(sal) from emp where job='MANAGER' group by deptno;
7,ÁгöËùÓÐÔ±¹¤µÄÄ깤×Ê£¬°´Äêн´ÓµÍµ½¸ßµÄ˳ÐòÅÅÐò¡£
select ename,((sal+nvl(comm,0))*12) sa from emp order by sa asc;
8,²é³öÿ¸öÔ±¹¤µÄÉϼ¶Ö÷¹Ü£¬²¢ÒªÇó³öÕâЩÖ÷¹ÜÖеÄнˮ³¬¹ý3000µÄ¡£
select distinct m.ename,m.sal
from emp e,emp m
where e.mgr=m.empno and m.sal>3000;
9,Çó³ö²¿ÃÅÃû³ÆÖдø'S'×Ö·ûµÄ²¿ÃÅÔ±¹¤µÄ¹¤×ʺϼƣ¬²¿ÃÅÈËÊý¡£
select distinct e.deptno,d.dname,ed.sum,ed.cou
from emp e,dept d,
(select deptno,sum(sal) sum,count(empno) cou from emp group by deptno) ed
where d.dname like '%S%' and e.deptno=ed.deptno and e.deptno=d.deptno;
10,¸øÈÎÖ°ÈÕÆÚ³¬¹ý29ÄêµÄÈ˼Óн10%¡£
update emp set sal=sal*1.1 where (sysdate-hiredate)/365>29;(¸üÐÂÊý¾Ý)
select ename,sal*(1+0.1) salh from emp where (sysdate-hiredate)/365>29;£¨½ö½öÊDzéѯ¼ÓнºóÊý¾Ý£©
11,ÁгöÔÚ²¿ÃÅ"SALES"(ÏúÊÛ²¿)¹¤×÷µÄÔ±¹¤µ