SQL练习

---

title: SQL练习题
time：2019年8月15日23:28:08
tags: SQL

---

SQL练习题

1. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

SELECT
	a.s_id "s_id",
	c.s_name "name",
	a.s_score "01",
	b.s_score "02" 
FROM
	( SELECT s_id, s_score FROM score WHERE c_id = '01' ) AS a
	INNER JOIN ( SELECT s_id, s_score FROM score WHERE c_id = '02' ) AS b ON a.s_id = b.s_id
	INNER JOIN student AS c ON c.s_id = a.s_id 
WHERE
	a.s_score > b.s_score

​

​

2. 查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

SELECT
	s_id,
	avg( s_score ) 
FROM
	score 
GROUP BY
	s_id 
HAVING
	avg( s_score ) > 60

3. 查询所有学生的学号、姓名、选课数、总成绩（不重要）

SELECT
	a.s_id "学号",
	a.s_name "姓名",
	count( b.c_id ) "选课数",
	sum( CASE WHEN b.s_score IS NULL THEN 0 ELSE b.s_score END ) "总成绩" 
FROM
	student AS a
	LEFT JOIN score AS b ON a.s_id = b.s_id 
GROUP BY
	a.s_id,
	a.s_name

4. 查询姓“张”的老师的个数（不重要）

SELECT
	count( t_id ) 
FROM
	teacher 
WHERE
	t_name LIKE '张%'

5. 查询姓“张”的老师的个数(去重）（不重要）

SELECT
	count( DISTINCT t_id ) 
FROM
	teacher 
WHERE
	t_name LIKE '张%'

6. 查询没学过“张三”老师课的学生的学号、姓名（重点）

   • 不能直接select * from score where c_id = ‘02’ 这是相当于把表中有02的记录删掉了，不代表没有学过

     SELECT
     s_id,
     s_name
     FROM
     student
     WHERE
     s_id NOT IN (
     SELECT
     s_id
     FROM
     score
     WHERE
     c_id = ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = ‘张三’ ) )
     )

SELECT
				s_id,
				s_name 
			FROM
				student 
			WHERE
				s_id NOT IN (
			SELECT
				s_id 
			FROM
				score AS s
				INNER JOIN course AS c ON s.c_id = c.c_id
				INNER JOIN teacher AS t ON t.t_id = c.t_id 
			WHERE
				t_name = '张三' 
				)

7. 查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

SELECT
	s_id,
	s_name 
FROM
	student 
WHERE
	s_id IN (
SELECT
	s_id 
FROM
	score AS s
	INNER JOIN course AS c ON s.c_id = c.c_id
	INNER JOIN teacher AS t ON t.t_id = c.t_id 
WHERE
	t_name = '张三' 
	)

8. 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

SELECT
	s_id,
	s_name 
FROM
	student 
WHERE
	s_id IN (
SELECT
	a.s_id 
FROM
	( SELECT * FROM score WHERE c_id = '01' ) AS a
	INNER JOIN ( SELECT * FROM score WHERE c_id = '02' ) AS b 
WHERE
	a.s_id = b.s_id 
	)

9. 查询课程编号为“02”的总成绩（不重点）

SELECT
	sum( s_score ) 
FROM
	score 
WHERE
	c_id = '02'

10. 查询所有课程成绩小于60分的学生的学号、姓名

SELECT
	a.s_id 
FROM
	( SELECT s_id, count( s_id ) AS cnt FROM score WHERE s_score < 60 GROUP BY s_id ) AS a
	INNER JOIN ( SELECT s_id, count( s_id ) AS cnt FROM score GROUP BY s_id ) AS b ON a.s_id = b.s_id 
WHERE
	a.cnt = b.cnt

11. 查询没有学全所有课的学生的学号、姓名(重点)
    – SELECT s_id from
    – (select s_id , count(c_id) as cnt from score group by s_id ) as a
    – inner join
    – (select count(c_id) as cnt from course ) as b on a.cnt != b.cnt

SELECT
	st.s_id 
FROM
	student AS st
	LEFT JOIN score AS cs ON st.s_id = cs.s_id 
GROUP BY
	cs.s_id 
HAVING
	count( DISTINCT cs.c_id ) < ( SELECT count( DISTINCT c_id ) FROM course )