数据库系统概论第三章课后习题（部分）

本文是《数据库系统概论》的第三章的部分课后习题，题目来源：【2019-2020春学期】数据库作业10：第三章课后题????共有四题第 3 题第 4 题第 5 题第 9 题第 3 题有两个关系S(A,B,C,D)和T(C,D,E,F)，写出与下列等查询等价的SQL表达式：1）σA=10(S)\sigma _{A=10}\left( S\right)σA=10(S)SELECT *FROM ...

Henry_626

25311人浏览 · 2020-03-29 10:44:19

Henry_626 · 2020-03-29 10:44:19 发布

本文是《数据库系统概论》的第三章的部分课后习题，题目来源：【2019-2020春学期】数据库作业10：第三章课后题

第 3 题

有两个关系S(A,B,C,D)和T(C,D,E,F)，写出与下列等查询等价的SQL表达式：

1） $\sigma _{A=10}\left( S\right)$

SELECT *
FROM S
WHERE A = 10;

选择S表中A=10的元组，即查询表中A=10这一行的记录
2） $\Pi _{A,B}\left( S\right)$

SELECT DISTINCT A, B
FROM S;

投影S表中A，B两个属性列，即查询表中A，B两列（注意：对于关系代数来说，投影需要消除重复行，所以加了DISTINCT）
3）S $\Join$ T

SELECT A, B, S.C, S.D, E, F
FROM S, T
WHERE S.C = T.C AND S.D = T.D;

自然连接S和T关系，连接后去掉重复列
4）S $\underset{S.C = T.C}\Join$ T

SELECT *
FROM S, T
WHERE S.C = T.C;

等值连接
5）S $\underset{A < E}\Join$ T

SELECT *
FROM S, T
WHERE A < E;

非等值连接
6） $\Pi _{C,D}\left( S\right)\times T$

SELECT S1.C, S1.D, T.C, T.D, E, F
FROM T, ( SELECT DISTINCT C, D FROM S) AS S1;

S表C，D两列的投影与T表的笛卡尔积，同样的（因为是关系代数）S表的C, D两列的投影需要去掉重复项

第 4 题

（因为这里涉及到了建表、插入数据啥的，我这部分会写的详细一点）
— — — — — — — — — — — — — — — — — — — — — — — — — — — —
👀有一个SPJ数据库，包括 S、P、J 及 SPJ 4个关系模式：
S(SNO,SNAME,STATUS,CITY)
P(PNO,PNAME,COLOR,WEIGHT)
J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)
— — — — — — — — — — — — — — — — — — — — — — — — — — — —
① 供成商表S由供应商代码(SNO)、供应商姓名(SNAME) 、供应商状态(STABUS)、供应商所在城市(CITY) 组成。
② 零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成。
③ 工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。
④ 供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)
组成，表示某供应商供应某种零件给某工程项目的数量为QTY。

CREATE DATABASE SPJ;
USE SPJ;    /* 这里可以使用USE来切换至数据库SPJ，也可在RDBMS中的可视化窗口的相应位置点击切换*/
CREATE TABLE S (
	SNO VARCHAR(5) PRIMARY KEY,
	SNAME VARCHAR(10) NOT NULL,
	STATUS INT NOT NULL,
	CITY VARCHAR(10) NOT NULL
)
CREATE TABLE P (
	PNO VARCHAR(5) PRIMARY KEY,
	PNAME VARCHAR(10) NOT NULL,
	COLOR VARCHAR(10) NOT NULL,
	WEIGHT INT NOT NULL
)
CREATE TABLE J (
	JNO VARCHAR(5) PRIMARY KEY,
	JNAME VARCHAR(10) NOT NULL,
	CITY VARCHAR(10) NOT NULL
)
CREATE TABLE SPJ (
	SNO VARCHAR(5),
	PNO VARCHAR(5) NOT NULL,
	JNO VARCHAR(5) NOT NULL,
	PRIMARY KEY (SNO, PNO, JNO),   /*SPJ表的主码是(SNO, PNO, JNO)，主码只能有一个，但主码的属性可以有多个，称为联合主码(键)*/
	FOREIGN KEY (SNO) REFERENCES S(SNO),    /* SNO是外码，被参照表是S*/
	FOREIGN KEY (PNO) REFERENCES P(PNO),    /* PNO是外码，被参照表是P*/
	FOREIGN KEY (JNO) REFERENCES J(JNO),    /* JNO是外码，被参照表是J*/
	QTY INT NOT NULL
)

