数据库三个表
学生表Student(SNo, SName, SAvg)
分数表Grade(CNo, SNo, CScore)
课程表Course(CNo, CName)
1: 查询学生的平均分，并且更新到Student的SAvg中
1: update Student set SAvg=us.avg
2:
3: from
4:
5: (
6:
7: select s.SNo,AVG(g.CScore) as avg from
8:
9: Student s left join Grade g
10:
11: on s.SNo=g.SNo
12:
13: group by s.SNo,s.SName
14:
15: ) us
16:
17: where Student.SNo=us.SNo
执行结果：
2：查询'语文'成绩比'数学'成绩高的所有学生的学生编号，学生姓名
1: select SNo as 学生编号,SName as 学生姓名 from Student where SNo in
2:
3: (
4:
5: select a.SNo from
6:
7: (select SNo,CScore from Grade g left join Course c on g.CNo=c.CNo where CName='语文') a,
8:
9: (select SNo,CScore from Grade g left join Course c on g.CNo=c.CNo where CName='数学') b
10:
11: where a.CScore>b.CScore and a.SNo=b.SNo
12:
13: group by a.SNo
14:
15: )
3：查询每个学生的选课数，及格率(及格的课程数\选课数)
1: select t.SNo as 学生编号,s.SName as 学生姓名,s.SAvg as 平均分,t.及格门数,t.总门数,t.及格门数/(t.总门数+0.0) as 及格率 from
2:
3: (
4:
5: select SNo,COUNT(CNo) as 总门数,
6:
7: sum(
8:
9: case
10:
11: when CScore>=60 then 1
12:
13: when CScore<60 then 0
14:
15: end
16:
17: ) as 及格门数
18:
19: from Grade group by SNo
20:
21: ) t left join Student s on t.SNo=s.SNo
4: 查询“语文”和“数学”都及格的学生的编号和姓名
1: select SNo as 学生编号,SName as 学生姓名 from Student where SNo in
2:
3: (
4:
5: select SNo from Grade g left join Course c on g.CNo=c.CNo where CName='语文' and CScore>60
6:
7: and exists
8:
9: (select SNo from Grade g left join Course c on g.CNo=c.CNo where CName='数学' and CScore>60)
10:
11: )
5：去掉最高分和最低分算平均成绩，如果少于三门直接算平均数
1: select SNo as 学生编号, (tb.sm-tb.mx-tb.mn)/(tb.ct-2) as 平均分 from
2:
3: (
4:
5: select SNo,COUNT(CScore) as ct, SUM(CScore) as sm,MAX(CScore) as mx,MIN(CScore) as mn
6:
7: from Grade where SNo in
8:
9: (
10:
11: select SNo from Grade group by SNo having Count(CNo)>=3
12:
13: ) group by SNo
14:
15: ) tb
16:
17: union
18:
19: select SNo, AVG(CScore) from Grade group by SNo having count(CScore)<3
根据需要我又添加了一条数据王五，只选修语文和数学
5：查询每门功课成绩最好的前两名
1: select SNo as 学生编号,CNo as 课程编号,CScore as 分数 from Grade g1
2:
3: where CScore in
4:
5: (
6:
7: select top 2 CScore FROM Grade WHERE g1.CNo=CNo ORDER BY CScore DESC
8:
9: )
10:
11: order by g1.CNo