1、第一阶段题目：以下练习题是根据初始化到数据库中的表执行的，请务必先执行如下建表语句。create table s1_student( sno varchar2(10) primary key, sname varchar2(30), sbirthday varchar2(30), ssex varchar2(10),sclass varchar2(10) ); create table s1_teacher( tno varchar2(10) primary key, tname varchar2(30),tsex varchar2(10),tbirthday varchar2(30),pro
2、f varchar2(30),depart varchar2(30) ); create table s1_course( cno varchar2(10), cname varchar2(30), tno varchar2(10) ); create table s1_score( sno varchar2(10), cno varchar2(10), degree number );create table s1_grade( low number, upp number, rank varchar2(2) );/*初始化学生表 s1_student*/ insert into s1_st
3、udent values (108, 曾华, 1997/9/1, 男, 95033);insert into s1_student values (105, 匡明, 1995/10/2, 男, 95031 );insert into s1_student values (107, 王丽, 1996/1/23, 女, 95033 );insert into s1_student values (101, 李军, 1996/2/20, 男, 95033 );insert into s1_student values (109, 王芳, 1995/2/10, 女, 95031 );insert in
4、to s1_student values (103, 陆君, 1994/6/3, 男, 95031);commit; /*初始化教师表 s1_teacher*/ insert into s1_teacher values (804, 李诚, 男,1979-12-2,副教授, 计算机系); insert into s1_teacher values (856, 张旭, 男,1985-3-12,讲师, 电子工程系); insert into s1_teacher values (825, 王萍, 女,1989-5-2 ,助教, 计算机系); insert into s1_teacher value
5、s (831, 刘冰, 女,1988-8-2 ,助教, 电子工程系); commit; /*初始化课程表 s1_course*/ insert into s1_course values (3-105, 计算机导论 , 825);insert into s1_course values (3-245, 操作系统 , 804);insert into s1_course values (6-166, 数据电路 , 856);insert into s1_course values (9-888, 高等数学 , 800);commit; /*初始化成绩表 s1_score*/insert into
6、 s1_score values (103, 3-245, 86);insert into s1_score values (105, 3-245, 75);insert into s1_score values (109, 3-245, 68);insert into s1_score values (103, 3-105, 92);insert into s1_score values (105, 3-105, 88);insert into s1_score values (109, 3-105, 76);insert into s1_score values (101, 3-105,
7、64);insert into s1_score values (107, 3-105, 91);insert into s1_score values (108, 3-105, 78);insert into s1_score values (101, 6-166, 85);insert into s1_score values (107, 6-106, 79);insert into s1_score values (108, 6-166, 81);commit; /*初始化成绩分类表 s1_grade*/insert into s1_grade values (90, 100, A);i
8、nsert into s1_grade values (80, 89, B);insert into s1_grade values (70, 79, C);insert into s1_grade values (60, 69, D);insert into s1_grade values (0, 59, E);commit;1.查询 s1_student 表中的所有记录的 Sname、Ssex 和 Class 列.select sname,ssex,sclass from s1_student;2.查询教师所有的单位即不重复的 Depart 列.select distinct depart
9、 from s1_teacher;3.查询 s1_student 表的所有记录.select * from s1_student;4.查询 s1_score 表中成绩在 60 到 80 之间的所有记录.select * from s1_score where degree between 60 and 80;5.查询 s1_score 表中成绩为 85，86 或 88 的记录.select * from s1_score where degree in (85,86,88);6.查询 s1_student 表中“95031”班或性别为“女”的同学记录.select * from s1_stud
10、ent where sclass=95031 or ssex=女;7.以 Class 降序查询 s1_student 表的所有记录.select * from s1_student order by sclass desc;8.以 Cno 升序、Degree 降序查询 s1_score 表的所有记录。select * from s1_score order by cno,degree desc;9.查询“95031”班的学生人数。select count(sno) from s1_student where sclass=95031;10.查询 s1_score 表中的最高分的学生学号和课程号
11、。select sno,cno from s1_score where degree =(select max(degree) from s1_score);11.查询3-105号课程的平均分。select avg(degree) from s1_score where cno=3-105;12.查询 s1_score 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数。select cno,avg(degree) from s1_score where cno like 3% group by cno having count(sno)5;13.查询最低分大于 70，最高分小于 90
12、 的 Sno 列。select sno from s1_score group by sno having min(degree)70 and max(degree)=degree)as rank from s1_score;19.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。(1)select * from s1_score where cno=3-105 and degree (select degree from s1_score where cno=3-105 and sno=109);(2)select x.CNO ,x.SNO ,x.DEGREE fro
13、m s1_score x,s1_score ywhere x.CNO =3-105 and x.DEGREE y.DEGREE and y.SNO =109and y.CNO=3-105;20.查询 s1_score 中选学一门以上课程的同学中分数为非最高分成绩的记录。select * from s1_score j where j.SNO in (select SNO from s1_score group by SNO having count(*)=2) and j.DEGREE (select degree from s1_score where sno=109and cno=3-10
14、5) and cno=3-105;22.查询和学号为 108 的同学同年出生的所有学生的 Sno、Sname 和 Sbirthday 列。select sno,sname,sbirthday from s1_student st1 where substr(sbirthday,1,4)in (select substr(sbirthday,1,4) from s1_student where sno=101);23.查询“张旭“教师任课的学生成绩。select sc.* from s1_score sc,s1_teacher th,s1_course co where o=o and co.t
15、no=th.tno and th.tname=张旭;24.查询选修某课程的同学人数多于 5 人的教师姓名。(1)select tname from s1_teacher th,s1_course co where th.tno=co.tno and o in (select cno from s1_score group by cno having count(sno)5);(2)select TNAME from s1_teacher where TNO in(select x.TNO from s1_course x inner join s1_score y on x.CNO =y.CN
16、O group by x.TNO having count(x.TNO )5);25.查询 95033 班和 95031 班全体学生的记录。select * from s1_student where sclass in(95033,95031);26.查询存在有 85 分以上成绩的课程 Cno.select cno from s1_score group by cno having max(degree)=85;27.查询出“计算机系“教师所教课程的成绩表。select sc.* from s1_score sc,s1_teacher th,s1_course co where o=o an
17、d co.tno=th.tno and th.depart=计算机系 ;28.查询“计算机系”与“电子工程系“不同职称的教师的 Tname 和 Prof。select TNAME,PROF from s1_teacher where DEPART =计算机系 and PROF not in(select PROF from s1_teacher where DEPART =电子工程系);29.查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的 Cno、Sno 和 Degree,并按 Degree 从高到低次序排序。(1)select sno,cno,degree f
18、rom s1_score where cno=3-105 and degree (select min(degree) from s1_score where cno=3-245)order by degree desc;(2)select * from s1_score where CNO =3-105 and DEGREE any (select DEGREE from s1_score where CNO =3-245)order by DEGREE desc;30.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的 Cno、Sno 和 Degree.(1)sel
19、ect sno,cno,degree from s1_score where cno=3-105 and degree (select max(degree) from s1_score where cno=3-245)order by degree desc;(2)select * from s1_score where CNO =3-105 and DEGREE all(select DEGREE from s1_score where CNO =3-245);31.查询所有教师和同学的 name、sex 和 birthday.select sname as name,ssex as se
20、x from s1_studentunion allselect tname as name,tsex as sex from s1_teacher;32.查询所有“女”教师和“女”同学的 name、sex 和 birthday.select sname as name,ssex as sex,sbirthday as birthday from s1_student where ssex=女union allselect tname as name,tsex as sex,tbirthday as birthday from s1_teacher where tsex=女;33.查询成绩比该
21、课程平均成绩低的同学的成绩表。(1)select sc1.* from s1_score sc1,(select cno,avg(degree)as degree from s1_score group by cno) sc2 where o=o and sc1.degree=2;37.查询 s1_student 表中不姓“王”的同学记录。select * from s1_student where sname not like 王%;38.查询 s1_student 表中每个学生的姓名和年龄。select sname,to_char(sysdate,yyyy)-substr(sbirthda
22、y,1,4) as tage from s1_student;39.查询 s1_student 表中最大和最小的 Sbirthday 日期值。select max(sbirthday),min(sbirthday) from s1_student;40.以班号和年龄从大到小的顺序查询 s1_student 表中的全部记录。select * from s1_student order by sclass desc,sbirthday asc;41.查询“男”教师及其所上的课程。select th.tno,th.tname,th.tsex,o from s1_teacher th,s1_cours
23、e co where th.tno=co.tno and th.tsex=男;42.查询最高分同学的 Sno、Cno 和 Degree 列。select * from s1_score where degree =(select max(degree) from s1_score);43.查询和“李军”同性别的所有同学的 Sname.select sname from s1_student where ssex =(select ssex from s1_student where sname=李军) and snamesc2.score;2.查询平均成绩大于 60 分的同学的学号和平均成绩；
24、select sno,avg(score) from sc group by sno having avg(score)60;3.查询所有同学的学号、姓名、选课数、总成绩；select sc.sno,sname,count(cno)as cnt,sum(score)as score from sc, student st where sc.sno=st.sno group by sc.sno,sname;4.查询姓“刘”的老师的个数；select count(tno) from teacher where tname like 刘% ;5.查询没学过“谌燕”老师课的同学的学号、姓名；selec
25、t sno,sname from student st where sno not in (select sno from sc,course co,teacher th where o=o and co.tno=th.tno and th.tname=谌燕 );6.查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；(1)select sc.sno,sname from sc,student st where sc.sno=st.sno and cno =c001 and sc.sno in(select sno from sc where cno=c002);(2)sel
26、ect st.* from sc a join sc b on a.sno=b.sno join student st on st.sno=a.sno where o=c001 and o=c002 and st.sno=a.sno;7.查询学过“谌燕”老师所教的所有课的同学的学号、姓名；select s.sno,d.sname,count(distinct o) from SC s,Student d,Course c,Teacher t where s.sno=d.sno and o=o and c.tno=t.tno and t.tname=谌燕 group by s.sno,d.sna
27、me having count(distinct o) = (select count(*) from Course c,Teacher t where c.tno=t.tno and t.tname=谌燕);8.查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；select sc1.sno,sname from sc sc1,student st,sc sc2 where sc1.sno=sc2.sno and sc1.scoresc2.score and o=c001 and o=c002 and sc1.sno=st.sno;9.查询所有课程成绩小于 60
28、 分的同学的学号、姓名；select sc.sno,sname from sc,student st where sc.sno=st.sno and score s001;12.查询至少学过学号为“s002”同学所有门课的其他同学学号和姓名；SELECT sno, Sname FROM student WHERE sno IN (SELECT sno,cno FROM SC WHERE cno IN (SELECT cno FROM SC WHERE sno = s002) AND sno = (SELECT COUNT(cno) FROM SC WHERE sno = s002) GROUP
29、 BY sno);13.把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；update sc sc1set score =( select avg(score) from sc sc2 where o=o group by o )where cno in ( select distinct o from course co,teacher th where o=o and co.tno=th.tno and th.tname=谌燕);commit;14.查询和“s002”号的同学学习的课程完全相同的其他同学学号和姓名；select distinct sno from sc whe
30、re sno=60 then 1 else 0 end)/count(*) as 及格率 from sc group by cno order by avg(score),及格率 desc ;19.查询不同老师所教不同课程平均分从高到低显示；select co.tno,o,avg(sc.score) from sc,course co where o=o group by co.tno,o;20.统计列印各科成绩,各分数段人数:课程 ID,课程名称,100-85,85-70,70-60, 1;27.1991 年出生的学生名单(注：Student 表中 Sage 列的类型是 number)；se
31、lect * from student where sage=to_char(sysdate,yyyy)-1991;28.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；select cno,avg(score) from sc group by cno order by avg(score),cno desc;29.查询平均成绩大于 85 的所有学生的学号、姓名和平均成绩；select sc.sno,sname,avg(score) from sc,student st where sc.sno=st.sno group by sc.sno,sname ha
32、ving avg(score)60;30.查询课程名称为“数据库”，且分数低于 60 的学生姓名和分数；select sname,score from student st,sc,course co where sc.sno=st.sno and o=o and cname=Java Web and score60;31.查询所有学生的选课情况；select sno,cno from sc;32.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数；select sname,cname,score from student st,sc,course co where sc.sno=st.
33、sno and o=o and score70;33.查询不及格的课程，并按课程号从大到小排列；select o,cname,score from sc,course co where o=o and score80 and cno=c001;35.求选了课程的学生人数；select count(distinct sno) from sc ;36.查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩；select st.sname,score from student st,sc ,course c,teacher t where st.sno=sc.sno and o=o and
34、 c.tno=t.tno and t.tname=谌燕 and score =(select max(score) from sc,course co,teacher th where o=o and co.tno=th.tno and th.tname=谌燕);37.查询各个课程及相应的选修人数；select cno,count(sno) from sc group by cno ;38.查询不同课程成绩相同的学生的学号、课程号、学生成绩；select sc1.sno,o,sc1.score from sc sc1,sc sc2 where sc1.score=sc2.score and o
35、=2;42.查询全部学生都选修的课程的课程号和课程名；select o,cname from sc,course co where o=o group by o,cname having count(sno)=(select count(distinct sno) from student);43.查询没学过“谌燕”老师讲授的任一门课程的学生姓名；select st.sno,st.sname from student st where st.sno not in (select distinct sc.sno from sc,course c,teacher t where o=o and c
36、.tno=t.tno and t.tname=谌燕);44.查询两门以上不及格课程的同学的学号及其平均成绩；select sno,avg(score) from sc where sno in (select sno from sc where score1) group by sno;45.检索“c004”课程分数小于 60，按分数降序排列的同学学号；select sno,score from sc where cno=c002 and score 80 order by score desc;46.删除“s002”同学的“c001”课程的成绩； delete from sc where sno=s002 and cno=c001;
