数据库SQL查询语句练习题(1)

设教学数据库Education 有三个关系：

学生关系S （SNO ，SNAME ，AGE ，SEX ，SDEPT ）；学习关系SC （SNO ，CNO ，GRADE ）；课程关系C （CNO ，CNAME ，CDEPT ，TNAME ） 查询问题：

（1）检索计算机系的全体学生的学号，姓名和性别； （2）检索学习课程号为C2的学生学号与姓名； （3）检索选修课程名为“DS ”的学生学号与姓名； （4）检索选修课程号为C2或C4的学生学号； （5）检索至少选修课程号为C2和C4的学生学号； （6）检索不学C2课的学生姓名和年龄； （7）检索学习全部课程的学生姓名；

（8）查询所学课程包含学生S3所学课程的学生学号。

（1）检索计算机系的全体学生的学号，姓名和性别； SELECT Sno ，Sname ，Sex FROM S

WHERE Sdept =’CS ’；

（2）检索学习课程号为C2的学生学号与姓名；

（3）检索选修课程名为“DS ”的学生学号与姓名

本查询涉及到学号、姓名和课程名三个属性，分别存放在S 和C 表中，但S 和C 表没有直接联系，必须通过SC 表建立它们二者的联系。 C → SC → S 基本思路：

（1）首先在C 表中找出“DS ”课程的课程号Cno ； （2）然后在SC 表中找出Cno 等于第一步给出的Cno 集合中的某个元素Cno ；

（3）最后在S 关系中选出Sno 等于第二步中Sno 集合中某个元素的元组，取出Sno 和Sname 送入结果表列。 SELECT Sno ，Sname FROM S

WHERE Sno IN （SELECT Sno FROM SC

WHERE Cno IN （SELECT Cno FROM C

WHERE Cname=‘DS ’））；

（4）检索选修课程号为C2或C4的学生学号； SELECT Sno FROM SC

WHERE Cno=‘C2’ OR Cno=‘C4’；

（5）检索至少选修课程号为C2和C4的学生学号；

SELECT Sno

FROM SC X ，SC Y

WHERE X.Sno=Y.Sno AND https://www.wendangku.net/doc/6a4607960.html,o=‘C2’ AND https://www.wendangku.net/doc/6a4607960.html,o=‘C4’ ；

（6）检索不学C2课的学生姓名和年龄；

（7）检索学习全部课程的学生姓名；

在表S 中找学生，要求这个学生学了全部课程。换言之，在S 表中找学生，在C 中不存在一门课程，这个学生没有学。 SELECT Sname

FROM S

WHERE NOT EXISTS （SELECT * FROM C

WHERE NOT EXISTS （SELECT * FROM SC

WHERE SC.Sno=S.Sno AND https://www.wendangku.net/doc/6a4607960.html,o=https://www.wendangku.net/doc/6a4607960.html,o ）)；

（8）查询所学课程包含学生S3所学课程的学生学号。 分析：不存在这样的课程Y ，学生S3选了Y ，而学生X 没有选。 SELECT DISTINCT Sno FROM SC X

WHERE NOT EXISTS （SELECT *

FROM SC Y

WHERE Y.Sno=‘S3’ AND NOT EXISTS （SELECT * FROM SC Z

WHERE Z.Sno=X.Sno AND https://www.wendangku.net/doc/6a4607960.html,o=https://www.wendangku.net/doc/6a4607960.html,o ）)；

2.SELECT S.Sno ，Sname

FROM S ，SC

WHERE S.Sno=SC.Sno AND https://www.wendangku.net/doc/6a4607960.html,o=‘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,Sage FROM S

WHERE NOT EXISTS （ SELECT * FROM SC

WHERE SC.Sno=S.Sno AND Cno=‘C2’)；

设教学数据库Education有三个关系：

学生关系S（SNO，SNAME，AGE，SEX，SDEPT）；

学习关系SC（SNO，CNO，GRADE）；

