第三章作業【資料庫原理】

第二章作业【数据库原理】

• ​​前言​​
• ​​推荐​​
• ​​第三章作业​​

• ​​第3章第1题简答题​​
• ​​第3章第2题（关系代数->SQL代码）​​
• ​​第3章第3题（创建SPJ数据库中的四张表）​​
• ​​模拟数据​​
• ​​第3章第4题（SPJ数据库上完成查询）​​
• ​​第3章第5题（SPJ数据库上完成各种操作）​​
• ​​恢复数据​​

• ​​最后​​

前言

以下内容源自数据库原理l

仅供学习交流使用

推荐

无

第三章作业

第3章第1题简答题

一. 简答题（共1题，100分）

1. (简答题)

   试述 SQL 语言的特点。

1） 一体化：SQL提供了一系列完整的数据定义和操纵功能，用SQL可以实现数据库生命周期中的全部活动，包括定义关系模式，
录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求。

2） 用SQL语言进行数据操作，只要提出“做什么”，而无需知名”怎么做“，因此无需了解存取路径，存取路径的选择语句SQL的操作过程由系统自动完成

3） 面向集合的操作方式：SQL语言才采用集合操作方式，不仅操作对象，查找结果可以是元组的集合，
而且一次插入、删除、更新操作的对象也就可以是元组的集合

4） 以同一种语言结构提供多种使用方式： SQL语言既是自含式语言，又是嵌入式语言。作为自含式语言，
他能给独立地用户联机交互的使用方式，作为嵌入式语言，它能够嵌入到高级语言程序中，供程序员设计程序时使用。

5） SQL语言可以对两种基本数据结构进行操作，一种是“表”，另一种是“视图(View)”。      

第3章第2题（关系代数->SQL代码）

一. 其它（共1题，100分）

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

（1）σA=10(S)

（2）πA,B(S)

（3）S∞T

（4）S ∞S.C=T.C T

（5）S ∞A<E T

（6）πC,D(S)×T

(1) SELECT * FROM S WHERE A=10
(2) SELECT A,B FROM S
(3) SELECT A,B,S.C,D,E,F FROM S,T WHERE S.C=T.C AND S.D=T.D
(4) SELECT * FROM S,T WHERE S.C=T.C
(5) SELECT * FROM S,T WHERE S.A<T.E
(6) SELECT ST.C,ST.D,T.C,T.D,E,F FROM T,(SELECT DISTINCT C,D FROM S) AS ST      

第3章第3题（创建SPJ数据库中的四张表）

一. 其它（共1题，100分）

1. (其它)

设有一个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）

试用SQL语句创建以上四张表。

我的答案：

