练习题

image.png

#1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT  * 
FROM    `Student` AS a
JOIN `Score` AS b ON a.s_id = b.s_id 
AND b.c_id = '01'
JOIN `Score` AS c 
ON a.s_id = c.s_id 
AND c.c_id = '02' 
WHERE b.s_score > c.s_score
#2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT  * 
FROM    `Student` AS a
JOIN `Score` AS b 
ON a.s_id = b.s_id 
AND b.c_id = '01'
JOIN `Score` AS c 
ON a.s_id = c.s_id 
AND c.c_id = '02' 
WHERE b.s_score < c.s_score
#3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.*, ROUND(AVG(b.s_score),2) as avg_score
FROM `Student` a
JOIN `Score` b
ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING ROUND(AVG(b.s_score),2) >= 60
#4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.*, ROUND(AVG(IFNULL(b.s_score,0)),2)
FROM `Student` a
LEFT JOIN `Score` b
ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING ROUND(AVG(IFNULL(b.s_score,0)),2) < 60
#5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.*, COUNT(IFNULL(b.c_id,0)), SUM(IFNULL(b.s_score,0))
FROM `Student` a
LEFT JOIN `Score` b
ON a.s_id = b.s_id
GROUP BY a.s_id
#6. 查询"李"姓老师的数量 
SELECT COUNT(a.t_id)
FROM `Teacher` a
WHERE a.t_name LIKE '李%'
#7. 查询学过"张三"老师授课的同学的信息 
SELECT *
FROM `Student` a
JOIN `Score` b
ON a.s_id = b.s_id
JOIN `Course` c
ON b.c_id = c.c_id
JOIN `Teacher` d
ON d.t_id = c.t_id
WHERE d.t_name = '张三'
#8. 查询没学过"张三"老师授课的同学的信息 
SELECT *
FROM `Student` a
WHERE a.s_id NOT IN (
SELECT b.s_id
FROM `Score` b
JOIN `Course` c
ON b.c_id = c.c_id
JOIN `Teacher` d
ON d.t_id = c.t_id
WHERE d.t_name = '张三'
)
#9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT *
FROM `Student` a
JOIN `Score` c
ON a.s_id = c.s_id
AND c.c_id = '02'
JOIN `Score` b
ON a.s_id = b.s_id
AND b.c_id = '01'
#10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT *
FROM `Student` a
JOIN `Score` b
ON a.s_id = b.s_id
AND b.c_id = '01'
WHERE a.s_id NOT IN (
SELECT c.s_id
FROM `Student` c
JOIN `Score` d
ON c.s_id = d.s_id
AND d.c_id = '02'
)
#11. 查询没有学全所有课程的同学的信息
SELECT a.*, COUNT(a.s_id)
FROM `Student` a
LEFT JOIN `Score` b
ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING COUNT(a.s_id) < (
SELECT COUNT(*) FROM Course
)
#12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT a.*
FROM `Student` a
JOIN `Score` b
ON a.s_id = b.s_id
WHERE b.c_id IN (
SELECT c_id
FROM `Score`
WHERE s_id = '01'
)
#13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息  
SELECT a.*
FROM Student a
JOIN Score b
ON a.s_id = b.s_id
WHERE b.s_id != '01'
AND b.c_id IN (
SELECT c_id 
FROM Score
WHERE s_id = '01'
) GROUP BY a.s_id
HAVING COUNT(b.s_score) = (select count(1) from Score where s_id='01')

#14. 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT d.s_name
FROM `Teacher` a
JOIN `Course` b
ON a.t_id = b.t_id
AND t_name = '张三'
JOIN `Score` c
ON c.c_id = b.c_id
RIGHT JOIN `Student` d
ON c.s_id = d.s_id
WHERE c.c_id IS NULL