Mysql面试题- 学生,课程,分数

课程表

CREATE TABLE `course` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `course` VALUES ('1', '语文', '1');
INSERT INTO `course` VALUES ('2', '数学', '2');
INSERT INTO `course` VALUES ('3', '英语', '3');

学生表

CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `number` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '学生1', '20200001', '18');
INSERT INTO `student` VALUES ('2', '学生2', '20200002', '19');
INSERT INTO `student` VALUES ('3', '学生3', '20200003', '20');
INSERT INTO `student` VALUES ('4', '学生4', '20200004', '11');
INSERT INTO `student` VALUES ('5', '学生5', '20200005', null);

成绩表

CREATE TABLE `score` (
  `student_no` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  `score` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `score` VALUES ('20200001', '1', '60');
INSERT INTO `score` VALUES ('20200001', '2', '40');
INSERT INTO `score` VALUES ('20200001', '3', '99');
INSERT INTO `score` VALUES ('20200002', '3', '99');
INSERT INTO `score` VALUES ('20200002', '2', '80');
INSERT INTO `score` VALUES ('20200002', '1', '60');
INSERT INTO `score` VALUES ('20200003', '1', '50');
INSERT INTO `score` VALUES ('20200003', '2', '59');
INSERT INTO `score` VALUES ('20200004', '2', '80');
INSERT INTO `score` VALUES ('20200004', '1', '50');

教师表

CREATE TABLE `teacher` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `number` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` VALUES ('1', '老师1', '30201');
INSERT INTO `teacher` VALUES ('2', '老师2', '30202');
INSERT INTO `teacher` VALUES ('3', '老师3', '30203');

问题及答案

https://blog.csdn.net/qq_42327755/article/details/109006251