mysql exercise ------ four 数据库练习参考

本文链接：https://blog.csdn.net/life_boy/article/details/120210797

mysql exercise four

大家可以采用SQLyog、Navicat 数据库可视化工具进行查看

用于以下试题使用的数据表地址:
mysql exercise four 数据库练习参考

#1、查询“JAVA”课程比“PHP”课程成绩高的所有学生的学号；
SELECT A.student_id FROM
(SELECT student_id , number FROM score JOIN course ON course.course_id=score.course_id AND cname=‘JAVA’)A ,
(SELECT student_id , number FROM score JOIN course ON course.course_id=score.course_id AND cname=‘PHP’)B
WHERE (A.number > B.number) AND (A.student_id = B.student_id);

#2、查询平均成绩大于60分的同学的学号和平均成绩；
#select student_id,avg(score.number) from score group by student_id having 60<avg(score.number);

#3、查询所有同学的学号、姓名、选课数、总成绩；
#select s.sid,s.name,count(a.course_id),sum(a.number) from student s join score a on s.sid=a.student_id group by s.sid;

#4、查询姓“赵”的老师的个数；
#select count(tid) from teacher where tname like “赵%”;

#5、查询没学过赵老师课的同学的学号、姓名；
SELECT sid,NAME FROM student WHERE sid NOT IN
(SELECT student_id FROM score WHERE
course_id=(SELECT course_id FROM course WHERE
teacher_id=(SELECT tid FROM teacher WHERE tname LIKE “赵%”)));

#6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
SELECT s.sid,s.name FROM student s WHERE s.sid IN
(SELECT student_id FROM score WHERE student_id IN
(SELECT student_id FROM score WHERE course_id=“1”) AND course_id=“2”);

#7、查询学过赵老师所教的所有课的同学的学号、姓名；
SELECT sid,NAME FROM student WHERE sid IN
(SELECT student_id FROM score WHERE
course_id=(SELECT course_id FROM course WHERE
teacher_id=(SELECT tid FROM teacher WHERE tname LIKE “赵%”)));

#9、查询有课程成绩小于60分的同学的学号、姓名；
SELECT sid,NAME FROM student WHERE sid IN
(SELECT student_id FROM score WHERE number<60);

#10、查询没有学全所有课的同学的学号、姓名

SELECT sid,NAME FROM student WHERE sid IN
(SELECT student_id FROM score GROUP BY student_id
HAVING COUNT(student_id)!=(SELECT COUNT(course_id) FROM course));

#11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
SELECT sid,NAME FROM student WHERE sid IN
(SELECT student_id FROM score WHERE course_id IN
(SELECT course_id FROM score WHERE student_id=1) AND student_id>=1);

#12、查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名；

SELECT student_id FROM score WHERE student_id NOT IN (
SELECT student_id FROM score WHERE course_id NOT IN
(SELECT course_id FROM score WHERE student_id=2))
AND student_id !=2 GROUP BY student_id
HAVING COUNT(course_id)= (SELECT COUNT(course_id) FROM score WHERE student_id=2);

#13、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；
INSERT INTO score(sid,course_id,number)
SELECT a.sid sid,2 course_id,b.c number FROM
(SELECT DISTINCT sid FROM score WHERE sid NOT IN
(SELECT sid FROM score WHERE course_id=2)) a,(SELECT AVG(number) c FROM score WHERE course_id=2) b;

#14、按平均成绩从低到高显示所有学生的“JAVA”、“PYTHON”、“PHP”三门的课程成绩，
#按如下形式显示：学生ID，JAVA，PYTHONPHP，有效课程数，有效平均分；
SELECT SC.student_id ‘学号’,
(SELECT NUMBER FROM score LEFT JOIN course ON
score.course_id = course.course_id WHERE course.cname = “JAVA” AND score.student_id = SC.student_id) AS ‘java’,
(SELECT NUMBER FROM score LEFT JOIN course ON
score.course_id = course.course_id WHERE course.cname = “PYTHON” AND score.student_id = SC.student_id) AS ‘python’,
(SELECT NUMBER FROM score LEFT JOIN course ON
score.course_id = course.course_id WHERE course.cname = “PHP” AND score.student_id = SC.student_id) AS ‘php’,
COUNT(SC.course_id) ‘课程数’,AVG(SC.number) ‘平均分’ FROM
score AS SC GROUP BY SC.student_id ORDER BY AVG(SC.number) DESC;

#15、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
#思路：通过课程id来进行分组，这个时候会显示四行，然后用聚合函数max，min来找出最大值和最小值。
SELECT course_id,MAX(number),MIN(number) FROM score GROUP BY course_id;

#16、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
SELECT course_id,AVG(number),(SUM(CASE WHEN number>=60 THEN 1 ELSE 0 END)/COUNT(1)*100)aaa
FROM score GROUP BY course_id ORDER BY AVG(number),
SUM(CASE WHEN number>=60 THEN 1 ELSE 0 END)/COUNT(1) DESC;

