以下是三个表:
学生表：Member
MID Char(10) 学生号，主键
MName Char(50) 姓名
课程表：F
FID Char(10) 课程，主键
FName Char(50) 课程名
成绩表：Score
SID int 自动编号，主键，成绩记录号
FID Char(10) 课程号，外键
MID Char(10) 学生号，外键
Score int 成绩
问题:
1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表
姓名 语文 数学 英语 历史
张萨 78 67 89 76
王强 89 67 84 96
李三 70 87 92 56
李四 80 78 97 66
2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。
3) 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。
4) 创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号
1.
select m.mname,yw.score as '语文',sx.score as '数学',yy.score as '英语',ls.score as '历史'
from member m
left outer join (select mid,score from score where fid=(select fid from f where fname='语文')) yw on m.mid=yw.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='数学')) sx on m.mid=sx.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='英语')) yy on m.mid=yy.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='历史')) ls on m.mid=ls.mid
2.
select m.mname,yw.score as '语文',sx.score as '数学',yy.score as '英语',ls.score as '历史'
from member m
left outer join (select mid,score from score where fid=(select fid from f where fname='语文') and score<70) yw on m.mid=yw.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='数学') and score<70) sx on m.mid=sx.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='英语') and score<70) yy on m.mid=yy.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='历史') and score<70) ls on m.mid=ls.mid
3.
select a.mname,b.avgscore from member a
inner join (select mid,avg(score) as avgscore from score group by mid) b on a.mid=b.mid order by b.avgscore desc
4.
create procedure xxxx(@fcount int)
if @foucnt>0
select b.mid,a.mname from member a
inner join (select mid from score group by mid having count(fid)=@fcount) b on a.mid=b.mid
else
select mid,mname from member where mid not in (select mid from score)
select m.mname,yw.score as '语文',sx.score as '数学',yy.score as '英语',ls.score as '历史'
from member m
left outer join (select mid,score from score where fid=(select fid from f where fname='语文')) yw on m.mid=yw.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='数学')) sx on m.mid=sx.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='英语')) yy on m.mid=yy.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='历史')) ls on m.mid=ls.mid
2.
select m.mname,yw.score as '语文',sx.score as '数学',yy.score as '英语',ls.score as '历史'
from member m
left outer join (select mid,score from score where fid=(select fid from f where fname='语文') and score<70) yw on m.mid=yw.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='数学') and score<70) sx on m.mid=sx.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='英语') and score<70) yy on m.mid=yy.mid
left outer join (select mid,score from score where fid=(select fid from f where fname='历史') and score<70) ls on m.mid=ls.mid
3.
select a.mname,b.avgscore from member a
inner join (select mid,avg(score) as avgscore from score group by mid) b on a.mid=b.mid order by b.avgscore desc
4.
create procedure xxxx(@fcount int)
if @foucnt>0
select b.mid,a.mname from member a
inner join (select mid from score group by mid having count(fid)=@fcount) b on a.mid=b.mid
else
select mid,mname from member where mid not in (select mid from score)
温馨提示：答案为网友推荐，仅供参考
