Mysql的作业练习及答案

Mysql的作业练习及答案

先附上创建的数据库导出的内容（表格以及表格内数据）：

   /*
Navicat MySQL Data Transfer

Source Server         : MySQL
Source Server Version : 50562
Source Host           : 127.0.0.1:3306
Source Database       : homework

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

Date: 2021-03-09 22:06:28
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `caption` varchar(20) NOT NULL,
  `grade_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`cid`),
  KEY `grade_id` (`grade_id`),
  CONSTRAINT `grade_id` FOREIGN KEY (`grade_id`) REFERENCES `class_grade` (`gid`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '一年一班', '1');
INSERT INTO `class` VALUES ('2', '二年一班', '2');
INSERT INTO `class` VALUES ('3', '三年二班', '3');
INSERT INTO `class` VALUES ('4', '一年二班', '1');
INSERT INTO `class` VALUES ('5', '一年三班', '1');
INSERT INTO `class` VALUES ('6', '一年四班', '1');
INSERT INTO `class` VALUES ('7', '二年二班', '2');
INSERT INTO `class` VALUES ('8', '四年一班', '4');
INSERT INTO `class` VALUES ('9', '三年一班', '3');

-- ----------------------------
-- Table structure for class_grade
-- ----------------------------
DROP TABLE IF EXISTS `class_grade`;
CREATE TABLE `class_grade` (
  `gid` int(10) NOT NULL,
  `gname` varchar(20) NOT NULL,
  PRIMARY KEY (`gid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class_grade
-- ----------------------------
INSERT INTO `class_grade` VALUES ('1', '一年级');
INSERT INTO `class_grade` VALUES ('2', '二年级');
INSERT INTO `class_grade` VALUES ('3', '三年级');
INSERT INTO `class_grade` VALUES ('4', '四年级');
INSERT INTO `class_grade` VALUES ('5', '五年级');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(10) NOT NULL AUTO_INCREMENT,
  `cname` varchar(20) DEFAULT NULL,
  `teacher_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`cid`),
  KEY `teacher_id` (`teacher_id`),
  CONSTRAINT `teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '英语', '1');
INSERT INTO `course` VALUES ('2', '生物', '1');
INSERT INTO `course` VALUES ('3', '物理', '2');
INSERT INTO `course` VALUES ('4', '化学', '3');
INSERT INTO `course` VALUES ('5', '美术', '4');
INSERT INTO `course` VALUES ('6', '音乐', '2');
INSERT INTO `course` VALUES ('7', '语文', '3');
INSERT INTO `course` VALUES ('8', '数学', '4');
INSERT INTO `course` VALUES ('9', '地理', '2');
INSERT INTO `course` VALUES ('10', '研究', '1');
INSERT INTO `course` VALUES ('11', 'IT', '5');
INSERT INTO `course` VALUES ('12', 'python', '5');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(10) NOT NULL AUTO_INCREMENT,
  `student_id` int(10) DEFAULT NULL,
  `course_id` int(10) DEFAULT NULL,
  `score` int(10) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `course_id` (`course_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `course_id` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE SET NULL,
  CONSTRAINT `student_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '60');
INSERT INTO `score` VALUES ('2', '1', '2', '59');
INSERT INTO `score` VALUES ('3', '2', '2', '99');
INSERT INTO `score` VALUES ('4', '3', '1', '80');
INSERT INTO `score` VALUES ('5', '1', '3', '32');
INSERT INTO `score` VALUES ('166', '8', '1', '60');
INSERT INTO `score` VALUES ('167', '8', '2', '59');
INSERT INTO `score` VALUES ('168', '6', '3', '58');
INSERT INTO `score` VALUES ('169', '1', '4', '22');
INSERT INTO `score` VALUES ('170', '1', '5', '59');
INSERT INTO `score` VALUES ('171', '1', '6', '60');
INSERT INTO `score` VALUES ('172', '1', '7', '99');
INSERT INTO `score` VALUES (