#17、课程平均分从高到低显示(现实任课老师)；
SELECT course_id,AVG(number)a FROM score GROUP BY course_id ORDER BY a DESC;

#18、查询每门课程被选修的学生数
SELECT course_id,COUNT(student_id) FROM score GROUP BY course_id;

#19、查询出只选修了两门课程的全部学生的学号和姓名；
SELECT sid,NAME,a.b FROM student INNER JOIN
(SELECT student_id,COUNT(course_id)b FROM score GROUP BY student_id)a
ON student.sid=a.student_id WHERE a.b=2;

#20、查询男生、女生的人数；
SELECT gender,COUNT(gender) FROM student GROUP BY gender;

#21、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
SELECT course_id,AVG(number)a FROM score GROUP BY course_id ORDER BY a ASC,course_id DESC;

#22、查询平均成绩大于75的所有学生的学号、姓名和平均成绩；
SELECT s.sid,s.name,b.a FROM student s RIGHT JOIN
(SELECT student_id,AVG(number)a FROM score GROUP BY student_id HAVING a>75)b
ON b.student_id=s.sid GROUP BY student_id;

#23、查询课程名称为“PHP”，且分数低于60的学生姓名和分数；
SELECT student.name,score.number FROM score LEFT JOIN course
ON course.course_id=score.course_id LEFT JOIN student
ON student.sid=score.student_id
WHERE score.number < 60 AND course.cname = ‘PHP’;

#24、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；
SELECT sid,NAME,number FROM score LEFT JOIN student ON score.student_id=student.sid
WHERE course_id = 3 AND number> 80;

#25、求选了课程的学生人数
SELECT COUNT(sid) FROM student WHERE sid IN (SELECT student_id FROM score);

#26、查询选修赵老师所授课程的学生中，成绩最高的学生姓名及其成绩；
SELECT NAME,number FROM student RIGHT JOIN
(SELECT student_id,number FROM score WHERE number=
(SELECT MAX(number) FROM score WHERE course_id=(SELECT course_id FROM course
WHERE teacher_id = (SELECT tid FROM teacher WHERE tname LIKE ‘赵%’))))a
ON student.sid=a.student_id;

SELECT a.name,b.number FROM student a,
(SELECT * FROM score WHERE number IN
(SELECT MAX(number)number FROM score WHERE course_id IN
(SELECT course_id FROM course WHERE teacher_id IN (SELECT tid FROM teacher WHERE tname LIKE ‘赵%’))))
AS b WHERE a.sid = b.student_id;

#27、查询各个课程及相应的选修人数；
SELECT course_id,COUNT(student_id) FROM score GROUP BY course_id;

#28、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
SELECT DISTINCT a.student_id,a.course_id,b.course_id,a.number,b.number FROM
score a,score b WHERE a.number=b.number AND a.course_id!=b.course_id ORDER BY student_id;

#29、检索至少选修两门课程的学生学号；
SELECT student_id FROM score GROUP BY student_id HAVING COUNT(student_id) > 1;

#30、查询全部学生都选修的课程的课程号和课程名；
SELECT course_id,NAME FROM course WHERE course_id=
(SELECT course_id FROM score GROUP BY course_id HAVING
COUNT(student_id) = (SELECT COUNT(sid) FROM student));

#31、查询没学过孙老师讲授的任一门课程的学生姓名；
SELECT NAME FROM student WHERE sid NOT IN
(SELECT student_id FROM score WHERE course_id=
(SELECT course_id FROM course WHERE teacher_id=
(SELECT tid FROM teacher WHERE tname LIKE “孙%”)));

#32、查询一门以上不及格课程的同学的学号及其平均成绩；
SELECT student_id,AVG(number) FROM score WHERE number<60 GROUP BY student_id HAVING COUNT(number)>1;

SELECT student_id,AVG(number) number FROM score GROUP BY student_id HAVING student_id IN
(SELECT student_id FROM score WHERE number<60 AND (number<60)>1);

#33、查询课程号为2且分数小于60的记录，按分数降序排列的同学学号；
SELECT student_id FROM score WHERE number< 60 AND course_id =2 ORDER BY number DESC;

#34、删除学习孙老师课的score表记录；
DELETE * FROM score WHERE course_id in (select
course_id from course where tid in (select tid from teacher where tname like “孙%”))

#35、删除“1”同学的“2”课程的成绩；
DELETE * FROM score WHERE student_id = 1 AND course_id = 2

在这里欢迎大家的点赞、关注、评论，以此来促进大家互相学习交流，同时可以让新加入的小伙伴更快的了解新知识！！!

以上内容如有侵权，请联系作者进行删除

≧◠◡◠≦ 1分2分都是爱，感谢已经打赏的老板，和正在打赏的老板们 ≧◠◡◠≦