数据库原理及应用教程第3章答案

第三章部分习题参考答案

查询方法可能不止一种，下面给出参考答案： 三1.

（1）. SELECT BAuth

FROM Book,Publish

WHERE Book.PNo= Publish.PNo AND BName=’操作系统’

AND Pname=’高等教育出版社’；

（2）. SELECT PTel

FROM Publish

WHERE PNo=ALL( SELECT DISTINCT PNo

FROM Book

WHERE BAuth= ‘张欣’AND BType=’小说’ )；

（3）. SELECT BPrice,PName,BType

FROM Book,Publish

WHERE Book.PNo= Publish.PNo AND PName=’电子工业出版社’

AND BType=’计算机’；

（4）. SELECT *

FROM Book

WHERE BPrice

FROM Book,Publish

WHERE Book.PNo= Publish.PNo AND BName=’高等数学’

AND PName=’人民邮电出版社’) AND BName=’高等数学’

（5）. SELECT BName, BAuth

FROM Book

WHERE BName Like ’%计算机%’；

（6）. ALTER TABLE Book

ADD BDate DateTime

（7）. CREATE INDEX

ON Book(BAuth)

四 5

（1）. CREATE TABLE S

(

S# CHAR(2) CONSTRAINT PRIMARY KEY, SN VARCHAR(8) NOT NULL, AGE TINYINT ,

DEPT VARCHAR(20) );

（2）. CREATE VIEW ComputerStu(S#,SN,C#,T#)

1

AS SELECT S.S#, SN, SC.C#,T# FROM S,SC, T

WHERE S.S#=SC.S# AND SC.C#=T.C# AND DEPT=’计算机’

AGE>20

（3）. SELECT S#

FROM S

WHERE DEPT=’计算机’ AND AGE>20；

（4）. SELECT C#,CN

FROM C =ANY

WHERE C# IN ( SELECT C#

FROM T

WHERE TN Like’王%’)

SELECT C.C#,CN FROM T,C

WHERE T. C# =C.C# AND TN LIKE’王%’

（5）. SELECT SN,C#,GR

FROM S,SC

WHERE SN=’张三’ AND S.S#=SC.S#；

（6）. SELECT SN,SC.C#,GR

FROM S,SC,T

WHERE S.S#=SC.S# AND SC.C#=T.C# AND (SAL+COMM)>1000；

（7）. INSERT INTO SC(S#,C#)

VALUES(‘S1’,’C3’);

INSERT INTO SC VALUES(‘S1’,’C3’,NULL);

（8）. DELETE FROM S

WHERE S# NOT IN ( SELECT DISTINCT S#

FROM SC );

<>ALL

2