--1、
/*找出所有供应商的姓名和所在城市*/
select sname,city
from s
--2、
/*找出所有零件的名称、颜色、重量*/
select pname,color,weight
from p
--3、
/*找出使用供应商s1所供应零件的工程号码*/
select distinct 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 s,spj
where s.sno=spj.sno and city='上海'
/*找出上海厂商供应的所有零件号码(另解)*/
select distinct pno
from spj
where sno in(select sno
from s
where city='上海'
)
--6、
/*找出使用上海产的零件的工程名称*/
select distinct jname
from spj,j
where sno in(select sno
from s
where city='上海'
)
and spj.jno=j.jno
/*找出使用上海产的零件的工程名称(另解1)*/
select distinct jname
from j,spj,s
where j.jno=spj.jno and spj.sno=s.sno and s.city='上海'
/*找出使用上海产的零件的工程名称(另解2)*/
select distinct jname
from j
where jno in(select jno
from s,spj
where spj.sno=s.sno and city='上海'
)
--7、
/*找出没有使用天津产的零件的工程号码*/
select jno
from j
where jno not in(select jno
from spj
where sno in(select sno
from s
where city='天津'
)
)
/*找出没有使用天津产的零件的工程号码(另解1)*/
select jno
from j
where jno not in(select jno
from s,spj
where s.sno=spj.sno and city='天津'
)
/*找出没有使用天津产的零件的工程号码(另解2)*/
select jno
from j
where not exists(select *
from spj
where spj.jno=j.jno
and sno in(select sno
from s
where city='天津'
)
)
--8、
/*求每个供应商的代码、供应商姓名和供应数量的总和*/
select s.sno,sname,SUM(qty)
from spj,s
where spj.sno=s.sno
group by s.sno,sname
————————————————————————————————————————————————————————————————————————————————更新于2021.4.9
/*9、把全部红色零件的颜色改成蓝色*/
update p
set color='蓝'
where color='红'
/*10、由S5供给J4的零件P6改为由S3供应*/
update spj
set sno='s3'
where sno='s5' and pno='p6' and jno='j4'
/*11、从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录*/
delete
from spj
where sno='s2'
delete
from s
where sno='s2'
/*12、请将(S2,J6,P4,200)插入供应情况关系*/
insert
into spj
values('s2','p4','j6',200)
—————————————————————————————————————————————————————————————————————————————————更新于2021.4.16
第三章关系数据库标准语言SQL习题参考答案链接:
百度网盘链接
提取码:vbv0