My sql from子查询 师生系统题

mysql C级 from子查询师生系统题

mysql C级 from子查询师生系统题

1、查询“001”课程比“002”课程成绩高的所有学生的学号；
2、查询学生平均成绩及其名次
3、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
4、按各科平均成绩从低到高和及格率的百分数从高到低顺序
5、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,数据库,企业管理,英语,有效课程数,有效平均分
1.
方法一：
select s1.sid from sc s1,sc s2 where s1.sid=s2.sid and s1.cid=1 and s2.cid=2 and s1.score>s2.score;
方法二：
select s1.sid from (select score,sid from sc where cid=1) s1 inner join (select score,sid from sc where cid=2) s2 on s1.sid=s2.sid and s1.score>s2.score;
2.
select b.平均分,1+(
select count(1) from (select avg(score) 平均 from sc group by sid) a where a.平均>b.平均分
) from (select avg(score) 平均分 from sc group by sid) b order by b.平均分 desc;
3.
方法一：
select s1.sid,s.sname from sc s1,sc s2,student s where s1.sid=s2.sid and s1.sid=s.sid and s1.cid=1 and s2.cid=2 and s1.score>s2.score;
方法二：
select a.sid,s.sname from (select s1.sid,s1.score from
(select sid,score from sc where cid=1) s1,
(select sid,score from sc where cid=2) s2 where s1.sid=s2.sid and s1.score>s2.score)
a inner join student s on s.sid=a.sid;
4.
select avg(score) 平均成绩,100*(sum(case when score>=60 then 1 or 0 end)/count(1)) 及格率
from sc group by cid order by 平均成绩,及格率 desc;
5.
select s1.sid,
(select score from sc s2,course c where s2.sid=s1.sid and s2.cid=c.cid and c.cname=“数据库”) 数据库,
(select score from sc s2, course c where s2.sid=s1.sid and s2.cid=c.cid and c.cname=“企业管理”) 企业管理,
(select score from sc s2,course c where s2.sid=s1.sid and c.cid=s2.cid and c.cname=“英语”) 英语,
count(1) 有效课程数,
avg(score) 平均数
from sc s1 group by s1.sid order by 平均数 desc;