嵌套查询练习

最新推荐文章于 2023-04-14 11:45:00 发布

2020440493

最新推荐文章于 2023-04-14 11:45:00 发布

阅读量1w

点赞数 11

文章标签：数据库

本文链接：https://blog.csdn.net/ays_lks/article/details/120624342

版权

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