数据库第三章课后习题答案

第三章课后习题

3-7

(1) delete from s

Where placeofb=’上海’;

(2)delete from sc

Where s# in (select s#

from s

where sname=’李建平’)；

(3)delete form s

Where s# in(select s#

from sc

where grade is null)；

3-8

(1)update sc

Set grade=61

Where grade<60 and c# in(select c#

from c

where cname=’计算机网络’);

(2)update sc

Set grade=grade*1.05

Where grade<(select avg(grade)

From sc

Where c# in(select c#

From c

Where cname=’数据结构’)) and

C# in(select c#

From c

Where cname=’数据结构’);

3-10

(1)create view grade_t

As

Select s.s#,sname,c.c#,cname,classh,grade,t.t#,tname

From s,c,t,sc,teach

Where s.s#=sc.s# and c.c#=sc.c# and sc.c#=teach.c# and teach.t#=t.t#;

(2)create view teach_l

As

Select t.t#,tname,c#,cname,classh,avg(grade) as avg_grade

From t,c,sc,teach

Where t.t#=teach.t# and teach.c#=sc.c# and sc.c#=c.c#

Group by t.t#;

3-11 select s#,sname

From s

Where ssex=’男’；

3-12 select s#,sname

From s

Where sbirthin>’1981-1-1’ and sex=’女’;

3-13 select s#,ssex,scode#

From s

Where s# in (select s#

From sc

Where c# in (select c#

From c

Where cname=’操作系统’));

3-14 select s#,sname,scode#

From s

Where s# in(select s#

From sc

Where c# in(select c#

From teach

Where t# in(select t#

From t

Where tname=’刘少华’)));

3-15 select s#,sname

From s

Where not exists(select *

From c

Where not exists(select *

From sc

Where sc.s#=s.s# and

c.c#=sc.c#));

3-16 select c#,classh,tname

From c,teach,t,s,sc

Where c.c#=sc.c# and sc.c#=teach.c# and teach.t#=t.t# and sc.s#=s.s# and s.sname=’王丽丽’;

3-17 select c#,classh

From c

Where c# in(select c#

From teach

Where t# in(select t#

From t

Where tname=’刘少华’));

3-18 select tname

From t

Where t# in(select t#

From teach);

3-19 select s#,sname,sbirthin

From s

Where scode# in(select scode#

From ss

Where ssname=’计算机应用技术’) order by sbirthin;

3-20 select s#,sname

From s

Where s# in(select s#

From sc

Where c# in(select c#

From c

Where cname=’计算机网络’)); 3-21 select s#,sname

From s

Where s# in(select s#

From sc

Where c# in(select c#

From c

Where cname=’计算机网络’));

Intersect

select s#,sname

From s

Where s# in(select s#

From sc

Where c# in(select c#

From c

Where cname=’信息安全技术’)); 3-22 select s#,sname，ssname

From s,ss

Where s.scode#=ss.scode# and s# not in(select s#

From sc

Where c# in

(select c#

From c

Where cname=’计算

机网络’));

3-23 select c#,cname

From c

Where c# in(select c#

From sc

Group by c#

Having count(*)>=5);