文档库

最新最全的文档下载
当前位置:文档库 > oracle数据库增删改查练习50例-答案(精)

oracle数据库增删改查练习50例-答案(精)

oracle 数据库增删改查练习50例-答案

一、建表--学生表drop table student;create table student (sno varchar2(10,sname varchar2(10,sage date,ssex varchar2(10;insert into student values('01','赵雷

',to_date('1990/01/01','yyyy/mm/dd','男';insert into student values('02','钱电

',to_date('1990/12/21','yyyy/mm/dd','男';insert into student values('03','孙风

',to_date('1990/05/20','yyyy/mm/dd','男';insert into student values('04','李云

',to_date('1990/08/06','yyyy/mm/dd','男';insert into student values('05','周梅

',to_date('1991/12/01','yyyy/mm/dd','女';insert into student values('06','吴兰

',to_date('1992/03/01','yyyy/mm/dd','女';insert into student values('07','郑竹

',to_date('1989/07/01','yyyy/mm/dd','女';insert into student values('08','王菊

',to_date('1990/01/20','yyyy/mm/dd','女';--课程表drop table course;create table course (cno varchar2(10,cname varchar2(10,tno varchar2(10;insert into course values ('01','语文','02';insert into course values ('02','数学','01';insert into course values ('03','英语','03';

--教师表drop table teacher;create table teacher (tno varchar2(10,tname

varchar2(10;insert into teacher values('01','张三';insert into teacher values('02','李四';insert into teacher values('03','王五';

--成绩表drop table sc;create table sc (sno varchar2(10,cno varchar2(10,score number(18,1;insert into sc values('01','01',80.0;insert into sc values('01','02',90.0;insert into sc values('01','03',99.0;insert into sc values('02','01',70.0;insert into sc

values('02','02',60.0;insert into sc values('02','03',80.0;insert into sc

values('03','01',80.0;insert into sc values('03','02',80.0;insert into sc

values('03','03',80.0;insert into sc values('04','01',50.0;insert into sc

values('04','02',30.0;insert into sc values('04','03',20.0;insert into sc

values('05','01',76.0;insert into sc values('05','02',87.0;insert into sc

values('06','01',31.0;insert into sc values('06','03',34.0;insert into sc

values('07','02',89.0;insert into sc values('07','03',98.0;

commit;

二、查询1.1、查询同时存在"01"课程和"02"课程的情况select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score from student s, sc sc1, sc sc2 where s.sno = sc1.sno and s.sno = sc2.sno and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = '01' and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = '02';

1.2、查询必须存在"01"课程,"02"课程可以没有的情况

select t.*, s.score_01, s.score_02 from student t inner join (select a.sno, a.score score_01, b.score score_02 from sc a left join (select * from sc where cno = '02' b on (a.sno = b.sno where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = '01' s on (t.sno = s.sno;

2.1、查询同时'01'课程比'02'课程分数低的数据

select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score from student s, sc sc1, sc sc2 where s.sno = sc1.sno and s.sno = sc2.sno and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = '01' and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = '02' and sc1.score < sc2.score;

2.2、查询同时'01'课程比'02'课程分数低或'01'缺考的数据select s.sno, s.sname, s.sage, s.ssex, t.score_01, t.score_02 from student s, (select b.sno, a.score score_01,

b.score score_02 from (select * from sc where cno = '01' a, (select * from sc where cno = '02' b where a.sno(+ = b.sno t where s.sno = t.sno and (t.score_01 < t.score_02 or

t.score_01 is null;

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩select s.sno, s.sname, t.avg_score avg_score from student s, (select sno, round(avg(score, 2 avg_score from sc group by sno having avg(score >= 60 order by sno t where s.sno = t.sno;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩4.1、有考试成绩,且小于60分select s.sno, s.sname, t.avg_score avg_score from student s,

(select sno, round(avg(score, 2 avg_score from sc group by sno having avg(score < 60 order by sno t where s.sno = t.sno;

4.2、包括没有考试成绩的数据select g.* from (select s.sno, s.sname,

nvl(t.avg_score, 0 avg_score from student s, (select sno, round(avg(score, 2 avg_score from sc group by sno order by sno t where s.sno = t.sno(+ g where g.avg_score < 60;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩5.1、查询所有成绩的(不含缺考的)。select s.sno,s.sname,c.count_cno,c.sum_scorefrom student s,(select sno,count(cno count_cno,sum(score sum_score from sc group by sno order by sno c

where s.sno = c.sno

5.2、查询所有成绩的(包括缺考的。select a.s_sno, a.s_sname, nvl(a.c_cno, 0,

a.s_score from (select s.sno s_sno, s.sname s_sname, c.count_cno c_cno, c.sum_score

s_score from student s, (select sno, count(cno count_cno, sum(score sum_score from sc group by sno order by sno c where s.sno = c.sno(+ a

6、查询"李"姓老师的数量(有几个老师姓李)select count(tn from (select tno, substr(tname, 0, 1 tn from teacher where substr(tname, 0, 1 = '李' a;

7、哪些学生上过张三(老师)的课select st.* from student st, course co, teacher te, sc where te.tno = co.tno and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo and sc.sno = st.sno and te.tname = '张三'

8、哪些学生没上过张三(老师)的课select * from studentminus select st.* from student st, course co, teacher te, sc where te.tno = co.tno and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo and sc.sno = st.sno and te.tname = '张三'

9、查询 '01' '02'都学过的同学的信息select st.* from student st, (select * from sc where cno = '01' a, (select * from sc where cno = '02' b where st.sno = a.sno and st.sno = b.sno

10、查询学过编号为'01'但是没有学过编号为'02'的课程的同学的信息select st.* from student st, ((select sno from sc where cno = '01' minus (select sno from sc where cno = '02' a where st.sno = a.sno;

11、查询没有学全所有课程的同学的信息11.1 学完所有课程的select st.* from student st, (select sno, count(cno from sc group by sno having count(cno = 3 a where st.sno = a.sno;

11.2 没有学完所有课程的select st.* from student st,

(select sno from student minus select sno from sc group by sno having count(cno = 3 a where st.sno = a.sno;

12、查询至少有一门课与学号为'01'的同学所学相同的同学的信息select st.* from student st, (select distinct sno from sc where cno in (select cno from sc where sno = '01' and sno != 1 a where st.sno = a.sno;

13、查询和'01'号的同学学习的课程完全相同的其他同学的信息select st.* from student st, (select sno from (select sno, count(cno CNT1 from sc group by sno a, (select count(cno CNT2 from sc where sno = '01' b where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlT1 = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlT2 and a.sno != '01' c where st.sno = c.sno;

14、查询没学过"张三"老师讲授的任一门课程的学生姓名select st.* from student st, (select sno from student minus select sno from sc where cno = (select http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo from teacher t, course c where t.tno = c.tno and tname = '张三' a where st.sno = a.sno;

15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩select

a.sno, st.sname, a.avg_score from student st, (select sno, count(cno, round(avg(score, 2 avg_score from (select * from sc where score < 60 group by sno a where st.sno = a.sno;

16、检索'01'课程分数小于60,按分数降序排列的学生信息select st.*, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo,

a.score from student st, (select sno, cno, score from sc where cno = '01' and score < 60 order by score desc a where st.sno = a.sno;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩select st.sno, st.sname, c.score, d.score, e.score, a.avg_score from student st, (select sno,

round(avg(score, 2 avg_score from sc group by sno

order by avg_score desc a, (select * from sc where cno = '01' c, (select * from sc where cno = '02' d, (select * from sc where cno = '03' e where st.sno = a.sno(+ and st.sno = c.sno(+ and st.sno = d.sno(+ and st.sno = e.sno(+

18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课

程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率select

http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo "课程编号", http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame "课程名称",

b.max_score "最高分",

c.min_score "最低分",

d.avg_socre "平均分",

e."及格率", e."中等率", e."优良率", e."优秀率" from course a, (select cno, max(score max_score from sc group by cno b, (select cno, min(score

min_score from sc group by cno c, (select cno, round(avg(score, 2 avg_socre from sc group by cno d, (select '01' cno, round((select count(1 from sc where cno = '01' and score >= 60 / (select count(1 from sc where cno = '01' * 100, 2 "及格率", round((select count(1 from sc where cno = '01' and score >= 70 and score < 80 / (select count(1 from sc where cno = '01' * 100, 2 "中等率", round((select count(1 from sc where cno = '01' and score >= 80 and score < 90 / (select count(1 from sc where cno = '01' * 100, 2 "优良率", round((select count(1 from sc where cno = '01' and score >= 90 / (select count(1 from sc where cno = '01' * 100, 2 "优秀率" from dual union select '02' cno, round((select count(1 from sc where cno = '02' and score >= 60 / (select count(1 from sc where cno = '02' * 100, 2 "及格率", round((select count(1 from sc where cno = '02' and score >= 70 and score < 80 / (select count(1 from sc where cno = '02' * 100, 2 "中等率",

round((select count(1 from sc where cno = '02' and score >= 80 and score < 90 / (select count(1 from sc where cno = '02' * 100, 2 "优良率", round((select count(1 from sc where cno = '02' and score >= 90 / (select count(1 from sc where cno = '02' * 100, 2 "优

秀率" from dual union select '03' cno, round((select count(1 from sc where cno = '03' and score >= 60 / (select count(1 from sc where cno = '03' * 100, 2 "及格率", round((select count(1 from sc where cno = '03' and score >= 70 and score < 80 / (select count(1 from sc where cno = '03' * 100, 2 "中等率", round((select count(1 from sc where cno = '03' and score >= 80 and score < 90 / (select count(1 from sc where cno = '03' * 100, 2 "优良率", round((select count(1 from sc where cno = '03' and score > 90 / (select count(1 from sc where cno = '03' * 100, 2 "优秀率" from dual e where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo;

19、按各科成绩进行排序,并显示排名select sno, cno, score,

rank( over(partition by cno order by score desc "名次" from sc;

select sno, cno, score, dense_rank( over(partition by cno order by score desc "名次" from sc;

20、查询学生的总成绩并进行排名20.1 查询学生的总成绩select a.sno, a.sname, nvl(b.sum_score, 0 "总成绩" from student a, (select sno, sum(score sum_score from sc group by sno order by sno b where a.sno = b.sno(+;

20.2 查询学生的总成绩并进行排名。select c."学生编号",c."学生姓名",c."总成绩",rank( over (order by c."总成绩" desc "排名"from (select a.sno "学生编号", a.sname "学生姓名", nvl(b.sum_score, 0 "总成绩" from student a, (select sno, sum(score

sum_score from sc group by sno order by sno b where a.sno = b.sno(+ c

21、查询不同老师所教不同课程平均分从高到低显示select a.tno, a.tname,

c.avg_score "平均分" from teacher a, course b, (select cno, round(avg(score, 2 avg_score from sc group by cno c where a.tno = b.tno and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo order by "平均分" desc;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩Score 重复时保留名次空缺select * from (select sno, cno, score, rank( over(partition by cno order by score desc order_sc from sc a where a.order_sc in (2, 3;

Score 重复时合并名次 select * from (select sno, cno, score,

dense_rank( over(partition by cno order by score desc order_sc from sc a where

a.order_sc in (2, 3;

23 、统计各科成绩各分数段人数:课程编号 , 课程名称,"100-85","85-70","70-60","0-60"及所占百分比23.1 统计各科成绩各分数段人数:课程编号 , 课程名称,"100-85","85-70","70-60","0-60"select http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, b."85-100", b."70-85", b."60-70", b."0-60" from course a, (select '01' cno, (select count(1 from sc where cno = '01' and score >= 85 and score <= 100 "85-100", (select count(1 from sc where cno = '01' and score >= 70 and score < 85 "70-85", (select count(1 from sc where cno = '01' and score >= 60 and score < 70 "60-70", (select count(1 from sc where cno = '01' and score < 60 "0-60" from dual union select '02' cno, (select count(1 from sc

where cno = '02' and score >= 85 and score <= 100 "85-100", (select count(1 from sc where cno = '02' and score >= 70 and score < 85 "70-85", (select count(1 from sc where cno = '02' and score >= 60 and score < 70 "60-70", (select count(1 from sc where cno = '02' and score < 60 "0-60" from dual union select '03' cno, (select count(1 from sc where cno = '03' and score >= 85 and score <= 100 "85-100", (select count(1 from sc where cno = '03' and score >= 70 and score < 85 "70-85", (select count(1 from sc where cno = '03' and score >= 60 and score < 70 "60-70", (select count(1 from sc where cno = '03' and score < 60 "0-60" from dual b where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo;

纵向显示 1(显示存在的分数段;

select http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, b.ff, b.c1 from course a, (select cno, c1, '0-60' ff from (select cno, count(1 c1 from sc where score < 60 group by cno union select cno, c1, '60-70' ff from (select cno, count(1 c1 from sc where score >= 60 and score < 70 group by cno

union select cno, c1, '70-85' ff from (select cno, count(1 c1 from sc where score >= 70 and score < 85 group by cno union select cno, c1, '85-100' ff from (select cno, count(1 c1 from sc where score >= 85 and score <= 100

group by cno b where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo

23.2 统计各科成绩各分数段人数:课程编号 , 课程名称,"100-85","85-70","70-60","<60"及所占百分比横向显示select http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, bb."0-60", bb."1百分比(%", bb."60-70", bb."2百分比(%", bb."70-85", bb."3百分比(%", bb."85-100", bb."4百分比(%" from course aa, ((select '01' cno, (select count(1 from sc where cno = '01' and score < 60 "0-60", round(((select count(1 from sc where cno = '01' and score < 60 * 100 / (select count(1 from sc where cno = '01', 2 "1百分比(%", (select count(1 from sc where cno = '01' and score >= 60 and score < 70 "60-70", round(((select count(1 from sc where cno = '01' and score >= 60 and score < 70 * 100 / (select count(1 from sc where cno = '01', 2 "2百分比(%", (select count(1 from sc where cno = '01' and score >= 70 and score < 85 "70-85", round(((select count(1 from sc where cno = '01' and score >= 70 and score < 85 * 100 / (select count(1 from sc where cno = '01', 2 "3百分比(%", (select count(1 from sc where cno = '01' and score >= 85 and score <= 100 "85-100", round(((select count(1 from sc where cno = '01' and score >= 85 and score <= 100 * 100 / (select count(1 from sc where cno = '01', 2 "4百分比(%" from dual union (select '02' cno, (select count(1 from sc

where cno = '02' and score < 60 "0-60", round(((select count(1 from sc where cno = '02' and score < 60 * 100 / (select count(1 from sc where cno = '02', 2 "1百分比(%", (select count(1 from sc where cno = '02' and score >= 60 and score < 70 "60-70",

round(((select count(1 from sc where cno = '02' and score >= 60 and score < 70 * 100 / (select count(1 from sc where cno = '02', 2 "2百分比(%", (select count(1 from sc where cno = '02' and score >= 70 and score < 85 "70-85", round(((select count(1 from sc where cno = '02' and score >= 70 and score < 85 * 100 / (select count(1 from sc where cno = '02', 2 "3百分比(%", (select count(1 from sc where cno = '02' and score >= 85 and score <=

100 "85-100", round(((select count(1 from sc where cno = '02' and score >= 85 and score <= 100 * 100 / (select count(1 from sc where cno = '02', 2 "4百分比(%" from dual union (select '03' cno, (select count(1 from sc where cno = '03' and score < 60 "0-60",

round(((select count(1 from sc where cno = '03' and score < 60 * 100 / (select count(1 from sc where cno = '03', 2 "1百分比(%", (select count(1 from sc where cno = '03' and score >= 60 and score < 70 "60-70", round(((select count(1 from sc where cno = '03' and score >= 60 and score < 70 * 100 / (select count(1 from sc where cno = '03', 2 "2百分比(%",

(select count(1 from sc where cno = '03' and score >= 70 and score < 85 "70-85", round(((select count(1 from sc where cno = '03' and score >= 70 and score < 85 * 100 / (select count(1 from sc where cno = '03', 2 "3百分比(%", (select count(1 from sc where cno = '03' and score >= 85 and score <= 100 "85-100", round(((select count(1 from sc where cno = '03' and score >= 85 and score <= 100 * 100 / (select count(1 from sc where cno = '03', 2 "4百分比(%" from dual bb where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo;

纵向显示

select t1.*, round(t1.num / t2.all_num * 100, 2 || '%' 百分比 from (select http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, (case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end as px, count(1 num from Course m, sc n where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo group by http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, (case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end order by http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, px t1, (select http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, count(1 all_num from course m, sc n where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo group by http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame order by http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame t2 where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo

24、查询学生的平均成绩并进行排名select c.sno, st.sname, c.avg_score, c.paim

from student st, (select b.*, rownum paim from (select a.sno, round(avg(score, 2 avg_score from (select s.sno sno, nvl(sc.score, 0 score from student s, sc where s.sno = sc.sno(+ a group by a.sno order by avg_score desc b c where st.sno = c.sno;

25、查询各科成绩前三名的记录25.1 分数重复时保留名次空缺select st.*,

http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, d.score from student st, (select a.* from (select * from sc where cno = '01' order by score desc a where rownum <= 3 union all select b.* from (select * from sc where cno = '02' order by score desc b where rownum <= 3 union all select c.* from (select * from sc where cno = '03' order by score desc c where rownum <= 3 d where st.sno = d.sno

25.2 分数重复时不保留名次空缺,合并名次select

st.sno,st.sname,st.sage,st.ssex,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo,b.score,b.paimfrom student st,(select

sno,cno,score,paim from (select sno, cno, score, dense_rank( over(partition by cno order by score desc paim from sc awhere a.paim <= 3 bwhere st.sno = b.sno

26、查询每门课程被选修的学生数select cno,count(1 from sc group by cno;

27、查询出只有两门课程的全部学生的学号和姓名 select st.sno, st.sname from student st, (select a.sno from (select sno, count(cno cn from sc group by sno a where http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.html = 2 b where st.sno = b.sno;

28、查询男生、女生人数select '男生人数' "男女情况", cn from (select

count(ssex cn from student where ssex = '男'union allselect '女生人数' "男女情况", cn from (select count(ssex cn from student where ssex = '女'

29、查询名字中含有"风"字的学生信息select * from student where sname like '%风%'

31、查询1990年出生的学生名单(注:Student表中Sage列的类型是dateselect * from student where to_char(sage,'yyyy' = '1990'

32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列select http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo ,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame,a.avg_score

from course c,(select cno,round(avg(score,2 avg_score from sc group by cno awhere http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmloorder by a.avg_score desc ,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo;

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩select st.sno, st.sname, a.avg_score from student st, (select sno, round(avg(score, 2 avg_score from sc group by sno having round(avg(score, 2 >= 85 a where st.sno = a.sno;

34、查询课程名称为"数学",且分数低于60的学生姓名和分数select

st.sname,a.scorefrom student st,(select sno, score from sc where cno = (select cno from course where cname = '数学' and score < 60 awhere st.sno = a.sno

35、查询所有学生的课程及分数情况select st.*, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, sc.score from student st, course c, sc where st.sno = sc.sno and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;select st.*,

http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, sc.score from student st, course c, sc, (select sno, min(score from sc group by sno having min(score >= 70 a where st.sno = a.sno and a.sno = sc.sno and

http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo

37、查询不及格的课程 select st.*,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo,a.scorefrom student st,course c,(select * from sc where score < 60 awhere st.sno = a.snoand http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名select st.*,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo,a.scorefrom student st,course c,(select sno,cno,scorefrom scwhere cno = '01' and score >= 80 awhere st.sno = a.snoand http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo

39、求每门课程的学生人数select http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlfrom course c,(select

cno,count(sno cn from sc group by cno awhere http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo

40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩select st.*, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlame, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, b.score

from student st, course c, (select * from sc where score = (select max(score from (select * from sc where cno = (select cno from course where tno = (select tno from teacher where tname = '张三' a and cno = (select cno from course where tno = (select tno from teacher where tname = '张三' b where st.sno = b.sno and http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo = http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩select distinct sc1.sno, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, sc1.scorefrom sc sc1,sc sc2where sc1.score = sc2.scoreand

http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo != http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo

42、查询每门功成绩最好的前两名

select st.sno, st.sname, st.sage, st.ssex, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, a.paim from student st, (select sno, cno, score, dense_rank( over(partition by cno order by score desc paim from sc a where st.sno = a.sno and a.paim <= 2

select st.sno, st.sname, st.sage, st.ssex, http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlo, a.paim from student st, (select sno, cno, score, rank( over(partition by cno order by score desc paim from sc a where st.sno =

a.sno and a.paim <= 2

44、检索至少选修两门课程的学生学号select st.sno,st.sname,http://www.wendangku.net/doc/980d897d7275a417866fb84ae45c3b3567ecddda.htmlfrom student st,(select sno,count(cno cn from sc group by sno having count(cno >= 2 aawhere st.sno = aa.sno

46、查询各学生的年龄select st.*, (to_char(sysdate, 'yyyy' - to_char(st.sage, 'yyyy' "年龄" from student st

47、查询本周过生日的学生select st.* from student st where to_char(st.sage,

'mmdd' between to_char(trunc(sysdate, 'iw', 'mmdd' and to_char(trunc(sysdate, 'iw' + 6,

'mmdd'

48、查询下周过生日的学生select st.* from student st where to_char(st.sage,

'mmdd' between to_char(trunc(sysdate, 'iw'+7, 'mmdd' and to_char(trunc(sysdate, 'iw' + 13, 'mmdd'

select to_char(trunc(sysdate, 'iw'+7, 'mmdd' from dual; select to_char(trunc(sysdate, 'iw'+ 13, 'mmdd' from dual;

49、查询本月过生日的学生select st.* from student st where to_char(st.sage, 'mm' = to_char(sysdate,'mm'

50、查询下月过生日的学生select st.*from student st where to_char(st.sage, 'mm' = to_char(add_months(trunc(sysdate,1,'mm'