子查询

-- 1.查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩）降序排列

-- 方式一：连接查询

SELECT studentno,r.subjectno,studentresult
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方式二：子查询(执行顺序：由里及外)

SELECT studentno,subjectno,studentresult
FROM result
WHERE subjectno = (
SELECT subjectno
FROM `subject`
WHERE subjectname = '数据库结构-1')
ORDER BY studentresult DESC;

-- 查询课程为高等数学-2 且分数不小于80分的同学信息（学号、姓名）

SELECT studentno,studentname
FROM student
WHERE studentno IN (
SELECT studentno 
FROM result
WHERE studentresult > 80 AND subjectno = (
SELECT subjectno
FROM `subject`
WHERE subjectname = '高等数学-2')
);