title: SQL练习4
time: 2019年8月17日20:59:51
tags:SQL
SQL练习 4
-
查询每门功成绩最好的前两名
SELECT s_id, row_number over ( PARTITION BY c_id ORDER BY s_score DESC ) AS rank FROM score WHERE rank IN ( 1, 2)
-
统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT c_id, count( c_id ) FROM score GROUP BY c_id HAVING COUNT( s_id ) > 5 ORDER BY count( s_id ) DESC, c_id ASC
-
检索至少选修两门课程的学生学号(不重要)
SELECT s_id, count( s_id ) FROM score GROUP BY s_id HAVING count( s_id ) >=2
-
查询选修了全部课程的学生信息
SELECT
s_id,
count( c_id )
FROM
score
GROUP BY
s_id
HAVING
COUNT( c_id ) = (
SELECT
count( 1 )
FROM
course)
-
查询没学过“张三”老师讲授的任一门课程的学生姓名
SELECT s_id, s_name FROM student WHERE s_id NOT IN ( SELECT sr.s_id FROM score AS sr INNER JOIN course AS c ON sr.c_id = c.c_id INNER JOIN teacher AS t ON c.t_id = t.t_id WHERE t.t_name = '张三' )
-
查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s_id, count( s_id ), avg( s_score ) FROM score WHERE s_score < 60 GROUP BY s_id HAVING count( s_id ) > 2 -- having 后面加统计函数
-
查询各学生的年龄(精确到月份)
SELECT s_id, s_name, s_birth, DATEDIFF( MONTH, s_birth, '2019-8-17' )/ 12 FROM student
-
查询本月过生日的学生(无法使用week、date(now())
SELECT * FROM student WHERE MONTH ( s_birth ) = MONTH ( date( now()))+1