数据库系统教材SPJ数据库题目参考答案

设有一个SPJ数据库，包括S、P、J、SPJ四个关系模式

S(SNO，SNAME，STATUS，CITY)：

P(PNO，PNAME，COLOR，WEIGHT)：

J(JNO，JNAME，CITY)；

SPJ(SNO，PNO，JNO，CITY)；

供应商表S由供应商代码(SNO)、供应商姓名(SNAME)、供应商所在城市(CITY)组成；

零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量（WEIGHT）组成：

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项J所在城，(CITY)组成：供应情况表SPJ供应南代码(SNO)、零件代码(PNO)、工程顶目代码（JNO），供应数量(QTY)组成，表示某供应商供应某种零件给某工程项目的数量为QTY。

题目及答案如下：

1. 用SQL语句建立这四个表。

create table S

(sno char(2) not null primary key,

sname varchar(10),

status int,

city varchar(10))

create table P

(pno char(2) not null primary key,

pname varchar(10),

color varchar(10),

weight int)

create table J

(jno char(2) not  null primary key,

jname varchar(10) ,

city varchar(10))

create table SPJ

(sno char(2) ,

pno char(2) ,

jno char(2) ,

qty int,

primary key(sno,pno,jno))

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,'上海');

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);

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','半导体厂','南京');

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);

2.针对以上四个表用SQL语句完成以下操作

(1)找出所有供应商的姓名和所在城市。

select sname,city from s

(2)找出所有零件的名称、颜色、重量。

select pname,color,weight from p

(3)找出使用供应商S1所供应零件的工程号码。

select jno from spj

where sno='S1'

(4)找出工程项目J2使用的各种零件的名称及其数量G、

select pname,qty

from p,spj

where p.pno=spj.pno and jno='J2'

(5)找出上海厂商供应的所有零件号码。

select pno

from s,spj

where s.sno=spj.sno

and city='上海'

(6)找出使用上海产的零件的工程名称。

select jname

from s,spj,j

where s.sno=spj.sno and j.jno=spj.jno

and s.city='上海'

(7)找出没有使用天津产的零件的工程号码。

select jno

from s,spj

where s.sno=spj.sno

and s.city<>'天津'

(8)把全部红色零件的颜色改成蓝色。

update p set color='蓝' where color='红'

(9)由S5供给J4的零件S6改为由S1供应，请做必要的修改

update spj set sno='S1'

where sno='S5' and jno='J4' and pno='S6'

(10)从供应商关系中删除s2的记录，并从供应情况关系中删除相应的记录。

delete from spj where sno='S2'

delete from s where sno='S2'

(11)请将(S2，J6，P4，200)插入供应情况关系。

insert into spj values('S2','P4','J6',200)

(12)统计各个供应商供应的零件总数。

select sno,SUM(qty) total_qty

from spj

group by sno

(13)查询总供应量在600以上的供应商的姓名、供应商编号、总供应数量。

select sname,s.sno,SUM(qty) total_qty

from spj,s

where s.sno=spj.sno

group by s.sno,sname

having SUM(qty)>600

(14)求至少用了供应商S1所供应的全部零件的工程号JNO：

select jno from j as a where not exists(

select *from

(select b.pno from spj as b where b.sno='S1') as d

where d.pno not in(

select c.pno from spj as c where c.jno=a.jno))

3、请为三建工程项目建立一个供应情况的视图，包括供应两代码(SNO)、零件代码(PNO)、供应数量(QTY)。

create view spj_view

as

select sno,pno,qty

from spj,j

where j.jno=spj.jno

and jname='三建'

针对该视图完成下列查询：

(1)找出三建工程项目使用的各种零件代码及其数量。

select pno,qty from spj_view

(2)找出供应商S1的供应情况。

select * from spj_view

where sno='S1'

4、针以上表，用SQL语言完成以下各项操作：

(1)把对表S的INSERT权限授予用户‘张勇’，并允许他再将此权限授予其他用户。

grant insert on s to 张勇 with grant option

(2)把查询SPJ表和修改QTY属性的权限授予用户‘李天明’。

grant select on spj to 李天明

grant update(qty) on spj to 李天明

WORD版下载（包含全套代码及运行截图）

链接: https://pan.baidu.com/s/1mvevbKAUj8ZCEB2h1NrPIg

提取码: