【MySql 数据库综合练习04】

sakura欣悠

已于 2022-08-25 14:08:27 修改

阅读量93

点赞数 1

分类专栏： MySql学习文章标签：数据库 mysql java

于 2022-08-23 17:36:42 首次发布

本文链接：https://blog.csdn.net/sakura22123/article/details/126459664

版权

MySql学习专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySql 数据库综合练习04

提示：部分数据不满足查询要求，即结果为null

数据库综合练习04

MySql 数据库综合练习04
一、数据库创建代码
二、题目

提示：以下均为内容为题目 + sql查询语句 + 查询结果图

一、数据库创建代码

CREATE TABLE class (
  cid int(11) NOT NULL AUTO_INCREMENT,
  caption varchar(32) NOT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB CHARSET=utf8;


CREATE TABLE student(
  sid int(11) NOT NULL AUTO_INCREMENT,
  gender char(1) NOT NULL,
  class_id int(11) NOT NULL,
  sname varchar(32) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_class (class_id),
  CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE teacher(
  tid int(11) NOT NULL AUTO_INCREMENT,
  tname varchar(32) NOT NULL,
  PRIMARY KEY (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE course(
  cid int(11) NOT NULL AUTO_INCREMENT,
  cname varchar(32) NOT NULL,
  teacher_id int(11) NOT NULL,
  PRIMARY KEY (cid),
  KEY fk_course_teacher (teacher_id),
  CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE score (
  sid int(11) NOT NULL AUTO_INCREMENT,
  student_id int(11) NOT NULL,
  course_id int(11) NOT NULL,
  num int(11) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_score_student (student_id),
  KEY fk_score_course (course_id),
  CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
  CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO class VALUES
(1, '三年二班'), 
(2, '三年三班'), 
(3, '一年二班'), 
(4, '二年九班');

INSERT INTO student VALUES
(1, '男', 1, '理解'), 
(2, '女', 1, '钢蛋'), 
(3, '男', 1, '张三'), 
(4, '男', 1, '张一'), 
(5, '女', 1, '张二'), 
(6, '男', 1, '张四'), 
(7, '女', 2, '铁锤'), 
(8, '男', 2, '李三'), 
(9, '男', 2, '李一'), 
(10, '女', 2, '李二'), 
(11, '男', 2, '李四'), 
(12, '女', 3, '如花'), 
(13, '男', 3, '刘三'), 
(14, '男', 3, '刘一'), 
(15, '女', 3, '刘二'), 
(16, '男', 3, '刘四');


INSERT INTO teacher VALUES
(1, '张磊老师'), 
(2, '李平老师'), 
(3, '刘海燕老师'), 
(4, '朱云海老师'), 
(5, '李杰老师');


INSERT INTO course VALUES
(1, '生物', 1), 
(2, '物理', 2), 
(3, '体育', 3), 
(4, '美术', 2);

INSERT INTO score VALUES
(1, 1, 1, 10),
(2, 1, 2, 9),
(5, 1, 4, 66),
(6, 2, 1, 8),
(8, 2, 3, 68),
(9, 2, 4, 99),
(10, 3, 1, 77),
(11, 3, 2, 66),
(12, 3, 3, 87),
(13, 3, 4, 99),
(14, 4, 1, 79),
(15, 4, 2, 11),
(16, 4, 3, 67),
(17, 4, 4, 100),
(18, 5, 1, 79),
(19, 5, 2, 11),
(20, 5, 3, 67),
(21, 5, 4, 100),
(22, 6, 1, 9),
(23, 6, 2, 100),
(24, 6, 3, 67),
(25, 6, 4, 100),
(26, 7, 1, 9),
(27, 7, 2, 100),
(28, 7, 3, 67),
(29, 7, 4, 88),
(30, 8, 1, 9),
(31, 8, 2, 100),
(32, 8, 3, 67),
(33, 8, 4, 88),
(34, 9, 1, 91),
(35, 9, 2, 88),
(36, 9, 3, 67),
(37, 9, 4, 22),
(38, 10, 1, 90),
(39, 10, 2, 77),
(40, 10, 3, 43),
(41, 10, 4, 87),
(42, 11, 1, 90),
(43, 11, 2, 77),
(44, 11, 3, 43),
(45, 11, 4, 87),
(46, 12, 1, 90),
(47, 12, 2, 77),
(48, 12, 3, 43),
(49, 12, 4, 87),
(52, 13, 3, 87);

二、题目

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

-- 1、查询所有的课程的名称以及对应的任课老师姓名
SELECT  C.cname,T.tname
FROM course C 
JOIN teacher T ON (C.teacher_id = T.tid)

在这里插入图片描述

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

-- 2、查询学生表中男女生各有多少人
SELECT COUNT(student.sid),student.gender
FROM student
GROUP BY student.gender

在这里插入图片描述

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

-- 3、查询物理成绩等于100的学生的姓名
SELECT S.sname
FROM score SC
JOIN course C ON (SC.course_id = C.cid)
JOIN student S ON (S.sid  = SC.student_id)
WHERE C.cname = '物理' AND SC.num = '100'

在这里插入图片描述

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

-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT S.sname,AVG(SC.num)
FROM score SC  
JOIN student S ON (S.sid = SC.student_id)
GROUP BY SC.student_id
HAVING AVG(SC.num) > 80

在这里插入图片描述

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

-- 5、查询所有学生的学号，姓名，选课数，总成绩
SELECT S.sid,S.sname,COUNT(SC.course_id),SUM(SC.num)
FROM student S 
LEFT JOIN score SC ON (SC.student_id = S.sid)
GROUP BY S.sid

在这里插入图片描述

6、查询姓李老师的个数

-- 6、 查询姓李老师的个数
SELECT COUNT(teacher.tid)
FROM teacher 
WHERE teacher.tname LIKE '李%'

在这里插入图片描述

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

-- 7、 查询没有报李平老师课的学生姓名
SELECT student.sname
FROM student 
WHERE student.sid NOT IN (SELECT S.sid
FROM student S
JOIN score SC ON (SC.student_id = S.sid)
JOIN course C  ON (C.cid = SC.course_id)
JOIN teacher T ON (T.tid = C.teacher_id)
WHERE T.tname = '李平老师'
GROUP BY S.sid)

在这里插入图片描述

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

-- 8、 查询物理课程比生物课程高的学生的学号
SELECT S.sname
FROM student S
WHERE  (SELECT SC.num
				FROM score SC 
				JOIN course C ON (C.cid = SC.course_id)
				WHERE C.cname = '物理' AND  SC.student_id = S.sid) 
				> 
				SELECT SC.num
				FROM score SC 
				JOIN course C ON (C.cid = SC.course_id)
				WHERE C.cname = '生物' AND   SC.student_id = S.sid)

在这里插入图片描述

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

-- 9、 查询没有同时选修物理课程和体育课程的学生姓名
SELECT student.sname
FROM student 
WHERE student.sid NOT IN (SELECT S.sid
			FROM student S  
			JOIN score SC ON (SC.student_id = S.sid)
			JOIN course C ON (C.cid = SC.course_id AND C.cname IN ('体育','物理'))
			GROUP BY S.sid)

在这里插入图片描述

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

-- 10、查询挂科超过两门(包括两门)的学生姓名和班级、查询选修了所有课程的学生姓名
SELECT  S.sname,CL.caption
FROM student S 
JOIN score SC ON (S.sid = SC.student_id)
JOIN class CL ON (CL.cid = S.class_id)
WHERE SC.num <60
GROUP BY S.sid
HAVING COUNT(SC.num) >=2

在这里插入图片描述

11、查询选修了所有课程的学生姓名

-- 11、查询选修了所有课程的学生姓名
SELECT S.sname,CL.caption
FROM student S 
JOIN score SC ON (S.sid = SC.student_id)
JOIN class CL ON (CL.cid = S.class_id)
GROUP BY S.sid
HAVING COUNT(SC.course_id) = (SELECT COUNT(*) 
	FROM course )

在这里插入图片描述

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

-- 12、查询李平老师教的课程的所有成绩记录
SELECT SC.num
FROM teacher T 
JOIN course C ON (T.tid = C.teacher_id)
JOIN score SC ON (SC.course_id = C.cid)
WHERE T.tname = '李平老师'

在这里插入图片描述

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

验证数据可知没有任何一个课程满足要求

-- 13、查询全部学生都选修了的课程号和课程名
SELECT C.cid,C.cname
FROM score SC 
JOIN student S ON (S.sid = SC.student_id)
JOIN course C ON (C.cid = SC.course_id)
GROUP BY SC.course_id
HAVING  COUNT(S.sid) = (SELECT COUNT(*) FROM student )

在这里插入图片描述

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

-- 14、查询每门课程被选修的次数
SELECT C.cname,COUNT(SC.student_id)
FROM course C
JOIN score SC ON (C.cid = SC.course_id)
GROUP BY SC.course_id

在这里插入图片描述

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

-- 15、查询只选修了一门课程的学生姓名和学号
SELECT S.sname,S.sid
FROM student S 
JOIN score SC ON (S.sid = SC.student_id)
GROUP BY SC.student_id 
HAVING COUNT(SC.course_id) = 1

在这里插入图片描述

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

-- 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT(num)
FROM score 
ORDER BY num DESC

在这里插入图片描述

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

-- 17、查询平均成绩大于85的学生姓名和平均成绩
SELECT S.sname,AVG(SC.num)
FROM score SC 
JOIN student S ON (SC.student_id = S.sid)
GROUP BY SC.student_id
HAVING AVG(SC.num) > 85

在这里插入图片描述

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

-- 18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT S.sname,SC.num
FROM score SC 
JOIN student S ON (SC.student_id = S.sid)
JOIN course C ON (C.cid = SC.course_id)
WHERE C.cname = '生物' AND SC.num < 60

在这里插入图片描述

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

-- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT S.sname,AVG(SC.num)
FROM student S 
JOIN score SC ON (S.sid = SC.student_id)
JOIN course C ON (C.cid = SC.course_id )
JOIN teacher T ON (T.tid = C.teacher_id)
WHERE T.tname ='李平老师'
GROUP BY SC.student_id 
ORDER BY AVG(SC.num) DESC
LIMIT 1

在这里插入图片描述

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

-- 20、查询每门课程成绩最好的前两名学生姓名
SELECT G.hnum,S.sname
FROM (SELECT MAX(score.num) AS hnum,score.course_id,score.student_id
FROM score 
GROUP BY score.course_id,score.student_id
UNION
SELECT MAX(SC.num) AS hnum,SC.course_id,SC.student_id
FROM score SC
WHERE SC.num < (SELECT MAX(score.num)
		FROM score 
		WHERE score.course_id = SC.course_id
		GROUP BY score.course_id)
GROUP BY SC.course_id,SC.student_id) AS G
JOIN student S ON (S.sid = G.student_id)
ORDER BY G.course_id ASC,G.hnum DESC

在这里插入图片描述

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

--  20、查询每门课程成绩最好的前两名学生姓名
SELECT
	MAX( score.num ) AS hnum,
	score.course_id
FROM
	score 
GROUP BY
	score.course_id

	UNION
SELECT
	MAX( SC.num ) AS hnum,
	SC.course_id
FROM
	score SC 
WHERE
	SC.num <= ( SELECT MAX( score.num ) FROM score WHERE score.course_id = SC.course_id AND score.sid <> SC.sid) 
GROUP BY
	SC.course_id

在这里插入图片描述

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

-- 21、查询不同课程但成绩相同的学号，课程号，成绩
SELECT DISTINCT(A.course_id),A.student_id,A.num
FROM score A 
WHERE A.num IN (SELECT num FROM score WHERE score.sid <> A.sid AND score.course_id <> A.course_id)
ORDER BY A.num

在这里插入图片描述

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

-- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
SELECT S.sname,C.cname
FROM student S 
JOIN score SC ON (S.sid =  SC.student_id)
JOIN course C ON (C.cid = SC.course_id)
WHERE S.sid NOT IN (SELECT S.sid
FROM student S 
JOIN score SC ON (S.sid =  SC.student_id)
JOIN course C ON (C.cid = SC.course_id)
JOIN teacher T ON (T.tid = C.teacher_id)
WHERE T.tname = '李平老师')

在这里插入图片描述

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

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

SELECT student.sid,student.sname
FROM student 
JOIN score ON (student.sid = score.student_id)
WHERE score.course_id IN (SELECT SC.course_id
FROM student S
JOIN score SC  ON (S.sid = SC.student_id)
WHERE S.sid = '1') AND student.sid <> '1'

在这里插入图片描述

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

-- 24、任课最多的老师中学生单科成绩最高的学生姓名
SELECT S.sname
FROM teacher T 
JOIN course C ON (T.tid = C.teacher_id)
JOIN score SC ON (SC.course_id = C.cid)
JOIN student S ON (S.sid = SC.student_id)
WHERE T.tid = (SELECT  T.tid
	FROM teacher T 
	JOIN course C ON (T.tid = C.teacher_id)
	GROUP BY T.tid
	ORDER BY MAX(C.cid) DESC 
	LIMIT 1)
ORDER BY SC.num DESC 
LIMIT 1