SPJ数据库分析和总结.docxVIP

设有一个 SPJ 数据库，包括S，P，J，SPJ 四个关系模式： 供应商 S(SNO , SNAME , STATUS , CITY) 零件 P(PNO , PNAME , COLOR , WEIGHT) 工程 J(JNO , JNAME , CITY) SPJ(SNO , PNO , JNO , QTY) 创建数据库： create database SPJ; 创建表： CREATE TABLE S ( SNO char(2) primary key, SNAME varchar(20), STATUS char(4), CITY char(10)); create table P ( PNO char(2) primary key, PNAME varchar(20), COLOR char(4), WEIGHT int); create table J ( JNO char(2) primary key, JNAME varchar(20), CITY char(10)); create table SPJ ( SNO char(2), PNO char(2), JNO char(2), QTY int, primary foreign key(SNO,PNO,JNO), key(SNO) references S(SNO) on delete cascade, foreign key(PNO) references P(PNO) on delete cascade, foreign ); key(JNO) references J(JNO) on delete cascade 插入数据： insert into S values(S1,供应商一,良好,郑州); insert into S values(S2,供应商二,良好,天津); insert into S values(S3,供应商三,良好,上海); insert into S values(S4,供应商四,优秀,郑州); insert into P values(P1,管材,红色,20); insert into P values(P2,管线,绿色,30); insert into P values(P3,螺帽,红色,10); insert into P values(P4,螺帽,黄色,10); 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 SPJ values(S1,P1,J1,100); insert into SPJ values(S1,P2,J1,100); insert into SPJ values(S2,P1,J1,100); insert into SPJ values(S2,P2,J1,100); insert into SPJ values(S1,P1,J3,100); insert into SPJ values(S1,P2,J3,100); insert into SPJ values(S2,P1,J3,100); insert into SPJ values(S2,P2,J3,100); insert into SPJ values(S3,P1,J2,100); insert into SPJ values(S3,P2,J2,100); insert into SPJ values(S4,P3,J2,100); 求供应工程 J1 零件的供应商号码 SNO： select distinct SNO from SPJ where JNO=J1; 求供应工程 J1 零件 P1 的供应商号码 SNO： select SNO from SPJ where JNO=J1 and PNO=P1; 求供应工程 J1 红色零件的供应商号码 SNO： select SNO from SPJ,P where SPJ.PNO=P.PNO and JNO=J1 and P.COLOR=红色; 或者 SELECT SNO /*这是嵌套查询*/ FROM SPJ WHERE JNO=J1 AND PNO IN /*找出红色零件的零件号码PNO */ (SELECT PNO FROM P /*从P表中找*/ WHERE COLOR =红色); 求没有使用天津供应商生产的红色零件的工程号 J