供应商spj数据库建立

CREATETABLES(SNOVARCHAR(5)NOTNULL,SNAMEVARCHAR(20)NOTNULL,STATUSVARCHAR(5),CITYVARCHAR(20),PRIMARYKEY(SNO));CREATETABLEP(PNOVARCHAR(5),PNAMEVARCHAR(20),COLORVARCHAR(10),WEIGHTSMALLINT,PRIMARYKEY(PNO));CREATETABLEJ(JNOVARCHAR(5),JNAMEVARCHAR(20),CITYVARCHAR(20),PRIMARYKEY(JNO));CREATETABLESPJ(SNOVARCHAR(5)NOTNULL,PNOVARCHAR(5),JNOVARCHAR(5),QTYVARCHAR(10),PRIMARYKEY(SNO,PNO,JNO),FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(PNO)REFERENCESP(PNO),FOREIGNKEY(JNO)REFERENCESJ(JNO));INSERTINTOSVALUES('S1','精益','20','天津');INSERTINTOSVALUES('S2','盛锡','10','北京');INSERTINTOSVALUES('S3','东方红','30','北京');INSERTINTOSVALUES('S4','丰泰盛','20','天津');INSERTINTOSVALUES('S5','为民','30','上海');INSERTINTOPVALUES('P1','螺母','红','12');INSERTINTOPVALUES('P2','螺栓','绿','17');INSERTINTOPVALUES('P3','螺丝刀','蓝','14');INSERTINTOPVALUES('P4','螺丝刀','红','14');INSERTINTOPVALUES('P5','凸轮','蓝','40');INSERTINTOPVALUES('P6','齿轮','红','30');INSERTINTOJVALUES('J1','三建','北京');INSERTINTOJVALUES('J2','一汽','长春');INSERTINTOJVALUES('J3','弹簧厂','天津');INSERTINTOJVALUES('J4','造船厂','天津');INSERTINTOJVALUES('J5','机车厂','唐山');INSERTINTOJVALUES('J6','无线电厂','常州');INSERTINTOJVALUES('J7','半导体厂','南京');INSERTINTOSPJVALUES('S1','P1','J1','200');INSERTINTOSPJVALUES('S1','P1','J3','100');INSERTINTOSPJVALUES('S1','P1','J4','700');INSERTINTOSPJVALUES('S1','P2','J2','100');INSERTINTOSPJVALUES('S2','P3','J1','400');INSERTINTOSPJVALUES('S2','P3','J2','200');INSERTINTOSPJVALUES('S2','P3','J4','500');INSERTINTOSPJVALUES('S2','P3','J5','400');INSERTINTOSPJVALUES('S2','P5','J1','400');INSERTINTOSPJVALUES('S2','P5','J2','100');INSERTINTOSPJVALUES('S3','P1','J1','200');INSERTINTOSPJVALUES('S3','P3','J1','200');INSERTINTOSPJVALUES('S4','P5','J1','100');INSERTINTOSPJVALUES('S4','P6','J3','300');INSERTINTOSPJVALUES('S4','P6','J4','200');INSERTINTOSPJVALUES('S5','P2','J4','100');INSERTINTOSPJVALUES('S5','P3','J1','200');INSERTINTOSPJVALUES('S5','P6','J2','200');INSERTINTOSPJVALUES('S5','P6','J4','500');SELECTSNAME,CITYFROMS;SELECTPNAME,COLOR,WEIGHTFROMP;SELECTJNOFROMSPJWHERESNO='S1';SELECTDISTINCTPNAME,QTYFROMP,SPJWHEREJNO='J2';SELECTDISTINCTPNOFROMSPJ,SWHERECITY='上海'ANDSPJ.SNO=S.SNOSELECTJNAMEFROMJWHEREJNOIN(SELECTDISTINCTJNOFROMSPJWHERESNO=(SELECTSNOFROMSWHERECITY='上海'));SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHERESPJ.JNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERECITY='天津'));UPDATEPSETCOLOR='蓝'WHERECOLOR='红';UPDATESPJSETSNO='S3'WHEREJNO='J4'ANDPNO='P6'ANDSNO='S5';DELETEFROMSPJWHERESNO='S2';DELETEFROMSWHERESNO='S2';INSERTINTOSPJVALUES('S2','J6','P4','200');CREATEVIEWSJASSELECTSNO,PNO,QTYFROMSPJWHEREJNO=(SELECTJNOFROMJWHEREJNAME='三建');SELECTPNO,QTYFROMSJ;SELECTPNO,QTYFROMSJWHERESNO='S1';