嵌套查询练习

(1)查询计算机工程系女学生的学生学号、姓名及考试成绩。 SELECT student.sno,sname,degree FROM student,sc WHERE student.sno=sc.sno and sdept IN (SELECT sdept FROM student WHERE sdept="计算机工程系") AND ssex IN (SELECT ssex FROM student WHERE ssex="女"); (2)查询“李勇”同学所选课程的成绩。 SELECT cno,degree FROM sc WHERE (SELECT sno FROM student WHERE sname="李勇")=sc.sno; (3) 查询“李新”老师所授课程的课程名称。 SELECT cname FROM course WHERE (SELECT cno FROM teaching WHERE (SELECT tno FROM teacher WHERE tname="李新")=teaching.tno)=course.cno; (4) 查询女教师所授课程的课程号及课程名称。 SELECT course.cno,cname FROM course WHERE cno IN (SELECT cno FROM teaching WHERE tno IN (SELECT tno FROM teacher WHERE tsex="女")); (5) 查询姓“王”的学生所学的课程名称。 SELECT cname FROM course WHERE (SELECT cno FROM sc WHERE (SELECT sno FROM student WHERE sname like "王%")=sc.sno)=course.cno; (6) 查询选修“数据库”课程且成绩在80～90分之间的学生学号及成绩。 SELECT distinct sno,degree FROM sc,course WHERE sc.cno IN (SELECT cno FROM course WHERE cname="数据库") AND degree BETWEEN 80 AND 90; (7) 查询选修“C04”课程的学生的平均年龄。 SELECT AVG(YEAR(NOW())-YEAR(sbirthday)) acv_age from student WHERE sno IN (SELECT sno FROM sc WHERE cno=(SELECT cno FROM course WHERE cno="C04")); (8) 查询学习课程名为“数学”的学生学号和姓名。 SELECT sno,sname from student WHERE sno in (SELECT sno FROM sc WHERE cno IN (SELECT cno FROM course WHERE cname="数学")); (9) 查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。 SELECT sno,degree FROM sc WHERE cno IN (SELECT cno FROM teaching WHERE tno IN (SELECT tno FROM teacher WHERE tname="钱军")); (10) 查询在第3学期所开课程的课程名称及成绩。 SELECT cname,degree FROM sc,course WHERE sc.cno=(SELECT cno FROM course WHERE cno in (SELECT cno FROM teaching WHERE cterm=3)) AND sc.cno=course.cno; (11) 查询与“李勇”同一个系的同学姓名。 SELECT sname FROM student WHERE sdept IN (SELECT sdept FROM student WHERE 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 ssex="女" AND sbirthday<ALL(SELECT sbirthday FROM student WHERE ssex="男"); (14) 查询成绩比该课程平均成绩高的学生的学号及成绩。 SELECT sno,degree FROM sc,(SELECT AVG(degree) avg_degree,cno FROM sc GROUP BY cno) a WHERE sc.cno=a.cno AND sc.degree>a.avg_degree; (15)  查询没有选修“C02”课程的学生学号及姓名。 SELECT sno,sname FROM student WHERE sno NOT IN (SELECT sno FROM sc WHERE cno IN (SELECT cno FROM sc WHERE cno="C02")); (16) 查询选修了“数据库”课程的学生学号、姓名及系别。 SELECT sno,sname,sdept FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno IN (SELECT cno FROM course WHERE cname="数据库")); (17)查询“C02”号课程不及格的学生信息。 SELECT * FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno="C02" AND degree<60); (18）查询只被一名学生选修的课程的课程号、课程名 SELECT cno,cname FROM course WHERE cno IN (SELECT a.cno FROM(SELECT COUNT(cno) ct,cno FROM sc GROUP BY cno) a WHERE a.ct=1);