MySQL经典练习题（六）

-- 20. 查询出只选修两门课程的学生学号和姓名

select * from student
where sid in
	(select sid
	from scores
	group by sid
	having count(cid) = 2);

其中：书写顺序是 select from where group having order limit;
执行顺序是 from where group select having order limit

-- 21. 查询男生、女生人数

select count(ssex) from student
group by ssex;
-- 换种方式
select 
	sum(case when ssex = '男' then 1 else 0 end) as '男',
	sum(case when ssex = '女' then 1 else 0 end) as '女'
from student;

-- 22. 查询名字中含有「风」字的学生信息

select * from student
where sname like '%风%';

-- 23. 查询同名同性学生名单，并统计同名人数

select A.*,B.同名人数 from student A
left join (select sname,ssex,COUNT(*)同名人数 from student group by sname,ssex)B 
on A.sname=B.sname and A.ssex=B.ssex
where B.同名人数>1;
-- 先查同名同性的人数
select sname, count(sname) as num
from student
group by sname;
-- 再查相关信息
select s1.*, num from student as s1 left join
	(select sname, ssex, count(sname) as num
	from student
	group by sname) as s2
on s1.sname = s2.sname and s1.ssex = s2.ssex
where num > 1;

-- 24. 查询 1990 年出生的学生名单

select * from student
where year(sage)='1990';

-- 25. 查询每门课程的平均成绩，结果按平均成绩降序排列，
-- 平均成绩相同时，按课程编号升序排列

select cid, avg(score) as avg_sc from scores
group by cid
order by avg_sc desc, cid asc;

-- 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 

-- 查询平均成绩大于85的sid

select s1.sid, s1.sname, avg_sc
from student as s1 inner join
	(select sid, avg(score) as avg_sc from scores
	group by sid
	having avg_sc >= 85) as s2
on s1.sid = s2.sid;

-- 27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

-- 先查询数学低于60的sid
select sid, score, cname 
from scores as sc inner join course as co
on sc.cid = co.cid
where score < 60 and cname = '数学';
-- 学生信息
select s1.sname, score, cname
from student as s1 inner join
	(select sid, score, cname 
	from scores as sc inner join course as co
	on sc.cid = co.cid
	where score < 60 and cname = '数学') as s2
on s1.sid = s2.sid;

-- 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select sname, co.cname, score from 
	(select * from student as st left join scores as sc 
	on st.sid = sc.sid) as temp 
left join course as co 
on co.cid = temp.cid;

-- 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

select sname, cname, score
from scores as sc inner join course as co
on sc.cid = co.cid
inner join student as st
on sc.sid = st.sid
where score >= 70;