课程关系C（CNO，CNAME，CDEPT，TNAME）

查询问题：

1：查所有年龄在20岁以下的学生姓名及年龄。

2：查考试成绩有不及格的学生的学号

3：查所年龄在20至23岁之间的学生姓名、系别及年龄。

4：查计算机系、数学系、信息系的学生姓名、性别。

5：查既不是计算机系、数学系、又不是信息系的学生姓名、性别

6：查所有姓“刘”的学生的姓名、学号和性别。

7：查姓“上官”且全名为3个汉字的学生姓名。

8：查所有不姓“张”的学生的姓名。

9：查DB_Design课程的课程号。

10：查缺考的学生的学号和课程号。

11：查年龄为空值的学生的学号和姓名。

12：查计算机系20岁以下的学生的学号和姓名。

13：查计算机系、数学系、信息系的学生姓名、性别。

14：查询选修了C3课程的学生的学号和成绩，其结果按分数的降序排列。

15：查询全体学生的情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。

16：查询学生总人数。

17：查询选修了课程的学生人数。

18：计算选修了C1课程的学生平均成绩。

19：查询学习C3课程的学生最高分数。

20：查询各个课程号与相应的选课人数。

21：查询计算机系选修了3门以上课程的学生的学号。

22：求基本表S中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。23：查询每个学生及其选修课程的情况。

24：查询选修了C2课程且成绩在90分以上的所有学生。

25：查询每个学生选修的课程名及其成绩。

26：统计每一年龄选修课程的学生人数。

27：查询选修了C2课程的学生姓名。

28：查询与“张三”在同一个系学习的学生学号、姓名和系别。29：查询选修课程名为“数据库”的学生学号和姓名。

30：查询与“张三”在同一个系学习的学生学号、姓名和系别。31：查询选修课程名为“数据库”的学生学号和姓名。

32：查询选修了C2课程的学生姓名。

33：查询所有未选修C2课程的学生姓名。

34：查询与“张三”在同一个系学习的学生学号、姓名和系别。35：查询选修了全部课程的学生姓名。

36：查询所学课程包含学生S3所学课程的学生学号（1）比较

例1：查所有年龄在20岁以下的学生姓名及年龄。

SELECT Sname，Sage

FROM S

WHERE Sage<20; (NOT age>=20)

例2：查考试成绩有不及格的学生的学号

SELECT DISTINCT Sno

FROM SC

WHERE grade<60；

(2)确定范围

例3：查所年龄在20至23岁之间的学生姓名、系别及年龄。

SELECT Sname，Sdept，Sage

FROM S

WHERE Sage BETWEEN 20 AND 23；

（3）确定集合

例4：查计算机系、数学系、信息系的学生姓名、性别。

SELECT Sname，Ssex

FROM S

WHERE Sdept IN （’CS’, ‘IS’, ‘MATH’）；

例5：查既不是计算机系、数学系、又不是信息系的学生姓名、性别

SELECT Sname，Ssex

FROM S

WHERE Sdept NOT IN （’CS’, ‘IS’, ‘MATH’）；

（4）字符匹配

例6：查所有姓“刘”的学生的姓名、学号和性别。

SELECT Sname，Sno，Ssex

FROM S

WHERE Sname LIKE ‘刘%’；

例7：查姓“上官”且全名为3个汉字的学生姓名。

SELECT Sname

FROM S

WHERE Sname LIKE ‘上官_ _’；

例8：查所有不姓“张”的学生的姓名。

SELECT Sname，Sno，Ssex

FROM S

WHERE Sname NOT LIKE ‘张%’；

例9：查DB_Design课程的课程号。

SELECT Cno

FROM C

WHERE Cname LIKE ‘DB\_Design’ ESCAPE ‘\’；

(5)涉及空值的查询

例10：查缺考的学生的学号和课程号。

SELECT Sno，Cno

FROM SC

WHERE Grade IS NULL；（不能用=代替）

{ 有成绩的 WHERE Grade IS NOT NULLL；}

