Oracle实战练习(续二)

#rownum的使用
 select empno, ename, rownum from emp where rownum<=5;
 select empno, ename from emp where rownum>10; #未选定行, 因为rownum只能与小于或小于等于号一起使用
  select rownum r ,ename from emp;
  select rownum, ename from emp;
  select ename from (select rownum r, ename from emp) where r>10;
# 求薪水最高的前5名 
 select ename, sal from emp order by sal desc;
 select ename, sal from emp where rownum<=5 order by sal desc; #这个是错误的,因为选出来的是前5条记录,然后再排序.
 #应该是先排序后再选择前5名
 select ename, sal from (select ename, sal from emp order by sal desc) where
  rownum <=5;
 #求薪水最高的第6位与第10位之间
 #1.先排序
   select ename, sal from emp order by sal desc;
 #2. 显示rownum, 如果是在上面显示的话就错误.
   select ename, sal, rownum from (select ename, sal from emp order by sal desc);
 #3.薪水最高的第6位与第10位之间
   select ename, sal, rownum r  from
   (select ename, sal, rownum r from
    (select ename, sal from
 emp order by sal desc ) ) where r between 6 and 10;
 # 或者
 select ename, sal, rownum r  from
   (select ename, sal, rownum r from
    (select ename, sal from
 emp order by sal desc ) ) where r >= 6 and r <= 10;
 
 #SQL面试题
 /*有三个表S,C,SC
 S(sno, sname) 代表(学号, 姓名)
 C(cno,cname,cteacher) 代表(课号,课名, 教师)
 SC(sno, cno, scgrade) 代表(学号, 课号成绩)
 问题:
 1.找出没选过"黎明"老师的所有学生姓名
 2.列出2门以上(含2门)不及格学生姓名及平均成绩
 3.求学过1号课程又学过2号课程所有学生的姓名
 */
 # 建表,并插入实验数据
 create table S(sno number(10), sname varchar2(10));
 create table C(cno number(10), cname varchar2(10), cteacher varchar2(20));
 create table SC(sno number(10), cno number(10), scgrade number(3));
 
 insert into S values(20090701, '邓永胜');
 insert into S values(20090702, '邓永寿');
 insert into S values(20090703, '邓永红');
 insert into S values(20090704, '邓雄展');
 insert into S values(20090705, '邓伟新');
 insert into S values(20090706, '邓嘉谊');
 insert into S values(20090707, '邓嘉健');
 
 insert into C values('1001', '英语', '张三');
 insert into C values('1002', '语文', '李四');
 insert into C values('1003', '数学', '王五');
 insert into C values('1004', '化学', '刘六');
 insert into C values('1005', '物理', '黎明');
 
 insert into SC values(20090701,'1001', 85);
 insert into SC values(20090702,'1002', 95);
 insert into SC values(20090703,'1003', 86);
 insert into SC values(20090704,'1004', 89);
 insert into SC values(20090704,'1005', 89);
 insert into SC values(20090705,'1004', 55);
 insert into SC values(20090705,'1005', 55);
  insert into SC values(20090705,'1006', 53);
 insert into SC values(20090706,'1005', 58);
 insert into SC values(20090707,'1005', 51);
 
 #1.找出没选过"黎明"老师的所有学生的姓名
 select sname from s join sc on(s.sno = sc.sno) join c  on(c.cno = sc.cno) where c.cteacher <>'黎明';
 #2.列出2门以上(含2门)不及格学生姓名的平均成绩
 select  avg(scgrade) from s join sc on (s.sno = sc.sno) where sname in (select sname from s where sno in(select sno from sc where scgrade <60 group
 by sno having count(*)>=2));
 #3.求学过1号课程又学过2号课程的所有学生的姓名
 select sname from s where sno in (select sno from sc where cno = '1' and sno in(select sno from sc where cno = '5'));
 
update emp2 set sal = sal*2;  #update 语句
delete from dept2;
insert into salgrade values(6,10000,20000);
  rollback; # 事务回滚, 先前的SQL语句无效
  select * from salgrade; #可以看到rollback后,上面的SQL语句无效
  select sal from emp2;   #可以看到rollback后,上面的SQL语句无效
   update emp2 set sal = sal*2;
   select * from emp2;
   select sal from emp2 where ename = 'KING';  #此时的KING的sal是10000
    commit; # 提交事务
    rollback;   #提交事务后再回滚,不起作用
   #查询一下
    select sal from emp2 where ename = 'KING'; # 此时的KING的sal还是10000, 因为已经提交了事务
   
 #当在事务提交前插入DDL语句或者DCL语句, 那么事务回滚无效
  #如下所示:
 update dept2 set deptno = deptno +3;
 select * from dept2;
 create table tt(aa varchar2(10));
 rollback;
 select * from dept2;           # 此时的deptno 是加上了3 的, 事务回滚无效
 #当用户正常断开的时候,事务自动提交
 #断电(非正常)