• 欢迎访问VPS岛网站,国外VPS,国内VPS,国外服务器,国内服务器,服务器主机,测评及优惠码,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站 QQ群

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

mysql技术 不鼓自鸣 71次浏览 已收录 0个评论

#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);


VPS岛 的文章和资源来自互联网,仅作为参考资料,如果有侵犯版权的资源请尽快联系站长,我们会在24h内删除有争议的资源。丨 转载请注明MySQL查询练习题,自我解答版本
喜欢 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址