mysql-练习题

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
思路：
获取所有有生物课程的人（学号，成绩） - 临时表
获取所有有物理课程的人（学号，成绩） - 临时表
根据【学号】连接两个临时表：
学号 物理成绩 生物成绩

SELECT student_id,student.sname,sw,sy FROM(SELECT
	A.student_id,
	sw,
	sy 
FROM
( SELECT student_id, num AS sw FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '生物' ) AS A
LEFT JOIN ( SELECT student_id, num AS sy FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '物理' ) AS B ON A.student_id = B.student_id 
WHERE
	A.sw > B.sy) AS c LEFT JOIN student ON c.student_id=student.sid 

3、查询平均成绩大于60分的同学的学号和平均成绩；
思路：
根据学生分组，使用avg获取平均值，通过having对avg进行筛选

SELECT student_id,AVG(num) FROM score GROUP BY student_id HAVING AVG(num)>60

查询所有同学的学号、姓名、选课数、总成绩；
方法一：

SELECT student_id,student.sname,SW,SY FROM
(SELECT student_id,COUNT(course_id) AS SW,SUM(num) AS SY FROM score GROUP BY student_id) AS A LEFT JOIN
student ON student.sid=A.student_id

方法二：

SELECT score.student_id,COUNT(score.course_id),SUM(score.num),student.sname FROM 
score LEFT JOIN student on score.student_id=student.sid GROUP BY score.student_id

、查询姓“李”的老师的个数；

SELECT
	teacher.tname tid 
FROM
	teacher 
WHERE
	tname LIKE '李%'

6、查询没学过“李平”老师课的同学的学号、姓名；
思路：
先查到“李平老师”老师教的所有课ID
获取选过课的所有学生ID
学生表中筛选

SELECT
	* 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT
		score.student_id 
	FROM
		score
		LEFT JOIN ( SELECT cid FROM teacher LEFT JOIN course ON course.teacher_id = teacher.tid WHERE tname = '李平老师' ) AS a ON score.course_id = a.cid 
	)

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
思路：
先查到既选择001又选择002课程的所有同学
根据学生进行分组，如果学生数量等于2表示，两门均已选择

SELECT student_id,sname FROM
(SELECT student_id,course_id FROM score  WHERE course_id=1 OR course_id=2) AS A
LEFT JOIN student ON A.student_id=student.sid GROUP BY student_id HAVING COUNT(student_id)>1

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
同上，只不过将001和002变成 in (叶平老师的所有课)

SELECT
	* 
FROM
	student 
WHERE
	sid  IN (
	SELECT
		score.student_id 
	FROM
		score
		LEFT JOIN ( SELECT cid FROM teacher LEFT JOIN course ON course.teacher_id = teacher.tid WHERE tname = '李平老师' ) AS a ON score.course_id = a.cid 
	)

9.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
同第1题

SELECT
	* 
FROM
	student 
WHERE
	sid IN (
	SELECT
		A.student_id 
	FROM
		( SELECT student_id, num AS SW FROM course LEFT JOIN score ON course.cid = score.student_id WHERE score.course_id = 1 ) AS A
		LEFT JOIN ( SELECT student_id, num AS SY FROM course LEFT JOIN score ON course.cid = score.student_id WHERE score.course_id = 2 ) AS B ON A.student_id = B.student_id 
	WHERE
		SW > SY 
	)

10、查询有课程成绩小于60分的同学的学号、姓名；

SELECT * FROM student WHERE sid IN
(SELECT student_id FROM score WHERE score.num<60)

11.查询没有学全所有课的同学的学号、姓名；

SELECT * from student WHERE sid IN
(SELECT student_id FROM
(SELECT student_id,COUNT(course_id) AS s FROM score GROUP BY student_id) as b WHERE s<4)

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
思路：
获取 001 同学选择的所有课程
获取课程在其中的所有人以及所有课程
根据学生筛选，获取所有学生信息
再与学生表连接，获取姓名
第一种方法

SELECT * FROM student WHERE sid IN 
(SELECT student_id FROM score WHERE student_id!=1 and course_id IN
(SELECT course_id FROM score WHERE student_id=1)) ```

第二种方法

SELECT student_id,COUNT(course_id),sname FROM
score LEFT JOIN student on score.student_id=student.sid  WHERE student_id!=1 and course_id IN
(SELECT course_id FROM score WHERE student_id=1) GROUP BY student_id 

13.查询至少学过学号为“001”同学所有课的其他同学学号和姓名；

先找到和001的学过的所有人
然后个数 ＝ 001所有学科     ＝＝》 其他人可能选择的更多

SELECT student_id,COUNT(course_id) FROM score WHERE student_id!=1 AND course_id in
(SELECT course_id FROM score WHERE student_id=2) GROUP BY student_id HAVING COUNT(course_id)>=(SELECT COUNT(course_id) FROM score WHERE student_id=2)

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

    个数相同
    002学过的也学过

SELECT student_id,sname from score LEFT JOIN student ON score.student_id=student.sid WHERE student_id IN
(SELECT student_id from score where student_id!=1 GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(course_id) FROM score WHERE student_id=2) 
) AND course_id in
(SELECT course_id FROM score WHERE student_id=2) GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(course_id) FROM score WHERE student_id=2)