#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 的, 事务回滚无效
#当用户正常断开的时候,事务自动提交
#断电(非正常)
Oracle实战练习(续二)
最新推荐文章于 2024-07-19 12:06:45 发布