Ò»¡¢ÔÚ²»ÖªµÀ²¿ÃÅ“SALES”µÄ²¿ÃűàºÅµÄÇé¿öÏ£¬²é³ö´Ë²¿ÃŵÄËùÓÐÔ±¹¤ÐÕÃû¡£
select e.ename
from emp e
where e.deptno=(select deptno from dept where dname='SALES');
2¡¢²éѯ³öÔÂн¸ßÓÚ¹«Ë¾Æ½¾ùÔÂнµÄËùÓÐÔ±¹¤±àºÅ£¬ÐÕÃû£¬ËùÓв¿ÃűàºÅ£¬²¿ÃÅÃû³Æ£¬Éϼ¶Áìµ¼Ãû£¬ÒÔ¼°
ËûµÄ¹¤×ʵȼ¶¡£
SELECT
e.empno,e.ename,e.deptno,d.deptno,d.dname,e1.ename,s.grade
from emp e,emp e1,dept d,salgrade s
where e.mgr=e1.empno(+) and e.deptno=d.deptno and e.sal between s.losal and s.hisal and
e.sal>(select avg(sal) from emp);
3¡¢²éѯ³öÓë"SCOTT"´ÓÊÂͬһ¹¤×÷µÄËùÓÐÔ±¹¤¼°²¿ÃÅÃû³Æ¡£(²»°üÀ¨scott,ÓÃ<>Ò²¿ÉÒÔ)
select e.ename,d.dname from emp e,dept d where job=(select job from emp where
ename='SCOTT') and e.deptno=d.deptno and e.ename!='SCOTT';
4¡¢²éѯ³öÔÂнµÈÓÚ²¿ÃÅ30ÖÐÔ±¹¤µÄÔÂнµÄËùÓÐÔ±¹¤ÐÕÃûºÍÔÂн¡£
select e.ename,e.sal from emp e where e.sal in (select sal from emp where deptno=30) and deptno!=30;
5¡¢²é³öÔÂн¸ßÓÚ²¿ÃÅ30¹¤×÷µÄËùÓÐÔ±¹¤ÔÂнµÄ£¬Ô±¹¤ÐÕÃû£¬²¿ÃÅÃû¡£
All(²éѯÓï¾ä):>×î´óÖµ£¬<×îСֵ
in() ÔÙÕâ¸ö·¶Î§ÄÚÓÐûÓÐ
any()±ÈÀïÃæ×îСµÄ´ó£¬±È×î´óµÄС£»
SELECT
e.ename,d.dname
from
emp e,dept d
where e.sal>(select max(sal) from emp where deptno=30) and e.deptno=d.deptno and e.deptno<>30;
6¡¢²éѯ³öÿ¸ö²¿ÃÅÃû£¬Ô±¹¤×ÜÊý£¬Æ½¾ù¹¤×Ê£¬Æ½¾ù·þÎñÄêÏÞ¡£
month_between(sysdate,e.hiredate)/12
//Á½¸öʱ¼ä²î¶àÉÙ¸öÔÂ/12ÊDzî¶àÉÙÄê
select d.dname,count(e.empno),avg(e.sal),avg(months_between(sysdate,e.hiredate)/12)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.dname;
delete ɾ³ýÒ»ÕÅ´ó±íʱ¿Õ¼ä²»ÊÍ·Å£¬·Ç³£ÂýÊÇÒòΪռÓôóÁ¿µÄϵͳ×ÊÔ´£¬Ö§³Ö»ØÍ˲Ù×÷£¬¿Õ¼ä»¹±»ÕâÕűíÕ¼ÓÃ×Å¡£
truncate table ±íÃû (ɾ³ý±íÖмǼʱÊͷűí¿Õ¼ä)
DML Óï¾ä£º
±í¼¶¹²ÏíËø£º ¶ÔÓÚ²Ù×÷Ò»ÕűíÖеIJ»Í¬¼Ç¼ʱ£¬»¥²»Ó°Ïì
Ðм¶ÅÅËüËø£º¶ÔÓÚÒ»ÐмǼ£¬oracle »áÖ»ÔÊÐíÖ»ÓÐÒ»¸öÓû§¶ÔËüÔÚͬһʱ¼ä½øÐÐÐ޸IJÙ×÷ ......