❗❕❗ 注意：这里新建表的时候一定要切换到刚刚新建的数据库SPJ下，不然四张表全建到别的地方去了哈哈哈~（我这里采用USE SPJ来切换至相应数据库，大家也可以如下图点击切换，总之别忘了就行！）
在这里插入图片描述
对了，我给SPJ表设置了外码，避免输入了不存在的SNO、PNO、JNO，如下图（因为我的S表的SNO最多只存到了S5，所以S6是不存在的，SPJ表的SNO来自S表，所以其SNO应该是S表的SNO的子集才对）：

/* S表插入数据：*/
INSERT INTO S VALUES('S1','精益',20,'天津')
INSERT INTO S VALUES('S2','盛锡',10,'北京')
INSERT INTO S VALUES('S3','东方红',30,'北京')
INSERT INTO S VALUES('S4','丰泰盛',20,'天津')
INSERT INTO S VALUES('S5','为民',30,'上海')
 
SELECT * FROM S;

/* P表插入数据：*/
INSERT INTO P VALUES('P1','螺母','红',12)
INSERT INTO P VALUES('P2','螺栓','绿',17)
INSERT INTO P VALUES('P3','螺丝刀','蓝',14)
INSERT INTO P VALUES('P4','螺丝刀','红',14)
INSERT INTO P VALUES('P5','凸轮','蓝',40)
INSERT INTO P VALUES('P6','齿轮','红',30)
 
SELECT * FROM P;

/* J表插入数据：*/
INSERT INTO J VALUES('J1','三建','北京')
INSERT INTO J VALUES('J2','一汽','长春')
INSERT INTO J VALUES('J3','弹簧厂','天津')
INSERT INTO J VALUES('J4','造船厂','天津')
INSERT INTO J VALUES('J5','机车厂','唐山')
INSERT INTO J VALUES('J6','无线电厂','常州')
INSERT INTO J VALUES('J7','半导体厂','南京')
 
SELECT * FROM J;

在这里插入图片描述

/* SPJ表插入数据：*/
INSERT INTO SPJ VALUES('S1','P1','J1',200)
INSERT INTO SPJ VALUES('S1','P1','J3',100)
INSERT INTO SPJ VALUES('S1','P1','J4',700)
INSERT INTO SPJ VALUES('S1','P2','J2',100)
INSERT INTO SPJ VALUES('S2','P3','J1',400)
INSERT INTO SPJ VALUES('S2','P3','J2',200)
INSERT INTO SPJ VALUES('S2','P3','J4',500)
INSERT INTO SPJ VALUES('S2','P3','J5',400)
INSERT INTO SPJ VALUES('S2','P5','J1',400)
INSERT INTO SPJ VALUES('S2','P5','J2',100)
INSERT INTO SPJ VALUES('S3','P1','J1',200)
INSERT INTO SPJ VALUES('S3','P3','J1',200)
INSERT INTO SPJ VALUES('S4','P5','J1',100)
INSERT INTO SPJ VALUES('S4','P6','J3',300)
INSERT INTO SPJ VALUES('S4','P6','J4',200)
INSERT INTO SPJ VALUES('S5','P2','J4',100)
INSERT INTO SPJ VALUES('S5','P3','J1',200)
INSERT INTO SPJ VALUES('S5','P6','J2',200)
INSERT INTO SPJ VALUES('S5','P6','J4',500)
 
SELECT * FROM SPJ;

在这里插入图片描述
All right，现在表建好了，数据也插入好了，开始做题~
1）求供应工程J1零件的供应商号码SNO：
（因为工程项目J1的同一个供应商供应的零件可能不同（即SNO相同，JNO相同，但PNO不同），比如同一个供应商可以供应螺丝刀、凸轮等等），所以在查询时需要加上DISTINCT去掉重复项，如下图对比：

SELECT DISTINCT SNO
FROM   SPJ
WHERE  JNO = 'J1';

在这里插入图片描述
2）求供应工程J1零件P1的供应商号码SNO：
（这里我也加上了DISTINCT是因为即使是同一个项目，但是如果是多次从同一个供应商进货，而且没把供应数量QTY汇总在一起的话是会查询到重复的SNO的，这里虽然加不加都一样，不过有些情况下应该是会有重复的数据的，所以最好还是加上叭）

