//找出所有供应商的姓名和所在城市;
use SPJ;
select SNAME name,CITY place
from S;

//找出所有零件的名称、颜色、重量;
use SPJ;
select PNAME name,COLOR color,WEIGHT weight
from P;

//找出使用供应商S1所供应零件的工程号;
select distinct PNO
from SPJ
where SNO = "S1";

//找出工程项目J1使用的各种零件的名称及其数量;
use SPJ;
select PNO product , sum(QTY) QTY
from SPJ
where JNO = "J1"
group by pno;

//找出上海供应商的所有零件号码;
use SPJ；
select distinct PNO pno
from SPJ
where sno in 
			(
				select sno
        from S
        where CITY = "上海"
			)；

//找出使用上海产的零件的工程名称。
use SPJ;
select distinct JNO
from SPJ
where  SNo in (
		select distinct sno
        from S
        where CITy = "上海"
	)
		
//求供应工程J1零件的供应商号码SNO;
use SPJ;
select distinct sno
from SPJ 
where JNO = "J1"

//求供应工程J1零件P1的供应商号码SNO;
use SPJ;
select distinct sno
from SPJ 
where JNO = "J1" and PNO  = "P1"

//求供应工程J1零件为红色的供应商号码SNO;
use SPJ;
select distinct sno
from SPJ 
where JNO = "J1" and exists(
select *
from P
where P.pno = SPJ.pno and P.color = "红"
)

//求没有使用天津供应商生产的红色零件的工程号JNO;
use SPJ;
select distinct JNO
from SPJ
where JNO not in
(
	select distinct JNO 
    from SPJ,P,S
    where SPJ.SNO = S.SNO and P.PNO = SPJ.PNO and P.color = "红" and s.city = "天津"
)

//求至少用了供应商S1所供应的全部零件的工程号JNO。
use SPJ;
select distinct JNO
from SPJ X
where not exists
(
	select *
    from SPJ Y
    where Y.SNO = "S1" and not exists
    (
		select *
        from SPJ Z
        where Z.PNO = Y.PNO and Z.JNO = X.JNO
    )
)