-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 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 st.*, sc1.s_score AS 01_score, sc2.s_score AS 02_score FROM score AS sc1, score AS sc2, student AS st WHERE sc1.c_id = '01' AND (sc2.c_id = '02' OR sc2.c_id = NULL) AND sc1.s_id = sc2.s_id AND sc1.s_score > sc2.s_score AND sc1.s_id = st.s_id; -- 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; SELECT st.*, sc1.s_score AS 01_score, sc2.s_score AS 02_score FROM score AS sc1, score AS sc2, student AS st WHERE sc1.c_id = '01' AND (sc2.c_id = '02' OR sc2.c_id = NULL) AND sc1.s_id = sc2.s_id AND sc1.s_score < sc2.s_score AND sc1.s_id = st.s_id; -- 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 ROUND(AVG(a.s_score), 2) >= 60; SELECT st.*, round(avg(s_score), 2) AS avg_score FROM score AS sc, student AS st WHERE sc.s_id = st.s_id GROUP BY s_id HAVING avg(s_score) >= 60; -- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- (包括有成绩的和无成绩的) SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score FROM student b LEFT JOIN score a ON b.s_id = a.s_id GROUP BY b.s_id, b.s_name HAVING ROUND(AVG(a.s_score), 2) < 60 UNION SELECT a.s_id, a.s_name, 0 AS avg_score FROM student a WHERE a.s_id NOT IN ( SELECT DISTINCT s_id FROM score); -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 SELECT a.s_id, a.s_name, count(b.c_id) AS sum_course, sum(b.s_score) AS sum_score FROM student a LEFT JOIN score b ON a.s_id = b.s_id GROUP BY a.s_id, a.s_name; SELECT st.s_id, st.s_name, count(sc.c_id), sum(sc.s_score) FROM student AS st LEFT JOIN score AS sc ON st.s_id = sc.s_id GROUP BY st.s_id; -- 6、查询"李"姓老师的数量 SELECT count(*) FROM teacher WHERE t_name LIKE '李%'; -- 7、查询学过"张三"老师授课的同学的信息 SELECT a.* FROM student a 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 = '张三')); SELECT * FROM student AS st WHERE st.s_id IN (SELECT sc.s_id FROM score AS sc WHERE sc.c_id IN (SELECT co.c_id FROM course AS co WHERE co.t_id IN (SELECT t_id FROM teacher AS te WHERE te.t_name = '张三'))); -- 8、查询没学过"张三"老师授课的同学的信息 SELECT * FROM student c WHERE c.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 c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三'))); SELECT * FROM student AS st WHERE st.s_id NOT IN (SELECT sc.s_id FROM score AS sc WHERE sc.c_id IN (SELECT co.c_id FROM course AS co WHERE co.t_id IN (SELECT t_id FROM teacher AS te WHERE te.t_name = '张三'))); -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 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'; SELECT st.* FROM score sc_1, score sc_2, student st WHERE sc_1.s_id = sc_2.s_id AND sc_1.c_id = '01' AND sc_2.c_id = '02' AND sc_1.s_id = st.s_id; -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 SELECT a.* FROM student a WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id = '01') AND a.s_id NOT IN (SELECT s_id FROM score); SELECT st.* FROM score sc_1, score sc_2, student st WHERE sc_1.s_id = sc_2.s_id AND sc_1.c_id = '01' AND sc_2.c_id = '02' AND sc_1.s_id = st.s_id; -- 11、查询没有学全所有课程的同学的信息 SELECT s.* FROM student s WHERE s.s_id IN ( SELECT s_id FROM score WHERE s_id NOT IN ( SELECT a.s_id FROM score a JOIN score b ON a.s_id = b.s_id AND b.c_id = '02' JOIN score c ON a.s_id = c.s_id AND c.c_id = '03' WHERE a.c_id = '01')); SELECT st.* FROM student st WHERE st.s_id IN ( SELECT sc.s_id FROM score sc WHERE sc.s_id NOT IN ( SELECT sc.s_id FROM score sc GROUP BY sc.s_id HAVING count(*) >= (SELECT count(*) FROM course))); -- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 SELECT * FROM student WHERE s_id IN ( SELECT DISTINCT a.s_id FROM score a WHERE a.c_id IN (SELECT a.c_id FROM score a WHERE a.s_id = '01') ); SELECT st.* FROM student st WHERE st.s_id IN (SELECT DISTINCT s_id FROM score WHERE c_id IN (SELECT sc.c_id FROM score sc WHERE sc.s_id = '01')); -- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 SELECT a.* FROM student a WHERE a.s_id IN ( SELECT DISTINCT s_id FROM score WHERE s_id != '01' AND c_id IN (SELECT c_id FROM score WHERE s_id = '01') GROUP BY s_id HAVING count(1) = (SELECT count(1) FROM score WHERE s_id = '01')); SELECT st.* FROM student st WHERE st.s_id != '01' AND st.s_id IN ( SELECT s_id FROM score WHERE c_id IN (SELECT c_id FROM score WHERE s_id = '01') GROUP BY s_id HAVING count(1) = (SELECT count(1) FROM score WHERE s_id = '01')); -- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 SELECT a.s_name FROM student a WHERE a.s_id NOT IN ( SELECT s_id FROM score WHERE c_id = (SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三')) GROUP BY s_id); SELECT s_name FROM student WHERE s_id NOT IN ( SELECT s_id FROM score WHERE c_id = (SELECT c_id FROM teacher te, course co WHERE te.t_name = '张三' AND co.t_id = te.t_id));