--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 jno='j2' and spj.pno=p.pno

--5、
/*找出上海厂商供应的所有零件号码*/
select distinct pno
from s,spj
where city='上海' and s.sno=spj.sno

/*找出上海厂商供应的所有零件号码(另解)*/
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='天津'
                              )
                 )

/*找出没有使用天津产的零件的工程号码(另解)*/
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