2023年6月27日发(作者：)

【数据库作业10】⽤SQL语句来表⽰关系代数中的表达式1、有两个关系S（A,B,C,D)和T（C,D,E,F），写出与下列查询等价的SQL表达式：（1）σA=10(S)//选择select *from Swhere A=10;（2）ΠA,B(S)//投影select A,Bfrom S;（3）S⋈T//⾃然连接select S.*,T.*from S,T;（4）SS.C=//等值连接select S.*,T.*from S,Twhere S.C=T.C;⋈（5）SA

select *from T;//⽅法⼆：select S.C,S.D,T.*from S,T2、⽤SQL语句建⽴以下的4个表；针对建⽴的4个表⽤SQL完成第⼆章习题6中的查询。S表：create table S(sno char(10), sname char(10), status smallint, city char(10) );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,'上海');P表：create table P(pno char(10), pname char(10), color char(10), weight smallint );insert into Pvalues('P1','螺母','红',12);insert into Pvalues('P2','螺栓','绿',17);insert into P

values('P3','螺丝⼑','蓝',14);insert into P

values('P4','螺丝⼑','红',14);insert into P

values('P5','凸轮','蓝',40);insert into Pvalues('P6','齿轮','红',30);J表：create table J(jno char(10), jname char(10), city char(10) );insert into Jvalues('J1','三建','北京');insert into Jvalues('J2','⼀汽','长春');insert into J

values('J3','弹簧⼚','天津');insert into J

values('J4','造船⼚','天津');insert into J

values('J5','机车⼚','唐⼭');insert into Jvalues('J6','⽆线电⼚','常州');insert into Jvalues('J7','半导体⼚','南京');SPJ:CREATE TABLE SPJ(SNO CHAR(4), PNO CHAR(4), JNO CHAR(4), QTY SMALLINT );INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J1',200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J3',100);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J4',700);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P2','J2',100);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J1',400);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J2',200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J4',500);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J5',400);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J1',400);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J2',100);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P1','J1',200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P3','J1',200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P5','J1',100);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J3',300);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J4',200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P2','J4',100);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P3','J1',200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J2',200);INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J4',500);最终结果：（1）求供应⼯程J1零件的供应商号码SNO；select snofrom spjwhere jno='j1'（2）求供应⼯程J1零件P1的供应商号码SNO；select snofrom spjwhere jno='j1' and pno='p1';（3）求供应⼯程J1零件为红⾊的供应商号码SNO；select snofrom spj,pwhere jno='j1' and = and color='红';（4）求没有使⽤天津供应商⽣产的红⾊零件的⼯程号JNO；select jnofrom jwhere not exists(select *

from spj,s,pwhere ='天津' and ='红'

and = and =);（5）求⾄少⽤了供应商S1所供应的全部零件的⼯程号JNO。select distinct jnofrom spj xwhere not exists( select * from spj y where ='s1' and not exists( select * from spj z where =

and = ));3、针对习题4中的4个表使⽤SQL完成以下各项操作：（1）找出所有供应商的姓名和所在城市；select jname,cityfrom j;（2）找出所有零件的名称、颜⾊、重量；select pname,color,weightfrom p;（3）找出使⽤供应商S1所供应零件的⼯程号码；select jnofrom spjwhere sno='s1';（4）找出⼯程项⽬J2使⽤的各种零件的名称及其数量；select pname,qtyfrom spj,pwhere jno='j2' and =;（5）找出上海⼚商供应的所有零件号码；select distinct pnofrom spj,swhere city='上海' and =;（6）找出使⽤上海产的零件的⼯程项⽬；select distinct jnamefrom spj,s,jwhere =

and ='上海'and =;（7）找出没有使⽤天津产的零件的⼯程号码；select jnofrom jwhere not exists( select * from spj,s where = and = and ='天津');（8）把全部红⾊零件的颜⾊改成蓝⾊；update pset color='蓝'where color='红';（9）由S5供给J4的零件P6改为由S3供应，请作必要的修改；update spjset sno='s3'where sno='s5'and jno='j4'and pno='p6';（10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录；delete

from spjwhere sno='s2'delete

from swhere sno='s2';（11）请将（S2，J6，P4，200）插⼊供应情况关系；insert into spjvalues('s2','p4','j6',200);4、请为三建⼯程项⽬建⽴⼀个供应情况的视图，包括供应商代码（SNO)、零件代码（PNO)、供应商数量（QTY）。针对该视图完成下列查询：create view view_sasselect sno,pno,qtyfrom spjwhere jno=(select jno from j where jname='三建');（1）找出三建⼯程项⽬使⽤的各种零件代码及其数量；select pno,qtyfrom view_s（2）找出供应商S1的供应情况；select *from view_swhere sno='s1'