Mysql练习

目录

提前准备的表

1. 学生表

2. 教师表

3. 课程表

4. 成绩表

练习开始

1. 查询1号课程比2号课程成绩高的所有学生的学号；

2. 查询平均成绩大于60分的同学的学号和平均成绩并从高到低降序排序； 

3. 查询所有同学的学号、姓名、选课数、总成绩，并按总成绩降序排序；

4. 查询姓"黄"的老师的个数；

5.查询选修"林老师"课的同学的学号、姓名；

---

提前准备的表

1. 学生表

2. 教师表

3. 课程表

4. 成绩表

练习开始

1. 查询1号课程比2号课程成绩高的所有学生的学号；

select a.S_id
from 
(select score , S_id from Score where C_id = 1)  a,
(select score , S_id from Score where C_id = 2)  b
where
a.score > b.score and a.S_id = b.S_id;

 

2. 查询平均成绩大于60分的同学的学号和平均成绩并从高到低降序排序； 

 select S_id,AVG(score) as AvgScore 
 from Score
 group by S_id
 having AvgScore>60
 order by AvgScore desc;

3. 查询所有同学的学号、姓名、选课数、总成绩，并按总成绩降序排序；

 select s.S_id , s.Sname , count(c.C_id) as courseCount , sum(c.score) as sumScore
 from
 Student as s left join Score as c
 on s.S_id = c.S_id
 group by s.Sname
 order by sumScore desc;

4. 查询姓"黄"的老师的个数；

 select count(T_id) as Count
 from Teacher
 where Tname like '黄%'

5.查询选修"林老师"课的同学的学号、姓名；

 select s.S_id as id,s.Sname as name
 from
 Student as s,
 Course as c,
 Score as sc,
 (select T_id from Teacher where Tname = '林老师') as t
 where
 c.T_id = t.T_id and s.S_id = sc.S_id and sc.C_id = c.C_id;

待更新.....

原帖传送门：点击前往