SQL举例（二）

最新推荐文章于 2025-07-19 11:57:49 发布

转载最新推荐文章于 2025-07-19 11:57:49 发布 · 163 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xiao-cheng/archive/2011/10/01/2197737.html

本文详细解析了SQL查询中五个典型问题的解决方案，包括学生姓名、平均成绩、关键词、新标签等，展示了如何通过SQL语句解决实际问题。

S (SNO,SNAME)学生关系。SNO 为学号，SNAME 为姓名

C (CNO,CNAME,CTEACHER)课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

SC(stu_No,cla_No,score)选课关系。score 为成绩

初始化表：

insert into S values("001","zhangsan");
insert into S values("002","padern");
insert into S values("003","wangwu");
insert into S values("004","xiaohong");
insert into S values("005","linda");
insert into S values("006","lucy");
insert into S values("007","lily");
insert into S values("008","jim");
insert into S values("009","Green");
insert into S values("010","Brone");
insert into S values("011","Peng");
insert into S values("012","Chern");
insert into S values("013","Tom");
insert into S values("014","Wei");

insert into sc values("003","1002",83);
insert into sc values("003","1004",56);
insert into sc values("003","1005",92);
insert into sc values("003","1007",91);
insert into sc values("003","1009",60);

insert into sc values("004","1005",91);
insert into sc values("004","1006",90);
insert into sc values("004","1003",92);
insert into sc values("004","1001",76);
insert into sc values("004","1007",88);
insert into sc values("004","1009",82);

insert into sc values("005","1004",91);
insert into sc values("005","1006",56);
insert into sc values("005","1008",54);
insert into sc values("005","1009",67);

insert into sc values("006","1002",91);
insert into sc values("006","1003",89);
insert into sc values("006","1005",88);
insert into sc values("006","1001",78);
insert into sc values("006","1004",91);
insert into sc values("006","1006",100);
insert into sc values("006","1007",80);
insert into sc values("006","1009",53);

insert into sc values("007","1003",91);
insert into sc values("007","1005",80);
insert into sc values("007","1007",95);
insert into sc values("007","1009",66);
insert into sc values("007","1008",71);

insert into sc values("008","1002",95);
insert into sc values("008","1004",90);
insert into sc values("008","1006",95);

insert into sc values("009","1001",90);
insert into sc values("009","1002",99);

insert into sc values("010","1002",92);
insert into sc values("010","1006",96);
insert into sc values("010","1007",91);
insert into sc values("010","1009",70);

insert into sc values("011","1001",91);
insert into sc values("011","1002",80);
insert into sc values("011","1004",75);
insert into sc values("011","1006",76);
insert into sc values("011","1008",41);
insert into sc values("011","1009",50);

insert into sc values("012","1001",90);
insert into sc values("012","1002",91);
insert into sc values("012","1003",75);

insert into sc values("013","1006",66);
insert into sc values("013","1008",41);
insert into sc values("013","1009",50);

insert into sc values("014","1004",81);
insert into sc values("014","1006",86);
insert into sc values("014","1007",91);

insert into C values("1001","英语","赵成");
insert into C values("1002","数学","陈余");
insert into C values("1003","语文","刘邦");
insert into C values("1004","物理","李少芬");
insert into C values("1005","地理","许春");
insert into C values("1006","化学","刘青");
insert into C values("1007","生物","宋江");
insert into C values("1008","政治","杨科夫");
insert into C values("1009","历史","沈库哈");

问题：

1. 找出没有选修过“宋江”老师讲授课程的所有学生姓名

Select SNAME FROM S Where NOT EXISTS( Select * FROM SC,C 
Where SC.cla_No=C.CNO AND CTECHER="宋江" AND SC.stu_No=S.SNO)

2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

错，这个语法只是统计他挂科的那些课程的平均成绩

select SNAME,avgscore from S,
(select stu_No,AVG(score) as avgscore from sc
where score<60
group by stu_No
having count(distinct cla_No)>=2) b
where S.SNO=b.stu_No

select SNAME, SNO, AVG(score) from S,SC
where S.SNO in
(select stu_No from sc
where score<60
group by stu_No
having count(distinct cla_No)>=2)
and S.SNO=SC.stu_No
group by stu_No

select SNAME,SNO,AVG(SC.score) from S,SC,
(select stu_No from sc
where score<60
group by stu_No
having count(distinct cla_No)>=2) b
where S.SNO=SC.stu_No and b.stu_No=S.SNO
group by SNO

3. 列出既学过“1001”号课程，又学过“1002”号课程的所有学生姓名

select SNAME from S,
(select stu_No from SC
where cla_No in ("1001","1002")
group by stu_No having count(distinct cla_No)=2) b
where S.SNO=b.stu_No

select SNAME from S
where S.SNO in
(select stu_No from SC
where cla_No in ("1001","1002")
group by stu_No having count(distinct cla_No)=2)

4. 列出“1001”号课成绩比“002”号同学该门课成绩高的所有学生的学号

select stu_No from sc a
where a.score>
(select score from sc
where sc.stu_No="002" and sc.cla_No="1001")
and a.cla_No="1001"

5. 列出“1001”号课成绩比“1002”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

select sc1.stu_No,sc1.score cla1001,sc2.score cla1002 from SC sc1,SC sc2
where sc1.stu_No=sc2.stu_No
and sc1.score>sc2.score and sc1.cla_No="1001"
and sc2.cla_No="1002"

转载于:https://www.cnblogs.com/xiao-cheng/archive/2011/10/01/2197737.html