rgx06.txt

此程序为一个简单的oracle数据库程序

Create table SUPP2006011534
	(SO char(2) primary key,
SNAME varchar(10),
CITY varchar(10));

Create table PART2006011534
	(PO char(2) primary key,
PNAME varchar(10),
COLOR varchar(10),
WEIGHT int);

Create table PROJ2006011534
	(JO char(3) primary key,
JNAME varchar(10),
CITY varchar(10));

Create table SPJ2006011534
	(SO char(2) references SUPP2006011534(SO),
PO char(2) references PART2006011534(PO),
JO char(3) references PROJ2006011534(JO),
QTY varchar(10),
Primary key(SO,PO,JO));

Insert  into  Supp2006011534
Values('S1','rgx','北京');
Insert  into  Supp2006011534
Values('S2','zeayan','北京');
Insert  into  Supp2006011534
Values('S3','ct','上海');
Insert  into  Supp2006011534
Values('S4','jesecg','上海');






Insert into Part2006011534
Values('P1','PN1','white','125');
Insert into Part2006011534
Values('P2','PN2','red','258');
Insert into Part2006011534
Values('P3','PN3','blue','232');
Insert into Part2006011534
Values('P4','PN4','black','221');
Insert into Part2006011534
Values('P5','PN5','blue','199');
Insert into Part2006011534
Values('P6','PN6','red','223');
Insert into Part2006011534
Values('P7','PN7','red','213');
Insert into Part2006011534
Values('P8','PN8','red','123');
Insert into Part2006011534
Values('P9','PN9','red','230');
Insert into Part2006011534
Values('10','PN10','red','223');







Insert into PROJ2006011534
Values('J1','Job1','北京');
Insert into PROJ2006011534
Values('J2','Job2','上海');
Insert into PROJ2006011534
Values('J3','Job3','北京');
Insert into PROJ2006011534
Values('J4','Job4','广州');
Insert into PROJ2006011534
Values('J5','Job5','北京');
Insert into PROJ2006011534
Values('J6','Job6','上海');
Insert into PROJ2006011534
Values('J7','Job7','上海');
Insert into PROJ2006011534
Values('J8','Job8','北京');





Insert into SPJ2006011534
Values('S2','P8','J1','9');
Insert into SPJ2006011534
Values('S3','P2','J1','7');
Insert into SPJ2006011534
Values('S2','P6','J2','8');
Insert into SPJ2006011534
Values('S3','P7','J2','3');
Insert into SPJ2006011534
Values('S1','P1','J3','3');
Insert into SPJ2006011534
Values('S2','P3','J4','8');
Insert into SPJ2006011534
Values('S3','P4','J3','4');
Insert into SPJ2006011534
Values('S4','P5','J6','6');
Insert into SPJ2006011534
Values('S1','P9','J7','8');
Insert into SPJ2006011534
Values('S3','P4','J8','5');
Insert into SPJ2006011534
Values('S1','10','J5','5');
Insert into SPJ2006011534
Values('S1','P1','J1','5');
Insert into SPJ2006011534
Values('S4','P1','J1','5');



（1）select * from PROJ2006011534;

（2）select * from PROJ2006011534 where CITY='上海';

（3）select SO from SPJ2006011534 where JO='J1';

（4）select SO from SPJ2006011534 where JO='J1' and PO='P1';

（5）select SO from SPJ2006011534,Part2006011534 where PO=(select PO from Part2006011534 where PNAME='PN3');
select SO from SPJ2006011534,Part2006011534 where Part2006011534.PNAME='PN3' and SPJ2006011534.PO=Part2006011534.PO;
select SO from SPJ2006011534 where Part2006011534.PNAME='PN3' and SPJ2006011534.PO=Part2006011534.PO;

（6）select PO from PART2006011534 where PO=(select PO from SPJ2006011534 where SO='S3');
select * from PART2006011534 where PO=(select PO from SPJ2006011534 where SO='S0');
select PNAME from PART2006011534 where PO in (select PO from SPJ2006011534 where SO='S3');

（7）select SO from SPJ2006011534 where JO='J1' or JO='J2'; 

（8）Select SO from Supp2006011534 where City='北京' and SO not in (select SO from SPJ2006011534 where PO in (select PO from Part2006011534 where Color='red'));

（9）select SO from SPJ2006011534 where PO in (select PO from PROJ2006011534 where CITY='上海');

（10）select SO from SUPP2006011534 where CITY='Beijing' and SO not in (select SO from SPJ2006011534 where PO in (select PO from PART2006011534 where COLOR='red'));

（11）select JO from SPJ where SO=’S1’;