例11：查年龄为空值的学生的学号和姓名。

SELECT Sno，Sname

FROM S

WHERE Sage IS NULL；

（6）多重条件查询

例12：查计算机系20岁以下的学生的学号和姓名。

SELECT Sno，Sname

FROM S

WHERE Sdept=‘CS’ AND Sage<20;

例13：查计算机系、数学系、信息系的学生姓名、性别。

SELECT Sname，Ssex

FROM S

WHERE Sdept =’CS’ OR Sdept =‘IS’ OR Sdept =’MATH’）；

3、对查询结果排序

例14：查询选修了C3课程的学生的学号和成绩，其结果按分数的降序排列。

SELECT Sno，Grade

FROM SC

WHERE Cno=‘C3’

ORDER BY Grade DESC;

例15：查询全体学生的情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。

SELECT *

FROM S

ORDER BY Sdep，Sage DESC；

4.聚合函数的使用

例16：查询学生总人数。

SELECT COUNT（*）

FROM S

例17：查询选修了课程的学生人数。

SELECT COUNT（DISTINCT Sno）

FROM SC

例18：计算选修了C1课程的学生平均成绩。

SELECT AVG（Grade）

FROM SC

WHERE Cno=‘C1’；

例19：查询学习C3课程的学生最高分数。

SELECT MAX（Grade）

FROM SC

WHERE Cno=‘C3’；

5、对查询结果分组

例20：查询各个课程号与相应的选课人数。 SELECT Cno，COUNT（Sno）

FROM SC

GROUP BY Cno；

该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用聚合函数COUNT以求得该组的学生人数。

如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件组，则可以使用HAVING短语指定筛选条件。

例21：查询计算机系选修了3门以上课程的学生的学号。

SELECT Sno

FROM SC

WHERE Sdept=‘CS’

GROUP BY Sno

HAVING COUNT（*）>3；

WHERE子句与HAVING短语的根本区别在于作用对象不同。WHERE 子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

例22：求基本表S中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。 SELECT Sage，COUNT（Sno）

FROM S

WHERE Ssex='M'

GROUP BY Sage

HAVING COUNT（*）> 50

ORDER BY 2，Sage DESC；

二、多表查询

1、联接查询

例23：查询每个学生及其选修课程的情况。

SELECT S.Sno,Sname,Sage,Ssex,Sdept,Cno,Grade

FROM S, SC

WHERE S.Sno=SC.Sno；

例24：查询选修了C2课程且成绩在90分以上的所有学生。

SELECT S.Sno，Sname

FROM S，SC

WHERE S.Sno=SC.Sno

AND https://www.wendangku.net/doc/6a4607960.html,o=‘C2’

AND SC.Grade > 90;

例25：查询每个学生选修的课程名及其成绩。

SELECT S.Sno，Sname，Cname，SC.Grade

FROM S，SC，C

WHERE S.Sno=SC.Sno AND https://www.wendangku.net/doc/6a4607960.html,o=https://www.wendangku.net/doc/6a4607960.html,o

例26：统计每一年龄选修课程的学生人数。

SELECT Sage，COUNT（DISTINCT S.Sno）

FROM S，SC

WHERE S.Sno=SC.Sno

GROUP BY S；

由于要统计每一个年龄的学生人数，因此要把满足WHERE子句中条件的查询结果按年龄分组，在每一组中的学生年龄相同。此时的SELECT子句应对每一组分开进行操作，在每一组中，年龄只有一个值，统计的人数是这一组中的学生人数。

1、嵌套查询

(1)带有IN谓词的子查询

指父查询与子查询之间用IN进行联接，判断某个属性列值是否在子查询的结果中。

例27：查询选修了C2课程的学生姓名。

SELECT Sname

FROM S

WHERE Sno IN

（ SELECT Sno

FROM SC

WHERE Cno=‘C2’);

例28：查询与“张三”在同一个系学习的学生学号、

姓名和系别。

分析：

（1）确定“张三”所在的系；

