数据库系统原理第二次作业答案

《数据库系统原理》第二次作业答案

（第十二周课程讲完之后交，用学院统一的作业纸书写，学院文印室有卖，要求抄题。）

一、选择题

1、C

2、D

3、D

4、B

5、A

6、C

二、填空题

1、0个或多个任意字符、一个任意字符

2、order by

3、AB 、1NF

4、MN、NP、NQ

三、应用题

1、create table S(S# char(10) primary key , SN varchar(10)

not null ,

AGE tinyint , DEPT varchar(20))

2、create view vcs as select S.S#, S.SN, SC.C#, T.T# from S,SC,T where

S.S#= SC.S# and SC.C#=T.C# and S.dept='计算机'

3、select S# from S where dept='计算机' and age>20

4、select T.C#, from C,T where C.C#= T.C# and T.TN like '王%'

5、select S.SN,SC.C#,SC.GR from S,SC where S.S#=SC.S# and S.SN='张三

'

6、select S.SN,SC.C#,SC.GR from S,SC,T where S.S#=SC.S# and C.C#= T.C#

and T.SAL>1000

7、SELECT SN,AVG(gr) FROM S,SC

WHERE (S.S#=SC.S#) AND ('C1' not in (select c# from sc WHERE S.S#=SC.S#))

GROUP BY Sn HAVING COUNT(sc.S#)=2

ORDER BY AVG(gr) DESC

或：

select sn,avggr from s inner join

(SELECT S#,AVG(gr) avggr FROM SC sc1

WHERE not exists (select * from sc sc2 where sc1.s#=sc2.s# and sc2.C#='C1')

GROUP BY S# HAVING COUNT(sc1.S#)=2) sc3 on s.S#=sc3.S#

ORDER BY avggr DESC

8、select sn,cn from s,sc,c where s.s#=sc.s# and sc.c#=c.c# and

s.sn<>'张三' and c.c# in (select c# from sc where s#=(select s# from s where sn='张三'))

9、insert into SC values('S1', 'C3',null)

10、delete from S where S# not in (select S# from SC)

11、select count(*) from T where SAL>1000;

select min(AGE) from S where DEPT='计算机'

12、update SC SET GR=60 where S#='S2'