求供应工程J1零件的供应商号码SNO 2 求供应工程J1零件P1的供应商号码SNO 3 求供应工程J1零件为红色的供应商号码SNO 4 求没有使用天津供应商生产的红色零件的工程号JNO 5 找出所有供

--P130 第4题
(1)、求供应工程J1零件的供应商号码SNO
select SNO from SPJ ;
--(2)、求供应工程J1零件P1的供应商号码SNO
select SNO from SPJ where PNO='P1';
--(3)、求供应工程J1零件为红色的供应商号码SNO；
--select PNO from P where COLOR='红'
select SNO from SPJ where PNO IN (select PNO from P where COLOR='红');
--(4)、求没有使用天津供应商生产的共色零件的工程号JNO；
--select SNO from S where CITY='天津';
--select PNO from P where COLOR='红';
select * from SPJ where SNO IN(select SNO from S where CITY='天津') 
AND PNO IN(select PNO from P where COLOR='红')
--(5)、求至少使用了供应商S1所供应的全部零件的工程号JNO；
--select * from SPJ where  SNO IN(select SNO from SPJ where SNO='S1') --只会查出来包含S1的数据
select * from SPJ where exists(select * from SPJ where SNO='S1') --如果S1存在就会把表里所有的数据都查出来
--select * from SPJ where exists(select * from SPJ where SNO='S111') --如果S111不存在就会啥都不查出来
--P130  第5题
--(1)、找出所有供应商的姓名和所在城市；
select SNAME,CITY from S;
--（2）、找出所有零件的名称、颜色、重量
select PNAME,COLOR,WEIGHT from P;
--（3）、找出使用供应商S1所供应零件的工程号码
select JNO from SPJ where SNO = 'S1';
--（4）、找出工程项目J2使用的各种零件的名称及其数量
select P.PNAME AS 零件名称,count(SPJ.JNO) AS 数量 from SPJ,P where SPJ.JNO='J2' AND SPJ.PNO = P.PNO GROUP BY P.PNAME;
--（5）、找出上海厂商供应的所有零件号码
--distinct PNO  查询结果不包含重复的
select distinct PNO from SPJ where SNO IN(select SNO from S where CITY='上海');
--(6)、找出使用上海产的零件的工程号码
select distinct JNO from SPJ where SNO =(select SNO from S where CITY='上海');
--（7）、找出没有使用天津产的零件的工程号码；
select distinct JNO from SPJ where SNO not in(select SNO from S where CITY='上海');
--（8）、把全部红色零件的颜色改成蓝色
--select * from P where COLOR='蓝'
update  P set COLOR='蓝' where COLOR='红'
--（9）、由S5提供给J4的零件P6改为由S3提供，请做必要的修改
--select * from SPJ where SNO='S3' AND PNO='P6'
update SPJ set SNO='S3' where SNO='S5' AND PNO='P6'
--(10)、从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录
--select * from SPJ where SNO='S2'
--select * from S where SNO='S2'
--因为有了约束，所以必须先删除字表，才能删除主表
delete from SPJ where SNO='S2'
delete from S where SNO='S2'
--（11）、请将（S2,J6,P4,200）插入供应情况关系
insert into SPJ(SNO,JNO,PNO,QTY) values('S2','J6','P4',200);

--创建供应商表 S   
create table S(
  SNO varchar(10) primary key ,  --设置SNO为主键 
  SNAME varchar(20) ,
  STATUS int ,
  CITY varchar(20) 
);
--创建零件表 P  
create table P(
  PNO varchar(10) primary key ,  --设置PNO为主键 
  PNAME varchar(20) ,
  COLOR varchar(10) ,
  WEIGHT int 
);
--创建工程表 J
create table J(
  JNO varchar(10) primary key ,  --设置JNO为主键 
  JNAME varchar(20) ,
  CITY varchar(10)
);
--创建供应情况表 SPJ 
create table SPJ(
  SNO varchar(10) ,  --设置外码    
  PNO varchar(10) ,
  JNO varchar(10)  ,
  QTY int,
  primary key (SNO,PNO,JNO),
  foreign key(SNO) references S(SNO),
  foreign key(PNO) references P(PNO),
  foreign key(JNO) references J(JNO)
);
--往S表插入语5条数据
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','上海');
--往P表插入语6条数据
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','半导体厂','南京');
--往SPJ表插入数据
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);
insert into SPJ values('S2','P3','J4',500);
insert into SPJ values('S2','P3','J5',400);
insert into SPJ values('S2','P5','J1',400);
insert into SPJ values('S2','P5','J2',100);
insert into SPJ values('S3','P1','J1',200);
insert into SPJ values('S3','P3','J1',200);
insert into SPJ values('S4','P5','J3',300);
insert into SPJ values('S4','P6','J4',200);
insert into SPJ values('S4','P6','J4',100);
insert into SPJ values('S5','P2','J4',100);
insert into SPJ values('S5','P3','J1',200);
insert into SPJ values('S5','P6','J2',200);
insert into SPJ values('S5','P6','J4',500);