（2）查找所有在X系学习的学生。

SELECT Sdept

FROM S

WHERE Sname=‘张三’；

SELECT Sno，Sname，Sdept

FROM S

WHERE Sdept=‘X’

把第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。

SELECT Sno，Sname，Sdept

FROM S

WHERE Sdept IN

（SELECT Sdept

FROM S

WHERE Sname=‘张三’）；

例29：查询选修课程名为“数据库”的学生学号和姓名。

本查询涉及到学号、姓名和课程名三个属性，分别存放在S 和C表中，但S和C表没有直接联系，必须通过SC表建立它们二者的联系。 C → SC → S

基本思路：

（1）首先在C表中找出“DB”课程的课程号Cno；

（2）然后在SC表中找出Cno等于第一步给出的Cno 集合中的某个元素Cno；

（3）最后在S关系中选出Sno等于第二步中Sno 集合中某个元素的元组，取出Sno 和Sname送入结果表列。 SELECT Sno，Sname

FROM S

WHERE Sno IN

（SELECT Sno

FROM SC

WHERE Cno IN

（SELECT Cno

FROM C

WHERE Cname=‘DB’））；

联接查询方式

(2)带有比较运算符的子查询

例30：查询与“张三”在同一个系学习的学生学号、

姓名和系别。

SELECT Sno，Sname，Sdept

FROM S

WHERE Sdept =

（SELECT Sdept

FROM S

WHERE Sname=‘张三’）；

例31：查询选修课程名为“数据库”的学生学号和姓名。

SELECT Sno，Sname

FROM S

WHERE Sno IN

（SELECT Sno

FROM SC

WHERE Cno =

（SELECT Cno

FROM C

WHERE Cname=‘DB’））；

（3）带有EXISTS谓词的子查询

（1）带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻辑值。

例32：查询选修了C2课程的学生姓名。

1.SELECT Sname

FROM S

WHERE Sno IN

（ SELECT Sno

FROM SC

WHERE Cno=‘C2’);

2.SELECT Sname

FROM S

WHERE EXISTS

（ SELECT *

FROM SC

WHERE SC.Sno=S.Sno AND Cno=‘C2’);

例33：查询所有未选修C2课程的学生姓名。

SELECT Sname

FROM S S1， S S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname=‘张三’

FROM S

WHERE NOT EXISTS

（ SELECT *

FROM SC

WHERE SC.Sno=S.Sno AND Cno=‘C2’);

[NOT]EXISTS 实际上是一种内、外层互相关的嵌套查询，只有当内层引用了外层的值，这种查询才有意义。

例34：查询与“张三”在同一个系学习的学生学号、

姓名和系别。

SELECT Sno，Sname，Sdept

FROM S AS S1

WHERE EXISTS

（SELECT *

FROM S AS S2

WHERE S2.Sdept=S1. Sdept AND S2.Sname=‘张三’）；相关子查询

例35：查询选修了全部课程的学生姓名。

在表S中找学生，要求这个学生学了全部课程。换言之，在S表中找学生，在C中不存在一门课程，这个学生没有学。

SELECT Sname

FROM S

WHERE NOT EXISTS

（SELECT *

FROM C

WHERE NOT EXISTS

（SELECT *

FROM SC

WHERE SC.Sno=S.Sno AND https://www.wendangku.net/doc/6a4607960.html,o=https://www.wendangku.net/doc/6a4607960.html,o）)；

例36：查询所学课程包含学生S3所学课程的学生学号分析：不存在这样的课程Y，学生S3选了Y，而学生X没有选。

SELECT DISTINCT Sno

FROM SC X

WHERE NOT EXISTS

（SELECT *

FROM SC Y

WHERE Y.Sno=‘S3’ AND NOT EXISTS

（SELECT *

FROM SC Z

WHERE Z.Sno=X.Sno AND https://www.wendangku.net/doc/6a4607960.html,o=https://www.wendangku.net/doc/6a4607960.html,o）)；