【学习记录】SQL练习题

基础SQL练习题记录

数据库类型：MySQL

练习工具：HeidiSQL

--------------------------------------------------

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

SELECT st.s_id FROM student_0516 AS st

JOIN (SELECT s_id,s_score FROM score_0516 WHERE c_id='01') AS a ON  st.s_id=a.s_id

JOIN (SELECT s_id,s_score FROM score_0516 WHERE c_id='02') AS b ON  st.s_id=b.s_id

WHERE a.s_score > b.s_scor

---

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

SELECT s_id,AVG(s_score) AS mean_s FROM score_0516

GROUP BY s_id

HAVING mean_s > 60

---

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

SELECT st.s_id,st.s_name,COUNT(sc.c_id),SUM(sc.s_score)

FROM student_0516 AS st

JOIN score_0516 AS sc ON st.s_id = sc.s_id

GROUP BY st.s_id,st.s_name

---

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

SELECT COUNT(t_id) FROM teacher_0516

WHERE t_name LIKE '猴%'

---

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

SELECT * FROM student_0516

WHERE s_id NOT IN (

SELECT st.s_id FROM student_0516 AS st

JOIN score_0516 AS sc ON st.s_id=sc.s_id

JOIN course_0516 AS co ON sc.c_id=co.c_id

JOIN teacher_0516 AS tea ON co.t_id=tea.t_id

WHERE tea.t_name ='张三')

这道题的思路是：先筛选出学过‘张三’老师课程的学号，再反选不在其中的学号，得到结果就是没有学过张三老师的课程

---

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

SELECT * FROM student_0516

WHERE s_id IN (

SELECT st.s_id FROM student_0516 AS st

JOIN score_0516 AS sc ON st.s_id=sc.s_id

JOIN course_0516 AS co ON sc.c_id=co.c_id

JOIN teacher_0516 AS tea ON co.t_id=tea.t_id

WHERE tea.t_name ='张三')

同上一题思路

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

SELECT st.s_id,st.s_name FROM student_0516 AS st

JOIN score_0516 AS s1 ON st.s_id=s1.s_id AND s1.c_id=01

JOIN score_0516 AS s2 ON st.s_id=s2.s_id AND s2.c_id=02

补充：

-- 查询学过编号为“01”的课程，但没有学过编号为“02”的课程的学生的学号、姓名（重点）

SELECT st.s_id,st.s_name FROM student_0516 AS st

WHERE st.s_id IN (SELECT s_id FROM score_0516 WHERE c_id=01) AND s_id  NOT IN

(SELECT s_id FROM score_0516 WHERE c_id=02)

---

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

SELECT c_id,sum(s_score) AS '总分'

FROM score_0516

GROUP BY c_id

HAVING c_id=02

---

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

SELECT st.s_id,st.s_name FROM student_0516 AS st

JOIN score_0516 AS sc ON st.s_id=sc.s_id AND  sc.s_score < 60

GROUP BY sc.s_id

---

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

SELECT st.s_id,st.s_name FROM student_0516 AS st

JOIN score_0516 AS s1

ON st.s_id=s1.s_id