SQL

SQL 数据库实验二

创建SQL脚本
CREATE TABLE S
(
SNO VARCHAR2(12),
SNAME VARCHAR2(20),
STATUS VARCHAR2(10) ,
CITY VARCHAR2(10),
CONSTRAINT PK_S PRIMARY KEY (SNO)
);
CREATE TABLE P
(
PNO VARCHAR2(10),
PNAME VARCHAR2(20) NOT NULL ,
COLOR VARCHAR2(10),
WEIGHT NUMBER(5,1),
CONSTRAINT PK_P PRIMARY KEY(PNO)
);

CREATE TABLE J
(
JNO VARCHAR2(12),
JNAME VARCHAR2(10),
CITY VARCHAR2(10),
CONSTRAINT FK_J PRIMARY KEY(JNO)
);

CREATE TABLE SPJ
(
SNO VARCHAR2(10),
PNO VARCHAR2(12),
JNO VARCHAR2(10),
QTY NUMBER(10,1),
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY (SNO) REFERENCES S(SNO),
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’,‘为民’,‘30’,‘上海’);

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’,‘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’);