**练习题**
1.各科前三
select * from sc sc1
where (select count(*) from sc sc2 where sc1.cid=sc2.cid and sc2.score>sc1.score )<=2
内层课程号= 外层课程号
内层分数比外层分数 大的 少于2个
2.分页查询
select * from
(
select rownum rn, empno ,ename ,job ,sal from
(select e.empno ,e.ename ,e.job ,e.sal
from emp e order by nvl(sal,0) desc
)
) t
where t.rn between 5 and 10
3.行列转换
select place,
sum(decode(sales.name, 'A', sales.sales)) A,
sum(decode(sales.name, 'B', sales.sales)) B,
sum(decode(sales.name, 'C', sales.sales)) C
from sales
group by place
查出来之后分组
4.选修相同课程的同学
select sid from stu
where sid <> (select sid from stu where sname = '小张')
and sid in
(
(select sid from record r1
where r1.cid in
(select cid from record --小张的 选课的 所有cid
where sid = (select sid from stu where sname = '小张')
)
group by sid
--count (*) 统计 别人 包含的小张的课 的数量
having count(*) = (select count(*) from record --等于小张的课程数
where sid = (select sid from stu where sname = '小张')))
)
and sid in
(select sid from record --外层套 课程数=小张的sid count分组
group by sid
having count(cid) = (select count(*) from record --找小张的课程数
where sid = (select sid from stu where sname = '小张')
)
)
5.成绩分级
select e.empno,e.ename, e.job ,e.sal ,e.hiredate,
initcap(
decode (sign(sal-801),-1,'e',
decode (sign(sal-1601),-1,'d',
decode(sign(sal-2501),-1,'c',
decode(sign(sal-2801),-1,'b',
decode(sign(sal-3001),-1,'a',
decode(sign(sal-5001),-1,'s')
)
)
)
)
)
) "Grade"
from emp e;
子查询
6.修改张三教授的课程的成绩为对应课程的平均成绩
update sc sc1
set score = (select avg(score ) from sc where cid = sc1.cid)
where cid in
(select cid from course
where tid in (select tid from teacher where tname ='张三'))
7.相同姓名性别 人数
select sname,ssex,(select count(*) from student s2 where s2.sname=s.sname and s2.ssex=s.ssex) 人数 from student s where
(select count(*) from student s2 where s2.sname=s.sname and s2.ssex=s.ssex)>1
8.不同课程成绩相同的学生
select * from sc sc1 where
(select count(*) from sc sc2
where sc1.score=sc2.score
and sc1.cid<>sc2.cid --直接排除原记录
group by score
)>0
Oracle 查询练习题
最新推荐文章于 2024-07-12 14:41:43 发布