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;