50道sql练习

查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

1. 需要全部的学生信息
2. 从score表中查出01课程的SId和score，注意不要忘了给score起别名                  整体给结果集起别名
3. 从score表中查出02课程的SId和score，注意不要忘了给score起另一个别名       整体给结果集起别名
4. 两表关联查询出符合条件的SId,和该同学对应的课程分数（你起的别名）
5. 与外表关联查询出全部信息

select * from Student RIGHT JOIN (
    select t1.SId, class1, class2 from
          (select SId, score as class1 from sc where sc.CId = '01')as t1, 
          (select SId, score as class2 from sc where sc.CId = '02')as t2
    where t1.SId = t2.SId AND t1.class1 > t2.class2
)r 
on Student.SId = r.SId;

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

 可以将此句作为子查询 关联 student表 查询用户全部信息

select * from 
    (select * from sc where sc.CId = '01') as t1, 
    (select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

    子查询查询出选了01课程的sid（做条件）

 

select * from sc
  where sc.SId not in (
      select SId from sc 
      where sc.CId = '01'
       ) 
   AND sc.CId= '02';

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select student.SId,sname,ss from student,(
    select SId, AVG(score) as ss from sc  
    GROUP BY SId 
    HAVING AVG(score)> 60
    )r
where student.sid = r.sid;

select Student.SId, Student.Sname, r.ss from Student right join(
      select SId, AVG(score) AS ss from sc
      GROUP BY SId
      HAVING AVG(score)> 60
)r on Student.SId = r.SId;

注意左连接或右连接的时候 条件用on

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

  子查询从成绩表中查询出  选课总数，学生编号，成绩总和           不要忘了起别名

  两表关联查询

select student.sid, student.sname, r.coursenumber, r.scoresum
from student,
  (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc 
   group by sc.sid)r
where student.sid = r.sid;

18.查询各科成绩前三名的记录
大坑比。mysql不能group by 了以后取limit。

计算比自己分数大的记录有几条，如果小于3 就select，因为对前三名来说不会有3个及以上的分数比自己大了，最后再对所有select到的结果按照分数和课程编号排名即可。

select * from sc
where (
select count(*) from sc as a 
where sc.cid = a.cid and sc.score<a.score and 课程号=‘’
)< 3
order by cid asc, sc.score desc;

21.查询男生、女生人数

select ssex, count(*) from student
group by ssex;