常见的sql面试题(学生、成绩、课程 三表操作)

一、学生、成绩、课程  三表操作

-- 问题1、查询课程1比课程2成绩高的所有学生的编号
-- 问题2、查询所有学生的学号、姓名、选课数、总成绩
-- 问题3、查询没有学完所有课程的学生的学号和姓名

首先初始化表和数据；

/*
Navicat MySQL Data TransferSource Server         : Mysql
Source Server Version : 50615
Source Host           : 127.0.0.1:3306
Source Database       : demoTarget Server Type    : MYSQL
Target Server Version : 50615
File Encoding         : 65001Date: 2020-10-11 22:53:24
*/SET FOREIGN_KEY_CHECKS=0; --禁用外键约束-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (`sno` int(3) NOT NULL AUTO_INCREMENT COMMENT '主键',`sname` varchar(10) NOT NULL COMMENT '姓名',`sage` int(4) DEFAULT NULL COMMENT '年龄',PRIMARY KEY (`sno`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '周杰伦', '18');
INSERT INTO `student` VALUES ('2', '周润发', '18');
INSERT INTO `student` VALUES ('3', '吴孟达', '25');
INSERT INTO `student` VALUES ('4', '刘德华', '25');
INSERT INTO `student` VALUES ('5', '李连杰', '29');-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (`sno` int(3) NOT NULL COMMENT '学号主键',`cno` int(3) NOT NULL COMMENT '成绩主键',`score` int(4) NOT NULL COMMENT '年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '60');
INSERT INTO `score` VALUES ('1', '2', '61');
INSERT INTO `score` VALUES ('2', '1', '80');
INSERT INTO `score` VALUES ('2', '2', '70');-- ----------------------------
-- Table structure for cource
-- ----------------------------
DROP TABLE IF EXISTS `cource`;
CREATE TABLE `cource` (`cno` int(3) NOT NULL AUTO_INCREMENT COMMENT '主键',`cname` varchar(10) NOT NULL COMMENT '姓名',PRIMARY KEY (`cno`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;-- ----------------------------
-- Records of cource
-- ----------------------------
INSERT INTO `cource` VALUES ('1', '语文');
INSERT INTO `cource` VALUES ('2', '数学');
INSERT INTO `cource` VALUES ('3', '英语');

实际问题：

-- 问题1、查询课程1比课程2成绩高的所有学生的编号
SELECT score1.sno FROM(SELECT * FROM score t WHERE t.cno = 1 ) AS score1,(SELECT * FROM score t WHERE t.cno = 2 ) AS score2
WHEREscore1.score > score2.score
AND score1.sno = score2.sno;-- 问题2、查询所有学生的学号、姓名、选课数、总成绩
SELECTt.sno,t.sname,COUNT(t1.cno) AS cources,-- SUM(t1.score) //无数据为nullSUM(case WHEN t1.score is NULL then 0 else t1.score END) AS scores -- 数据为null,则补为0
FROMstudent t
LEFT JOIN score t1 ON t.sno = t1.sno
GROUP BYt.sno;-- 问题3、查询没有学完所有课程的学生的学号和姓名
SELECT DISTINCT t.sno,t.sname FROM student t
LEFT JOIN score t1 ON t.sno = t1.sno
WHERE t1.score is null;