网上流行的学生选课表的例子

Student(Sid,Sname,Sage,Ssex) 学生表

create table student (sid int(5),sname varchar(10),sage int(3),ssex varchar(5));
insert into student values(1,'韩梅梅','26', '女');
insert into student values(2,'李磊','27','男');
insert into student values(3,'林涛','27','男');
insert into student values(4,'吉姆','24','男');


Course(Cid,Cname,Tid) 课程表
create table course (cid int (5),cname varchar(15),tid int (5));
insert into course values (111,'语文',201);
insert into course values (222,'数学',301);
insert into course values (333,'英语',401);
insert into course values (444,'物理',201);


SC(Sid,Cid,score) 成绩表
create table sc (sid int (5),cid int(5),score int(5));
insert into sc values (1,111,80);
insert into sc values (1,222,90);
insert into sc values (1,333,80);
insert into sc values (1,444,90);
insert into sc values (2,111,80);
insert into sc values (2,222,70);
insert into sc values (3,111,80);
insert into sc values (3,222,60);
insert into sc values (3,333,80);


Teacher(Tid,Tname) 教师表
create table teacher (tid int(5),tname varchar(10));
insert into teacher values(201,'张三');
insert into teacher values(301,'李四');
insert into teacher values(401,'王五');
insert into teacher values(501,'李奎');


select a.sid from sc a,sc b where a.sid=b.sid and a.cid=111 and b.cid=222 and a.score>b.score;

1、查询“111”课程比“222”课程成绩高的所有学生的学号;
① select a.S# from (select sid,score from SC where cid=111) a,(select sid,score
from SC where cid=222) b
where a.score>b.score and a.s#=b.s#;

②select a.sid from sc a ,sc b where a.sid=b.sid and a.cid=111 and b.cid=222 and a.score>b.score;

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

3、查询所有同学的学号、姓名、选课数、总成绩;//注意是所有同学,所以需要外连接
① select a.sid,a.sname ,count(cid) ,sum(score) from student a left outer join sc b on a.sid=b.sid group by a.sid,a.sname; //吉姆不好好学习，没选课

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

5、查询没学过“张三”老师课的同学的学号、姓名;
① select a.sid,a.sname from student a where sid not in(
select sid from sc b where b.cid in(
select c.cid from course c,teacher d where c.tid=d.tid and d.tname='张三'));

② select sid,sname from student where sid not in( select a.sid from sc a ,course b, teacher c where a.cid=b.cid and b.tid=c.tid and c.tname='张三');

6、查询学过“111”并且也学过编号“222”课程的同学的学号、姓名;

① select c.sid,c.sname from sc a ,sc b,student c where a.sid=b.sid and b.sid=c.sid and a.cid='111' and b.cid='222';
② select b.sid,b.sname from sc a,student b where a.sid=b.sid and a.cid=111 and exists (select 1 from sc c where a.sid=c.sid and c.cid=222);

7、查询学过“张三”老师所教的所有课的同学的学号、姓名; //有难度
① select * from student where sid in(
select sc.sid from sc , course ,teacher where sc.cid=course.cid and course.tid=teacher.tid and teacher.tname='张三' group by sid having count(sc.cid)=
(select count(1) from course,teacher where course.tid=teacher.tid and teacher.tname='张三'));

8、查询课程编号“222”的成绩比课程编号“111”课程低的所有同学的学号、姓名;
① select c.sid,c.sname from
(select sid,score from sc where cid=222)a, (select sid,score from sc where cid=111) b ,student c
where a.sid=b.sid and b.sid=c.sid and a.score<b.score ;
② select a.sid,a.sname from student a,sc b,sc c where b.cid=222 and c.cid=111 and a.sid=b.sid and b.sid=c.sid and b.score>c.score;

9、查询所有课程成绩小于85分的同学的学号、姓名;
① select sid,sname from student where sid not in( select distinct(sid) from sc where sc.score >=85);

10、查询没有学全所有课的同学的学号、姓名;
① select sid,sname from student where sid in(
select sid from sc group by sid having count(cid)<(select count(1) from course ));
② select student.sid,sname from student,sc where student.sid=sc.sid group by student.sid,sname having count(cid)<(select count(1) from course);

11、查询至少有一门课与学号为“3”的同学所学相同的同学的学号和姓名;
select sid,sname from student where sid in(
select sid from sc where cid in(
select cid from sc where sid=3)
)
12、查询至少学过学号为“1”同学所有一门课的其他同学学号和姓名;
①select distinct a.sid,a.sname from student a,sc b where a.sid=b.sid and b.cid in(select cid from sc where sid='1');

13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩; //不知道怎么做

14、查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名;
① select sc.sid from sc where cid in (select cid from sc where sid=1) group by sid having count(cid)=(select count(1) from sc where sid=1);

15、删除学习“张三”老师课的SC表记录;
① delete from sc where cid in(
select b.cid from course b,teacher c where b.tid=c.tid and c.tname='张三');