题目一、
1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select t1.sid from sc t1 ,sc t2 where t1.sid=t2.sid and t1.cid='001' and t2.cid='002' and t1.score >t2.score;
--扩展查询：
1a、查询“001”课程比“002”课程成绩高的所有学生的详细信息（包含学号、姓名、年龄、性别）；
select t3.sid as 学号, t3.sname as 姓名,t3.sage as 年龄,t3.ssex as 性别 from student t3, (select t1.sid as stuid from sc t1 ,sc t2 where t1.sid=t2.sid and t1.cid='001' and t2.cid='002' and t1.score >t2.score) a where a.stuid=t3.sid;
1b、统计各个课程的平均分并按课程编号升序；
select t.cid as 课程编号, round(avg(t.score)) as 课程平均分 from sc t group by t.cid order by t.cid asc ;1c、统计各个同学的平均成绩 并按照学号升序排列
select t.sid as 学号, round(avg(t.score)) as 学生平均分 from sc t group by t.sid order by t.sid asc;1d、统计各个同学的平均成绩 、并显示学生的详细信息 姓名 年龄 性别 --按照学生的平均成绩排列
--思路 将以上1c查到的结果看成临时表
select t2.sid as 学号 ,t2.sname as 姓名 ,t2.sage as 年龄 , t2.ssex as 性别 ,a.myavg as 平均分 from student t2, (select t1.sid as sidno ,round(avg(t1.score)) as myavg from sc t1 group by t1.sid) a where t2.sid= a.sidno order by a.myavg ;