数据查询-4-优快云博客

本文链接：https://blog.youkuaiyun.com/zhanghongyi_cpp/article/details/137835459

本文介绍了五个SQL查询示例，涉及学生选修成绩的筛选、排序，部门班级关系查询，成绩高于平均的统计，以及特定学号的课程信息和年龄比较。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1[简答题]查询学生选修成绩不为空的选修明细，包括学生姓名、课程名称、教师姓名和成绩，结果先按学生编号升序排列，再按成绩降序排列。

SELECT
students.stdname,
courses.crsname,
teachers.tchname,
studying.mark


FROM
    studying
    INNER JOIN students ON studying.stdid = students.stdid
    INNER JOIN courses ON studying.crsid = courses.crsid
    INNER JOIN teachers ON studying.tchid = teachers.tchid
WHERE
    studying.mark IS NOT NULL
ORDER BY
    students.stdid ASC,
    studying.mark DESC;

2[简答题]查询所有系部的所属班级情况（左外连接）。

SELECT
    departments.dptname,
    classes.classname
FROM
    departments
    LEFT OUTER JOIN classes ON departments.dptcode = classes.dptcode;

3[简答题]在选修表 studying 中查询成绩高于或等于平均成绩的选修情况（子查询）。

SELECT
    stdid,
    crsid,
    tchid,
    semester,
    mark
FROM
    studying
WHERE
    mark >= (SELECT AVG(mark) FROM studying WHERE mark IS NOT NULL);

4[简答题]使用子查询查询学号为 3 的学生选修的课程信息。

SELECT
    *
FROM
    studying
    INNER JOIN courses ON studying.crsid = courses.crsid
    INNER JOIN teachers ON studying.tchid = teachers.tchid
WHERE
    stdid = 3;

5[简答题]使用子查询比 E -20 EL1班所有同学都小的同学信息

SELECT
    *
FROM
    students
WHERE
    dob > (SELECT dob FROM students WHERE classcode = 'EL-20EL1' ORDER BY dob DESC LIMIT 1)
    AND classcode &lt;> 'EL-20EL1';