Oracle ¶à±íÁ¬½Ó×Ó²éѯ
1.Çó²¿ÃÅÖÐÄÄЩÈËнˮ×î¸ß£º
select ename,sal
from emp join
(
select max(sal) max_sal, deptno
from emp
group by deptno
) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
2.Çó²¿ÃÅÆ½¾ùнˮµÄµÈ¼¶£º
select deptno, avg_sal, grade
from
(
select deptno, avg(sal) avg_sal
from emp
group by deptno
) t join salgrade s
on (t.avg_sal between s.losal and s.hisal);
3.Çó²¿ÃÅÆ½¾ùµÄнˮµÈ¼¶£º
select deptno, avg(grade)
from (
select deptno, ename, grade
from emp join salgrade s
on emp.sal between s.losal and s.hisal
) t
group by deptno;
4.ÇóÄÄЩÈËÊǾÀíÈË£º
select ename from emp where empno in (select distinct mgr from emp);
5.²»×¼Óþۼ¯º¯Êý£¬ÇóнˮµÄ×î¸ßÖµ£º
select distinct sal
from emp
where sal not in
(
select distinct e1.sal
from emp e1 join emp e2 on (e1.sal < e2.sal)
);
6.Ç󯽾ùнˮ×î¸ß²¿ÃŵIJ¿ÃűàºÅ£º
select deptno, avg_sal
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) where avg_sal = (
select max(avg_sal)
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
)
);
7.Ç󯽾ùнˮ×î¸ß²¿ÃŵIJ¿ÃÅÃû³Æ£º
select deptno,dname
from dept
where deptno = (
select deptno
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) where avg_sal = (
select max(avg_sal)
 
Ïà¹ØÎĵµ£º
dualÊÇÒ»¸öÐéÄâ±í£¬ÓÃÀ´¹¹³ÉselectµÄÓï·¨¹æÔò£¬oracle±£Ö¤dualÀïÃæÓÀÔ¶Ö»ÓÐÒ»Ìõ¼Ç¼¡£ÎÒÃÇ¿ÉÒÔÓÃËüÀ´×öºÜ¶àÊÂÇ飬ÈçÏ£º
1¡¢²é¿´µ±Ç°Óû§£¬¿ÉÒÔÔÚ SQL PlusÖÐÖ´ÐÐÏÂÃæÓï¾ä select user from dual;
2¡¢ÓÃÀ´µ÷ÓÃϵͳº¯Êý
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--»ñµÃµ±Ç°ÏµÍ³Ê±¼ ......
ÏÖÔÚÓÐÒÔÏÂÁ½ÕÅ±í£º
±íA ......
ÕâÒ»ÕÂÖУ¬ÎÒÃǽ«·ÖÎö¹¹³ÉÊý¾Ý¿âºÍʵÀýµÄ8 ÖÖÎļþÀàÐÍ¡£
ÓëʵÀýÏà¹ØµÄÎļþÖ»ÓУº
²ÎÊýÎļþ£¨parameter file£©£ºÕâЩÎļþ¸æËßOracle ʵÀýÔÚÄÄÀï¿ÉÒÔÕÒµ½¿ØÖÆÎļþ£¬²¢ÇÒÖ¸
¶¨Ä³Ð©³õʼ»¯²ÎÊý£¬ÕâЩ²ÎÊý¶¨ÒåÁËijÖÖÄÚ´æ½á¹¹Óжà´óµÈÉèÖá£ÎÒÃÇ»¹»á½éÉÜ´æ´¢Êý¾Ý¿â²Î
ÊýÎļþµÄÁ½ÖÖÑ¡Ôñ¡£
¸ú×ÙÎļþ£¨trace file£ ......
×î½üÔÚÏîÄ¿±¨±íÖÐÐèÒªÒ»¸ö²éѯÓï¾ä£¬ÓÃÀ´Í³¼Æ·ûºÏijһÁÐÌõ¼þµÄÆäËü¼¸ÁеĸöÊý
±ÈÈçÓÐÏÂÃæÒ»¸ö±í½á¹¹£º
ÐèÒªÔÚÁÐDºóÃæÔö¼ÓÒ»ÁУ¬Í³¼ÆÔÚÁÐA²»Îª¿Õ£¬²¢ÇÒÁÐB¡¢C¡¢D²»Îª¿ÕµÄ¸öÊý
¾¹ý×Ô¼ºÊÔÑ飬²éÕÒ°ïÖú£¬×ÜËãʵÏÖÁËÉÏÃæµÄ²éѯ¡£
&nb ......
ÈÝÒ×µÃ˵£¬¾¹ýdbms_random°üµ÷ÓÃËæ»úÊýµÄ²½Öè´óÌåÓÐ4ÖÖ£º
Ò»¡¢dbms_random.normal
Õâ¸öº¯Êý²»´ø²ÎÊý£¬»á»Øµ½normal distributionµÄÒ»¸önumberÃÅÀ֮࣬ËùÒÔ´óµÖËæ»úÊý»áÔÚ-Ò»µ½Ò»Æä¼ä¡£
¼òÂÔ²âÊÔÁËÒ»ÏÂ×Ó£¬·¢×÷100000´Î×î´óÄܵ½ÎåÉÏÏ£º
SQL> declare
¶þ i number:=Áã;
Èý j number:=Áã;
ËÄ begin
Îå for ......