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

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

#(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='c02'
	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 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='3'
);


#(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='数据库'
));