(五)sql语句-sql50题

b站链接

解题思路

Sql50题

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

SELECT a.s_id,c.s_name,a.s_score AS ascore,b.s_score AS bscore
FROM
(
SELECT s_id,c_id,s_score
FROM Score
WHERE c_id = ‘01’
) AS a
INNER JOIN
(
SELECT s_id,c_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

inner join(等值连接) 只返回两个表中联结字段相等的行

2.查询平均成绩大于60分的学生的学号和平均成绩

SELECT s_id,AVG(s_score)
FROM Score
GROUP BY s_id
HAVING AVG(s_score) > 60

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

SELECT st.s_id,st.s_name,COUNT(sc.s_id) AS xuankeshu,SUM(sc.s_score) AS zongchengji
FROM Student st
JOIN Score sc
ON st.s_id= sc.s_id
GROUP BY sc.s_id,st.s_name

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

SELECT COUNT(t.t_id)
FROM Teacher t
WHERE t.t_name LIKE “猴%”

注: 名字可能重复，所以用t_id

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

)
)

错误答案；

因为te.t_name !=‘张三’ 查找出另外两个老师，对应的学生

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

SELECT s_id,s_name
FROM Student
WHERE s_id 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 = “张三”
)

)
)

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

我的答案

SELECT s_id
FROM Score
WHERE c_id = 2 IN
(
SELECT s_id
FROM Score
WHERE c_id = 1
)

错的 ，这个还包含了学了3的

正确答案

SELECT *
FROM Student
WHERE s_id IN
(
SELECT a.s_id
FROM
(
SELECT s_id
FROM Score
WHERE c_id = 2
)a
INNER JOIN
(
SELECT s_id
FROM Score
WHERE c_id = 1
)b ON a.s_id = b.s_id

)

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

SELECT SUM(s_score)
FROM Score
WHERE c_id = 2

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

题目的意思是他所有课都小于60分而不是所有的有挂科的人

SELECT s_id,s_name
FROM Student
WHERE s_id IN
(
SELECT s_id
FROM Score
WHERE s_score < 60
)

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

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY,’,’’));这个有效
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,’’));这个无效

很奇怪

SELECT st.,sc.
FROM Student AS st
LEFT JOIN Score AS sc ON st.s_id = sc.s_id
GROUP BY st.s_id
HAVING
COUNT(DISTINCT sc.c_id) < (SELECT COUNT(DI