MySql综合题目练习--class,student,score,course,teacher

MaGgIeOo0

已于 2023-08-23 09:24:51 修改

阅读量73

点赞数 1

分类专栏： SQL 文章标签： mysql 数据库

于 2023-08-22 20:52:53 首次发布

本文链接：https://blog.csdn.net/weixin_62971115/article/details/132436457

版权

SQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1、查询所有的课程的名称以及对应的任课老师姓名

2、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5、查询所有学生的学号，姓名，选课数，总成绩

6、查询姓李老师的个数

7、查询没有报李平老师课的学生姓名

8、查询物理课程比生物课程高的学生的学号

9、查询没有同时选修物理课程和体育课程的学生姓名

10、查询挂科超过两门(包括两门)的学生姓名和班级、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录

13、查询全部学生都选修了的课程号和课程名

14、查询每门课程被选修的次数

15、查询只选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

20、查询每门课程成绩最好的前两名学生姓名

21、查询不同课程但成绩相同的学号，课程号，成绩

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24、任课最多的老师中学生单科成绩最高的学生姓名

-- 1、查询所有的课程的名称以及对应的任课老师姓名
SELECT course.cname,teacher.tname FROM course , teacher 
WHERE course.cid = teacher.tid;
-- 2、查询学生表中男女生各有多少人
SELECT gender , COUNT(gender) FROM student
GROUP BY gender;
-- 3、查询物理成绩等于100的学生的姓名
SELECT score.student_id , student.sname , score.num FROM course,score,student
WHERE course.cid = score.course_id 
AND student.sid = score.student_id
AND score.num = 100 
AND course.cname = "物理";
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT sname,avgNum FROM student , (
SELECT student_id , AVG(num) AS avgNum FROM score
GROUP BY student_id
HAVING avgNum >80) temp
WHERE student.sid = temp.student_id;

-- 5、查询所有学生的学号，姓名，选课数，总成绩
SELECT sid , sname , counScore , sumScore FROM student , (
SELECT student_id , COUNT(student_id) counScore , SUM(num) sumScore FROM score
GROUP BY student_id) temp 
WHERE student.sid = temp.student_id;
-- 
-- 6、 查询姓李老师的个数
SELECT * FROM teacher
WHERE tname LIKE "李%";
-- 
-- 7、 查询没有报李平老师课的学生姓名
-- SELECT sname ,sid FROM student
-- WHERE sid in(
-- SELECT student_id FROM score
-- WHERE course_id NOT IN (
-- SELECT cid FROM teacher,course
-- WHERE teacher.tid = course.teacher_id
-- AND teacher.tname = "李平老师"));    有问题

-- （1）查询李平老师的ID找到他代课的课程ID
SELECT cid FROM teacher,course
WHERE teacher.tid = course.teacher_id
AND teacher.tname = "李平老师";
-- （2）查询参加了李平课程的学生ID
SELECT student_id FROM score
WHERE course_id IN(
SELECT cid FROM teacher,course
WHERE teacher.tid = course.teacher_id
AND teacher.tname = "李平老师")
GROUP BY student_id;
-- (3)查询没有报李平老师课的学生姓名
SELECT sname FROM student
WHERE sid NOT IN (
SELECT student_id FROM score
WHERE course_id IN(
	SELECT cid FROM teacher,course
	 WHERE teacher.tid = course.teacher_id
	   AND teacher.tname = "李平老师")
GROUP BY student_id);

-- 
-- 8、 查询物理课程比生物课程高的学生的学号
-- （1）分别查询物理课程的id以及生物课程的id
SELECT cid FROM course
WHERE cname = "物理";
SELECT cid FROM course
WHERE cname = "生物";
-- （2）分别查询选了物理课程和生物课程的学生id和成绩
SELECT student_id ,num FROM score
WHERE course_id = (SELECT cid FROM course WHERE cname = "物理");
SELECT student_id ,num FROM score
WHERE course_id = (SELECT cid FROM course WHERE cname = "生物");
-- （3）查询物理课程比生物课程高的学生的学号
SELECT tem1.student_id FROM (
SELECT student_id ,num FROM score
WHERE course_id = (SELECT cid FROM course WHERE cname = "物理")) tem1,(
SELECT student_id , num FROM score
WHERE course_id = (SELECT cid FROM course WHERE cname = "生物")) tem2
WHERE tem1.student_id = tem2.student_id AND tem1.num>tem2.num;
-- 
-- 9、 查询没有同时选修物理课程和体育课程的学生姓名
-- (1)查询选了物理和体育课程的学生id
SELECT cid FROM course
WHERE cname = "物理" or cname = "体育";

