MySQL数据库子查询练习

课程表DDL

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseName` varchar(20) NOT NULL COMMENT '课程名称',
  `department` varchar(30) NOT NULL,
  `lv` int(11) DEFAULT NULL COMMENT '年级',
  `number` int(11) DEFAULT NULL COMMENT '课程人数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询语句

#score表中计算所有科目各自的平均分，选出分数最高的一组，获取它的课程名称，院系，课程人数。
#子查询
select courseId from score GROUP BY courseId ORDER BY avg(result)desc limit 1;
#完整查询
select courseName'课程名称'，department'院系'，number '人数' from course where id=
(
select courseId from score GROUP BY courseId ORDER BY avg(result)desc limit 1
);