《数据库系统概论》各章复习题及答案（2013给学生）

WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno 26.统计每一年龄选修课程的学生人数。

SELECT Sage，COUNT（DISTINCT S.Sno） FROM S，SC

WHERE S.Sno=SC.Sno GROUP BY S；

由于要统计每一个年龄的学生人数，因此要把满足WHERE子句中条件的查询结果按年龄分组，在每一组中的学生年龄相同。此时的SELECT子句应对每一组分开进行操作，在每一组中，年龄只有一个值，统计的人数是这一组中的学生人数。 27.查询选修了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’);

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=‘张三’）； 或

SELECT Sno，Sname，Sdept FROM S

WHERE Sdept =

（SELECT Sdept FROM S

WHERE Sname=‘张三’）； 或

SELECT Sno，Sname，Sdept FROM S AS S1 WHERE EXISTS （SELECT *

FROM S AS S2

WHERE S2.Sdept=S1. Sdept AND S2.Sname=‘张三’）；

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

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

（1）首先在C表中找出“数据库”课程的课程号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=‘数据库’））； 或

SELECT Sno，Sname FROM S

WHERE Sno IN

（SELECT Sno FROM SC

WHERE Cno =

（SELECT Cno FROM C

WHERE Cname=‘数据库’））；

30.查询所有未选修C2课程的学生姓名。 SELECT Sname FROM S

WHERE NOT EXISTS （ SELECT * FROM SC

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

（四） 设教学数据库中有三个基本表：学生表S（SNO，SNAME，AGE，SEX），其属性分别表示学号、学生姓名、年龄、性别。课程表C（CNO，CNAME，TEACHER），其属性分别表示课程号、课程名、上课教师名。选修表SC（SNO，CNO，GRADE），其属性分别表示学号、课程号、成绩。

有如下SQL查询语句： SELECT CNO FROM C

WHERE CNO NOT IN (SELECT CNO FROM S,SC

WHERE S.SNO=SC.SNO

AND SNAME='张三');

请完成下列问题：

（1）用汉语句子阐述上述SQL语句的含义；

（2）用等价的关系代数表达式表示上述SQL查询语句。 解：

（1）查询张三同学没有选修的课程的课程号。（2分） （2）πCNO(C)- πCNO (?SNAME='张三' (S)SC) 或 πCNO(C)- πCNO (?SNAME='张三' (SSC)) （2分） （五）

A B

A# 101 204 256 345 620 ANAME 韶山商店 前门百货商店 东风商场 铁道商店 第一百货公司 WQTY 15 89 501 76 413 CITY 长沙 北京 北京 长沙 上海 B＃ 1 2 3 4 BNAME PRICE 毛笔 羽毛球 收音机 书包 21 784 1325 242

AB

A＃ 101 101 101 101 204 256 256 345 345 345 620 B＃ 1 2 3 4 3 1 2 1 2 4 4

试用SQL语言写出下列查询：

（1）找出店员人数不超过100人或者在长沙市的所有商店的代号和商店名。 （2）找出代号为’256’的商店所供应的全部商品的品名和数量。 （3）统计各商店各自的销售总量，按销售总量从大到小排序。

解：

（1）SELECT A#, ANAME FROM A WHERE WQTY<=100 OR CITY='长沙'; （2）

SELECT BNAME,QTY FROM B,AB WHERE B.B#=AB.B# and A#=’256’

QTY 105 42 25 104 61 241 91 141 18 74 125

（3）

SELECT B#,SUM(QTY) FROM AB GROUP BY B#

ORDER BY SUM(QTY) DESC

（六）设有职工基本表：EMP（ENO，ENAME，AGE，SEX，SALARY），其属性分别表示职工号、姓名、年龄、

性别、工资。为每个工资低于1000元的女职工加薪200元，试写出这个操作的SQL语句。

UPDATE EMP

SET SALARY=SALARY+200

WHERE SALARY<1000 AND SEX='女';

（七）设某工厂数据库中有两个基本表：

车间基本表：DEPT（DNO，DNAME，MGR_ENO），其属性分别表示车间编号、车间名和车间主任的职工号。 职工基本表：ERP（ENO，ENAME，AGE，SEX，SALARY，DNO），其属性分别表示职工号、姓名、年龄、性别、工资和所在车间的编号。

建立一个有关女车间主任的职工号和姓名的视图，其结构如下：

VIEW6（ENO，ENAME）。试写出创建该视图VIEW6的SQL语句。 参考答案1：

CREATE VIEW VIEW6 AS

SELECT ENO, ENAME FROM EMP WHERE SEX='女' AND ENO IN (SELECT MGR_ENO FROM DEPT) 参考答案2：

CREATE VIEW VIEW6 AS

SELECT ENO, ENAME FROM DEPT, EMP

WHERE MGR_ENO=ENO AND SEX='女'

（八）设有一个工程供应数据库系统，包括如下四个关系模式： ? S(SNO，SNAME， STATUS，CITY)； ? P(PNO，PNAME，COLOR，WEIGHT)； ? J(JNO，JNAME，CITY)；

? SPJ(SNO，PNO，JNO，QTY)；

供应商表S由供应商号、供应商名、状态、城市组成； 零件表P 由零件号、零件名、颜色、重量组成； 工程项目表J由项目号、项目名、城市组成；

供应情况表SPJ由供应商号、零件号、项目号、供应数量组成； （1）用关系代数查询没有使用天津供应商生产的红色零件的工程号； （2） 用关系代数查询至少使用了供应商S1所供应的全部零件的工程号JNO； （3）用SQL查询供应工程J1零件为红色的工程号JNO； （4）用SQL查询没有使用天津供应商生产的零件的工程号； （5）用SQL语句将全部红色零件改为蓝色；

（6）用SQL语句将（S2，P4，J6，400）插入供应情况关系。

１、

（1）参考答案：