SELECT DISTINCT SNO
FROM   SPJ
WHERE  JNO = 'J1' AND PNO = 'P1';

在这里插入图片描述
3）求供应工程J1零件为红色的供应商号码SNO：
同样，因为咱们只是想知道谁供应的，不需要知道他供应了几次（或者说在表中重复出现了几次），所以依然使用了DISTINCT

SELECT DISTINCT SNO
FROM   SPJ
WHERE  JNO = 'J1' AND PNO IN
						 ( SELECT PNO
						   FROM   P
						   WHERE  COLOR = '红');

在这里插入图片描述
4）求没有使用天津供应商生产的红色零件的工程号JNO：
（这里求没有使用的，那我们可以逆向思维，查询使用了天津的，然后运用NOT EXISTS来解决这个问题）

SELECT JNO
FROM   J
WHERE  NOT EXISTS
     ( SELECT *
	   FROM   S,P,SPJ
	   WHERE SPJ.JNO = J.JNO
	     and SPJ.SNO = S.SNO
	     and SPJ.PNO = P.PNO
	     and S.CITY  = '天津'
	     and P.COLOR = '红');

SELECT * FROM J;   /*打印出J表参照*/

SELECT *           /*打印出使用了天津供应商和零件为红色的工程*/
FROM   S,P,J,SPJ
WHERE SPJ.JNO = J.JNO
  and SPJ.SNO = S.SNO
  and SPJ.PNO = P.PNO
  and S.CITY  = '天津'
  and P.COLOR = '红';

在这里插入图片描述
可看到工程号J1的厂家是北京，不是天津，但是它供应的零件为红色，所以也不满足条件，如果是一点一点去写条件来查询的话可能会漏掉，所以用NOT EXISTS是不错的选择✌~

5）求至少用了供应商S1所提供的全部零件的工程号JNO：
（这里我分成了两步来做：①先查出S1供应的零件号有哪些； ②在第一步知道了具体零件号的基础上查询使用了S1供应的全部的这几种零件的JNO）
在这里插入图片描述
可见，S1供应了P1和P2两种零件，最终查询结果为空。但是如下图J4同时使用了P1和P2那为什么不是呢，因为它的P2是来自于S5供应商的，并不满足题目要求的由供应商S1所提供的零件，所以查询结果为空也能理解~
在这里插入图片描述

第 5 题

（我就直接写解题的代码和运行结果啦，需要注意的地方我再强调~）
1）找出所有供应商的姓名和所在城市：

SELECT SNAME, CITY
FROM S;

在这里插入图片描述
2）找出所有零件的名称、颜色、重量：

SELECT PNAME, COLOR, WEIGHT
FROM P;

在这里插入图片描述
3）找出使用供应商S1所供应零件的工程号码：

SELECT JNO
FROM SPJ
WHERE SNO = 'S1';

在这里插入图片描述
4）找出工程项目J2使用的各种零件的名称和数量：

SELECT PNAME, QTY
FROM SPJ, P
WHERE SPJ.PNO = P.PNO AND JNO = 'J2';

在这里插入图片描述
5）找出上海厂商供应的所有零件号码：

SELECT DISTINCT PNO
FROM SPJ, S
WHERE SPJ.SNO = S.SNO AND S.CITY = '上海';

（ps：我觉得这样连接起来做比较简洁，当然也可用下面的这种子查询方式来做）

SELECT DISTINCT PNO
FROM SPJ
WHERE SNO IN
     (SELECT SNO
      FROM S
      WHERE CITY = '上海');

在这里插入图片描述
6）找出使用上海产的零件的工程名称：

SELECT DISTINCT JNAME
FROM J, SPJ, S
WHERE J.JNO = SPJ.JNO AND SPJ.SNO = S.SNO AND S.CITY = '上海';

以上是我把三张涉及到的表先连接成了一张大表，然后直接找S表中CITY为上海都就可，当然也可以用比较直观好理解的嵌套子查询来做：

SELECT JNAME
FROM J
WHERE JNO IN
          ( SELECT JNO
		    FROM SPJ
			WHERE SNO IN
			          ( SELECT SNO
					    FROM S
						WHERE CITY = '上海'));

