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

第三章课后习题
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);。