sql练习题及总结

https://mp.weixin.qq.com/s/VNZr6FhtKW_kXkjZ7W89aQ

1、

3、

SELECT AVG(s.s_score) AS avg_score,st.s_id,st.s_name FROM student st ,score s  GROUP BY st.s_id HAVING avg_score>60 WHERE st.s_id=s.s_id

这种写法语法报错，应该是先查询where条件再 group by ，聚合函数出问题。要么就是join的时候把表关联关系写在on 后面。

ROUND(AVG(a.s_score),2) ，round加上了取2位小数

4、和无成绩的，使用union连接2个结果集，在分数中没有结果的即为0分的，参加考试distinct去重后的s_id不在里面的为没有参加考试的

UNION
SELECT 0,a.s_id,a.s_name AS avg_score FROM
student a
WHERE a.s_id NOT IN (
SELECT DISTINCT s_id FROM score);

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,a.s_name;

看似没有分组，实际按学生来统计的来分组的

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

我SELECT st.* FROM student st JOIN score s ON st.`s_id`=s.`s_id` JOIN course c ON c.`c_id`= s.`c_id` JOIN teacher t ON t.t_id=c.t_id
WHERE t.t_name='张三' ，四张表连起来感觉也没什么问题。

8、查询没学过"张三"老师授课的同学的信息 

select a.* from 

student a 

join score b on a.s_id=b.s_id where b.c_id not in(

select c_id from course where t_id =(

select t_id from teacher where t_name = '张三'));  not in这个地方位置写法错误，A可能上过01、02、03，not in 02，那A还是有可能被筛选出来

 

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

11、查询没有学全所有课程的同学的信息 

--@wendiepei的写法

select s.* from student s 

left join Score s1 on s1.s_id=s.s_id

group by s.s_id having count(s1.c_id)<(select count(*) from course)。 分组后聚合的条件如count(s1.c_id)可以不放在select查询那里，直接作为having的条件比较

 

30、查询同名同性学生名单，并统计同名人数

select a.s_name,a.s_sex,count(*) from student a  JOIN 

student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex

GROUP BY a.s_name,a.s_sex

同名同姓显然是相同的2张表对比，自连接