SQL测试题

一.网上收集了一些测试题,对于掌握SQL查询语句。
  1 /*
  2  Navicat MySQL Data Transfer
  3 
  4  Source Server         : lizebo's MYSQL
  5  Source Server Type    : MySQL
  6  Source Server Version : 50720
  7  Source Host           : localhost:3306
  8  Source Schema         : sql练习
  9 
 10  Target Server Type    : MySQL
 11  Target Server Version : 50720
 12  File Encoding         : 65001
 13 
 14  Date: 07/11/2017 20:09:05
 15 */
 16 
 17 SET NAMES utf8mb4;
 18 SET FOREIGN_KEY_CHECKS = 0;
 19 
 20 -- ----------------------------
 21 -- Table structure for class
 22 -- ----------------------------
 23 DROP TABLE IF EXISTS `class`;
 24 CREATE TABLE `class`  (
 25   `cid` int(11) NOT NULL AUTO_INCREMENT,
 26   `caption` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 27   PRIMARY KEY (`cid`) USING BTREE
 28 ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 29 
 30 -- ----------------------------
 31 -- Records of class
 32 -- ----------------------------
 33 INSERT INTO `class` VALUES (1, '三年二班');
 34 INSERT INTO `class` VALUES (2, '三年三班');
 35 INSERT INTO `class` VALUES (3, '一年二班');
 36 INSERT INTO `class` VALUES (4, '二年九班');
 37 
 38 -- ----------------------------
 39 -- Table structure for course
 40 -- ----------------------------
 41 DROP TABLE IF EXISTS `course`;
 42 CREATE TABLE `course`  (
 43   `cid` int(11) NOT NULL AUTO_INCREMENT,
 44   `cname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 45   `teacher_id` int(11) NOT NULL,
 46   PRIMARY KEY (`cid`) USING BTREE,
 47   INDEX `fk_course_teacher`(`teacher_id`) USING BTREE,
 48   CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT
 49 ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 50 
 51 -- ----------------------------
 52 -- Records of course
 53 -- ----------------------------
 54 INSERT INTO `course` VALUES (1, '生物', 1);
 55 INSERT INTO `course` VALUES (2, '物理', 2);
 56 INSERT INTO `course` VALUES (3, '体育', 3);
 57 INSERT INTO `course` VALUES (4, '美术', 2);
 58 
 59 -- ----------------------------
 60 -- Table structure for score
 61 -- ----------------------------
 62 DROP TABLE IF EXISTS `score`;
 63 CREATE TABLE `score`  (
 64   `sid` int(11) NOT NULL AUTO_INCREMENT,
 65   `student_id` int(11) NOT NULL,
 66   `course_id` int(11) NOT NULL,
 67   `num` int(11) NOT NULL,
 68   PRIMARY KEY (`sid`) USING BTREE,
 69   INDEX `fk_score_student`(`student_id`) USING BTREE,
 70   INDEX `fk_score_course`(`course_id`) USING BTREE,
 71   CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
 72   CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT
 73 ) ENGINE = InnoDB AUTO_INCREMENT = 53 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 74 
 75 -- ----------------------------
 76 -- Records of score
 77 -- ----------------------------
 78 INSERT INTO `score` VALUES (1, 1, 1, 10);
 79 INSERT INTO `score` VALUES (2, 1, 2, 9);
 80 INSERT INTO `score` VALUES (5, 1, 4, 66);
 81 INSERT INTO `score` VALUES (6, 2, 1, 8);
 82 INSERT INTO `score` VALUES (8, 2, 3, 68);
 83 INSERT INTO `score` VALUES (9, 2, 4, 99);
 84 INSERT INTO `score` VALUES (10, 3, 1, 77);
 85 INSERT INTO `score` VALUES (11, 3, 2, 66);
 86 INSERT INTO `score` VALUES (12, 3, 3, 87);
 87 INSERT INTO `score` VALUES (13, 3, 4, 99);
 88 INSERT INTO `score` VALUES (14, 4, 1, 79);
 89 INSERT INTO `score` VALUES (15, 4, 2, 11);
 90 INSERT INTO `score` VALUES (16, 4, 3, 67);
 91 INSERT INTO `score` VALUES (17, 4, 4, 100);
 92 INSERT INTO `score` VALUES (18, 5, 1, 79);
 93 INSERT INTO `score` VALUES (19, 5, 2, 11);
 94 INSERT INTO `score` VALUES (20, 5, 3, 67);
 95 INSERT INTO `score` VALUES (21, 5, 4, 100);
 96 INSERT INTO `score` VALUES (22, 6, 1, 9);
 97 INSERT INTO `score` VALUES (23, 6, 2, 100);
 98 INSERT INTO `score` VALUES (24, 6, 3, 67);
 99 INSERT INTO `score` VALUES (25, 6, 4, 100);
100 INSERT INTO `score` VALUES (26, 7, 1, 9);
101 INSERT INTO `score` VALUES (27, 7, 2, 100);
102 INSERT INTO `score` VALUES (28, 7, 3, 67);
103 INSERT INTO `score` VALUES (29, 7, 4, 88);
104 INSERT INTO `score` VALUES (30, 8, 1, 9);
105 INSERT INTO `score` VALUES (31, 8, 2, 100);
106 INSERT INTO `score` VALUES (32, 8, 3, 67);
107 INSERT INTO `score` VALUES (33, 8, 4, 88);
108 INSERT INTO `score` VALUES (34, 9, 1, 91);
109 INSERT INTO `score` VALUES (35, 9, 2, 88);
110 INSERT INTO `score` VALUES (36, 9, 3, 67);
111 INSERT INTO `score` VALUES (37, 9, 4, 22);
112 INSERT INTO `score` VALUES (38, 10, 1, 90);
113 INSERT INTO `score` VALUES (39, 10, 2, 77);
114 INSERT INTO `score` VALUES (40, 10, 3, 43);
115 INSERT INTO `score` VALUES (41, 10, 4, 87);
116 INSERT INTO `score` VALUES (42, 11, 1, 90);
117 INSERT INTO `score` VALUES (43, 11, 2, 77);
118 INSERT INTO `score` VALUES (44, 11, 3, 43);
119 INSERT INTO `score` VALUES (45, 11, 4, 87);
120 INSERT INTO `score` VALUES (46, 12, 1, 90);
121 INSERT INTO `score` VALUES (47, 12, 2, 77);
122 INSERT INTO `score` VALUES (48, 12, 3, 43);
123 INSERT INTO `score` VALUES (49, 12, 4, 87);
124 INSERT INTO `score` VALUES (52, 13, 3, 87);
125 
126 -- ----------------------------
127 -- Table structure for student
128 -- ----------------------------
129 DROP TABLE IF EXISTS `student`;
130 CREATE TABLE `student`  (
131   `sid` int(11) NOT NULL AUTO_INCREMENT,
132   `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133   `class_id` int(11) NOT NULL,
134   `sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135   PRIMARY KEY (`sid`) USING BTREE,
136   INDEX `fk_class`(`class_id`) USING BTREE,
137   CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
138 ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
139 
140 -- ----------------------------
141 -- Records of student
142 -- ----------------------------
143 INSERT INTO `student` VALUES (1, '', 1, '理解');
144 INSERT INTO `student` VALUES (2, '', 1, '钢蛋');
145 INSERT INTO `student` VALUES (3, '', 1, '张三');
146 INSERT INTO `student` VALUES (4, '', 1, '张一');
147 INSERT INTO `student` VALUES (5, '', 1, '张二');
148 INSERT INTO `student` VALUES (6, '', 1, '张四');
149 INSERT INTO `student` VALUES (7, '', 2, '铁锤');
150 INSERT INTO `student` VALUES (8, '', 2, '李三');
151 INSERT INTO `student` VALUES (9, '', 2, '李一');
152 INSERT INTO `student` VALUES (10, '', 2, '李二');
153 INSERT INTO `student` VALUES (11, '', 2, '李四');
154 INSERT INTO `student` VALUES (12, '', 3, '如花');
155 INSERT INTO `student` VALUES (13, '', 3, '刘三');
156 INSERT INTO `student` VALUES (14, '', 3, '刘一');
157 INSERT INTO `student` VALUES (15, '', 3, '刘二');
158 INSERT INTO `student` VALUES (16, '', 3, '刘四');
159 
160 -- ----------------------------
161 -- Table structure for teacher
162 -- ----------------------------
163 DROP TABLE IF EXISTS `teacher`;
164 CREATE TABLE `teacher`  (
165   `tid` int(11) NOT NULL AUTO_INCREMENT,
166   `tname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167   PRIMARY KEY (`tid`) USING BTREE
168 ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
169 
170 -- ----------------------------
171 -- Records of teacher
172 -- ----------------------------
173 INSERT INTO `teacher` VALUES (1, '张磊老师');
174 INSERT INTO `teacher` VALUES (2, '李平老师');
175 INSERT INTO `teacher` VALUES (3, '刘海燕老师');
176 INSERT INTO `teacher` VALUES (4, '朱云海老师');
177 INSERT INTO `teacher` VALUES (5, '李杰老师');
178 
179 SET FOREIGN_KEY_CHECKS = 1;
View Code 二.查询题1.查询平均成绩大于60分的同学的学号和平均成绩。(由于一个同学可能选择了多个课程,我们要先查询大于60分的同学就必须分组;得到大于60分的同学;最后查询他们的学号和平均成绩)2.查询学生的平均分,姓名,student_id;3.根据上面的查询结果我们可以进行子查询出student_id和avg(num)

相关内容推荐