西电计科大三上数据库系统概论代码合集

/*-- 删除已有的表*/ /*-- 注意要先删除参照表（如SC），再删除被参照表（如Student，Course）*/ DROP TABLE SPJ; DROP TABLE S; DROP TABLE P; DROP TABLE J; /*创建基本表*/ /*注意要先创建被参照表，再创建参照表*/ CREATE TABLE S( SNO CHAR(10) PRIMARY KEY, /*-- 列级完整性约束的方式，定义主码*/ SNAME CHAR(20), STATUS INT, CITY CHAR(10) ); CREATE TABLE P( PNO CHAR(10) PRIMARY KEY, PNAME CHAR(20), COLOR CHAR(10), WEIGHT CHAR(10) ); CREATE TABLE J( JNO CHAR(10) PRIMARY KEY, JNAME CHAR(20), CITY CHAR(10) ); CREATE TABLE SPJ( SNO CHAR(10), PNO CHAR(10), JNO CHAR(10), QTY INT, PRIMARY KEY (SNO,PNO,JNO), FOREIGN KEY (SNO) REFERENCES S(SNO), /*-- 表级完整性约束的方式，定义外码；注意参照列Cpno必须加括号*/ FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO) ); /*-- 向基本表中插入数据*/ 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','为民',20,'上海'); /*-- 如果根据参照关系表中一行参照了另外一行，应先插入被参照行（如('6',...,...,...)），后插入参照行（如(...,...,'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','半导体厂','南京'); 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); /*求供应工程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 PNO IN( SELECT DISTINCT PNO FROM P WHERE COLOR='红' ); /*求没有使用天津供应商生产的红色零件的工程号JNO*/ SELECT DISTINCT JNO FROM SPJ WHERE SNO NOT IN ( SELECT SNO FROM S WHERE CITY='天津' ) OR PNO NOT IN( SELECT DISTINCT PNO FROM P WHERE COLOR='红' ); /*求至少使用了供应商S1所供应的全部零件的工程号JNO*/ /*方法一*/ SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1' GROUP BY JNO HAVING COUNT(PNO)=(SELECT COUNT(DISTINCT PNO) FROM SPJ WHERE SNO='S1'); /*方法二*/ SELECT DISTINCT JNO FROM SPJ X WHERE NOT EXISTS( SELECT DISTINCT PNO FROM SPJ Y WHERE SNO='S1' AND NOT EXISTS( SELECT * FROM SPJ Z WHERE Z.SNO='S1' AND Y.PNO=Z.PNO AND X.JNO=Z.JNO ) ); /*找出所有供应商的姓名和所在城市*/ SELECT SNAME,CITY FROM S; /*找出所有零件的名称、颜色、重量*/ SELECT PNAME,COLOR,WEIGHT FROM P; /*找出使用供应商S1所供应零件的工程号码*/ SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1'; /*找出工程项目J2使用的各种零件的名称及其数量*/ SELECT PNAME,QTY FROM SPJ,P WHERE SPJ.PNO=P.PNO AND JNO='J2'; /*找出上海厂商供应的所有零件号码*/ SELECT DISTINCT PNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND CITY='上海'; /*找出使用上海产的零件的工程名称*/ SELECT DISTINCT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND CITY='上海'; /*找出没有使用天津产的零件的工程号码*/ SELECT DISTINCT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND CITY!='天津'; /*把全部红色零件的颜色改成蓝色*/ UPDATE P SET COLOR='蓝' WHERE COLOR='红'; /*由S5供给J4的零件P6改为由S3供应，请作出必要的修改*/ UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND PNO='P6' AND JNO='J4'; /*从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录*/ ALTER TABLE SPJ ADD FOREIGN KEY (SNO) REFERENCES S(SNO) ON DELETE CASCADE;/*添加外键，设置级联删除*/ DELETE FROM S WHERE SNO='S2'; /*请将(S2,J6,P4,200)插入供应情况关系*/ INSERT INTO SPJ VALUES ('S1','P1','J1',200); /*为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）*/ CREATE VIEW PROD(SNO,PNO,QTY) AS SELECT SNO,PNO,QTY FROM SPJ WHERE JNO='J1' /*找出三建工程项目使用的各种零件代码及其数量*/ SELECT PNO,SUM(QTY) FROM PROD GROUP BY PNO; /*找出供应商S1的供应情况*/ SELECT PNO,QTY FROM PROD WHERE SNO='S1';