SELECT student_id FROM score
WHERE course_id IN (SELECT cid FROM course WHERE cname IN ("物理","体育"));

-- (2)查询没有同时选了者两门课程的学生id
SELECT student_id FROM score
WHERE course_id IN (SELECT cid FROM course WHERE cname IN ("物理","体育"))
GROUP BY student_id
HAVING COUNT(student_id) = 1;
-- (3)
SELECT sname FROM student
WHERE sid IN (
SELECT student_id FROM score
WHERE course_id IN (SELECT cid FROM course WHERE cname IN ("物理","体育"))
GROUP BY student_id
HAVING COUNT(student_id) = 1);
-- 
-- 10、查询挂科超过两门(包括两门)的学生姓名和班级
-- 查询两门以上课程分数低于60分的学生id
SELECT student_id FROM score
WHERE num<60
GROUP BY student_id
HAVING COUNT(student_id) >= 2;
-- 查询此学生的姓名和班级
SELECT student.sname,class.caption FROM student,class,(
SELECT student_id FROM score
WHERE num<60
GROUP BY student_id
HAVING COUNT(student_id) >= 2) temp
WHERE student.sid = temp.student_id AND student.class_id = class.cid;

-- 11、查询选修了所有课程的学生姓名
-- 查询所有课程的id
SELECT cid FROM course;
-- 查询选了所有课程的学生id
SELECT student_id FROM score
WHERE course_id IN (SELECT cid FROM course);

SELECT student_id FROM score
WHERE course_id IN (SELECT cid FROM course)
GROUP BY student_id
HAVING COUNT(student_id) = 4;
-- 查询学生姓名
SELECT sname FROM student
WHERE sid IN (
SELECT student_id FROM score
WHERE course_id IN (SELECT cid FROM course)
GROUP BY student_id
HAVING COUNT(student_id) = 4);

-- 优化
SELECT sname FROM student
WHERE sid IN (
SELECT student_id FROM score 
GROUP BY student_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM course));
-- 
-- 12、查询李平老师教的课程的所有成绩记录
-- 查询李平老师所教课程的ID
SELECT cid FROM teacher,course
WHERE teacher.tid = course.teacher_id
AND teacher.tname = "李平老师";
-- 查询李平课程的学生成绩
SELECT * FROM score
WHERE course_id IN(
SELECT cid FROM teacher,course
WHERE teacher.tid = course.teacher_id
AND teacher.tname = "李平老师");
--  
-- 13、查询全部学生都选修了的课程号和课程名
-- SELECT score.student_id , course.cid , course.cname FROM score LEFT JOIN course
-- ON score.course_id = course.cid;   错误答案
SELECT cid,cname FROM course 
WHERE cid
IN (
SELECT course_id FROM score 
GROUP BY course_id
HAVING COUNT(*)= (SELECT COUNT(*) FROM student));
-- 
-- 14、查询每门课程被选修的次数
SELECT course_id , COUNT(course_id) "被选修的次数" FROM score
GROUP BY course_id;
-- 
-- 15、查询只选修了一门课程的学生姓名和学号
SELECT sname,sid FROM student
WHERE sid = (
SELECT student_id FROM score
GROUP BY student_id
HAVING COUNT(student_id) = 1);
-- 
-- 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT num FROM score 
ORDER BY num DESC;
-- 
-- 17、查询平均成绩大于85的学生姓名和平均成绩
-- 查询平均成绩大于85分的学生id
SELECT student_id FROM score
GROUP BY student_id
HAVING  AVG(num)>85;
-- 查询大于85分的学生姓名，和成绩
SELECT sname ,num FROM score,student
WHERE score.student_id = student.sid
AND student.sid = (
SELECT student_id FROM score
GROUP BY student_id
HAVING  AVG(num)>85);
-- 
-- 18、查询生物成绩不及格的学生姓名和对应生物分数
-- 查询生物课程id
SELECT cid FROM course
WHERE cname = "生物";
-- 查询所有选生物课程成绩不及格的学生id
SELECT student_id,num FROM score
WHERE course_id = (
SELECT cid FROM course
WHERE cname = "生物")
AND num<60;
-- 查询不及格的分数的学生姓名和分数
SELECT sname,temp.num FROM student ,(
SELECT student_id ,num FROM score
WHERE course_id = (
SELECT cid FROM course
WHERE cname = "生物")
AND num<60) temp
WHERE temp.student_id = student.sid ;

