day21_task

a.查询男生、女生人数

涉及的表student

select * from student;
select distinct sgender as sex, count(sgender) as nums from student
group by sgender desc ;

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

涉及的表：student

select * from student
where sname like ‘%风%’;

c.查询同名同姓的学生名单，并统计同名的人数

涉及的表：student

select sname, count(sname) as nums from student
group by sname
having nums > 1;

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

涉及的表：sc、course

select s.cid, c.cname, avg(score) as avg_score from sc as s
join course as c on s.cid = c.cid
group by cid
order by avg_score desc , s.cid asc;

e.查询平均成绩大于等于 85 的学生信息

涉及的表：sc、student

select * from student as a
join
(
select sid, avg(score) as avg_score from sc
group by sid having avg_score >= 85
) as b
on a.sid = b.sid;

f.查询「数学」不及格的学生信息

涉及的表：student、score

select st.,s., c.* from sc as s
join course as c on s.cid = c.cid
join student as st on st.sid = s.sid
where c.cname = ‘数学’ and s.score < 60;

g.查询有挂科学生的课程信息以及任课老师

涉及的表：sc，course，teacher

select * from teacher;
select * from teacher as t
join course as c on t.tid = c.tid join (
select cid, score from sc
where score < 60) as e on e.cid = c.cid;