爱站程序员基地— 20. 查询出只选修两门课程的学生学号和姓名
[code]select * from studentwhere sid in(select sidfrom scoresgroup by sidhaving count(cid) = 2);
其中：书写顺序是 select from where group having order limit;
执行顺序是 from where group select having order limit
— 21. 查询男生、女生人数
[code]select count(ssex) from studentgroup by ssex;-- 换种方式selectsum(case when ssex = \'男\' then 1 else 0 end) as \'男\',sum(case when ssex = \'女\' then 1 else 0 end) as \'女\'from student;
— 22. 查询名字中含有「风」字的学生信息
[code]select * from studentwhere sname like \'%风%\';
— 23. 查询同名同性学生名单，并统计同名人数
[code]select A.*,B.同名人数 from student Aleft join (select sname,ssex,COUNT(*)同名人数 from student group by sname,ssex)Bon A.sname=B.sname and A.ssex=B.ssexwhere B.同名人数>1;-- 先查同名同性的人数select sname, count(sname) as numfrom studentgroup by sname;-- 再查相关信息select s1.*, num from student as s1 left join(select sname, ssex, count(sname) as numfrom studentgroup by sname) as s2on s1.sname = s2.sname and s1.ssex = s2.ssexwhere num > 1;
— 24. 查询 1990 年出生的学生名单
[code]select * from studentwhere year(sage)=\'1990\';
— 25. 查询每门课程的平均成绩，结果按平均成绩降序排列，
— 平均成绩相同时，按课程编号升序排列
[code]select cid, avg(score) as avg_sc from scoresgroup by cidorder by avg_sc desc, cid asc;
— 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
— 查询平均成绩大于85的sid
[code]select s1.sid, s1.sname, avg_scfrom student as s1 inner join(select sid, avg(score) as avg_sc from scoresgroup by sidhaving avg_sc >= 85) as s2on s1.sid = s2.sid;
— 27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
[code]-- 先查询数学低于60的sidselect sid, score, cnamefrom scores as sc inner join course as coon sc.cid = co.cidwhere score < 60 and cname = \'数学\';-- 学生信息select s1.sname, score, cnamefrom student as s1 inner join(select sid, score, cnamefrom scores as sc inner join course as coon sc.cid = co.cidwhere score < 60 and cname = \'数学\') as s2on s1.sid = s2.sid;
— 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
[code]select sname, co.cname, score from(select * from student as st left join scores as scon st.sid = sc.sid) as templeft join course as coon co.cid = temp.cid;
— 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
[code]select sname, cname, scorefrom scores as sc inner join course as coon sc.cid = co.cidinner join student as ston sc.sid = st.sidwhere score >= 70;
