MySQL数据库（嵌套子查询）答案-武洪萍主编

MySQL数据库（嵌套子查询）答案-武洪萍主编

代码如下:

一，
--  1，创建数据库 t10
1，删除数据库
	DROP DATABASE IF EXISTS t10;
2，创建数据库
	CREATE DATABASE t10;
3，修改数据库编码方式和字符集排列顺序
	ALTER DATABASE t10 CHARACTER SET utf8 COLLATE utf8_bin;
4，使用数据库
	USE t10;
--  2， 表的结构数据从脚本a1导入 
--  3，查询a1中所有的表记录，并用别名显示
-- 查看各表数据
SELECT*FROM course;  -- [课程表]主键cno（课程编号）课程名称cname
SELECT*FROM sc; -- [成绩表]双主键（sno,cno）外键sno参考学生表student，外键cno参考课程course，degree 分数，
SELECT*FROM student; -- [学生表]主键sno(学号)，姓名snmae，性别ssex，电话sphone，地址saddress，生日sbirthday，所属院系sdept，照片sphoto，专业speciality，
SELECT*FROM suser;  -- [用户表]用户名username，密码password，用户权限 userlevel
SELECT*FROM teacher; -- [教师表]主键tno（教师编号）教师性别ssex，教师生日sbirthday，所属院系sdept，
SELECT*FROM teaching;  -- [授课表]课程编号cno(参考课程表)，教师编号（教师表tno），开课学期cterm，
-- 查看各表结构
DESCRIBE course;
DESCRIBE sc;
DESCRIBE student;
DESCRIBE suser;
DESCRIBE teacher;
DESCRIBE teaching;

-- 四，嵌套子查询
-- 1，查询‘李勇’同学所选课程的成绩
SELECT degree 成绩
FROM sc
WHERE EXISTS(SELECT cno
FROM student
WHERE student.sno=sc.sno AND sname='李勇');
-- 2，查询‘李新’教师所授课程的课程名称
SELECT cname 课程名称
FROM course a
WHERE EXISTS(
		SELECT b.cno
		FROM teaching b,teacher c
		WHERE b.tno=c.tno AND b.cno=a.cno
		AND tname='李新'
);
-- 3，查询女教师所授课程的课程号及课程名称
SELECT a.cno 课程号,cname 课程名称
FROM course a
WHERE EXISTS(
		SELECT cno
		FROM teacher b,teaching c
		WHERE b.tno=c.tno AND c. cno=a.cno
		AND tsex='女'
);
-- 4，查询‘王’的学生所学的课程名称
SELECT cname 课程名称
FROM course a
WHERE EXISTS(
		SELECT c.cno
		FROM student b,sc c
		WHERE a.cno=c.cno AND b.sno=c.sno
		AND sname LIKE '王%'
);
-- 5，查询‘C02’课程不及格的学生信息
SELECT *
FROM student
WHERE sno IN(
	SELECT sno
	FROM sc
	WHERE cno='c01'
	AND degree<60);
-- 6，查询选修‘数据库’课程且成绩在80~90分的学生学号及成绩
SELECT sno 学号,degree 成绩
FROM sc a
WHERE EXISTS(
		SELECT cno
		FROM course b
		WHERE a.cno=b.cno
		AND cname='数据库' AND degree BETWEEN 80 AND 90
);
-- 7，查询选修‘C04’课程的学生的平均年龄
SELECT AVG(YEAR(CURDATE())-YEAR(sbirthday)) 平均年龄
FROM student a
WHERE EXISTS(
	SELECT a.sno
	FROM sc b
	WHERE a.sno=b.sno AND cno='c04'
);
-- 8，查询选修课程名为‘数学’的学生学号与姓名
SELECT sno 学号,sname 姓名
FROM student a
WHERE EXISTS(
	SELECT b.cno
	FROM sc b,course c
	WHERE b.cno=c.cno AND a.sno=b.sno
	AND cname LIKE '%数学%'
);
-- 9，查询‘钱军’教师任课的课程号、选修其课程的学生的学号与成绩
SELECT cno 课程号,sno 学号,degree 成绩
FROM sc
WHERE cno=(
	SELECT cno
	FROM teaching
	WHERE tno=(
		SELECT tno
		FROM teacher
		WHERE tname='钱军'
	)
);
-- 10，查询在第3学期所开课程的课程名称及学生的成绩 
SELECT cno 课程名称,degree 成绩 
FROM sc
WHERE cno=ANY(
	SELECT cno
	FROM teaching
	WHERE cterm='2'
);
-- 11，查询与‘李勇’同一个系的学生姓名
SELECT sname 姓名
FROM student
WHERE sdept=(
	SELECT sdept
	FROM student
	WHERE sname='李勇'
)AND sname<>'李勇';
-- 12，查询学号比刘晨学生的大，而出生日期比她的小的学生姓名
SELECT sname 姓名
FROM student
WHERE (sno>(
	SELECT sno
	FROM student
	WHERE sname='刘晨'
	))AND(sbirthday>(
	SELECT sbirthday
	FROM student
	WHERE sname='刘晨'
)		
);
-- 13，查询出生日期大于所有女生出生日期的男生的姓名及系别
SELECT sname 姓名,sdept 系别
FROM student
WHERE (sbirthday>ALL(
	SELECT sbirthday
	FROM student
	WHERE ssex='女'
))AND ssex='男';
-- 14，查询成绩比该课程平均成绩高的学生的学号和成绩
SELECT sno 学号,degree 成绩
FROM sc
WHERE degree>=
(SELECT AVG(degree)
	FROM sc
	WHERE sc.cno=sc.cno
); 
-- 15，查询不讲授'C01'课的教师姓名
SELECT tname 教师姓名
FROM teacher
WHERE  EXISTS(
	SELECT tno
	FROM teaching
	WHERE cno<>'c01'
);
-- 16，查询没有选修‘C02’课程的学生学号及姓名
SELECT sno 学号,sname 姓名
FROM student
WHERE sno IN(
	SELECT sno
	FROM sc
	WHERE cno<>'c02'
);
-- 17，查询选修‘数据库’课程的学生学号，姓名和系别
SELECT sno 学号,sname 姓名,sdept 系别
FROM student
WHERE sno IN(
	SELECT sno
	FROM sc
	WHERE cno=(
		SELECT cno
		FROM course
		WHERE cname='数据库'
));