嵌套查询

我猜是【实验一】

(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) 查询选修“高等数学”课程且成绩在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
);

(6) 查询学习课程名为“数学”的学生学号和姓名。

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 '%数学%'
);

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