SQL练习题&答案 Part_3

表结构

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 score

---

BEGIN;
INSERT INTO score VALUES (‘1’, ‘1’, ‘1’, ‘10’), (‘2’, ‘1’, ‘2’, ‘9’), (‘5’, ‘1’, ‘4’, ‘66’), (‘6’, ‘2’, ‘1’, ‘8’), (‘8’, ‘2’, ‘3’, ‘68’), (‘9’, ‘2’, ‘4’, ‘99’), (‘10’, ‘3’, ‘1’, ‘77’), (‘11’, ‘3’, ‘2’, ‘66’), (‘12’, ‘3’, ‘3’, ‘87’), (‘13’, ‘3’, ‘4’, ‘99’), (‘14’, ‘4’, ‘1’, ‘79’), (‘15’, ‘4’, ‘2’, ‘11’), (‘16’, ‘4’, ‘3’, ‘67’), (‘17’, ‘4’, ‘4’, ‘100’), (‘18’, ‘5’, ‘1’, ‘79’), (‘19’, ‘5’, ‘2’, ‘11’), (‘20’, ‘5’, ‘3’, ‘67’), (‘21’, ‘5’, ‘4’, ‘100’), (‘22’, ‘6’, ‘1’, ‘9’), (‘23’, ‘6’, ‘2’, ‘100’), (‘24’, ‘6’, ‘3’, ‘67’), (‘25’, ‘6’, ‘4’, ‘100’), (‘26’, ‘7’, ‘1’, ‘9’), (‘27’, ‘7’, ‘2’, ‘100’), (‘28’, ‘7’, ‘3’, ‘67’), (‘29’, ‘7’, ‘4’, ‘88’), (‘30’, ‘8’, ‘1’, ‘9’), (‘31’, ‘8’, ‘2’, ‘100’), (‘32’, ‘8’, ‘3’, ‘67’), (‘33’, ‘8’, ‘4’, ‘88’), (‘34’, ‘9’, ‘1’, ‘91’), (‘35’, ‘9’, ‘2’, ‘88’), (‘36’, ‘9’, ‘3’, ‘67’), (‘37’, ‘9’, ‘4’, ‘22’), (‘38’, ‘10’, ‘1’, ‘90’), (‘39’, ‘10’, ‘2’, ‘77’), (‘40’, ‘10’, ‘3’, ‘43’), (‘41’, ‘10’, ‘4’, ‘87’), (‘42’, ‘11’, ‘1’, ‘90’), (‘43’, ‘11’, ‘2’, ‘77’), (‘44’, ‘11’, ‘3’, ‘43’), (‘45’, ‘11’, ‘4’, ‘87’), (‘46’, ‘12’, ‘1’, ‘90’), (‘47’, ‘12’, ‘2’, ‘77’), (‘48’, ‘12’, ‘3’, ‘43’), (‘49’, ‘12’, ‘4’, ‘87’), (‘52’, ‘13’, ‘3’, ‘87’);
COMMIT;

---

– Table structure for student

---

DROP TABLE IF EXISTS student;
CREATE TABLE student (
sid INT(11) NOT NULL AUTO_INCREMENT,
gender CHAR(1) NOT NULL,
class_id INT(11) NOT NULL,
sname VARCHAR(32) NOT NULL,
PRIMARY KEY (sid),
KEY fk_class (class_id),
CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=INNODB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

---

– Records of student

---

BEGIN;
INSERT INTO student VALUES (‘1’, ‘男’, ‘1’, ‘理解’), (‘2’, ‘女’, ‘1’, ‘钢蛋’), (‘3’, ‘男’, ‘1’, ‘张三’), (‘4’, ‘男’, ‘1’, ‘张一’), (‘5’, ‘女’, ‘1’, ‘张二’), (‘6’, ‘男’, ‘1’, ‘张四’), (‘7’, ‘女’, ‘2’, ‘铁锤’), (‘8’, ‘男’, ‘2’, ‘李三’), (‘9’, ‘男’, ‘2’, ‘李一’), (‘10’, ‘女’, ‘2’, ‘李二’), (‘11’, ‘男’, ‘2’, ‘李四’), (‘12’, ‘女’, ‘3’, ‘如花’), (‘13’, ‘男’, ‘3’, ‘刘三’), (‘14’, ‘男’, ‘3’, ‘刘一’), (‘15’, ‘女’, ‘3’, ‘刘二’), (‘16’, ‘男’, ‘3’, ‘刘四’);
COMMIT;

---

– Table structure for teacher

---

DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (
tid INT(11) NOT NULL AUTO_INCREMENT,
tname VARCHAR(32) NOT NULL,
PRIMARY KEY (tid)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

---

– Records of teacher

---

BEGIN;
INSERT INTO teacher VALUES (‘1’, ‘张磊老师’), (‘2’, ‘李平老师’), (‘3’, ‘刘海燕老师’), (‘4’, ‘朱云海老师’), (‘5’, ‘李杰老师’);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

题目

– 查询男生、女生的人数；
– 查询姓“张”的学生名单；
– 课程平均分从高到低显示
– 查询有课程成绩小于60分的同学的学号、姓名；
– 查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
– 查询出只选修了一门课程的全部学生的学号和姓名；
– 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
– 查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
– 查询“生物”课程比“物理”课程成绩高的所有学生的学号；
– 查询平均成绩大于60分的同学的学号和平均成绩;
– 查询所有同学的学号、姓名、选课数、总成绩；
– 查询姓“李”的老师的个数；
– 查询没学过“张磊老师”课的同学的学号、姓名；
– 查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
– 查询学过“李平老师”所教的所有课的同学的学号、姓名；

答案

-- 查询男生、女生的人数；
-- 

SELECT gender,COUNT(*) FROM student GROUP BY gender;

-- 查询姓“张”的学生名单；
--