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分都是爱,感谢已经打赏的老板,和正在打赏的老板们 ≧◠◡◠≦