sql in interview for a job

1、mysql下建表及插入数据

/*
Navicat MySQL Data Transfer

Source Server         : mysql
Source Server Version : 50640
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50640
File Encoding         : 65001

Date: 2018-11-22 18:24:08
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for cource
-- ----------------------------
DROP TABLE IF EXISTS `cource`;
CREATE TABLE `cource` (
  `cno` int(3) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  `cname` varchar(20) DEFAULT NULL COMMENT '课程名称',
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='课程表';

-- ----------------------------
-- Records of cource
-- ----------------------------
INSERT INTO `cource` VALUES ('1', '语文');
INSERT INTO `cource` VALUES ('2', '数学');
INSERT INTO `cource` VALUES ('3', '英语');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sno` int(3) unsigned DEFAULT NULL COMMENT '学生编号',
  `cno` int(3) unsigned DEFAULT NULL COMMENT '课程编号',
  `score` int(3) unsigned DEFAULT NULL COMMENT '考试成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生成绩表';

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '63');
INSERT INTO `score` VALUES ('1', '2', '61');
INSERT INTO `score` VALUES ('2', '1', '80');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` int(3) unsigned NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `sname` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `sage` int(3) unsigned DEFAULT NULL COMMENT '学生年龄',
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='学生表';

-- ----------------------------
-- 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');

---

2、查询语文成绩比数学成绩高的所有学生的编号
分析思路：
（1）在学生表上添加两个字段，分别为数学成绩字段和语文成绩字段
（2）把成绩表分成两个表，语文表和数学表
书写过程：
（1）语文表
select sno, score from score where cno = 1
（2）数学表
select sno, score from score where cno = 2
（3）结果
select sno, sname, score_chinese, score_math from
    (
        select s.sno, s.sname,  score_chinese from student s
        left join (select sno sno_chinese, score score_chinese from score where cno = 1) chinese
        on s.sno = chinese.sno_chinese
    ) s_chinese
left join (select sno sno_math, score score_math from score  where cno = 2) math
on s_chinese.sno = math.sno_math
where score_chinese > score_math

---

3、查询所有学生的学号、姓名、选课数、总成绩
分析思路：
（1）在学生表上添加两个字段，分别为选课数和总成绩
（2）把成绩表按学生编号分组，并计算出count(*)和sum(score)
书写过程：
（1）分组并统计
select sno, count(*), sum(score) from score group by sno
（2）结果
select s.sno, s.sname, select_count, select_sum from student s
left join (select sno sno_count_sum, count(*) select_count, sum(score) select_sum from score group by sno) count_sum
on s.sno = count_sum.sno_count_sum

---

4、查询没有学完所有课程的学生学号、姓名
分析思路：把成绩表按学生编号并count(*)
select sno, count(cno) cno_count from score group by sno having cno_count > 1