sql操作实例（采用的oracle 11g数据库）

1、建表

课程表（主键CID+TID）

成绩表（主键SID+CID）

学生表（主键SID）

老师表（主键TID）

至于外键的关联，大家从上面的图上应该很容易得出。

完成下列任务：

1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select s2.sid, s2.course1, s2.course2 from(select s1.sid, SUM(s1.course1) course1, SUM(s1.course2) course2 from(select sid,
 case when cid = '001' then score
 else 0
 end course1,
 case when cid = '002' then score
 else 0
 end course2
from sc) s1 group by sid) s2 where s2.course1 > s2.course2;

2、查询平均成绩大于60分的同学的学号和平均成绩；
select sid as "学号" , avg(score) as "平均成绩" from sc group by sid having avg(score) > 60;

3、查询所有同学的学号、姓名、选课数、总成绩；
select s.sid as "学号", s.sname as "姓名", c.coursenum as "选课数", c.totalscore as "总成绩"
 from student s, (select sid, count(cid) coursenum, sum(score) totalscore from sc group by sid) c
 where s.sid = c.sid;

4、查询姓“李”的老师的个数；
select count(tid) as "姓李的老师的个数" from teacher where tname like '李%';

5、查询没学过“叶平”老师课的同学的学号、姓名；
select s.sid as "学号", s.sname as "姓名" from student s where s.sid not in
(select sid from sc where cid in (
        select cid from teacher, course where teacher.tid = course.cid and tname = '叶平'
));

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select sid as "学号", sname as "姓名" from student where exists
(select sid from sc where sc.cid in (001, 002) and student.sid = sc.sid group by sc.sid );

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名
select sid as "学号", sname as "姓名" from student where sid in
(select sid from sc where cid in
(select cid from course, teacher where course.tid=teacher.tid and tname='叶平'));
8、查询所有课程成绩小于60分的同学的学号、姓名；
select sid as "学号", sname as "姓名" from student where exists
(select s1.sid from sc s1 where not exists
(select sid from sc s2 where s2.score>60 and s1.sid=s2.sid) and s1.sid=student.sid);

9、查询没有学全所有课的同学的学号、姓名；
select sid as "学号", sname as "姓名" from student where not exists
(select sid from (select sid, count(sid) snum from sc group by sid) k
where snum = (select count(cid) from course) and k.sid=student.sid) order by student.sid;

10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select student.sid as "学号", student.sname as "姓名" from student, (select distinct sid from sc where cid in
 (select cid from sc where sid=1001) and sid !=1001) k
 where k.sid=student.sid;

11、删除学习“叶平”老师课的SC表记录；
delete from sc where cid in (select cid from course where tid=(select tid from teacher where tname='叶平'));
12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select sc.cid as "课程ID", max(score) as "最高分", min(score) as "最低分" from course, sc
where sc.cid=course.cid group by sc.cid;

13、查询学生平均成绩及其名次
select sid, avg(score) from sc group by sid order by avg(score) asc; 

14、查询各科成绩前三名的记录(不考虑成绩并列情况)
select s1.sid, s1.cid, s1.score from sc s1 left join sc s2 on s1.cid=s2.cid
and s1.score<s2.score group by s1.sid, s1.cid, s1.score having count(s2.sid)<3
order by s1.cid, s1.score desc;

15、查询每门功成绩最好的前两名
select s1.sid, s1.cid, s1.score from sc s1 left join sc s2 on s1.cid=s2.cid
and s1.score<s2.score group by s1.sid, s1.cid, s1.score having count(s2.sid)<2
order by s1.cid, s1.score desc;