MySQL50-9-第31-35题

select *
from Student
where s_birth like '1990%';   -- 模糊匹配

-- 自己的方法
select
	c_id
	,round(avg(s_score),2) avg_score
from Score
group by 1
order by 2 desc, c_id;  -- 指定字段和排序方法

-- 自己的方法
select
	c.c_id
	,c.c_name
	,round(avg(sc.s_score),2) avg_score
from Score sc
join Course c
on sc.c_id = c.c_id
group by 1,2
order by 3 desc, c.c_id;  -- 指定字段和排序方法

-- 自己的方法
select
	sc.s_id
	,s.s_name
	,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id = s.s_id
group by sc.s_id,s.s_name
having avg_score >= 85;

select
	s.s_name
	,sc.s_score
from Score sc   -- 成绩表
join Student s  -- 学生信息表
on sc.s_id = s.s_id
join Course c  -- 课程表，指定数学
on sc.c_id = c.c_id
where c.c_name = '数学'
and sc.s_score < 60;  -- 指定成绩

select 
	s.s_id
	,s.s_name
	,sum(case c.c_name when '语文' then sc.s_score else 0 end) as '语文'  -- 语文分数
	,sum(case c.c_name when '数学' then sc.s_score else 0 end) as '数学'
	,sum(case c.c_name when '英语' then sc.s_score else 0 end) as '英语'
	,sum(sc.s_score) as '总分'  -- 每个人的总分
from Student s
left join Score sc
on s.s_id = sc.s_id
left join Course c 
on sc.c_id = c.c_id
group by s.s_id, s.s_name;   -- 学号和姓名的分组