在这里插入图片描述
7）找出没有使用天津产的零件的工程号码：
（先找出使用了天津产的工程号码，然后使用NOT IN即可得到题中所求JNO）

SELECT JNO
FROM SPJ
WHERE JNO NOT IN
	  ( SELECT JNO
        FROM S, SPJ
        WHERE S.SNO = SPJ.SNO AND CITY = '天津');

SELECT DISTINCT JNO
FROM S, SPJ
WHERE S.SNO = SPJ.SNO AND CITY = '天津';    /*找出使用了天津产的工程号码*/

SELECT DISTINCT JNO FROM SPJ;   /*全部的JNO*/

在这里插入图片描述
8）把全部红色零件的颜色改为蓝色：

UPDATE P
SET COLOR = '蓝'
WHERE COLOR = '红';

SELECT * FROM P;   /*打印P表出来看看*/

更改数据，自然得用UPDATE关键字啦。可见P表中已没有红色的零件了，更改成功~
在这里插入图片描述
9）由S5供给J4的零件P6改为由S3供应：

UPDATE SPJ
SET SNO = 'S3'
WHERE SNO = 'S5' AND PNO = 'P6' AND JNO = 'J4';

在这里插入图片描述
10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录：
（这里涉及到两个表，先是删除S表的S2记录，然后删除SPJ表的S2相关的记录，删除前建议大家先备份一下数据库SPJ哈，备份我在上一篇博客有提到，也可以直接参考这里：Sqlserver之数据库的备份与还原）

DELETE
FROM S
WHERE SNO = 'S2';

DELETE
FROM SPJ
WHERE SNO = 'S2';

SELECT * FROM S;
SELECT * FROM SPJ;

需要注意！！ 因为我用S表的主码SNO给SPJ表加了外码约束，所以不能先删除S表的S2记录，必须先删除了SPJ表的S2记录才能删除S表的S2记录，如下图所示：
在这里插入图片描述
删除S表的S2记录失败，先来删除SPJ表的：

删除成功，现在可以来删除S表的啦：

11）请将（S2,J6,P4,200）插入供应情况关系：
（需要注意，如果INTO语句中不写VALUES中的数据对应的列名的话，VALUES中的数据要以表中列的顺序插入，比如这里就得把题中的J6和P4颠倒一下顺序）

INSERT
INTO SPJ
VALUES ('S2', 'P4', 'J6', 200);

SELECT * FROM SPJ;

emmm，失败了，原因是刚刚我把S表中S2的记录给删了，所以由于受到外码约束，S表中没有S2，DBMS拒绝了改语句执行，我先把刚刚备份好的数据库还原一下
在这里插入图片描述
好了，成功插入~

第 9 题

请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。针对该视图完成下列查询：

CREATE VIEW SANJIAN
AS 
SELECT SNO, PNO, QTY
FROM SPJ, J
WHERE SPJ.JNO = J.JNO AND JNAME = '三建';

SELECT SNO, PNO, QTY       /*打印出来康康*/
FROM SPJ, J
WHERE SPJ.JNO = J.JNO AND JNAME = '三建';
SELECT *
FROM SPJ, J
WHERE SPJ.JNO = J.JNO;

在这里插入图片描述
当然也可以在左侧找到相应位置后打开（编辑前200行）看到：

1）找出三建项目使用的各种零件代码PNO及其数量：

SELECT DISTINCT PNO, QTY
FROM SANJIAN;

SELECT PNO, QTY   /*不去重的作对比*/
FROM SANJIAN;

可见有PNO和QTY完全相同的情况，所以加上DISTINCT去掉重复项
在这里插入图片描述
2）找出供应商S1的供应情况：

SELECT *
FROM SANJIAN
WHERE SNO = 'S1';

SELECT * FROM SANJIAN;     /*打印出整个视图参照*/

在这里插入图片描述

这期习题博客到这里也就结束啦，感谢阅读~😊
对了，细心的小伙伴可能发现我的截图中有一部分是被系统标红了的，其实原因是我在新建表或者视图之后接着使用了，如果关闭SSMS重新打开的话这些标红就没了，嘻嘻，不过并不影响正常操作，但是为了美观一些，我后来还是重启了SSMS截图 ~
那咱们下期见~
在这里插入图片描述