oracle sqlÃæÊÔÌâ2

Ò»£®¼òµ¥SQL²éѯ£º
1£©:ͳ¼Æÿ¸ö²¿ÃÅÔ±¹¤µÄÊýÄ¿
select dept,count(*) from employee group by dept;
2£©:ͳ¼Æÿ¸ö²¿ÃÅÔ±¹¤µÄÊýÄ¿´óÓÚÒ»¸öµÄ¼Ç¼
select dept,count(*) from employee group by dept having count(*)>1;
3£©:ͳ¼Æ¹¤×ʳ¬¹ý1200µÄÔ±¹¤ËùÔÚ²¿ÃŵÄÃû³Æ
select e.first_name,salary,d.name
from s_emp e, s_dept d
where e.dept_id = d.id
and salary > 1200;
4£©:²éѯÄĸö²¿ÃÅûÓÐÔ±¹¤
select e.empno, d.deptno
from emp e, dept d
where e.deptno(+) = d.deptno
and e.deptno is null;
or
select *
from dept d
where not exists(select 1 from emp e
                            where e.deptno = d.deptno
                           );
¶þ£®¸´ÔÓSQL²éѯ
ÓÐ3¸ö±í£¨15·ÖÖÓ£©£º(SQL)
Student ѧÉú±í (ѧºÅ£¬ÐÕÃû£¬ÐÔ±ð£¬ÄêÁ䣬×éÖ¯²¿ÃÅ)
Course ¿Î³Ì±í (±àºÅ£¬¿Î³ÌÃû³Æ)
Sc Ñ¡¿Î±í (ѧºÅ£¬¿Î³Ì±àºÅ£¬³É¼¨)
±í½á¹¹ÈçÏ£º
 
1)    дһ¸öSQLÓï¾ä£¬²éѯѡÐÞÁË’JAVA’µÄѧÉúѧºÅºÍÐÕÃû£¨3·ÖÖÓ£©
´ð£ºSQLÓï¾äÈçÏ£º
select stu.sno, stu.sname
from student stu, course c, sc
where stu.sno = sc.sno
and sc.cno = c.cno
and c.cname=’JAVA’;
2)    дһ¸öSQLÓï¾ä£¬²éѯ’a’ͬѧѡÐÞÁ˵ĿγÌÃû×Ö£¨3·ÖÖÓ£©
´ð£ºSQLÓï¾äÈçÏ£º
select stu.sname, c.cname
from student stu, course c, sc
where stu.sno = sc.sno
and sc.cno = c.cno
and stu.sname = ’a’;
3)    дһ¸öSQLÓï¾ä£¬²éѯѡÐÞÁË5Ãſγ̵ÄѧÉúѧºÅºÍÐÕÃû£¨9·ÖÖÓ£©
´ð£ºSQLÓï¾äÈçÏ£º
select stu.sno, stu.sname
from student stu
where (select count(*) from sc where sno=stu.sno) = 5;
Èý. ÔÚSQLÖÐɾ³ýÖظ´¼Ç¼µÄ·½·¨:£¨Óõ½rowid (oracleαÁÐ)£©
1£©Í¨¹ý½¨Á¢ÁÙʱ±íÀ´ÊµÏÖ
SQL>create table temp_emp as (select distinct * from employee) 
SQL>truncate table employee; (Çå¿Õemployee±íµÄÊý¾Ý£©