SQL练习题（一） 附答案

表数据以及题

CREATE DATABASE db1;
USE db1;

-- 班级表
CREATE TABLE class (
  cid INT(11) NOT NULL AUTO_INCREMENT,
  caption VARCHAR(32) NOT NULL,
  PRIMARY KEY (cid)
);

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

-- 老师表
CREATE TABLE teacher(
  tid INT(11) NOT NULL AUTO_INCREMENT,
  tname VARCHAR(32) NOT NULL,
  PRIMARY KEY (tid)
);

INSERT INTO teacher VALUES
(1, '刘正风老师'), 
(2, '东方不败老师'), 
(3, '风清扬老师'), 
(4, '绿竹翁老师'), 
(5, '令狐冲老师');

-- 课程表
CREATE TABLE course(
  cid INT(11) NOT NULL AUTO_INCREMENT,
  cname VARCHAR(32) NOT NULL,
  teacher_id INT(11) NOT NULL,
  PRIMARY KEY (cid),
  CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ;

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


-- 学生表
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),
  CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
);
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, '刘四');

-- 选课表
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),
  CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
  CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
);

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);


-- 1、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 2、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
-- 3、查询没有报东方不败老师课的学生姓名
-- 4、查询挂科超过两门(包括两门)的学生姓名和班级
-- 5、查询选修了所有课程的学生姓名
-- 6、查询东方不败老师教的课程的所有成绩记录
-- 7、查询全部学生都选修了的课程号和课程名
-- 8、查询之选修了一门课程的学生姓名和学号
-- 9、查询平均成绩大于85的学生姓名和平均成绩
-- 10、查询生物成绩不及格的学生姓名和对应生物分数

答案

-- 1、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
	student.sname '学生姓名',
	(sum(score.num)/(SELECT COUNT(course.cid) FROM course)) '平均成绩' 
FROM
	-- 没参加考试的学生不考虑, 直接选择内连接
	score, student 
WHERE
	-- 连接条件
	score.student_id = student.sid 
GROUP BY
	-- 通过学生id分组
	score.student_id 
HAVING
	-- 每个学生的平均分 (总分数 / 总课程科目数) 并大于 80
	(sum(score.num) / (SELECT COUNT(course.cid) FROM course)) >= 80
	
	
-- 2、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
SELECT
	-- 获取选课数与总成绩
	student.sid '学号',student.sname '姓名',IFNULL(COUNT(score.course_id),0) '选课数',IFNULL(SUM(score.num),0) '总分数'
	-- student.sid '学号',student.sname '姓名'
FROM
	-- 由于要所有学生, 所以我们使用外连接
	student
LEFT JOIN 
	score ON score.student_id = student.sid
GROUP BY
	student.sid

	
-- 3、查询没有报东方不败老师课的学生姓名
SELECT 
	student.sname '学生姓名' 
FROM 
	student
WHERE
-- 使用排除法,获取没有报东方老师课的学生
 student.sid
NOT IN
-- 获取东方不败老师的所带科目
(SELECT score.student_id FROM teacher,course,score WHERE teacher.tname='东方不败老师' AND teacher.tid = course.teacher_id AND course.cid = score.course_id GROUP BY score.student_id)


-- 4、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT
	student.sname '学生姓名',class.caption '班级' 
FROM 
	student,class
WHERE
	-- 全部学生id排除掉合格学生的id, 然和进行和对应班级内连接
	student.sid NOT IN 	(
	-- 考试合格的学生的id
SELECT
	nb.id
FROM
	-- 单个学生已合格数
	(SELECT score.student_id id,COUNT(score.student_id) count FROM score WHERE score.num > 60 GROUP BY score.student_id) nb
WHERE
	-- 总合格总数 - 已合格数 = 不合格数
	-- (不合格数>=2 代表本学生不合格, 不合格数 < 2 代表本学生合格)
	((SELECT COUNT(*) FROM course) - nb.count) < 2)
AND
	student.class_id = class.cid


-- 5、查询选修了所有课程的学生姓名
SELECT
	student.sname '姓名'
FROM
	score,student
WHERE
	score.student_id = student.sid
GROUP BY
	score.student_id
HAVING
	-- 学生已选课程总数 = 课程总数
	COUNT(score.course_id) = (SELECT COUNT(*) FROM course)
	


-- 6、查询东方不败老师教的课程的所有成绩记录
SELECT
	score.*
FROM
	teacher,course,score
WHERE
	teacher.tname = '东方不败老师' AND course.teacher_id = teacher.tid AND course.cid = score.course_id



-- 7、查询全部学生都选修了的课程号和课程名
SELECT
	course.cid '课程号', course.cname '课程名' 
FROM
	score,course
WHERE
	course.cid = score.course_id
GROUP BY	
	score.course_id
HAVING
	-- 查询被选科目的总数与全部学生比较,看看哪些课程被全部学生所选
	COUNT(score.course_id) = (SELECT COUNT(student.sid) FROM student)
	

-- 8、查询之选修了一门课程的学生姓名和学号
SELECT
	student.sname '学生姓名',student.sid '学号' 
FROM
	score,student
WHERE
	score.student_id = student.sid
GROUP BY
	score.student_id
HAVING
	COUNT(score.course_id) = 1
	
	
-- 9、查询平均成绩大于85的学生姓名和平均成绩
SELECT
	student.sname '学生姓名', (SUM(score.num) / (SELECT COUNT(course.cid) FROM course)) '平均成绩'
FROM
	score, student
WHERE
	score.student_id = student.sid
GROUP BY
	score.student_id
HAVING
	-- 平均分 (总分数 / 课程科目数 ) 并大于 85
	(SUM(score.num) / (SELECT COUNT(course.cid) FROM course)) > 85




-- 10、查询生物成绩不及格的学生姓名和对应生物分数
SELECT
	student.sname '不及格生物学生',IFNULL(score.num,0) '分数'
FROM
	student
LEFT JOIN
	score
ON
	-- 匹配分数表中的生物分数集 , 外连接到学生表上 	
	score.course_id = (SELECT course.cid FROM course WHERE course.cname = '生物')  AND  student.sid = score.student_id
WHERE
	-- 查询分数不及格的学生, 以及没有参加考试的学生
	score.num NOT BETWEEN 60 AND 100 OR ISNULL(score.num)