面试题解答

编写一个SQL，找出那些 没有满分(100分)课程 的学生的 最高分和对应课程名字

期望结果：

+------------------------------------------------+
| 无满分成绩的学生名| 最高成绩 | 最高成绩课程名 |
+------------------------------------------------+
| jack  | 91  | English |
|mike | 99  | Sport    |
|tom  | 96  | English |

数据库脚本如下（MySQL数据库）
CREATE TABLE IF NOT EXISTS `tbl_middle_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  `grade` varchar(50) NOT NULL,
  `gender` varchar(50) NOT NULL,
  `height` int(11) DEFAULT NULL COMMENT '身高',
  `classid` int(11) DEFAULT NULL COMMENT '班级ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `tbl_middle_student` (`id`, `name`, `age`, `grade`, `gender`, `height`, `classid`) VALUES
(1, 'jack', 13, '3', 'male', 160, 1),
(2, 'kate', 13, '3', 'female', 165, 1),
(3, 'candy', 13, '3', 'female', 155, 1),
(4, 'tom', 13, '3', 'male', 175, 2),
(5, 'bob', 13, '3', 'male', 167, 2),
(6, 'jerry', 13, '3', 'male', 166, 2),
(7, 'mary', 13, '3', 'female', 167, 3),
(8, 'amanda', 13, '3', 'female', 170, 3),
(9, 'lee', 13, '3', 'male', 175, 3),
(10, 'mike', 13, '3', 'male', 178, 2);

CREATE TABLE IF NOT EXISTS `tbl_middle_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) NOT NULL COMMENT '学生表ID',
  `course_name` varchar(50) NOT NULL COMMENT '课程名',
  `score` int(11) NOT NULL COMMENT '课程分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;


INSERT INTO `tbl_middle_score` (`id`, `stu_id`, `course_name`, `score`) VALUES
(1, 1, 'Chinese', 80),
(2, 1, 'Math', 66),
(3, 1, 'English', 91),
(4, 1, 'Sport', 88),
(5, 2, 'Chinese', 99),
(6, 2, 'Math', 100),
(7, 2, 'English', 88),
(8, 2, 'Sport', 81),
(9, 3, 'Chinese', 81),
(10, 3, 'Math', 60),
(11, 3, 'English', 100),
(12, 3, 'Sport', 90),
(13, 4, 'Chinese', 77),
(14, 4, 'Math', 85),
(15, 4, 'English', 96),
(16, 4, 'Sport', 66),
(17, 5, 'Chinese', 55),
(18, 5, 'Math', 85),
(19, 5, 'English', 45),
(20, 5, 'Sport', 100),
(21, 6, 'Chinese', 86),
(22, 6, 'Math', 100),
(23, 6, 'English', 56),
(24, 6, 'Sport', 75),
(25, 7, 'Chinese', 100),
(26, 7, 'Math', 99),
(27, 7, 'English', 85),
(28, 7, 'Sport', 78),
(29, 8, 'Chinese', 86),
(30, 8, 'Math', 100),
(31, 8, 'English', 100),
(32, 8, 'Sport', 100),
(33, 9, 'Chinese', 86),
(34, 9, 'Math', 100),
(35, 9, 'English', 87),
(36, 9, 'Sport', 100),
(37, 10, 'Chinese', 79),
(38, 10, 'Math', 56),
(39, 10, 'English', 85),
(40, 10, 'Sport', 99);