CREATE TABLE `s` (
  `SNO` VARCHAR(10) NOT NULL COMMENT '供应商代码',
  `SNAME` VARCHAR(10) NOT NULL COMMENT '供应商姓名',
  `STATUS` INT NOT NULL COMMENT '供应商状态',
  `CITY` VARCHAR(10) DEFAULT NULL COMMENT '供应商所在城市',
  PRIMARY KEY (`SNO`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3



CREATE TABLE `p` (
  `PNO` VARCHAR(10) NOT NULL COMMENT '零件代码',
  `PNAME` VARCHAR(10) NOT NULL COMMENT '零件名',
  `COLOR` VARCHAR(2) DEFAULT NULL COMMENT '颜色',
  `WEIGHT` INT NOT NULL COMMENT '重量',
  PRIMARY KEY (`PNO`),
  CONSTRAINT `p_chk_1` CHECK ((`WEIGHT` >= 0))
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3



CREATE TABLE `j` (
  `JNO` VARCHAR(10) NOT NULL COMMENT '工程项目代码',
  `JNAME` VARCHAR(10) NOT NULL COMMENT '工程项目名',
  `CITY` VARCHAR(10) DEFAULT NULL COMMENT '工程项目所在城市',
  PRIMARY KEY (`JNO`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3


CREATE TABLE `spj` (
  `SNO` VARCHAR(10) NOT NULL COMMENT '供应商代码',
  `PNO` VARCHAR(10) NOT NULL COMMENT '零件代码',
  `JNO` VARCHAR(10) NOT NULL COMMENT '工程项目代码',
  `QTY` INT DEFAULT NULL COMMENT '供应数量',
  PRIMARY KEY (`SNO`,`PNO`,`JNO`),
  KEY `PNO` (`PNO`),
  KEY `JNO` (`JNO`),
  CONSTRAINT `spj_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `s` (`SNO`),
  CONSTRAINT `spj_ibfk_2` FOREIGN KEY (`PNO`) REFERENCES `p` (`PNO`),
  CONSTRAINT `spj_ibfk_3` FOREIGN KEY (`JNO`) REFERENCES `j` (`JNO`),
  CONSTRAINT `spj_chk_1` CHECK ((`QTY` >= 0))
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3      

模拟数据

来自​​第二章作业【数据库原理】​​

# 模拟数据
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S1','精益',10,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S2','东方红',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S3','群众',30,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S4','大众',20,'上海');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S5','为民',30,'天津');

INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P1','螺母','红',13);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P2','螺栓','绿',16);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P3','螺丝刀','蓝',15);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P4','螺丝刀','红',15);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P5','凸轮','蓝',30);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P6','齿轮','红',25);

INSERT INTO J(JNO,JNAME,CITY) VALUES('J1','三建','北京');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J2','一汽','长春');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J3','弹簧厂','天津');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J4','机车厂','北京');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J5','无线电厂','福州');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J6','半导体厂','上海');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J7','重机厂','太原');

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J4',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P2','J3',200);

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J1',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J2',500);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J5',700);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J1',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2', 'P5', 'J2',400);

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3', 'P1', 'J1',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3', 'P3', 'J1',200);

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4', 'P5', 'J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4', 'P5', 'J3',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4', 'P6', 'J4',200);

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5', 'P2', 'J3',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5', 'P3', 'J1',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5', 'P6', 'J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5', 'P6', 'J5',400);      

第3章第4题（SPJ数据库上完成查询）

一. 其它（共1题，100分）

1. (其它)

设有一个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）

试用SQL语句完成下列查询操作：

（1）求供应工程J1零件的供应商编号SNO。

（2）求供应工程J1零件P1的供应商编号SNO。

（3）求供应工程J1零件为红色的供应商编号SNO。

（4）求没有使用天津供应商生产的红色零件的工程号JNO。

（5）求至少使用了供应商S1所供应的全部零件的工程号JNO。

#  第3章第4题（SPJ数据库上完成查询）

#（1）求供应工程J1零件的供应商编号SNO。
SELECT DISTINCT SNO 
FROM SPJ;
WHERE JNO='J1';

# S1      S3      S5      S2      S4   

#（2）求供应工程J1零件P1的供应商编号SNO。
SELECT DISTINCT SNO 
FROM SPJ;
WHERE JNO='J1'
AND PNO='P1';
# S1      S3      S5      S2      S4   

#（3）求供应工程J1零件为红色的供应商编号SNO。
SELECT DISTINCT SNO
FROM SPJ
INNER JOIN P
ON SPJ.`PNO`=p.`PNO`
WHERE JNO='J1' AND COLOR='红';

#S1      S3  

#（4）求没有使用天津供应商生产的红色零件的工程号JNO。
SELECT DISTINCT JNO
FROM SPJ
WHERE JNO NOT IN(
  SELECT DISTINCT JNO
  FROM SPJ
  INNER JOIN S
  ON SPJ.`SNO`=S.`SNO`
  INNER JOIN P
  ON SPJ.`PNO`=p.`PNO`
  WHERE CITY='天津' AND COLOR='红'
);

#J1      J4 J3

#（5）求至少使用了供应商S1所供应的全部零件的工程号JNO。
SELECT DISTINCT JNO
FROM SPJ
WHERE NOT EXISTS
  (SELECT * 
  FROM SPJ SPJZ
  WHERE EXISTS
    (SELECT * FROM SPJ SPJX
    WHERE SPJX.`PNO`=SPJZ.`PNO` AND SPJX.`SNO`='S1')
  
  AND NOT EXISTS(
    SELECT * FROM SPJ SPJY
    WHERE SPJY.JNO=SPJ.`JNO` AND SPJY.`PNO`=SPJZ.`PNO`) 
  )

#无      

第3章第5题（SPJ数据库上完成各种操作）

一. 其它（共1题，100分）

1. (其它)

   设有一个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）

试用SQL语句完成下列各项查询操作：

（1）找出所有供应商的姓名和所在城市；

（2）找出所有零件的名称、颜色、重量；

（3）找出使用供应商S1所供零件的工程号码；

（4）找出工程项目J2使用的各种零件的名称及其数量；

（5）找出上海厂商供应的所有零件号码；

（6）找出使用上海产的零件的工程号码；

（7）找出没有使用天津产的零件的工程号码；

（8）把全部红色零件的颜色改成蓝色；

（9）由S5供给J4的零件P6改为由S3供应；

（10）从供应商关系S中删除S2的记录，并从供应情况关系SPJ中删除相应的记录；

（11）请将（S2,J6,P4,200）插入供应情况关系。

#  第3章第5题（SPJ数据库上完成各种操作）

#（1）找出所有供应商的姓名和所在城市；
SELECT SNAME,CITY
FROM S;

#（2）找出所有零件的名称、颜色、重量；
SELECT PNAME,COLOR,WEIGHT
FROM P;

#（3）找出使用供应商S1所供零件的工程号码；
SELECT DISTINCT JNO
FROM SPJ
WHERE SNO='S1';
#J1      J2   J4    J3         

#（4）找出工程项目J2使用的各种零件的名称及其数量；
SELECT DISTINCT PNAME,QTY
FROM SPJ
INNER JOIN P
ON SPJ.`PNO`=p.`PNO`
WHERE JNO='J2';


#（5）找出上海厂商供应的所有零件号码；
SELECT DISTINCT PNO
FROM SPJ
INNER JOIN S
ON SPJ.`SNO`=S.`SNO`
WHERE CITY='上海';
# P5  P6 

#（6）找出使用上海产的零件的工程号码；
SELECT DISTINCT JNO
FROM SPJ
INNER JOIN S
ON SPJ.`SNO`=S.`SNO`
WHERE CITY='上海';
#J1   J3  J4

#（7）找出没有使用天津产的零件的工程号码；
SELECT DISTINCT JNO
FROM SPJ
WHERE JNO NOT IN(

  SELECT DISTINCT JNO
  FROM SPJ
  INNER JOIN S
  ON SPJ.`SNO`=S.`SNO`
  WHERE CITY='天津'

);

#J4


#（8）把全部红色零件的颜色改成蓝色；
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红'



#（9）由S5供给J4的零件P6改为由S3供应；
UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND PNO='P6';


#（10）从供应商关系S中删除S2的记录，并从供应情况关系SPJ中删除相应的记录；
DELETE FROM SPJ 
WHERE SNO='S2';

DELETE FROM S
WHERE SNO='S2';

#（11）请将（S2,J6,P4,200）插入供应情况关系。 
# 先确认有没有S2厂商，有外键约束
INSERT INTO SPJ(SNO,JNO,PNO,QTY) VALUES('S2', 'J6', 'P4',200);      

恢复数据

#恢复原先数据
#删除表再创建，插入
DROP TABLE spj;
DROP TABLE s;
DROP TABLE p;
DROP TABLE j;      

最后

这篇博客能写好的原因是：站在巨人的肩膀上

这篇博客要写好的目的是：做别人的肩膀