数据库第二章习题5作业答案

建立第二章中习题5中的4个表

1. S表

CREATE TABLE `p` (

`PNO` varchar(2) NOT NULL DEFAULT '',

`PNAME` varchar(10) DEFAULT NULL,

`COLOR` varchar(2) DEFAULT NULL,

`WEIGHT` int(2) DEFAULT NULL,

PRIMARY KEY (`PNO`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. P表

CREATE TABLE `p` (

`PNO` varchar(2) NOT NULL DEFAULT '',

`PNAME` varchar(10) DEFAULT NULL,

`COLOR` varchar(2) DEFAULT NULL,

`WEIGHT` int(2) DEFAULT NULL,

PRIMARY KEY (`PNO`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

3. J表

CREATE TABLE `j` (

`JNO` varchar(2) NOT NULL,

`JNAME` varchar(10) DEFAULT NULL,

`CITY` varchar(10) DEFAULT NULL,

PRIMARY KEY (`JNO`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

4. SPJ表

CREATE TABLE `spj` (

`SNO` varchar(2) NOT NULL DEFAULT '',

`PNO` varchar(2) NOT NULL DEFAULT '',

`JNO` varchar(2) NOT NULL DEFAULT '',

`QTY` int(3) DEFAULT NULL,

PRIMARY KEY (`SNO`,`PNO`,`JNO`),

KEY `PNO` (`PNO`),

KEY `JNO` (`JNO`),

CONSTRAINT `spj_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `s` (`SNO`) ON DELETE CASCADE,

CONSTRAINT `spj_ibfk_2` FOREIGN KEY (`PNO`) REFERENCES `p` (`PNO`) ON DELETE CASCADE,

CONSTRAINT `spj_ibfk_3` FOREIGN KEY (`JNO`) REFERENCES `j` (`JNO`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8

完成第二章5中的查寻

1. 求供应工程J1零件的供应商号码SNO;

Query: select distinct SPJ.SNO from SPJ where JNO = 'j1';

Result:

2. 求供应工程J1零件P1的供应商号SNO;

Query: select SPJ.SNO from SPJ where JNO='J1' and PNO='P1'

Result:

3. Q求供应工程J1零件为红色的供应商号码SNO;

Query:

select distinct SPJ.SNO from SPJ where SPJ.JNO = 'J1' and SPJ.PNO in(select P.PNO from P where P.COLOR='红')

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

Query:

select distinct SPJ.JNO from SPJ where SPJ.JNO not in

(select distinct SPJ.JNO from SPJ where SPJ.PNO in

(select P.PNO from P where COLOR='红') and SPJ.SNO in

(select S.SNO from S where S.CITY='天津')

)

Result:

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

Query: select distinct JNO from spj s1 where not exists

(select * from spj s2 where s2.SNO='S1' and not exists

(select * from spj s3 where s3.SNO = s1.SNO and s3.PNO = s2.PNO

)

);

Result:

针对系统3中的四个表使用SQL完成下列操作

1. 找出所有的供应商的姓名和所在城市

Query: select SNAME,CITY from S

Result:

2. 找出所有零件的名称，颜色，重量

Query: select PNAME,COLOR,WEIGHT from P;

Result:

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

Query: select distinct JNO from SPJ where SNO='S1';

Result:

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

Query: select PNAME ,sum(QTY) QUILITY from SPJ,P where SPJ.PNO=P.PNO and SPJ.JNO='J2' group by(SPJ.PNO);

Result:

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

Query: select distinct SPJ.PNO from SPJ where SPJ.SNO in (select S.SNO from S where S.CITY='上海')

Result:

6. 找出使用上海产的零件工程名称；

Query:

select J.JNAME from J where J.JNO in

(

select distinct SPJ.JNO from SPJ where SNO in

(select S.SNO from S where CITY='上海')

);

Result:

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

Query:

select SPJ.JNO from SPJ where SPJ.JNO not in

(

select distinct SPJ.JNO from SPJ where SPJ.SNO in

(select S.SNO from S where S.CITY='天津')

)

Result:

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

Update: update P set COLOR='蓝' where COLOR='红'

9. 由S5供给J4的零件P6改为由S3供应，请做必要的修改；

Update: update SPJ set SNO='S3' where SNO='S5' and JNO='J4' and PNO='P6';

10. 从供应商表中删除S2的记录，并从供应情况表中删除相应的

Delete from S where SNO = ‘S2’

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

由于第10小题已经把S2从S表中删除了，而SPJ有一个外键关联到了S表所以必须先在S表中插入关于S2的记录

插入记录S2：Insert into S2 values(‘SNO’,’SNAME’,10,’CITY’)

插入记录（S2,J6,P4,200）insert into SPJ values('S2','P4','J6',200);

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

1. 创建视图

create view sj_support_info(SNO,PNO,QTY) as select SNO,PNO,QTY from spj where JNO in(select JNO from j where JNAME='三建')

2. 找出三建工程所使用的各种零件代码及其数量

Query:select PNO,sum(QTY) from sj_support_info group by(PNO);

Result:

3. 找出供应商S1的供应情况

Query: select SNO,PNO,QTY from sj_support_info where SNO='S1'

Result: