MySQL 五十题（慢慢更）

自己拿来练习SQL的，每天几道题慢慢更新
很多地方都有数据可以导入，就不贴了

– 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT student.*,a.s_score as 01_score,b.s_score as 02_score
FROM student
JOIN score a
ON student.s_id = a.s_id AND a.c_id = 01
LEFT OUTER JOIN score b
ON student.s_id = b.s_id AND b.c_id = 02
WHERE a.s_score>b.s_score;

SELECT student.*,a.s_score,b.s_score
FROM student,score a,score b
WHERE a.s_id = b.s_id
AND a.s_id = student.s_id
AND a.c_id = 01 AND b.c_id = 02
AND a.s_score > b.s_score;

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

SELECT a.*,b.s_score,c.s_score
FROM student a,score b,score c
WHERE a.s_id = b.s_id
AND b.s_id = c.s_id
AND b.c_id = 01
AND c.c_id = 02
AND b.s_score < c.s_score;

SELECT a.*,b.s_score,c.s_score
FROM student a
JOIN score b
ON a.s_id = b.s_id AND b.c_id = 01
LEFT OUTER 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 a.*,AVG(b.s_score)
FROM student a
JOIN score b
ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING AVG(b.s_score) >= 60;

– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
– (包括有成绩的和无成绩的)

SELECT a.,AVG(b.s_score)as avg_score
FROM student a
LEFT OUTER JOIN score b
ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING AVG(b.s_score)<60
UNION
SELECT a.,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),SUM(b.s_score)
FROM student a
LEFT OUTER JOIN score b
ON a.s_id =b.s_id
GROUP BY a.s_id;

– 6、查询"李"姓老师的数量

SELECT COUNT(t_name) FROM teacher WHERE t_name LIKE ‘%李%’;

– 7、查询学过"张三"老师授课的同学的信息;

SELECT a.*
FROM student a
JOIN score b ON b.s_id = a.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 a.* FROM student a
WHERE a.s_id NOT IN
(SELECT a.s_id
FROM student a
JOIN score b ON b.s_id = a.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 = ‘张三’);

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

SELECT DISTINCT a.*
FROM student a
JOIN score b ON a.s_id = b.s_id
WHERE b.c_id in(‘01’,‘02’);

– 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
（这道题来说，想要一个更好的答案。）

SELECT DISTINCT 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 WHERE c_id = 02);

– 11、查询没有学全所有课程的同学的信息

SELECT a.*
FROM student a
JOIN score b ON a.s_id =b.s_id
GROUP BY a.s_id
HAVING COUNT(b.c_id)<(SELECT count(*) FROM course)

– 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT a.*
FROM student a
JOIN score b ON a.s_id =b.s_id AND a.s_id != 01
WHERE b.c_id IN (SELECT b.c_id FROM score b WHERE b.s_id = 01)
GROUP BY a.s_id;

– 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT a.*
FROM student a
JOIN score b ON a.s_id =b.s_id AND a.s_id != 01
WHERE b.c_id IN (SELECT b.c_id FROM score b WHERE b.s_id = 01)
GROUP BY a.s_id
HAVING COUNT(b.c_id) = (SELECT COUNT(c_id) 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 = ‘张三’)));

– 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
(这题并不清楚最后的GROUP作用，但是不加的话答案是不对的，如果有看到这点的大佬，望解惑）

SELECT a.s_id,a.s_name,AVG(b.s_score)
FROM student a
LEFT OUTER JOIN score b on a.s_id =b.s_id
WHERE a.s_id IN
(SELECT s_id FROM score
WHERE s_score < 60
GROUP BY s_id
HAVING COUNT(1)>=2)
GROUP BY a.s_id,a.s_name;

– 16、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT a.*
FROM student a
JOIN score b ON a.s_id = b.s_id
WHERE b.s_score < 60
AND b.c_id = ‘01’
ORDER BY b.s_score DESC;

– 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT a.s_id,
(SELECT s_score FROM score WHERE s_id = a.s_id AND c_id = 01) as ‘Chinese’,
(SELECT s_score FROM score WHERE s_id = a.s_id AND c_id = 02) as ‘Math’,
(SELECT s_score FROM score WHERE s_id = a.s_id AND c_id = 03) as ‘English’,
AVG(s_score) as ‘avg’
FROM score a
GROUP BY a.s_id
ORDER BY avg DESC;