1. 子查询的返回值为单列单值
例1:查询与学号(sNo)为“1308013101”的同学在同一个班级的学生名单。
代码展示
SELECT *
FROM student
WHERE deptName = (
SELECT deptName
FROM student
WHERE sNo = "1308013101");```
例2:查询选修课程编号(cNo)为“01001”的课程且成绩超过该课程平均分的学生的学号、姓名、班级、课程名称和成绩。
SELECT sNo,sName,deptName,cName,grade
FROM course JOIN score ON course.id = score.cId
JOIN student ON student.id = score.sId
WHERE cNo ="01001" AND grade > (
SELECT AVG(grade)
FROM course JOIN score ON course.id = score.cId
WHERE cNo ="01001"
)
这道题代码不是很难,比较困难的是分析题目,和条件比对。
2. 子查询的返回值为单列多值
例1:查询选修课程编号(cNo)为“01001”的课程的学生名单。
SELECT *
FROM student
WHERE id IN
(SELECT sId
FROM score JOIN course ON score.cId = course.id
WHERE cNo="01001