-- 
-- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
-- 查询李平课程ID
SELECT cid FROM teacher,course
WHERE teacher.tid = course.teacher_id
AND teacher.tname = "李平老师";
-- 查询选修了李平所有课程的学生ID
SELECT student_id FROM score
WHERE course_id IN (
SELECT cid FROM teacher,course
WHERE teacher.tid = course.teacher_id
AND teacher.tname = "李平老师")
GROUP BY student_id
HAVING COUNT(student_id) = 2;
-- 查询选修了李平所有课程的学生平均成绩
SELECT student_id , AVG(num) avgNum FROM score
WHERE course_id IN (
SELECT cid FROM teacher,course
WHERE teacher.tid = course.teacher_id
AND teacher.tname = "李平老师")
GROUP BY student_id
HAVING COUNT(student_id) = 2 ;
-- 成绩最高
SELECT sname FROM student , (
SELECT student_id , AVG(num) avgNum FROM score
WHERE course_id IN (
SELECT cid FROM teacher,course
WHERE teacher.tid = course.teacher_id
AND teacher.tname = "李平老师")
GROUP BY student_id
HAVING COUNT(student_id) = 2 ) temp
WHERE student.sid = temp.student_id
ORDER BY temp.avgNum DESC LIMIT 1;

-- 
-- 20、查询每门课程成绩最好的前两名学生姓名,成绩相同并列
-- 查询成绩最好的
SELECT MAX(num) max1Num,course_id FROM score
GROUP BY course_id;
-- 第二好成绩 并且合在一起
SELECT score.course_id,
			 max1Num,
			 MAX(num) max2Num
FROM score,(
		 SELECT MAX(num) max1Num,course_id FROM score
		 GROUP BY course_id) temp
WHERE score.course_id = temp.course_id
AND score.num < max1Num
GROUP BY score.course_id;
-- 找到对应的学生ID
SELECT student_id,score.num ,score.course_id cId FROM score , (
			 SELECT score.course_id,
						  max1Num,
						  MAX(num) max2Num
			 FROM score,(
					  SELECT MAX(num) max1Num,course_id FROM score
					  GROUP BY course_id) temp
			 WHERE score.course_id = temp.course_id
			 AND score.num < max1Num
			 GROUP BY score.course_id) temp1
WHERE score.course_id = temp1.course_id
AND score.num IN (temp1.max1Num,temp1.max2Num);
-- 最终
SELECT sname , cId FROM student , (
				SELECT student_id,score.num ,score.course_id cId FROM score , (
							 SELECT score.course_id,
											max1Num,
											MAX(num) max2Num
							 FROM score,(
										SELECT MAX(num) max1Num,course_id FROM score
										GROUP BY course_id) temp
							 WHERE score.course_id = temp.course_id
							 AND score.num < max1Num
							 GROUP BY score.course_id) temp1
				WHERE score.course_id = temp1.course_id
				AND score.num IN (temp1.max1Num,temp1.max2Num)) temp2
WHERE student.sid = temp2.student_id;
-- 
-- 21、查询不同课程但成绩相同的学号，课程号，成绩
-- 查询成绩相同的学生ID
SELECT score.student_id , score.course_id , score.num FROM score,( SELECT student_id , course_id , num FROM score) temp
WHERE score.course_id != temp.course_id
AND score.num = temp.num;
-- 
-- 22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；
-- 学过李平课程的学生ID
SELECT student_id FROM score
WHERE course_id IN(
      SELECT cid FROM teacher,course
       WHERE teacher.tid = course.teacher_id
         AND teacher.tname = "李平老师")
