#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_id
IN(
SELECT s.s_id
FROM score AS s WHERE s.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
='张三'))));
#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_id
FROM 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_id
IN(SELECT b.s_id
FROM score AS b WHERE b.c_id
='01')
AND a.s_id
IN(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_id
IN(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_id
FROM 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_id
FROM 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_score
FROM score WHERE a.s_id
=score.s_id
AND score.c_id
='01')AS 语文,
(SELECT score.s_score
FROM score WHERE a.s_id
=score.s_id
AND score.c_id
='02')AS 数学,
(SELECT score.s_score
FROM 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 avg
DESC;
#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 avg
DESC;
#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_id
AND 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_score
DESC;
#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
);