面试中遇到的SQL笔试题

题1：建一学生表student，课程表course，包含选修课程，最后建一关联学生表与课程表的scourse表，写出建表与插入SQL语句。

DROP TABLE IF EXISTS `cource`;
CREATE TABLE `cource` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of cource
-- ----------------------------
INSERT INTO `cource` VALUES ('1', '语文');
INSERT INTO `cource` VALUES ('2', '数学');
INSERT INTO `cource` VALUES ('3', '物理');
INSERT INTO `cource` VALUES ('4', '化学');
INSERT INTO `cource` VALUES ('5', '地理');
INSERT INTO `cource` VALUES ('6', '历史');
INSERT INTO `cource` VALUES ('7', '英语');
INSERT INTO `cource` VALUES ('8', '政治');
INSERT INTO `cource` VALUES ('9', '计算机');
INSERT INTO `cource` VALUES ('10', '体育');

-- ----------------------------
-- Table structure for `scouse`
-- ----------------------------
DROP TABLE IF EXISTS `scouse`;
CREATE TABLE `scouse` (
  `sc_id` int(11) NOT NULL AUTO_INCREMENT,
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`sc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of scouse
-- ----------------------------
INSERT INTO `scouse` VALUES ('1', '1', '1');
INSERT INTO `scouse` VALUES ('2', '1', '2');
INSERT INTO `scouse` VALUES ('3', '1', '3');
INSERT INTO `scouse` VALUES ('4', '1', '4');
INSERT INTO `scouse` VALUES ('5', '2', '1');
INSERT INTO `scouse` VALUES ('6', '2', '2');
INSERT INTO `scouse` VALUES ('7', '2', '3');
INSERT INTO `scouse` VALUES ('8', '2', '7');
INSERT INTO `scouse` VALUES ('9', '2', '9');
INSERT INTO `scouse` VALUES ('10', '3', '2');
INSERT INTO `scouse` VALUES ('11', '3', '5');
INSERT INTO `scouse` VALUES ('12', '4', '3');
INSERT INTO `scouse` VALUES ('13', '5', '5');
INSERT INTO `scouse` VALUES ('14', '6', '1');
INSERT INTO `scouse` VALUES ('15', '6', '3');
INSERT INTO `scouse` VALUES ('16', '7', '2');
INSERT INTO `scouse` VALUES ('17', '7', '4');
INSERT INTO `scouse` VALUES ('18', '7', '10');
INSERT INTO `scouse` VALUES ('19', '8', '6');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '13');
INSERT INTO `student` VALUES ('2', '李四', '15');
INSERT INTO `student` VALUES ('3', '王五', '17');
INSERT INTO `student` VALUES ('4', '赵六', '19');
INSERT INTO `student` VALUES ('5', '田七', '16');
INSERT INTO `student` VALUES ('6', '钱八', '12');
INSERT INTO `student` VALUES ('7', '孙九', '17');
INSERT INTO `student` VALUES ('8', '甄十', '14');

题2：查询所有学生的选修课大于2门课程的。

select * from student s left join scouse sc on s.sid=sc.sid 
left join cource c on sc.cid=c.cid GROUP BY s.sid HAVING count(sc.sid)>2