sql计算机成绩等于60分,sql语法练习

最近项目有用到，相关的点复习下。  例子都是摘抄的网上一些比较好的。

前置条件

四张表学生表 Student(s_id,s_name,s_sex) ： 学生编号、 姓名、 性别

课程表Class(c_id,c_name,t_id): 课程编号、 课程名称、教师编号

教师表Teacher(t_id，t_name)：教师编号、教师姓名

成绩表Score(s_id, c_id, s_score): 学生编号、课程编号、成绩

1、查询 01课程 比 02课程成绩高的学生信息和课程分数。

select a.*, b.s_score as 01_score,c.s_score as 02_score from student a

join score b on a.s_id = b.s_id and b.c_id='01'

left join score c on a.s_id=c.s_id and a.c_id='02' or c.c_id=NULL

where b.s_score > c.score ;复制代码

select a.* , b.s_score as 01_score, c.s_score as 02_score from student a,score b,score c

where a.s_id =b.s_id

and a.s_id = c.s_id

and b.c_id="01"

and c.c_id="02"

and b.s_score>c.s_score ;复制代码

2、查询 01课程比02课程成绩低的学生信息及课程分数

select a.* , b.s_score as 01_score, c.s_score as 02_score from student a

left join score b on a.s_id = b.s_id and b.c_id='01' or b.c_id=NULL

join score c on a.s_id=c.s_id and c.c_id='02'

where b.s_score

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

select a.s_id,a.s_name, ROUND(AVG(b.s_score),2) as avg_score from Student a

join score b on a.s_id= b.s_id

GROUP BY b.s_id,b.s_name HAVING avg_score >= 60;复制代码

4、查询平均成绩小于60的同学的学生编号、学生姓名和平均成绩  (包含有成绩和无成绩的)

select a.s_id,a.s_name,Round(AVG(b.s_score),2) as avg_score from Student a

join score b on a.s_id = b.s_id

GROUP BY a.s_id,a.s_name HAVING avg_score <60

union

select a.s_id,a.s_name,0 as avg_score from student a

where a.s_id not in (select distinct s_id from socre); //没成绩的

复制代码

distinct 去掉重复的行。

union    前后两个sql语句合并为一个结果返回。  不会去掉重复。

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

select a.s_id,a.s_name, count(b.c_id) as sum_course, sum(b.s_score) as sum_score

from Student a

left join Score b

on a.s_id = b.s_id

GROUP BY a.s_id, b.s_name; 复制代码

6、查询 “李” 姓老师的数量

select count(t.t_id) from Teacher t where t.t_name like '李%';复制代码

7、查询学过 “张三”老师授课同学的信息

select a.* from Student a

left join score b on a.s_id = b.s_id

where b.c_id in

(select c_id from Course c

where t_id=(select t_id from teacher where t_name="张三" )) 复制代码