sql 练习题（1）

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
-- mark一下，"一个表当两个表用"
SELECT a.* ,b.s_score AS 01_score,c.s_score AS 02_score FROM 
student a 
    JOIN score b ON a.s_id=b.s_id AND b.c_id='01'
    LEFT JOIN score c ON a.s_id=c.s_id AND c.c_id='02' OR c.c_id = NULL WHERE b.s_score>c.s_score

-- 也可以这样写
    SELECT a.*,b.s_score AS 01_score,c.s_score AS 02_score FROM student a,score b,score c 
            WHERE a.s_id = b.s_id 
            AND a.s_id = c.s_id 
            AND b.c_id = '01' 
            AND c.c_id = '02' 
            AND b.s_score > c.s_score

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

SELECT a.* ,b.s_score AS 01_score,c.s_score AS 02_score FROM 
    student a LEFT JOIN score b ON a.s_id=b.s_id AND b.c_id='01' OR b.c_id=NULL 
     JOIN score c ON a.s_id=c.s_id AND c.c_id='02' WHERE b.s_score < c.s_score

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score FROM 
    student b 
    JOIN score a ON b.s_id = a.s_id
    GROUP BY b.s_id,b.s_name HAVING avg_score >=60;

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)
SELECT a.s_id 学生编号,ROUND(AVG(a.s_score),2) 平均成绩, b.s_name 学生姓名  FROM score a
LEFT JOIN student b ON a.`s_id` = b.`s_id`
GROUP BY a.s_id ,b.s_name
HAVING AVG(a.s_score) < 60 
UNION
SELECT a.s_id 学生编号,0 平均成绩, a.s_name 学生姓名  FROM student a
WHERE a.`s_id` NOT IN (SELECT DISTINCT score.s_id FROM score)

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT s.s_id 编号, s.s_name 姓名, COUNT(*) 选课数量, SUM(c.s_score) 总成绩
FROM student s LEFT JOIN score c ON s.s_id = c.s_id
GROUP BY s.s_id, s.s_name


-- 6、查询"李"姓老师的数量 
SELECT COUNT(*) FROM teacher WHERE t_name LIKE '李%';
SELECT COUNT(1) FROM teacher WHERE t_name LIKE '李%';
SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '李%';

-- 7、查询学过"张三"老师授课的同学的信息
-- 解法1
SELECT s1.* FROM student s1 LEFT JOIN score s2 ON s1.`s_id` = s2.`s_id`
WHERE s2.`c_id` IN 
(SELECT c.`c_id` 课程号 FROM 
teacher t LEFT JOIN course c
ON t.`t_id` = c.`t_id`
WHERE t.`t_name` = '张三')

-- 解法2
SELECT a.* FROM student    a 
LEFT JOIN score b ON a.s_id = b.s_id
WHERE b.c_id IN 
(SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name = '张三'))

-- 8、查询没学过"张三"老师授课的同学的信息 
-- 把第7题中的 in 换为 NOT IN 就是第8题的结果了吗？当然不是。 
-- 正确思路：首先查询出张三授课的全部课程编码集合A，然后查询出每个学生所选的全部课程编码集合B，查询出B中编码出现在A中的对应的每条记录的学生学号集合，    
--     不在该集合中的学生即为结果
SELECT * FROM 
    student s
    WHERE s.s_id NOT IN(
        SELECT a.s_id FROM student a JOIN score b ON a.s_id=b.s_id WHERE b.c_id IN(
        SELECT a.c_id FROM course a LEFT JOIN teacher b ON a.t_id = b.t_id WHERE t_name ='张三'));

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

-- 解法1 ***  mark一下这种解法
SELECT a.* FROM 
    student a,score b,score c 
    WHERE a.s_id = b.s_id  AND a.s_id = c.s_id AND b.c_id='01' AND c.c_id='02';
    
--  解法2 ：查出选课01的学生编号(记为A)，查询选课02的并且出现在A中的学生编号，相当于二者取交集（即为应求的学生编号的集合）。
-- 注：mysql没有支持交集的关键字，只好采用in实现取两个查询结果的交集
SELECT s.* FROM student s WHERE s.s_id IN (
SELECT t.s_id FROM score t WHERE t.c_id = '02' AND t.s_id IN  (SELECT t.s_id FROM score t WHERE t.c_id = '01'))

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT s.* FROM student s WHERE s.s_id IN (
SELECT t.s_id FROM score t WHERE t.c_id = '01' AND t.s_id NOT IN (SELECT t.s_id FROM score t WHERE t.c_id = '02'))