MySQL查询练习--答案3

21、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT * FROM
(SELECT sno,cno,score,row_number()over(PARTITION BY cno ORDER BY score DESC) rn FROM sc)
WHERE rn<4

22、查询每门课程被选修的学生数

SELECT cno,COUNT(sno)FROM sc GROUP BY cno;

23、查询出只选修了一门课程的全部学生的学号和姓名

SELECT sc.sno,st.sname,COUNT(cno) FROM student st
LEFT JOIN sc
ON sc.sno=st.sno
GROUP BY st.sname,sc.sno HAVING COUNT(cno)=1;

24、查询男生、女生人数

SELECT ssex,COUNT(*)FROM student GROUP BY ssex;

25、查询姓“张”的学生名单

SELECT * FROM student WHERE sname LIKE '张%';

26、查询同名同性学生名单，并统计同名人数

SELECT sname,COUNT(*)FROM student GROUP BY sname HAVING COUNT(*)>1;

27、1981 年出生的学生名单(注：Student 表中Sage 列的类型是number)

SELECT sno,sname,sage,ssex FROM student t WHERE to_char(SYSDATE,'yyyy')-sage =1988

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

SELECT cno,AVG(score) FROM sc GROUP BY cno ORDER BY AVG(score)ASC,cno DESC;

29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩

SELECT st.sno,st.sname,AVG(score) FROM student st
LEFT JOIN sc
ON sc.sno=st.sno
GROUP BY st.sno,st.sname HAVING AVG(score)>85;

30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数

SELECT sname,score FROM student st,sc,course c
WHERE st.sno=sc.sno AND sc.cno=c.cno AND c.cname='Oracle' AND sc.score<60