SQL练习4

---

title: SQL练习4
time: 2019年8月17日20:59:51
tags：SQL

---

SQL练习 4

2. 查询每门功成绩最好的前两名

   SELECT
   	s_id,
   	row_number over ( PARTITION BY c_id ORDER BY s_score DESC ) AS rank 
   FROM
   	score 
   WHERE
   	rank IN (
   	1,
   	2)
   

3. 统计每门课程的学生选修人数（超过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
   

4. 检索至少选修两门课程的学生学号（不重要）

   SELECT
   	s_id,
   	count( s_id ) 
   FROM
   	score 
   GROUP BY
   	s_id 
   HAVING
   	count( s_id ) >=2
   

5. 查询选修了全部课程的学生信息

SELECT
	s_id,
	count( c_id ) 
FROM
	score 
GROUP BY
	s_id 
HAVING
	COUNT( c_id ) = (
	SELECT
		count( 1 ) 
FROM
	course)

6. 查询没学过“张三”老师讲授的任一门课程的学生姓名

   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 = '张三' 
   	)
   

7. 查询两门以上不及格课程的同学的学号及其平均成绩

   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 后面加统计函数
   

8. 查询各学生的年龄（精确到月份）

   SELECT
   	s_id,
   	s_name,
   	s_birth,
   	DATEDIFF( MONTH, s_birth, '2019-8-17' )/ 12 
   FROM
   	student
   

9. 查询本月过生日的学生（无法使用week、date(now()）

   SELECT
   	* 
   FROM
   	student 
   WHERE
   	MONTH ( s_birth ) = MONTH (
   	date(
   	now()))+1