数据库中SQL查询语句习题含的答案解析

查询问题 设教学数据库查询问题 设教学数据库 EducationEducation 有三个关系 有三个关系 学生关系学生关系 S S SNOSNO SNAMESNAME AGEAGE SEXSEX SDEPTSDEPT 学习关 学习关 系系 SCSC SNOSNO CNOCNO GRADEGRADE 课程关系 课程关系 C C CNOCNO CNAMECNAME CDEPTCDEPT TNAMETNAME 1 1 检索计算机系的全体学生的学号 姓名和性别 检索计算机系的全体学生的学号 姓名和性别 2 2 检索学习课程号为检索学习课程号为 C2C2 的学生学号与姓名 的学生学号与姓名 3 3 检索选修课程名为检索选修课程名为 DS DS 的学生学号与姓名 的学生学号与姓名 4 4 检索选修课程号为 检索选修课程号为 C2C2 或或 C4C4 的学生学号 的学生学号 5 5 检索至少选修课程号为 检索至少选修课程号为 C2C2 和和 C4C4 的学生学号 的学生学号 6 6 检索不学 检索不学 C2C2 课的学生姓名和年龄 课的学生姓名和年龄 7 7 检索学习全部课程的学生姓名 检索学习全部课程的学生姓名 8 8 查询所学课程包含学生 查询所学课程包含学生 S3S3 所学课程的学生学号 所学课程的学生学号 1 1 检索计算机系的全体学生的学号 姓名和性别 检索计算机系的全体学生的学号 姓名和性别 SELECTSELECT SnoSno SnameSname SexSex FROMFROM S S WHEREWHERE SdeptSdept CS CS 2 2 检索学习课程号为 检索学习课程号为 C2C2 的学生学号与姓名 的学生学号与姓名 3 3 检索选修课程名为 检索选修课程名为 DS DS 的学生学号与姓名的学生学号与姓名 本查询涉及到学号 姓名和课程名三个属性 分本查询涉及到学号 姓名和课程名三个属性 分 别存放在别存放在 S S 和和 C C 表中 但表中 但 S S 和和 C C 表没有直接联系 必表没有直接联系 必 须通过须通过 SCSC 表建立它们二者的联系 表建立它们二者的联系 C C SCSC S S 基本思路 基本思路 1 1 首先在 首先在 C C 表中找出表中找出 DS DS 课程的课程号课程的课程号 CnoCno 2 2 然后在 然后在 SCSC 表中找出表中找出 CnoCno 等于第一步给出的等于第一步给出的 CnoCno 集合中的某个元素集合中的某个元素 CnoCno 3 3 最后在 最后在 S S 关系中选出关系中选出 SnoSno 等于第二步中等于第二步中 SnoSno 集合集合 中某个元素的元组 取出中某个元素的元组 取出 SnoSno 和和 SnameSname 送入结果表列 送入结果表列 SELECTSELECT SnoSno SnameSname FROMFROM S S WHEREWHERE SnoSno ININ SELECTSELECT SnoSno FROMFROM SCSC WHEREWHERE CnoCno ININ SELECTSELECT CnoCno FROMFROM C C WHEREWHERE Cname DS Cname DS 4 4 检索选修课程号为检索选修课程号为 C2C2 或或 C4C4 的学生学号 的学生学号 SELECTSELECT SnoSno FROMFROM SCSC WHEREWHERE Cno C2 Cno C2 OROR Cno C4 Cno C4 5 5 检索至少选修课程号为检索至少选修课程号为 C2C2 和和 C4C4 的学生学号 的学生学号 SELECTSELECT SnoSno FROMFROM SCSC X X SCSC Y Y WHEREWHERE X Sno Y SnoX Sno Y Sno ANDAND X Cno C2 X Cno C2 ANDAND Y Cno C4 Y Cno C4 6 6 检索不学检索不学 C2C2 课的学生姓名和年龄 课的学生姓名和年龄 7 7 检索学习全部课程的学生姓名 检索学习全部课程的学生姓名 在表在表 S S 中找学生 要求这个学生学了全部课程 换言中找学生 要求这个学生学了全部课程 换言 之 在之 在 S S 表中找学生 在表中找学生 在 C C 中不存在一门课程 这个中不存在一门课程 这个 学生没有学 学生没有学 SELECTSELECT SnameSname FROMFROM S S WHEREWHERE NOTNOT EXISTSEXISTS SELECTSELECT FROMFROM C C WHEREWHERE NOTNOT EXISTSEXISTS SELECTSELECT FROMFROM SCSC WHEREWHERE SC Sno S SnoSC Sno S Sno ANDAND SC Cno C CnoSC Cno C Cno 8 8 查询所学课程包含学生查询所学课程包含学生 S3S3 所学课程的学生学号 所学课程的学生学号 分析 不存在这样的课程分析 不存在这样的课程 Y Y 学生 学生 S3S3 选了选了 Y Y 而其他 而其他 学生没有选 学生没有选 SELECTSELECT DISTINCTDISTINCT SnoSno FROMFROM SCSC ASAS X X WHEREWHERE NOTNOT EXISTSEXISTS SELECTSELECT FROMFROM SCSC ASAS Y Y WHEREWHERE Y Sno S3 Y Sno S3 ANDAND NOTNOT EXISTSEXISTS SELECTSELECT FROMFROM SCSC ASAS Z Z WHEREWHERE Z Sno X SnoZ Sno X Sno ANDAND Z Cno Y CnoZ Cno Y Cno 2 SELECT S Sno Sname FROM S SC WHERE S Sno SC Sno AND SC Cno C2 1 SELECT Sno Sname FROM S WHERE Sno IN SELECT Sno FROM SC WHERE Cno C2 1 SELECT Sname FROM S WHERE Sno NOT IN SELECT Sno FROM SC WHERE Cno C2 2 SELECT Sname FROM S WHERE NOT EXISTS SELECT FROM SC WHERE SC Sno S Sno AND Cno C2 设教学数据库设教学数据库 EducationEducation 有三个关系 有三个关系 学生关系学生关系 S S SNOSNO SNAMESNAME AGEAGE SEXSEX SDEPTSDEPT 学习关系学习关系 SCSC SNOSNO CNOCNO GRADEGRADE 课程关系课程关系 C C CNOCNO CNAMECNAME CDEPTCDEPT TNAMETNAME 查询问题 查询问题 1 1 查所有年龄在 查所有年龄在 2020 岁以下的学生姓名及年龄 岁以下的学生姓名及年龄 2 2 查考试成绩有不及格的学生的学号 查考试成绩有不及格的学生的学号 3 3 查所年龄在 查所年龄在 2020 至至 2323 岁之间的学生姓名 系别及年岁之间的学生姓名 系别及年 龄 龄 4 4 查计算机系 数学系 信息系的学生姓名 性别 查计算机系 数学系 信息系的学生姓名 性别 5 5 查既不是计算机系 数学系 又不是信息系的学生 查既不是计算机系 数学系 又不是信息系的学生 姓名 性别姓名 性别 6 6 查所有姓 查所有姓 刘刘 的学生的姓名 学号和性别 的学生的姓名 学号和性别 7 7 查姓 查姓 上官上官 且全名为且全名为 3 3 个汉字的学生姓名 个汉字的学生姓名 8 8 查所有不姓 查所有不姓 张张 的学生的姓名 的学生的姓名 9 9 查 查 DB DesignDB Design 课程的课程号 课程的课程号 1010 查缺考的学生的学号和课程号 查缺考的学生的学号和课程号 1111 查年龄为空值的学生的学号和姓名 查年龄为空值的学生的学号和姓名 1212 查计算机系 查计算机系 2020 岁以下的学生的学号和姓名 岁以下的学生的学号和姓名 1313 查计算机系 数学系 信息系的学生姓名 性别 查计算机系 数学系 信息系的学生姓名 性别 1414 查询选修了 查询选修了 C3C3 课程的学生的学号和成绩 其结果课程的学生的学号和成绩 其结果 按分数的降序排列 按分数的降序排列 1515 查询全体学生的情况 查询结果按所在系升序排 查询全体学生的情况 查询结果按所在系升序排 列 对同一系中的学生按年龄降序排列 列 对同一系中的学生按年龄降序排列 1616 查询学生总人数 查询学生总人数 1717 查询选修了课程的学生人数 查询选修了课程的学生人数 1818 计算选修了 计算选修了 C1C1 课程的学生平均成绩 课程的学生平均成绩 1919 查询学习 查询学习 C3C3 课程的学生最高分数 课程的学生最高分数 2020 查询各个课程号与相应的选课人数 查询各个课程号与相应的选课人数 2121 查询计算机系选修了 查询计算机系选修了 3 3 门以上课程的学生的学号 门以上课程的学生的学号 2222 求基本表 求基本表 S S 中男同学的每一年龄组 超过中男同学的每一年龄组 超过 5050 人 人 有多少人 要求查询结果按人数升序排列 人数相同有多少人 要求查询结果按人数升序排列 人数相同 按年龄降序排列 按年龄降序排列 2323 查询每个学生及其选修课程的情况 查询每个学生及其选修课程的情况 2424 查询选修了 查询选修了 C2C2 课程且成绩在课程且成绩在 9090 分以上的所有学分以上的所有学 生 生 2525 查询每个学生选修的课程名及其成绩 查询每个学生选修的课程名及其成绩 2626 统计每一年龄选修课程的学生人数 统计每一年龄选修课程的学生人数 2727 查询选修了 查询选修了 C2C2 课程的学生姓名 课程的学生姓名 2828 查询与 查询与 张三张三 在同一个系学习的学生学号 姓在同一个系学习的学生学号 姓 名和系别 名和系别 2929 查询选修课程名为 查询选修课程名为 数据库数据库 的学生学号和姓名 的学生学号和姓名 3030 查询与 查询与 张三张三 在同一个系学习的学生学号 姓在同一个系学习的学生学号 姓 名和系别 名和系别 3131 查询选修课程名为 查询选修课程名为 数据库数据库 的学生学号和姓名 的学生学号和姓名 3232 查询选修了 查询选修了 C2C2 课程的学生姓名 课程的学生姓名 3333 查询所有未选修 查询所有未选修 C2C2 课程的学生姓名 课程的学生姓名 3434 查询与 查询与 张三张三 在同一个系学习的学生学号 姓在同一个系学习的学生学号 姓 名和系别 名和系别 3535 查询选修了全部课程的学生姓名 查询选修了全部课程的学生姓名 3636 查询所学课程包含学生 查询所学课程包含学生 S3S3 所学课程的学生学号所学课程的学生学号 1 1 比较 比较 例例 1 1 查所有年龄在 查所有年龄在 2020 岁以下的学生姓名及年龄 岁以下的学生姓名及年龄 SELECTSELECT SnameSname SageSage FROMFROM S S WHEREWHERE Sage 20 Sage 20 age 20 例例 2 2 查考试成绩有不及格的学生的学号 查考试成绩有不及格的学生的学号 SELECTSELECT DISTINCTDISTINCT SnoSno FROMFROM SCSC WHEREWHERE grade 60grade 60 2 2 确定范围确定范围 例例 3 3 查所年龄在 查所年龄在 2020 至至 2323 岁之间的学生姓名 系别及岁之间的学生姓名 系别及 年龄 年龄 SELECTSELECT SnameSname SdeptSdept SageSage FROMFROM S S WHEREWHERE SageSage BETWEENBETWEEN 2020 ANDAND 2323 3 3 确定集合 确定集合 例例 4 4 查计算机系 数学系 信息系的学生姓名 性别 查计算机系 数学系 信息系的学生姓名 性别 SELECTSELECT SnameSname SsexSsex FROMFROM S S WHEREWHERE SdeptSdept ININ CS CS IS IS MATH MATH 例例 5 5 查既不是计算机系 数学系 又不是信息系的学 查既不是计算机系 数学系 又不是信息系的学 生姓名 性别生姓名 性别 SELECTSELECT SnameSname SsexSsex FROMFROM S S WHEREWHERE SdeptSdept NOTNOT ININ CS CS IS IS MATH MATH 4 4 字符匹配 字符匹配 例例 6 6 查所有姓 查所有姓 刘刘 的学生的姓名 学号和性别 的学生的姓名 学号和性别 SELECTSELECT SnameSname SnoSno SsexSsex FROMFROM S S WHEREWHERE SnameSname LIKELIKE 刘刘 例例 7 7 查姓 查姓 上官上官 且全名为且全名为 3 3 个汉字的学生姓名 个汉字的学生姓名 SELECTSELECT SnameSname FROMFROM S S WHEREWHERE SnameSname LIKELIKE 上官上官 例例 8 8 查所有不姓 查所有不姓 张张 的学生的姓名 的学生的姓名 SELECTSELECT SnameSname SnoSno SsexSsex FROMFROM S S WHEREWHERE SnameSname NOTNOT LIKELIKE 张张 例例 9 9 查 查 DB DesignDB Design 课程的课程号 课程的课程号 SELECTSELECT CnoCno FROMFROM C C WHEREWHERE CnameCname LIKELIKE DB Design DB Design ESCAPEESCAPE 5 5 涉及空值的查询涉及空值的查询 例例 1010 查缺考的学生的学号和课程号 查缺考的学生的学号和课程号 SELECTSELECT SnoSno CnoCno FROMFROM SCSC WHEREWHERE GradeGrade ISIS NULLNULL 不能用 不能用 代替 代替 有成绩的有成绩的 WHEREWHERE GradeGrade ISIS NOTNOT NULLLNULLL 例例 1111 查年龄为空值的学生的学号和姓名 查年龄为空值的学生的学号和姓名 SELECTSELECT SnoSno SnameSname FROMFROM S S WHEREWHERE SageSage ISIS NULLNULL 6 6 多重条件查询 多重条件查询 例例 1212 查计算机系 查计算机系 2020 岁以下的学生的学号和姓名 岁以下的学生的学号和姓名 SELECTSELECT SnoSno SnameSname FROMFROM S S WHEREWHERE Sdept CS Sdept CS ANDAND Sage 20 Sage3 3 WHEREWHERE 子句与子句与 HAVINGHAVING 短语的根本区别在于作用对象不短语的根本区别在于作用对象不 同 同 WHEREWHERE 子句作用于基本表或视图 从中选择满足条子句作用于基本表或视图 从中选择满足条 件的元组 件的元组 HAVINGHAVING 短语作用于组 从中选择满足条件短语作用于组 从中选择满足条件 的组 的组 例例 2222 求基本表 求基本表 S S 中男同学的每一年龄组 超过中男同学的每一年龄组 超过 5050 人 人 有多少人 要求查询结果按人数升序排列 人数相同有多少人 要求查询结果按人数升序排列 人数相同 按年龄降序排列 按年龄降序排列 SELECTSELECT SageSage COUNTCOUNT SnoSno FROMFROM S S WHEREWHERE Ssex M Ssex M GROUPGROUP BYBY SageSage HAVINGHAVING COUNTCOUNT 5050 ORDERORDER BYBY 2 2 SageSage DESCDESC 二 多表查询二 多表查询 1 1 联接查询 联接查询 例例 2323 查询每个学生及其选修课程的情况 查询每个学生及其选修课程的情况 SELECTSELECT S Sno Sname Sage Ssex Sdept Cno GradeS Sno Sname Sage Ssex Sdept Cno Grade FROMFROM S S SCSC WHEREWHERE S Sno SC SnoS Sno SC Sno 例例 2424 查询选修了 查询选修了 C2C2 课程且成绩在课程且成绩在 9090 分以上的所有分以上的所有 学生 学生 SELECTSELECT S SnoS Sno SnameSname FROMFROM S S SCSC WHEREWHERE S Sno SC SnoS Sno SC Sno ANDAND SC Cno C2 SC Cno C2 ANDAND SC GradeSC Grade 90 90 例例 2525 查询每个学生选修的课程名及其成绩 查询每个学生选修的课程名及其成绩 SELECTSELECT S SnoS Sno SnameSname CnameCname SC GradeSC Grade FROMFROM S S SCSC C C WHEREWHERE S Sno SC SnoS Sno SC Sno ANDAND SC Cno C CnoSC Cno C Cno 例例 2626 统计每一年龄选修课程的学生人数 统计每一年龄选修课程的学生人数 SELECTSELECT SageSage COUNTCOUNT DISTINCTDISTINCT S SnoS Sno FROMFROM S S SCSC WHEREWHERE S Sno SC SnoS Sno SC Sno GROUPGROUP BYBY S S 由于要统计每一个年龄的学生人数 因此要把满由于要统计每一个年龄的学生人数 因此要把满 足足 WHEREWHERE 子句中条件的查询结果按年龄分组 在每一子句中条件的查询结果按年龄分组 在每一 组中的学生年龄相同 此时的组中的学生年龄相同 此时的 SELECTSELECT 子句应对每一组子句应对每一组 分开进行操作 在每一组中 年龄只有一个值 统计分开进行操作 在每一组中 年龄只有一个值 统计 的人数是这一组中的学生人数 的人数是这一组中的学生人数 1 1 嵌套查询 嵌套查询 1 1 带有带有 ININ 谓词的子查询谓词的子查询 指父查询与子查询之间用指父查询与子查询之间用 ININ 进行联接 判断某个进行联接 判断某个 属性列值是否在子查询的结果中 属性列值是否在子查询的结果中 例例 2727 查询选修了 查询选修了 C2C2 课程的学生姓名 课程的学生姓名 SELECTSELECT SnameSname FROMFROM S S WHEREWHERE SnoSno ININ SELECTSELECT SnoSno FROMFROM SCSC WHEREWHERE Cno C2 Cno C2 例例 2828 查询与 查询与 张三张三 在同一个系学习的学生学号 在同一个系学习的学生学号 姓名和系别 姓名和系别 分析 分析 1 1 确定 确定 张三张三 所在的系 所在的系 2 2 查找所有在 查找所有在 X X 系学习的学生 系学习的学生 SELECTSELECT SdeptSdept FROMFROM S S WHEREWHERE Sname Sname 张三张三 SELECTSELECT SnoSno SnameSname SdeptSdept FROMFROM S S WHEREWHERE Sdept X Sdept X 把第一步查询嵌入到第二步查询中 用以构造第二步把第一步查询嵌入到第二步查询中 用以构造第二步 查询的条件 查询的条件 SELECTSELECT SnoSno SnameSname SdeptSdept FROMFROM S S WHEREWHERE SdeptSdept ININ SELECTSELECT SdeptSdept FROMFROM S S WHEREWHERE Sname Sname 张三张三 例例 2929 查询选修课程名为 查询选修课程名为 数据库数据库 的学生学号和姓的学生学号和姓 名 名 本查询涉及到学号 姓名和课程名三个属性 分本查询涉及到学号 姓名和课程名三个属性 分 别存放在别存放在 S S 和和 C C 表中 但表中 但 S S 和和 C C 表没有直接联系 必表没有直接联系 必 须通过须通过 SCSC 表建立它们二者的联系 表建立它们二者的联系 C C SCSC S S 基本思路 基本思路 1 1 首先在 首先在 C C 表中找出表中找出 DB DB 课程的课程号课程的课程号 CnoCno 2 2 然后在 然后在 SCSC 表中找出表中找出 CnoCno 等于第一步给出的等于第一步给出的 CnoCno 集合中的某个元素集合中的某个元素 CnoCno 3 3 最后在 最后在 S S 关系中选出关系中选出 SnoSno 等于第二步中等于第二步中 SnoSno 集合集合 中某个元素的元组 取出中某个元素的元组 取出 SnoSno 和和 SnameSname 送入结果表列 送入结果表列 SELECTSELECT SnoSno SnameSname FROMFROM S S WHEREWHERE SnoSno ININ SELECTSELECT SnoSno FROMFROM SCSC WHEREWHERE CnoCno ININ SELECTSELECT CnoCno FROMFROM C C WHEREWHERE Cname DB Cname DB 联接查询方式联接查询方式 2 2 带有比较运算符的子查询带有比较运算符的子查询 FROM S AS S1 S AS S2 WHERE S1 Sdept S2 Sdept AND S2 Sname 张三 例例 3030 查询与 查询与 张三张三 在同一个系学习的学生学号 在同一个系学习的学生学号 姓名和系别 姓名和系别 SELECTSELECT SnoSno SnameSname SdeptSdept FROMFROM S S WHEREWHERE SdeptSdept SELECTSELECT SdeptSdept FROMFROM S S WHEREWHERE Sname Sname 张三张三 例例 3131 查询选修课程名为 查询选修课程名为 数据库数据库 的学生学号和姓的学生学号和姓 名 名 SELECTSELECT SnoSno SnameSname FROMFROM S S WHEREWHERE SnoSno ININ SELECTSELECT SnoSno FROMFROM SCSC WHEREWHERE CnoCno SELECTSELECT CnoCno FROMFROM C C WHEREWHERE Cname DB Cname DB 3 3 带有 带有 EXISTSEXISTS 谓词的子查询谓词的子查询 1 1 带有 带有 EXISTSEXISTS 谓词的子查询不返回任何实际数据 谓词的子查询不返回任何实际数据 它只产生逻辑值 它只产生逻辑值 例例 3232 查询选修了 查询选修了 C2C2 课程的学生姓名 课程的学生姓名 1 SELECT1 SELECT SnameSname FROMFROM S S WHEREWHERE SnoSno ININ SELECTSELECT SnoSno FROMFROM SCSC WHEREWHERE Cno C2 Cno C2 2 SELECT2 SELECT SnameSname FROMFROM S S WHEREWHERE EXISTSEXISTS SELECTSELECT FROMFROM SCSC WHEREWHERE SC Sno S SnoSC Sno S Sno ANDAND Cno C2 Cno C2 例例 3333 查询所有未选修 查询所有未选修 C2C2 课程的学生姓名 课程的学生姓名 SELECTSELECT SnameSname FROMFROM S S WHEREWHERE NOTNOT EXISTSEXISTS SELECTSELECT FROMFROM SCSC WHEREWHERE SC Sno S SnoSC Sno S Sno ANDAND Cno C2 Cno C2 NOT EXISTS NOT EXISTS 实际上是一种内 外层互相关的实际上是一种内 外层互相关的 嵌套查询 只有当内层引用了外层的值 这种查询才嵌套查询 只有当内层引用了外层的值 这种查询才 有意义 有意义 例例 3434 查询与 查询与 张三张三 在同一个系学习的学生学号 在同一个系学习的学生学号 姓名和系别 姓名和系别 SELECTSELECT SnoSno SnameSname SdeptSdept FROMFROM S S ASAS S1S1 WHEREWHERE EXISTSEXISTS SELECTSELECT FROMFROM S S ASAS S2S2 WHEREWHERE S2 Sdept S1 S2 Sdept S1 SdeptSdept ANDAND S2 Sname S2 Sname 张三张