mysql查询挂科门数_MySQL查询练习题

说明

工作多年，对mysql查询语句的使用一直停留在将就用，遇到难一点的、复杂一点的就束手无策的状态。这几天闲着没事做，找了一些练习题做下，感觉对select语句稍微熟悉一点了。原文转载自其他地方，但原文没有答案，所以写此文记录一下。

### 数据准备

/*

数据导入：

Navicat Premium Data Transfer

Source Server : localhost

Source Server Type : MySQL

Source Server Version : 50624

Source Host : localhost

Source Database : sqlexam

Target Server Type : MySQL

Target Server Version : 50624

File Encoding : utf-8

Date: 10/21/2016 06:46:46 AM

*/

SET NAMES utf8;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for `class`

-- ----------------------------

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (

`cid` int(11) NOT NULL AUTO_INCREMENT,

`caption` varchar(32) NOT NULL,

PRIMARY KEY (`cid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of `class`

-- ----------------------------

BEGIN;

INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');

COMMIT;

-- ----------------------------

-- Table structure for `course`

-- ----------------------------

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`cid` int(11) NOT NULL AUTO_INCREMENT,

`cname` varchar(32) NOT NULL,

`teacher_id` int(11) NOT NULL,

PRIMARY KEY (`cid`),

KEY `fk_course_teacher` (`teacher_id`),

CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of `course`

-- ----------------------------

BEGIN;

INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');

COMMIT;

-- ----------------------------

-- Table structure for `score`

-- ----------------------------

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (

`sid` int(11) NOT NULL AUTO_INCREMENT,

`student_id` int(11) NOT NULL,

`course_id` int(11) NOT NULL,

`num` int(11) NOT NULL,

PRIMARY KEY (`sid`),

KEY `fk_score_student` (`student_id`),

KEY `fk_score_course` (`course_id`),

CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),

CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)

) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of `