mysql聚合函数练习

stude(s_id,s_name)，score（s_id,c_id,s_core）,course(c_id,c_name,t_id),teacher(t_id,t_name)

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

select s.*,sc1.s_score as score_01,sc2.s_score as score_02
from student s
inner join (
	select * from score where c_id = 1
) sc1
on s.s_id = sc1.s_id
inner join (
	select * from score where c_id = 2
) sc2
on s.s_id = sc2.s_id
where sc1.s_score > sc2.s_score;

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

select s.*,sc1.s_score as score_01,sc2.s_score as score_02
from student s
inner join (
	select * from score where c_id = 1
) sc1
on s.s_id = sc1.s_id
inner join (
	select * from score where c_id = 2
) sc2
on s.s_id = sc2.s_id
where sc1.s_score < sc2.s_score;

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

select s.s_id,s.s_name,round(avg_score, 2) as avg_score
from student s
inner join (
	select s_id,avg(s_score) as avg_score
	from score
	group by s_id
	having avg_score >= 60
) t1
on s.s_id = t1.s_id;

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

select s.s_id,s.s_name,ifnull(round(avg_score, 2), 0) as avg_score
from student s
left join (
	select s_id,avg(s_score) as avg_score
	from score
	group by s_id
) t1
on s.s_id = t1.s_id
where avg_score is null or avg_score < 60;

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

select s.s_id,s.s_name,ifnull(cnt_course, 0) as cnt_course,ifnull(sum_score, 0) as sum_score
from student s
left join (
	select s_id,count(*) as cnt_course,sum(s_score) as sum_score
	from score
	group by s_id
) t1
on s.s_id = t1.s_id;

查询"李"姓老师的数量

select count(*) as cnt_name_li
from teacher
where t_name like '李%';

学过"张三"老师授课的同学的信息

select s.*
from student s
where s_id in (
	select distinct s_id
	from score sc
	inner join (
		select c_id
		from course c
		inner join teacher t on c.t_id = t.t_id
		where t_name = '张三'
	) t1
	on sc.c_id = t1.c_id
);

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

select s.*
from student s
where s_id not in (
	select distinct s_id
	from score sc
	inner join (
		select c_id
		from course c
		inner join teacher t on c.t_id = t.t_id
		where t_name = '张三'
	) t1
	on sc.c_id = t1.c_id
);

查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select s.*
from student s
where s_id in (
	select s_id
	from score
	where c_id in (1,2)
	group by s_id
	having count(*) = 2
);

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

select s.*
from student s
where s_id in (
	select s_id
	from score
	where c_id = 1
	and s_id not in (
		select s_id from score where c_id = 2
	)
);

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

select *
from student
where s_id not in (
	select s_id
	from score
	group by s_id
	having count(*) = (
		select count(*) from course
	)
);

查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select *
from student
where s_id in (
	select distinct s_id
	from score s
	inner join (
		select c_id from score where s_id = 1
	) t1
	on s.c_id = t1.c_id
);

查询和"01"号的同学学习的课程完全相同的其他同学的信息

select *
from student
where s_id in (
	 select s_id
	 from score s
	 inner join (
		select c_id from score where s_id = 1
	 ) t1
	 on s.c_id = t1.c_id
	 where s_id != 1
	 group by s_id having count(*) = (
		select count(*) from score where s_id = 1
	 )
);

查询没学过"张三"老师讲授的任一门课程的学生姓名

select s_name
from student
where s_id not in (
	select s_id
	from score sc
	inner join (
		select c_id
		from course c
		inner join teacher t on c.t_id = t.t_id
		where t_name = '张三'
	) t1
	on sc.c_id = t1.c_id
);

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.s_id,s.s_name,round(avg(s_score), 2) as avg_score
from student s
inner join (
	select s_id
	from score
	where s_score < 60
	group by s_id
	having count(*) >= 2
) t1
on s.s_id = t1.s_id
inner join score sc on s.s_id = sc.s_id
group by s.s_id;

检索"01"课程分数小于60，按分数降序排列的学生信息

select s.*,s_score as score_01
from student s
inner join (
	select s_id,s_score
	from score
	where c_id = 1
	and s_score < 60
) t1
on s.s_id = t1.s_id
order by score_01 desc;

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT 
    s.s_id AS '学号',
    s.s_name AS '姓名',
    SUM(IF(sc.c_id = 1, sc.s_score, 0)) AS '语文',
    SUM(IF(sc.c_id = 2, sc.s_score, 0)) AS '数学',
    SUM(IF(sc.c_id = 3, sc.s_score, 0)) AS '英语',
    ROUND(AVG(sc.s_score), 2) AS '平均成绩'
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
ORDER BY '平均成绩' DESC;

查询各科成绩最高分、最低分和平均分，以如下形式显示：

课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select
    c.c_id as 课程ID,c.c_name as 课程name,
	max(s_score) as 最高分,
	min(s_score) as 最低分,
	round(avg(s_score), 2) as 平均分,
	concat(round(sum(case when s_score >= 60 then 1 else 0 end) / count(*) * 100, 2), '%') as 及格率,
	concat(round(sum(case when s_score between 70 and 80 then 1 else 0 end) / count(*) * 100, 2), '%') as 中等率,
	concat(round(sum(case when s_score between 80 and 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优良率,
	concat(round(sum(case when s_score >= 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优秀率
from course c
inner join score s on c.c_id = s.c_id
group by c.c_id;