MySQL查询练习题，自我解答版本

#1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT c.*,a.s_score,b.s_score
FROM score AS a,score AS b,student AS c
WHERE c.s_id=a.s_id AND a.s_id=b.s_id AND a.c_id='01'AND b.c_id='02'AND a.s_score>b.s_score;

#2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT c.*,a.s_score,b.s_score
FROM score AS a,score AS b,student AS c
WHERE c.s_id=a.s_id AND a.s_id=b.s_id AND a.c_id='01'AND b.c_id='02'AND a.s_score<b.s_score;

#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.*,AVG(b.s_score)AS total
FROM student AS a,score AS b
WHERE a.s_id=b.s_id
GROUP BY a.s_id
HAVING total>=60;

#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.*,AVG(IFNULL(b.s_score,0))AS total
FROM student AS a
LEFT JOIN score AS b ON a.s_id=b.s_id
GROUP BY a.s_id
HAVING total<60;

#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.s_id,a.s_name,COUNT(b.c_id),SUM(IFNULL(b.s_score,0))
FROM student AS a
LEFT JOIN score AS b ON a.s_id=b.s_id
GROUP BY a.s_id;

#6、查询"李"姓老师的数量
SELECT COUNT(1)
FROM teacher AS a
WHERE a.t_name LIKE'李%';

#7、查询学过"张三"老师授课的同学的信息
SELECT a.*
FROM student AS a WHERE a.s_idIN(
SELECT s.s_id FROM score AS s WHERE s.c_id=(
(SELECT c.c_idFROM course AS c WHERE c.t_id=(
SELECT t.t_id FROM teacher AS t WHERE t.t_name='张三'))));

#8、查询没学过"张三"老师授课的同学的信息
SELECT a.*
FROM student AS a WHERE a.s_id NOT IN(
SELECT s.s_id FROM score AS s WHERE s.c_id=(
(SELECT c.c_idFROM course AS c WHERE c.t_id=(
SELECT t.t_id FROM teacher AS t WHERE t.t_name='张三'))));

#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT a.*
FROM student AS a
WHERE a.s_idIN(SELECT b.s_id FROM score AS b WHERE b.c_id='01')
AND a.s_idIN(SELECT c.s_id FROM score AS c WHERE c.c_id='02');

#10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT a.*
FROM student AS a
WHERE a.s_idIN(SELECT b.s_id FROM score AS b WHERE b.c_id='01')
AND a.s_id NOT IN(SELECT c.s_id FROM score AS c WHERE c.c_id='02');

#11、查询没有学全所有课程的同学的信息
SELECT a.*
FROM student AS a
LEFT JOIN score AS b ON a.s_id=b.s_id
GROUP BY a.s_id
HAVING COUNT(1)<(SELECT COUNT(1)FROM course);

#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT a.*
FROM student AS a
LEFT JOIN score AS b ON a.s_id=b.s_id
WHERE b.c_id IN
(SELECT c.c_idFROM score AS c WHERE c.s_id='01')
AND b.s_id!='01';

#13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT a.*
FROM student AS a
WHERE a.s_id!='01' AND a.s_id IN
(SELECT b.s_idFROM score AS b GROUP BY b.s_id HAVING COUNT(1)=(SELECT COUNT(1)FROM score WHERE score.s_id='01'));

#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT a.s_id,a.s_name, AVG(IFNULL(b.s_score,0))
FROM student AS a
LEFT JOIN score AS b ON a.s_id=b.s_id
WHERE IFNULL(b.s_score,0)<60
GROUP BY a.s_id
HAVING COUNT(1)>=2

#16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT a.*
FROM student AS a
LEFT JOIN score AS b ON a.s_id=b.s_id
WHERE b.c_id='01'AND b.s_score<60;

#17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.*,(SELECT score.s_scoreFROM score WHERE a.s_id=score.s_id AND score.c_id='01')AS 语文,
(SELECT score.s_scoreFROM score WHERE a.s_id=score.s_id AND score.c_id='02')AS 数学,
(SELECT score.s_scoreFROM score WHERE a.s_id=score.s_id AND score.c_id='03')AS 英语,
ROUND(AVG(b.s_score),2)AS 平均成绩
FROM student AS a INNER JOIN score AS b ON a.s_id=b.s_id GROUP BY a.s_id ORDER BY 平均成绩 DESC;

#18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，
#最低分，平均分，及格率，中等率，优良率，优秀率
#--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT b.c_id,b.c_name,MAX(a.s_score),MIN(a.s_score),AVG(a.s_score)
,(SUM(CASE WHEN a.s_score>=90 THEN 1 ELSE 0 END)/COUNT(1)) AS 优秀率
,(SUM(CASE WHEN a.s_score>=80 AND a.s_score<90 THEN 1 ELSE 0 END)/COUNT(1)) AS 优良率
,(SUM(CASE WHEN a.s_score>=70 AND a.s_score<80 THEN 1 ELSE 0 END)/COUNT(1)) AS 中等率
,(SUM(CASE WHEN a.s_score>=60 THEN 1 ELSE 0 END)/COUNT(1)) AS 及格率
FROM score AS a,course AS b WHERE a.c_id=b.c_id
GROUP BY a.c_id;

#21、查询不同老师所教不同课程平均分从高到低显示
SELECT b.t_id,b.c_name,AVG(a.s_score)AS avg
FROM score AS a,course AS b WHERE b.c_id=a.c_id
GROUP BY b.c_id
ORDER BY avgDESC;

