数据库第三章作业

第3题

select * from S where A=10;
select A,B from S;

select A,B,S.C,S.D,T.C,T.D,E,F from S,T
WHERE S.c=T.c and s.d=t.d;

select * from S,T
WHERE S.c=T.c;

select * from S,T
where A<E;

select S.C,S.D,T.C,T.D,T.E,T.F from S,T; 

第4题

create table s(sno char(5),sname char(5),status int,city char(5));
insert into  s values ('s1','精益',20,'天津');
INSERT INTO S VALUES ('S2','盛锡',10,'北京');
INSERT INTO S VALUES ('S3','东方红',20,'北京');
INSERT INTO S VALUES ('S4','丰泰盛',20,'天津');
INSERT INTO S VALUES ('S5','为民',20,'上海');

 CREATE TABLE P( PNO CHAR(5),PNAME CHAR(5), COLOR CHAR(5), WEIGHT INT);
 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);

CREATE TABLE J( JNO CHAR(5),JNAME CHAR(10),CITY CHAR(5)); 
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','半导体厂','南京');



CREATE TABLE SPJ( SNO CHAR(5), PNO CHAR(5), JNO CHAR(5),QTY SMALLINT); 
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); 



select sno from spj where jno='j1';

select SNO  from SPJ where JNO = 'J1' AND PNO='P1';

select sno from spj,p
where p.pno = spj.pno and jno='p1' and color ='红';

select jno from spj where jno not in (
	select jno from spj,p,s where s.city='天津' and color ='红' and s.sno=spj.sno)



select JNO
from SPJ as x
where not exists
	(select *
	from SPJ as y
	where SNO='S1' and not exists
		(select *
		from SPJ as z
		where z.PNO=y.PNO and z.JNO=x.JNO
		)
	);

第五题

select SNAME,CITY  from S;
select pname,color,weight from p;
select distinct jno from spj where sno='s1';
select pname,qty from spj,p where spj.sno= p.pno and spj.jno='j2';
select pno from spj,s where  s.snp=spj.sno and s.city='上海';
select jno from spj,s where  s.snp=spj.sno and s.city='上海';

select jno from j where not exists 	
		(select * from s,spj
		 where spj.jnp=j.jno and spj.sno = s.sno
		  and s.city ='天津' 
		  );
		 
update p set color='蓝色' where color='红色';

update spj
 set sno='s3'
 where sno='s5' and jno ='j4' and pno ='p6';

delete from s where sno='s2';
delete from SPJ where sno='S2';

insert into spj values ('s2','p4','j6',200);

第九题

create view view1 as 
	select sno,pno,qty
from spj,j where   spj.sno=j.sno and j.jname='三建';
 
select pno,qty from view1;
 
select pno,qty from view1  
	where sno='s1';