IT公司面试手册

给出下面三个表的关系如下：S(sid,sname),C(cid,cname,teacher),SC(sid,cid,scgrade)
1.    写出查询语句得到学生课程成绩表，要求有以下字段：sname,cname,teacher,scgrade
select S.sname,C.cname,C.teacher,SC.scgrade from S,C,SC where S.Sid=SC.sid and C.cid=SC.cid
2.    写出查询语句得到每个教师所认学科不极格的学生人数。
select C.teacher,C.cname,count(SC.scgrade) AS NotPassCount from C,SC
where C.cid=SC.cid and SC.scgrade<60
GROUP By C.teacher,C.cname
order by C.teacher,cname
3.    写出查询语句得到学生课程成绩表，要求有以下字段：sname,cname,teacher,等级。要求等级有四等，＞＝90为A等，＞＝80为B等，＞＝60为C等，＜60为D等。

select S.sname,C.cname,C.teacher,grade=
case
when SC.scgrade>=90 then ‘A’
when SC.scgrade>=80 then ‘B’
when SC.scgrade>=60 then ‘C’
when SC.scgrade<60 then ‘D’
end
from S,C,SC
where S.Sid=SC.sid and C.cid=SC.cid
4.    写出查询语句得到每个教师所认学科中，为A等和B等的学生人数。
第一种：查询语句得到每个教师所认所有学科的，为A等和B等的学生人数。
select A.teacher,M1.gradeCount_A ,M2.gradeCount_B from
(select distinct C.teacher from C)
as A
join
(select C.teacher,count(SC.scgrade)as gradeCount_A
from S,C,SC
where S.Sid=SC.sid and C.cid=SC.cid and SC.scgrade>=90
group by C.teacher
) as M1 on M1.teacher=A.teacher
join
(select C.teacher,count(SC.scgrade)as gradeCount_B
from S,C,SC
where S.Sid=SC.sid and C.cid=SC.cid and SC.scgrade>=80 and SC.scgrade<90
group by C.teacher
) as M2
on M2.teacher=A.teacher
第二种：查询语句得到每个教师所认每个学科的，为A等和B等的学生人数。
select A.teacher,A.cname,M1.gradeCount_A ,M2.gradeCount_B from
(select distinct C.teacher,C.cname from C)
as A
left join
(select C.teacher,C.cname,count(SC.scgrade)as gradeCount_A
from S,C,SC
where S.Sid=SC.sid and C.cid=SC.cid and SC.scgrade>=90
group by C.teacher,C.cname

) as M1 on M1.teacher=A.teacher and M1.cname=A.cname
left join
(select C.teacher,C.cname,count(SC.scgrade)as gradeCount_B
from S,C,SC
where S.Sid=SC.sid and C.cid=SC.cid and SC.scgrade>=80 and SC.scgrade<90
group by C.teacher,C.cname

) as M2
on M2.teacher=A.teacher and M2.cname=A.cname