针对数据库系统概论第四版第二章SQL实现
--数据库系统原理表以及数据

create table S (
   SNO varchar(8) not null primary key,
   SNAME nvarchar(20) unique,
   STATUS int ,
   CITY nvarchar(20)
);

create table P (
   PNO varchar(8) not null primary key,
   PNAME nvarchar(20),
   COLOR nvarchar(10),
   WEIGHT int
);

create table J(
   JNO varchar(8) not null primary key,
   JNAME nvarchar(30),
   CITY  nvarchar(20)
);

create table SPJ (
   SNO varchar(8),
   PNO varchar(8),
   JNO varchar(8),
   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)
);

delete from s;
delete from J;
delete from P;
delete from SPJ;

--S表：供应商表；
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表：零件表：
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);

--J表：工程项目表：
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','J1',100);
insert into SPJ values('S4','P6','J3',300);
insert into SPJ values('S4','P6','J4',200);

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

insert into SPJ values('S1','P1','J2',5000);


--①、求供应工程J1零件的供应商的号码SNO：
select distinct SNO '供应商号码'
  from  SPJ
  where JNO = 'J1';

--②、求供应工程J1零件P1的供应商号码:SNO:
select distinct SNO '供应商号码'
  from SPJ
  where JNO = 'J1' and PNO = 'P1';

--③、求供应工程J1零件为红色的供应商号码SNO：
select distinct SNO '供应商号码'
  from SPJ
  where JNO = 'J1' and exists(select * from P where  SPJ.PNO = P.PNO and p.COLOR = '红' );

--④、求没有使用天津供应商生产的红色零件的工程号JNO：
--select distinct SPJ.SNO from S,P,SPJ where S.SNO = SPJ.SNO and P.PNO = SPJ.PNO and P.COLOR = '红' and S.CITY = '天津';
select distinct JNO '工程号'
  from SPJ
  where SNO not in (select distinct SPJ.SNO
                       from S,P,SPJ
                       where S.SNO = SPJ.SNO and P.PNO = SPJ.PNO and P.COLOR = '红' and S.CITY = '天津'
                    );


--⑤、求至少用了供应商S1供应的所有零件的工程号JNO：
--也就是说：不存在一个JNO（工程号）,它S1供应的零件它没有使用。
  --或者说：不存在一个JNO（工程号）,不存在某个S1供应的零件，在此工程的零件列表中不存在。
select * from SPJ;
--供应商S1供应的所有零件为：
select distinct  PNO from SPJ where SNO = 'S1';
select distinct JNO from SPJ where PNO='P1' and JNO in (select JNO from SPJ where PNO = 'P2');

--第一个not exists对应的是不存在，第二个not exists对应的是供应商S1供应的零件它没有使用；
select  distinct JNO
   from SPJ
   where not exists(
       select * from SPJ SPJ1    
          where SNO='S1' and  not exists (
              select * from SPJ SPJ2
                 where SPJ2.PNO = SPJ1.PNO and SPJ2.JNO = SPJ.JNO
          )
);

--第三章：第5题：

--（1）找出所有供应商的姓名和所在城市；
select SNAME,CITY from S;
--（2）找出所有零件的名称、颜色、重量；
select PNAME,COLOR,WEIGHT from P;
--（3）找出使用供应商S1供应的所有零件的工程号码；
select distinct JNO
   from SPJ
   where not exists(
      select * from SPJ SPJ1
        where SNO = 'S1' and not exists(
           select * from SPJ SPJ2
              where SPJ2.PNO = SPJ1.PNO and SPJ2.JNO = SPJ.JNO
        )
);
--（4）找出工程项目J2使用的各种零件的名称和重量；
select P.PNAME,P.WEIGHT
   from SPJ,P
   where JNO = 'J2' and P.PNO = SPJ.PNO;
--（5）找出上海厂商供应的所有零件号码；
select distinct PNO
   from SPJ,S
   where S.CITY = '上海' and SPJ.SNO = S.SNO
--（6）找出使用上海产的零件的工程名称；
select distinct J.JNAME
   from SPJ,J
   where J.JNO = SPJ.JNO and  SPJ.SNO in (
         select SNO from S where CITY = '上海');
--（7）找出没有使用天津产的零件的工程号码；
select  distinct JNO
   from SPJ
   where  exists (
       select * from S where S.SNO = SPJ.SNO and S.CITY != '天津');
--（8)把全部红色零件的颜色改为蓝色；
--select * from P;
update P set COLOR = '蓝' where COLOR = '红';
--（9）由S5供给J4的零件P6改为由S3供应，请做出必要的修改。
--select * from SPJ where SNO = 'S3' and PNO = 'P6' and JNO = 'J4';
update SPJ set SNO = 'S3'
   where SNO = 'S5' and PNO = 'P6' and JNO = 'J4';
--（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);
--select * from SPJ;