这是前几天一刚毕业的朋友的面试题,算是面试常遇到的sql经典题目,记录一下(如图)
在此我在电脑数据库上建立了对应的表结构及数据,以供检验sql语句正误。
建表语句:
- 学生表(student_info)
DROP TABLE IF EXISTS `student_info`;
CREATE TABLE `student_info` (
`no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(10) NULL DEFAULT NULL,
`dept` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;INSERT INTO `student_info` VALUES ('1612101', '李勇', '男', 21, '数学系');
INSERT INTO `student_info` VALUES ('1612102', '刘晨', '男', 23, '数学系');
INSERT INTO `student_info` VALUES ('1612103', '王敏', '女', 21, '信息工程系');
INSERT INTO `student_info` VALUES ('1612104', '张立', '男', 20, '信息工程系');
- 课程表(course_info)
DROP TABLE IF EXISTS `course_info`;
CREATE TABLE `course_info` (
`no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`hours` int(10) NULL DEFAULT NULL,
PRIMARY KEY (`no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;INSERT INTO `course_info` VALUES ('C01', '计算机导论', 70);
INSERT INTO `course_info` VALUES ('C02', 'VC++', 90);
INSERT INTO `course_info` VALUES ('C03', '计算机网络', 80);
INSERT INTO `course_info` VALUES ('C04', '数据库基础', 105);
INSERT INTO `course_info` VALUES ('C05', '高等数学', 180);
INSERT INTO `course_info` VALUES ('C06', '数据结构', 72);
- 选课表(choice_info)
DROP TABLE IF EXISTS `choice_info`;
CREATE TABLE `choice_info` (
`student_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`course_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`grade` int(10) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;INSERT INTO `choice_info` VALUES ('1612101', 'C01', 90);
INSERT INTO `choice_info` VALUES ('1612101', 'C02', 86);
INSERT INTO `choice_info` VALUES ('1612101', 'C05', 84);
INSERT INTO `choice_info` VALUES ('1612102', 'C01', 85);
INSERT INTO `choice_info` VALUES ('1612102', 'C03', 91);
INSERT INTO `choice_info` VALUES ('1612102', 'C06', 76);
INSERT INTO `choice_info` VALUES ('1612103', 'C04', 80);
INSERT INTO `choice_info` VALUES ('1612103', 'C03', 82);
对应各个表中的数据根据图中提供进行插入
1.查询学生都选修了哪些课程,要求列出课程名、课程号、选修人数以及最好成绩;
SELECT
course.`name`,
course.`no`,
COUNT(choice.course_no) `count`,
MAX(choice.grade) Maxgrade
FROM
course_info course,
choice_info choice
WHERE
choice.course_no = course.`no`
GROUP BY
choice.course_no;
2.统计每个学生的选课门数,并按选课门数的递减顺序显示(注:题为每个学生,故包含未选课学生)
SELECT
student.`name`,
COUNT(choice.course_no) `count`
FROM
choice_info choice
RIGHT JOIN student_info student ON choice.student_no = student. NO
GROUP BY
student.no
ORDER BY
COUNT(choice.course_no) DESC;
3.查询选课门数超过2门的学生的平均成绩和选课门数;
SELECT
a.`name`,
a.avg,
a.count
FROM
(
SELECT
student.`name` `name`,
IFNULL(AVG(choice.grade), 0) `avg`,
COUNT(choice.course_no) `count`
FROM
student_info student
LEFT JOIN choice_info choice ON choice.student_no = student.`no`
GROUP BY
student.`no`
) a
WHERE
a.count > 2;
4.查询有考试成绩的所有学生的姓名、修课名称及考试成绩,并将查询结果放到一张新的永久表(假设新表名为tb_rel_new)中
注:此题包含新建表和插入查询数据
- 查询
SELECT
a.`name` studentName,
course.`name` courseName,
a.grade
FROM
course_info course
LEFT JOIN (
SELECT
student.`name` `name`,
choice.grade grade,
choice.course_no courseNo
FROM
student_info student
RIGHT JOIN choice_info choice ON choice.student_no = student.`no`
) a ON a.courseNo = course.`no`;
- 创建tb_rel_new表
CREATE TABLE `tb_rel_new` (
`studentName` VARCHAR (255) DEFAULT NULL,
`courseName` VARCHAR (255) DEFAULT NULL,
`grade` INT (10) DEFAULT NULL
) ENGINE = INNODB DEFAULT CHARSET = utf8;
- 插入数据
INSERT INTO tb_rel_new (
studentName,
courseName,
grade
) SELECT
a.`name` studentName,
course.`name` courseName,
a.grade
FROM
course_info course
LEFT JOIN (
SELECT
student.`name` `name`,
choice.grade grade,
choice.course_no courseNo
FROM
student_info student
RIGHT JOIN choice_info choice ON choice.student_no = student.`no`
) a ON a.courseNo = course.`no`;
总结:此题主要考察数据库sql基础,面试中要快速理清数据关系,问题便迎刃而解。