mysql查询练习（二）

1. 查询"01"课程⽐"02"课程成绩⾼的学⽣的信息及课程分数

SELECT c.*,a.`s_score` c01,b.`s_score` c02 FROM Score a,Score b,Student c WHERE
	c.`s_id`=a.`s_id` AND 
	a.`s_id` = b.`s_id` AND a.`c_id`!=b.`c_id` AND a.`c_id` = '01' AND b.`c_id` = '02' AND a.`s_score` > b.`s_score`

2. 查询"01"课程⽐"02"课程成绩低的学⽣的信息及课程分数

SELECT s.*,a.`s_score` c01,b.`s_score` c02 FROM Score a INNER JOIN Student s ON a.`s_id` = s.`s_id` 
	INNER JOIN Score b ON a.`s_score` < b.`s_score` AND a.`s_id` = b.`s_id` AND a.`c_id`!=b.`c_id`
	WHERE a.`c_id` = '01' AND b.`c_id` = '02'

3. 查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩

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

4. 查询平均成绩⼩于60分的同学的学⽣编号和学⽣姓名和平均成绩 (包括有成绩的和⽆成绩的)

SELECT sco.s_id,s.`s_name`,AVG(s_score) `avg` 
FROM Score sco 
JOIN Student AS s 
	ON s.`s_id` = Sco.`s_id` 
GROUP BY s_id HAVING `avg` > 60

5. 查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩

SELECT stu.s_name,COUNT(sco.s_score) `count`,SUM(sco.s_score) `sum`
FROM Score AS sco
JOIN Student AS stu
	ON stu.s_id = sco.`s_id`
GROUP BY sco.s_id

6. 查询"李"姓⽼师的数量

SELECT COUNT(1)
FROM Teacher
WHERE t_name 
	LIKE '李%' 

7. 查询学过"张三"⽼师授课的同学的信息

SELECT stu.* 
FROM Teacher t
	JOIN  Course c
		ON c.t_id = t.t_id
	JOIN score s
		ON s.c_id = c.c_id
	JOIN Student stu
		ON stu.s_id = s.`s_id`
WHERE t.t_name = '张三'

8. 查询没学过"张三"⽼师授课的同学的信息

SELECT * 
FROM Student result 
WHERE result.s_id NOT IN
(SELECT stu.s_id 
FROM Student stu
	JOIN score s
		ON s.s_id = stu.s_id
	JOIN  Course c
		ON s.c_id = c.c_id
	JOIN Teacher t
		ON t.t_id = c.`t_id`
WHERE t.t_name='张三')

9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT * 
FROM Student 
WHERE Student.`s_id` IN(
SELECT sco1.`s_id`
FROM score sco1
	JOIN score sco2
		ON sco1.s_id = sco2.s_id
WHERE sco1.c_id = '01' AND sco2.c_id = '02');

10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT * 
FROM score s1 
WHERE s1.`c_id` = '01' 
AND s1.`s_id`  NOT IN (SELECT s2.`s_id` FROM score s2 WHERE s2.`c_id` = '02')