SQL面试50题

MYSQL五十题思路

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
首先想办法构建一张
s_id score1 score2 的这样一张表

先把课程为01的表查询出来
SELECT sid,score AS score1 FROM SC WHERE SC.cid = ‘01’

先把课程为02的表查询出来
SELECT sid,score AS score2 FROM sc WHERE sc.cid = ‘02’

利用子查询将两张表拼接在一起

--查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数


SELECT * FROM student
	INNER JOIN
	(
		SELECT Score1.s_id,Score1.s_score AS '分数1',Score2.s_score AS '分数2' FROM 
		( SELECT score.s_id, score.s_score FROM score WHERE score.c_id = 01 ) AS Score1
		INNER JOIN
		( SELECT score.s_id, score.s_score FROM score WHERE score.c_id = 02 ) AS Score2
		ON Score1.s_id = Score2.s_id
		WHERE Score1.s_score > Score2.s_score 
	)
	AS T1
	ON student.s_id=T1.s_id

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

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


--原本写法，用AVG 统计了5,6,7成绩少一门的学生成绩 
SELECT score.s_id,AVG(score.s_score)
	FROM
	GROUP BY s_id HAVING AVG(s_score)>60

--修改写法
SELECT score.s_id,((SUM(score.s_score)/3)) AS 平均成绩
FROM score
GROUP BY  score.s_id 
HAVING  (SUM(score.s_score)/3)>60

3、查询所有学生的学号、姓名、选课数、总成绩（不重要）
构建一张这样的表，用学生表 左连接 成绩表

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

-- 运用子查询
SELECT student.s_id,student.s_name,T.`选课数`,T.`总分` FROM student
	INNER JOIN
	(	
		SELECT T1.s_id,T1.`选课数`,T2.`总分` FROM 
		(SELECT score.s_id,COUNT(score.s_id) AS '选课数' FROM score GROUP BY score.s_id) AS T1
			INNER JOIN
		(SELECT score.s_id,SUM(score.s_score) AS '总分'  FROM score GROUP BY score.s_id) AS T2
		ON T1.s_id=T2.s_id
	)
	AS T
  ON student.s_id=T.s_id

--原本的写法
-- SELECT  student.s_id,student.s_name,score.c_id,score.s_score
SELECT  
	Stu.s_id,
	Stu.s_name,
	COUNT(SC.c_id) AS '选课数',
	SUM(
			CASE
				WHEN SC.s_score IS NULL THEN 0
				ELSE SC.s_score
			END
		 ) AS '总成绩'
	
FROM student AS Stu
LEFT JOIN score AS SC 
ON Stu.s_id=SC.s_id
GROUP BY Stu.s_id
	

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

SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE '猴%';

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

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


-- 思路
-- 1.先查询张三老师的教师号
-- 2.根据教师号去查询张三老师教授的科目名称,拿到课程id
-- 3.根据课程id,查询出选择张三老师课的学生id （因为score表是多对多，查询选张三老师的课学生是简单的）
SELECT student.s_id,student.s_name FROM student
WHERE student.s_id 
NOT IN 
	(	SELECT s_id FROM score 
		WHERE score.c_id=
			(
				SELECT course.c_id 
				FROM course
				WHERE course.t_id=
					(SELECT t_id 
					FROM teacher
					WHERE teacher.t_name='张三')
			)
		)


-- 思路2 多表连接
-- 1.成绩表连接课程表,（成绩表是多对多）
-- 2.再连接老师表，获取教师名称
-- 3.查找学过张三老师课程的学生,作为student表条件过滤
SELECT student.s_id,student.s_name 
FROM student
WHERE student.s_id NOT IN
	(		SELECT score.s_id
			FROM score 
			INNER JOIN course
			ON score.c_id=course.c_id 
			INNER JOIN teacher 
			ON course.t_id=teacher.t_id
			WHERE teacher.t_name='张三'
	)

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

-- 查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
-- 思路1 子查询 有张三先用张三，拿到t_id 和课程表结合
--  再跟成绩表结合

SELECT student.s_id,student.s_name FROM student
WHERE student.s_id IN
	(	SELECT s_id FROM score
		WHERE score.c_id=
				(SELECT c_id FROM course
					WHERE course.t_id=
						(SELECT teacher.t_id FROM teacher
						WHERE teacher.t_name='张三')
				)
	)

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

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


-- 经典的错误，新手
(SELECT *
FROM score
WHERE score.c_id=01 AND score.c_id=02)



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



SELECT student.s_id,student.s_name FROM student
	WHERE student.s_id  IN
	(
	SELECT A.s_id FROM 
		(SELECT score.s_id,score.s_score AS '01课程分数'
		FROM score
		WHERE score.c_id=01) AS A
		INNER JOIN
		(SELECT score.s_id,score.s_score AS '02课程分数'
		FROM score 
		WHERE score.c_id=02) AS B
		ON A.s_id=B.s_id
		
		)

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

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

-- 不知道WHERE score.C_id 怎么写，因为课程数量不是固定的。
-- 所以要先查询score表中得分小于60的得分，按s_id 分组，会得到唯一的学号
-- COUNT (c_id) 得到学习了几门课
-- 使用COUNT 要用 AS ！！

SELECT A.s_id 
FROM 
	-- 得到学生科目不及格的数量
 ( SELECT s_id,COUNT(c_id) AS cnt
		FROM score
		WHERE s_score<60
		GROUP BY s_id
	)AS A
	
	INNER JOIN 
	-- 得到学生学习科目的数量
	(
		SELECT s_id,COUNT(c_id) AS cnt 
		FROM score
		GROUP BY s_id
	 )AS B
	 ON A.s_id=B.s_id
	 WHERE A.cnt=B.cnt
	

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

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

-- 先查询所有的学生拼接成绩表  用左连接 所有成绩没有的学生会消失
-- 分组by 三门学生成绩 会变成一个成绩
-- 用having  统计 DISTINCT（去除）sc.c_id<3  COUNT 返回复合条件的行
SELECT * 
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<3)