#23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT b.c_id,b.c_name
,(SUM(CASE WHEN a.s_score>=85 THEN 1 ELSE 0 END)/COUNT(1))AS '[100-85]'
,(SUM(CASE WHEN a.s_score>=70 AND a.s_score<85 THEN 1 ELSE 0 END)/COUNT(1))AS '[85-70]'
,(SUM(CASE WHEN a.s_score>=60 AND a.s_score<70 THEN 1 ELSE 0 END)/COUNT(1))AS '[70-60]'
,(SUM(CASE WHEN a.s_score<60 THEN 1 ELSE 0 END)/COUNT(1))AS '[0-60]'
FROM score AS a ,course AS b WHERE b.c_id=a.c_id
GROUP BY b.c_id;

#26查询每门课程被选修的学生数
SELECT score.c_id,COUNT(1) FROM score GROUP BY score.c_id;

#27查询出只有两门课程的全部学生的学号和姓名
SELECT a.*
FROM student AS a,score AS b
WHERE a.s_id=b.s_id
GROUP BY a.s_id
HAVING COUNT(1)=2;

#28查询男生、女生人数
SELECT a.s_sex,COUNT(1) FROM student AS a GROUP BY a.s_sex;

#29查询名字中含有"风"字的学生信息
SELECT a.* FROM student AS a WHERE a.s_name LIKE '%风%';

#30、查询同名同性学生名单，并统计同名人数
SELECT a.s_name,COUNT(1)
FROM student AS a,student AS b
WHERE a.s_name=b.s_name AND a.s_id!=b.s_id
GROUP BY a.s_name;

#31、查询1990年出生的学生名单
SELECT a.*FROM student AS a WHERE YEAR(a.s_birth)='1990';

#32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT a.c_id,AVG(a.s_score)AS avg FROM score AS a GROUP BY a.c_id ORDER BY avgDESC;

#33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT a.*,AVG(b.s_score)AS avg
FROM student AS a,score AS b
WHERE a.s_id=b.s_id
GROUP BY a.s_id
HAVING avg>=85;

#34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT b.s_name,c.s_score FROM student AS b ,score AS c WHERE b.s_id=c.s_id AND c.c_id=
(SELECT a.c_id FROM course AS a WHERE a.c_name='数学') AND c.s_score<60;

#35、查询所有学生的课程及分数情况
SELECT a.*,(SELECT b.s_score FROM score AS b WHERE b.c_id='01' AND a.s_id=b.s_id)AS chinese
,(SELECT b.s_score FROM score AS b WHERE b.c_id='02' AND a.s_id=b.s_id)AS math
,(SELECT b.s_score FROM score AS b WHERE b.c_id='03' AND a.s_id=b.s_id)AS english
FROM student AS a;

#36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT a.s_name,c.c_name,b.s_score
FROM student a,score b,course c
WHERE a.s_id=b.s_id AND b.c_id=c.c_idAND b.s_score>70;

#37、查询不及格的课程
SELECT a.s_id,a.c_id,b.c_name,a.s_score
FROM score AS a,course AS b
WHERE a.c_id=b.c_id AND a.s_score<60;

#38、查询课程编号为01且课程成绩在70分以上的学生的学号和姓名；
SELECT a.s_id,a.s_name,b.s_score
FROM student AS a,score AS b
WHERE a.s_id=b.s_id AND b.c_id='01'AND b.s_score>70;

#39、求每门课程的学生人数
SELECT COUNT(1) FROM score AS a GROUP BY a.c_id;

#40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT a.*,b.c_id,b.s_score FROM student AS a ,score AS b WHERE a.s_id=b.s_id AND b.c_id=
(SELECT c.c_id FROM course AS c WHERE c.t_id=
(SELECT t.t_id FROM teacher AS t WHERE t.t_name='张三'))
ORDER BY b.s_scoreDESC;

#41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT a.s_id,b.c_id,b.s_score
FROM student AS a,score AS b,score AS c
WHERE a.s_id=b.s_id AND b.c_id!=c.c_id AND b.s_score=c.s_score;

#43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，
#查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT a.c_id,COUNT(1)AS 人数
FROM score AS a
GROUP BY a.c_id
HAVING 人数>5
ORDER BY 人数 DESC ,a.c_id ASC;

#44、检索至少选修两门课程的学生学号
SELECT a.s_id,COUNT(1)AS 选课数
FROM score AS a
GROUP BY a.s_id
HAVING 选课数>=2;

#45、查询选修了全部课程的学生信息
SELECT a.* FROM student AS a,score AS b
WHERE a.s_id=b.s_id
GROUP BY a.s_id
HAVING COUNT(1)=(SELECT COUNT(1)FROM course );

#46、查询各学生的年龄
SELECT a.*,(YEAR(NOW())-YEAR(a.s_birth))AS 年龄
FROM student AS a ;

#47、查询本周过生日的学生
SELECT *FROM student WHERE (WEEK(NOW())-WEEK(student.s_birth))=0;

#48查询下周过生日的学生
SELECT * FROM student WHERE (WEEK(student.s_birth)-WEEK(NOW()))=1;

#49、查询本月过生日的学生
SELECT * FROM student WHERE MONTH(NOW())=MONTH(student.s_birth);

#50、查询下月过生日的学生
SELECT * FROM student WHERE MONTH(NOW())+1=MONTH(student.s_birth);