SNAME VARCHAR2(8),
STATUS VARCHAR2(8),
CITY VARCHAR2(8)
);
CREATE TABLE P
(
PNO VARCHAR2(8) PRIMARY KEY,
PNAEM VARCHAR2(8),
COLOR VARCHAR2(8),
WEIGHT NUMBER(10)
);
CREATE TABLE J
(
JNO VARCHAR2(8) PRIMARY KEY,
JNAME VARCHAR2(8),
CITY VARCHAR2(8)
);
CREATE TABLE SPJ
(
SNO VARCHAR2(8),
PNO VARCHAR2(8),
JNO VARCHAR2(8),
QTY NUMBER(10),
CONSTRAINT SPJ_SNO FOREIGN KEY(SNO)
REFERENCES S(SNO),
CONSTRAINT SPJ_PNO FOREIGN KEY(PNO)
REFERENCES P(PNO),
CONSTRAINT SPJ_JNO FOREIGN KEY(JNO)
REFERENCES J(JNO)
);
//索引自己建立 在上面建立外键约束的时候自己建立了
CREATE INDEX S_SNO_INDEX
ON S(SNO)
CREATE INDEX P_PNO_INDEX
ON P(PNO)
CREATE INDEX J_JNO_INDEX
ON J(JNO)
CREATE INDEX SPJ_SNO_PNO_JNO_INDEX
ON SPJ(SNO,PNO,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,'上海');
COMMIT;
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);
COMMIT;
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','仪器厂','南京');
COMMIT;
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);
COMMIT;
第二个数据库
CREATE TABLE PRODUCT
(
MAKER VARCHAR2(5) ,
MODEL VARCHAR2(12) PRIMARY KEY,
TYPE VARCHAR2(12)
);
CREATE TABLE PC
(
MODEL VARCHAR2(12) PRIMARY KEY,
SPEED NUMBER(10),
RAM NUMBER(10),
HD NUMBER(8,2),
CD VARCHAR2(4),
PRICE NUMBER(10),
CONSTRAINT PC_PRODUCT_MODEL FOREIGN KEY(MODEL)
REFERENCES PRODUCT(MODEL)
);
CREATE TABLE LAPTOP
(
MODEL VARCHAR2(12) PRIMARY KEY,
SPEED NUMBER(10),
RAM NUMBER(10),
HD NUMBER(8,2),
SCREEN NUMBER(8,2),
PRICE NUMBER(10),
CONSTRAINT LAPTOP_PRODUCT_MODEL FOREIGN KEY(MODEL)
REFERENCES PRODUCT(MODEL)
);
CREATE TABLE PEINTER
(
MODEL VARCHAR2(12) PRIMARY KEY,
COLOR VARCHAR2(10),
TYPE VARCHAR2(10),
PRICE NUMBER(10),
CONSTRAINT PEINTER_PRODUCT_MODEL FOREIGN KEY(MODEL)
REFERENCES PRODUCT(MODEL)
);
INSERT INTO PRODUCT VALUES('A','1001','PC');
INSERT INTO PRO