06数据库原理练习题答案

第三章 5．用关系代数完成操作： （1） sno (  jno=’J1’(spj)) (2)  sno (  jno=’J1’∧ pno=’p1’(spj)) (3)  sno (  jno=’J1’(spj)) color=’红’(p) (4) jno(j)- jno(city=’天津’(s) ( spj) color=’红’(p)) (5)  jno,pno (spj) ÷ pno( sno=’s1’(spj))

第四章 3. 用SQL语句建立第二章习题5中的四个表

解：建立表格S Create Table S( SNO Varchar(2) Constraint PK_S Primary Key, SNAME Varchar(10) Not NULL, STATUS Number(6), CITY Varchar(10) );

Create Table P( PNO Varchar(2) Constraint PK_S Primary Key, PNAME Varchar(10) Not NULL, WEIGHT Number(6), COLOR Varchar(10) );

Create Table J( JNO Varchar(2) Constraint PK_J Primary Key, JNAME Varchar(10) Not NULL, CITY Varchar(10) ); 建立表格SPJ Create Table SPJ( SNO Varchar(2) Constraint FKS_SPJ References S(SNO) ON Delete Set NULL, PNO Varchar(2) Constraint FKP_SPJ References P(PNO) ON Delete Set NULL, JNO Varchar(2) Constraint FKJ_SPJ References J(JNO) ON Delete Set NULL, QTY Number (6) 用SQL语句建立第二章习题5中的四个表 解：建立表格S Create Table S( SNO Varchar(2) Constraint PK_S Primary Key, SNAME Varchar(10) Not NULL, STATUS Number(6), CITY Varchar(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,'上海');

Create Table P( PNO Varchar(2) Constraint PK_S Primary Key, PNAME Varchar(10) Not NULL, WEIGHT Number(6), COLOR Varchar(10) ); 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 Varchar(2) Constraint PK_J Primary Key, JNAME Varchar(10) Not NULL, CITY Varchar(10) ); 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','半导体厂','南京');

建立表格SPJ Create Table SPJ( SNO Varchar(2) Constraint FKS_SPJ References S(SNO) ON Delete Set NULL, PNO Varchar(2) Constraint FKP_SPJ References P(PNO) ON Delete Set NULL, JNO Varchar(2) Constraint FKJ_SPJ References J(JNO) ON Delete Set NULL, QTY Number (6) ); 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',200); 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','P5', 'J4',500);

4）求没有使用天津供应商生产的红色零件的工程号JNO 解一：(SELECT JNO FROM J) MINUS (Select Distinct SPJ.JNO from SPJ, P , S WHERE SPJ.SNO=S.SNO and SPJ.PNO=P.PNO AND S.CITY=‘天津’ and P.COLOR=‘红’）； 解二： SELECT DISTINCT JNO AS JN FROM SPJ WHERE JNO NOT IN (SELECT JNO FROM SPJ, S , P WHERE SPJ.SNO=S.SNO AND S.CITY=‘天津’ AND P.PNO=SPJ.PNO AND P.COLOR=‘红’）； 5）求至少使用了供应商S1所供应的全部零 件的工程号JNO SELECT DISTINCT JNO FROM SPJ SPJX WHERE NOT EXISTS （SELECT * FROM SPJ SPJY WHERE SPJY.SNO=‘S1’ AND NOT EXISTS (SELECT * FROM SPJ SPJZ WHERE SPJZ.JNO=SPJX.JNO AND SPJZ.PNO=SPJY.PNO);

5.针对习题3中的四个表试用SQL语言完成以下各项操作： 4）找出工程项目J2使用的各种零件的名称及其数量 SELECT PNAME, SUM(QTY) AS SUM FROM (SELECT PNAME,QTY FROM SPJ WHERE JNO=‘J2’) GROUP BY PNAME 7)找出没有使用天津产的零件的工程号码 解一：(SELECT JNO FROM J) MINUS (SELECT DISTINCT JNO FROM S,SPJ WHERE S.CITY=‘天津’ AND S.SNO=SPJ.SNO; )

4．完成第二章的习题5的查询 （1）select distinct sno from spj where jno=’J1’； (2) select distinct sno from spj where jno=’J1’ and pno=’ P1’； (3) select sno from spj,p where spj.pno=p.pno and color=’红’ and jno=’J1’； (4)select jno from j where jno not In (select jno