1、Oracle练习及答案新练习一： 创建4张表： 学生表student(sid,sname) 教师表teacher(tid,tname) 课程表course(cid,cname,ctype) 选课表choose_course(ccid,sid,tid,cid) 在4张表中插入如下数据： 学生表(1,小明) 学生表(2,小花) 学生表(3,小红) 教师表(1,陈红) 教师表(2,陈白) 课程表(1,语文,文科) 课程表(2,数学,理科) 选课表(1,1,1,1) 选课表(2,1,1,2) 选课表(3,2,1,2) 选课表(4,2,1,1) 选课表(5,3,2,1) 准备create user te
2、st identified by testdefault tablespace users； grant connect,resource to test;grant create view to test;rem set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBKrem sqlplus /nolog rem conn test/testorcl drop table student;drop table teacher;drop table course;drop table choose_course;drop view v_course;crea
3、te table student(sid number(2),sname varchar2(20)tablespace USERS;create table teacher(tid number(2),tname varchar2(20)tablespace USERS;create table course(cid number(2),cname varchar2(20),ctype varchar2(20)tablespace USERS;create table choose_course(ccid number(2),sid number(2),tid number(2),cid nu
4、mber(2)tablespace USERS;insert into student values(1,小明);insert into student values(2,小花);insert into student values(3,小红);insert into teacher values(1,陈红);insert into teacher values(2,陈白);insert into course values(1,语文,文科);insert into course values(2,数学,理科);insert into choose_course values(1,1,1,1)
5、;insert into choose_course values(2,1,1,2);insert into choose_course values(3,2,1,2);insert into choose_course values(4,1,2,1);insert into choose_course values(5,2,1,1);insert into choose_course values(6,3,2,1); commit;create view v_course as select t.tname,s.sname,ame,c.ctype from student s, choose
6、_course cc, teacher t, course c where s.sid=cc.sid and t.tid=cc.tid and c.cid=cc.cid ;题目1. 查找陈红老师教的学生是哪些？ 2. 找学生小明所有的文科老师 3. 找出没有选修陈红老师的学生 4. 教的学生最少的老师 答案1、 select distinct sname from student s,choose_course cc,teacher t where s.sid=cc.sid and t.tid=cc.tid and t.tname=陈红； 2、 select t.tname from teac
7、her t, choose_course cc where t.tid=cc.tid and sid=(select sid from student where sname=小明) and cc.cid=(select cid from course where ctype=文科);3、select distinct s.sname from teacher t, student swhere s.sid not in( select s.sid from teacher t, student s, choose_course cc where t.tid=cc.tid and s.sid=
8、cc.sid and t.tname=陈红） 4、select tname from teacher where tid=(select tid from(select cc.tid from teacher t,choose_course cc where t.tid=cc.tid group by cc.tid order by count(sid) where ROWNUM=1) 练习二：create table student2(sno varchar2(10) primary key,sname varchar2(20),sage number(2),ssex varchar2(5)
9、create table teacher2(tno varchar2(10) primary key,tname varchar2(20)create table course2(cno varchar2(10),cname varchar2(20),tno varchar2(20),constraint pk_course primary key(cno,tno)create table sc(sno varchar2(10),cno varchar2(10),score number(4,2),constraint pk_sc primary key(sno,cno)insert into
10、 student2 values(s001,张三,23,男);insert into student2 values(s002,李波,23,男);insert into student2 values(s003,吴鹏,25,男);insert into student2 values(s004,琴沁,20,女);insert into student2 values(s005,王丽,20,女);insert into student2 values(s006,李波,21,男); insert into student2 values(s007,刘玉,21,男);insert into stud
11、ent2 values(s008,萧蓉,21,女);insert into student2 values(s009,陈萧晓,23,女);insert into student2 values(s010,陈美,22,女);commit;insert into teacher2 values(t001,刘阳);insert into teacher2 values(t002,洪燕);insert into teacher2 values(t003,胡明星);commit;insert into course2 values(c001,J2SE,t002);insert into course2
12、values(c002,Java Web,t002);insert into course2 values(c003,SSH,t001);insert into course2 values(c004,Oracle,t001);insert into course2 values(c005,SQL Server 2005,t003);insert into course2 values(c006,C#,t003);insert into course2 values(c007,JavaScript,t002);insert into course2 values(c008,DIV+CSS,t0
13、01);insert into course2 values(c009,PHP,t003);insert into course2 values(c010,EJB3.0,t002);commit;insert into sc values(s001,c001,78.9);insert into sc values(s002,c001,80.9);insert into sc values(s003,c001,81.9);insert into sc values(s004,c001,60.9);insert into sc values(s002,c002,72.9);insert into
14、sc values(s003,c002,81.9);insert into sc values(s001,c003,59);insert into sc values(s001,c006,50);insert into sc values(s006,c004,56);insert into sc values(s005,c004,49);insert into sc values(s001,c002,70);insert into sc values(s001,c004,90);insert into sc values(s001,c005,65);insert into sc values(
15、s001,c007,30);insert into sc values(s001,c008,88.5);insert into sc values(s001,c009,74);insert into sc values(s001,c010,61);commit;create view v_course2 as select ame,t.tname,s.sname,sc.score from student2 s, teacher2 t, course2 c, sc where s.sno=sc.sno and t.tno=c.tno and o=o;题目：基础查询1. 求选了课程的学生人数 s
16、elect count(distinct sno) from sc; 2. 删除s002同学的c001课程的成绩 delete from sc where sno=s002 and cno=c001;3. 查询姓“刘”的老师的个数 select count（*） from teacher2 where tname like 刘%;4. 1981年出生的学生名单（注：student表中sage列的类型是number）select sname,sage from student2 where sage=23;5. 查询不及格的课程，并按课程号从大到小排列select * from sc where
17、 score60 order by cno desc;6. 检索c004课程分数小于60，按分数降序排列的同学学号 select sno,score from sc where cno=c004 and score=2;12. 查询同名同姓学生名单，并统计同名人数 select sname,count(sno) from student2 group by sname having count(sno)1;13. 查询平均成绩大于60分的同学的学号和平均成绩 select s.sno,avg(sc.score) from student2 s, sc where s.sno=sc.sno gr
18、oup by s.sno having avg(sc.score)60;14. 统计每门课程的学生选修人数（超过1人的课程才统计）。要求输出课程和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列select cno,count(sno) from sc group by cno having count(sno)1 order by count(sno) desc, cno;表关联查询15. 查询课程编号为c001且课程成绩在80分以上的学生的学号和姓名select s.sno,sname,sc.score from sc,student2 s where sc.sno=s.sn
19、o and score80 and cno=c001;16. 查询所有学生的选课情况 select sname,cname from student2 s,course2 c,sc where s.sno=sc.sno and o=o;17. 查询不同课程成绩相同的学生的学号、课程号、学生成绩（自连接） select s1.sno,o,s1.score from sc s1,sc s2 where s1.score=s2.score and o!=o;18. 查询学过“洪燕”老师所教的所有课的同学的学号、姓名 select distinct s.sno,s.sname from student
20、2 s,teacher2 t, course2 c, sc where s.sno=sc.sno and t.tno=c.tno and o=o and tname=洪燕;19. 查询所有同学的学号、姓名、选课数、总成绩； select s.sno, s.sname, count(o), sum(sc.score) from student2 s left join sc on s.sno=sc.snogroup by s.sno,s.sname;20. 查询课程名称是“SSH”，且分数低于60的学生姓名和分数select s.sname,score from student2 s,sc,co
21、urse2 c where s.sno=sc.sno and o=o and cname=SSH and score70;嵌套查询22. 查询“c001”课程比“c002”课程成绩高的所有学生的学号 select sc1.sno from sc sc1 where sc1.score(select sc2.score from sc sc2 where cno=c002 and sc1.sno=sc2.sno) and cno=c001;23. 查询学过c001并且也学过编号c002课程的同学的学号、姓名 select s.sno, s.sname from student2 s, scwhe
22、re s.sno=sc.sno and o=c001 and sc.sno in (select sno from sc where cno=c002);24. 查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名 select distinct s.sno, s.sname from student2 s, sc where s.sno=sc.sno and cno=any(select cno from sc where sno=s001) and s.sno!=s00125. 查询所有课程成绩小于60分的同学的学号、姓名（提示：不在60分以上的学生中） select s.
23、sno,sname from student2 s join sc on s.sno=sc.sno where s.sno not in (select distinct sno from sc where score=60);26. 查询没学过洪燕老师讲授的任一门课程的学生姓名select sname from student2 where sno not in ( select sno from sc, course2 c, teacher2 t where o=o and t.tno=c.tno and tname=洪燕);27. 查询没学过“洪燕”老师课的同学的学号、姓名select
24、sno,sname from student2 where sno not in (select distinct s.sno from student2 s,teacher2 t, course2 c, sc where s.sno=sc.sno and t.tno=c.tno and o=o and tname=洪燕)；28. 查询出只选修了一门课程的全部学生的学号和姓名select sno,sname from student2 where sno in (select sno from sc group by sno having count(cno)=1);29. 查询平均成绩大于8
25、0的所有学生的学号、姓名和平均成绩select s1.sno,s1.sname,s2.avg from student2 s1, (select sno,avg(score) avg from sc group by sno having avg(score)80) s2where s1.sno=s2.sno;30. 查询没有学全所有课的同学的学号、姓名 select s.sno,sname from student2 s where sno not in ( select sno from sc group by sno having count(cno)=(select count(cno
26、) from course2);31. 查询两门以上不及格课程的同学的学号及其平均成绩select sc.sno, avg(score) from sc where sno in (select count(cno) from sc where score60 group by sno)group by sno 32. 查询两门以上不及格课程的同学的学号及其平均成绩select sc.sno, avg(score) from sc where sno in (select count(cno) from sc where score60 group by sno)group by sno 33
27、. 查询不同老师所教不同课程平均分从高到低显示 select t.tname, ame, avg from (select c.tno,o,avg(score) avg from course2 c,sc where o=o group by c.tno,o order by avg(score) desc) c2, teacher2 t, course2 cwhere t.tno=c.tno and o=o;34. 查询全部学生都选修的课程的课程号和课程名 select cid,cname from course where cid in (select cid from choose_co
28、urse group by cid having count(distinct sid)=(select count(distinct sid) from student)35. 查询和s002号的同学学习的课程完全相同的其他同学学号和姓名(有s001学习的课程，并且课程数相同) select s.sno, s.sname from student2 s, sc where sc.sno=s.sno and o in (select cno from sc where sno=s002) and sc.sno in (select sno from sc where sno!=s002 group by sno havi
