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<c.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="张三" ))