本文共 4033 字，大约阅读时间需要 13 分钟。

（1）

select Afrom Swhere A=10;

（2）

select A,Bfrom S;

（3）

select *from S,Twhere S.C=T.C and S.D=T.D;

（4）

select *from S,Twhere S.C=T.C;

（5）

select *from S,Twhere S.A

（6）

select S.C,S.D,*from S,T;

建立习题六的四个表：

-- 建立S表：create table S(Sno char(2) unique,Sname char(6),Status char(2),City char(4));-- 建立P表create table P(Pno char(2) unique,Pname char(6),COLOR char(2),WEIGHT int);-- 建立J表：create table J(Jno char(2) unique,JNAME char(8),CITY char(4));-- 建立SPJ表：create table SPJ(Sno char(2),Pno char(2),Jno char(2),QTY int);-- 填入数据：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','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',300)insert into SPJ values('S5','P6','J4',200)

建立五个查询：

（1）求供应工程J1零件的供应商的号码SNO；

select distinct SNO  from SPJ where JNO='J1';

（2）求供应工程J1零件P1的供应商号码SNO；

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

（3）求供应工程J1零件为红色的供应商号码SNO；

select SNOfrom SPJ,Pwhere SPJ.PNO=P.PNO and COLOR='红' and JNO='J1';

（4）求没有使用天津供应商生产的红色零件的工程号SNO；

select distinct JNOfrom SPJwhere JNO not in(select JNO				 from SPJ,S,P				 where S.CITY='天津' 				 and COLOR = '红' 				 and S.SNO = SPJ.SNO 				 and P.PNO = SPJ.PNO);

（5）求至少用了供应商S1所供应的全部零件的工程号JNO；

select distinct JNOfrom SPJ tab1where not exists	(select *	from SPJ tab2	where SNO='S1' and not exists 		(select *		from SPJ tab3		where tab3.Jno=tab1.Jno		and tab3.Pno=tab2.Pno)	);

（1）找出所有供应商的姓名和所在地：

select SNAME,CITY from S;

（2）找出所有零件的名称、颜色、重量：

select PNAME,COLOR,WEIGHT from P;

（3）找出使用供应商S1所供应零件的工程号码：

select JNO from SPJ where SNO = 'S1';

（4）找出工程项目J2使用的各种零件的名称及其数量：

select Pname,QTYfrom SPJ,Pwhere P.PNO = SPJ.PNO and SPJ.JNO = 'J2';

（5）找出上海厂商供应的所有零件号码：

select PNO from SPJ,S where S.SNO = SPJ.SNO and CITY = '上海';

（6）找出使用上海产的零件的工程名称：

select JNAME from SPJ,S,Jwhere S.SNO = SPJ.SNO and S.CITY = '上海' and J.JNO = SPJ.JNO;

（7）找出没有使用天津产的零件的工程号码：

select JNOfrom SPJ tab1where not exists (select * 				  from S,SPJ tab2				  where S.SNO = tab2.SNO and CITY = '天津' and tab2.JNO=tab1.Jno);

这个写麻烦了。下面的更简单。

select JNO from SPJ  where JNO not in (					select distinct JNO                   	from SPJ,S                   	where S.SNO = SPJ.SNO and S.CITY = '天津'                  ) ;

（8）把全部红色零件颜色改为蓝色：

update P set COLOR = '蓝'  where COLOR = '红';

（9）由S5供给J4的零件P6改为由S3供应，作必要的修改：

update SPJ  set SNO = 'S3' where SNO = 'S5' and JNO = 'J4' and PNO = 'P6';

（10）从供应商关系中删除S2的记录，并从供应情况中删除相应的记录：

delete from S where SNO = 'S2';delete from SPJ where SNO = 'S2';

（11）请将(S2 , J6 , P4, 200) 插入供应关系情况：

insert into SPJ  values('S2' , 'J6' , 'P4' , 200);

建立视图：

create view J1_view ASselect SNO,PNO,QTYfrom SPJwhere JNO='J1';

（1）找出三建工程项目使用的各种零件代码及其数量：

select PNO,sum(QTY)from J1_viewgroup by PNO

（2）找出供应商S1的供应情况：

select PNO,QTYfrom J1_viewwhere SNO='S1';

转载地址：http://dknb.baihongyu.com/