单表学生成绩之SQL面试题

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `course` varchar(30) NOT NULL,
  `gore` int(11) NOT NULL,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '王', '语文', '91');
INSERT INTO `student` VALUES ('2', '王', '数学', '59');
INSERT INTO `student` VALUES ('3', '王', '英语', '65');
INSERT INTO `student` VALUES ('4', '王', '化学', '71');
INSERT INTO `student` VALUES ('5', '王', '物理', '80');
INSERT INTO `student` VALUES ('6', '李', '语文', '92');
INSERT INTO `student` VALUES ('7', '李',