2021-03-23

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

SELECT s.*,b.s_score,a.s_score
FROM student s
INNER JOIN (SELECT s_id,s_score FROM score WHERE c_id = '01') a ON s.s_id = a.s_id
INNER JOIN (SELECT s_id,s_score FROM score WHERE c_id = '02') b ON a.s_id = b.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、查询成绩小于60分的学生的学号和姓名

SELECT st.s_id,st.s_name
FROM student st
INNER JOIN (SELECT DISTINCT s_id FROM score WHERE s_score < 60) a ON a.s_id = st.s_id

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

SELECT t_id
FROM teacher
WHERE t_name LIKE '猴%'

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

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

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

将查询出的值作为下个查询中where的输入

方法一

SELECT st.s_id,st.s_name
FROM student st
INNER JOIN 
(SELECT s_id FROM score WHERE c_id = (
SELECT c_id FROM course WHERE t_id = (
SELECT t_id FROM teacher WHERE t_name = '张三'
))) a ON a.s_id = st.s_id

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

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

查询学过‘01’，没学过‘02’的同学

SELECT s_id,s_name
FROM student
WHERE s_id IN (SELECT s_id FROM score WHERE c_id = '01')
AND s_id NOT IN (SELECT s_id FROM score WHERE c_id = '02')

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

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

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

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

10.查询没有学全所有课的学生的学号、姓名(重点)

SELECT st.s_id,st.s_name
FROM student st
INNER JOIN (
SELECT a.s_id
FROM (SELECT s_id,COUNT(s_score) AS cnt FROM score GROUP BY s_id HAVING COUNT(s_score)) a 
WHERE a.cnt != (SELECT COUNT(c_id) FROM course)
) a ON st.s_id = a.s_id

11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

SELECT st.s_id,st.s_name
FROM student st 
INNER JOIN (
SELECT DISTINCT s_id
FROM score 
WHERE c_id IN (SELECT c_id FROM score WHERE s_id = '01')
AND s_id != '01'
) a ON st.s_id = a.s_id

13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）

SELECT s_id,s_name
FROM student
WHERE s_id NOT IN (
	SELECT DISTINCT sc.s_id
	FROM score sc
	INNER JOIN (SELECT c_id 
				FROM course
				WHERE t_id = (SELECT t_id FROM teacher WHERE t_name = '张三')
				) a ON a.c_id = sc.c_id
	)

15、查询两门或两门以上不及格课程的同学的学号，姓名及其平均成绩（重点）

SELECT st.s_id,st.s_name,AVG(sc.s_score)
FROM student st
INNER JOIN score sc ON sc.s_id = st.s_id
WHERE sc.s_id IN (
	SELECT s_id
	FROM score 
	WHERE s_score < 60
	GROUP BY s_id HAVING COUNT(s_score) >= 2 
)
GROUP BY s_id