GROUP BY student_id;
-- 没学过李平课程的学生id,姓名
SELECT sid,sname FROM student
WHERE sid NOT IN (
			SELECT student_id FROM score
			 WHERE course_id IN(
             SELECT cid FROM teacher,course
              WHERE teacher.tid = course.teacher_id
                AND teacher.tname = "李平老师")
       GROUP BY student_id);
-- 最终结果
SELECT sName,cname FROM course RIGHT JOIN (
			 SELECT course_id , temp.sid,temp.sname sName FROM score RIGHT JOIN (
						  SELECT sid,sname FROM student
							WHERE sid NOT IN (
										SELECT student_id FROM score
										 WHERE course_id IN(
													 SELECT cid FROM teacher,course
														WHERE teacher.tid = course.teacher_id
															AND teacher.tname = "李平老师")
										 GROUP BY student_id)) temp
          ON score.student_id = temp.sid) temp2
ON course.cid = temp2.course_id;
-- 
-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
-- 找到学号为1所选修的课程ID
SELECT course_id FROM score
WHERE student_id = 1;
-- 
SELECT student_id ,sname FROM score,student
WHERE student.sid = score.student_id
AND score.course_id IN (
	SELECT course_id FROM score
	WHERE student_id = 1)
GROUP BY student_id;
-- 
-- 24、任课最多的老师中学生单科成绩最高的学生姓名
-- 找到代课最多的教师ID 和课程id
SELECT teacher_id FROM course 
GROUP BY teacher_id
ORDER BY COUNT(*) DESC LIMIT 1;
-- 查找老师带的课程ID
SELECT cid FROM course
WHERE teacher_id =(
		SELECT teacher_id FROM course 
		GROUP BY teacher_id
		ORDER BY COUNT(*) DESC LIMIT 1);
-- 找到学这些课程的最高分
SELECT course_id,MAX(num) maxNum FROM score
WHERE course_id IN (
			SELECT cid FROM course
			WHERE teacher_id =(
						SELECT teacher_id FROM course 
						GROUP BY teacher_id
						ORDER BY COUNT(*) DESC LIMIT 1))
GROUP BY course_id;
-- 找到单科成绩最高的学生id
SELECT score.course_id , student_id FROM score ,(
			 SELECT course_id,MAX(num) maxNum FROM score
			 WHERE course_id IN (
						 SELECT cid FROM course
						 WHERE teacher_id =(
									 SELECT teacher_id FROM course 
									 GROUP BY teacher_id
									 ORDER BY COUNT(*) DESC LIMIT 1))
			 GROUP BY course_id) temp 
 WHERE score.course_id = temp.course_id
 AND score.num = temp.maxNum;
--  最终答案
SELECT sname FROM student , (
	     SELECT score.course_id , student_id FROM score ,(
						  SELECT course_id,MAX(num) maxNum FROM score
						  WHERE course_id IN (
									  SELECT cid FROM course
									  WHERE teacher_id =(
												  SELECT teacher_id FROM course 
												  GROUP BY teacher_id
												  ORDER BY COUNT(*) DESC LIMIT 1))
							GROUP BY course_id) temp 
				WHERE score.course_id = temp.course_id
					AND score.num = temp.maxNum) temp1
WHERE student.sid = temp1.student_id;

MaGgIeOo0

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySql综合题目练习--class,student,score,course,teacher

查询在所有选修了李平老师课程的学生中，这些课程。的同学选修过的一门或者多门课程的同学学号和姓名；、查询平均成绩大于八十分的同学的姓名和平均成绩。、查询所有的课程的名称以及对应的任课老师姓名。查询没有同时选修物理课程和体育课程的学生姓名。、查询不同课程但成绩相同的学号，课程号，成绩。、查询全部学生都选修了的课程号和课程名。、查询只选修了一门课程的学生姓名和学号。老师课程的学生姓名以及选修的课程名称；查询物理课程比生物课程高的学生的学号。、查询李平老师教的课程的所有成绩记录。、查询选修了所有课程的学生姓名。
复制链接

扫一扫