56章部分习题参考.doc

select c.*,选课人数 from c,(select cno,count(sno) 选课人数 from sc group by cno) as a where a.cno=c.cno select c.*,sc.sno from c left join sc on sc.cno=c.cno order by sc.cno compute count(sno) by sc.cno 求全是女同学选修的课程号 select distinct cno from sc x where not exists (select * from s where ssex=男 and exists(select * from sc y where y.cno=x.cno and y.sno=s.sno)) select distinct cno from sc where cno not in(select cno from s,sc where s.sno=sc.sno and ssex=男) select distinct cno from sc x where not exists (select * from s,sc y where s.sno=y.sno and y.cno=x.cno and ssex=男 ) select distinct cno from sc where not exists (select * from s where s.sno=sc.sno and ssex=男) 第四个错误 select distinct cno from sc where not exists (select * from s,sc where s.sno=sc.sno and ssex=男 ) 第五个错误 把选课表中每门课的平均成绩插入到令一个已存在的表中cavg(cno,can.avggrade)中 insert into cavg select c.cno,cna,avggrade from c,(select cno,avg(grade) as avggrade from sc group by cno)as scavg where c.cno=scavg.cno 或 insert into cavg select c.cno,cna,avg(grade) from sc,c where sc.cno=c.cno group by c.cno,cna 建立删除学生记录时，同时也要把相应的选课记录删除的触发器。 CREATE TRIGGER s_delete ON s FOR DELETE AS IF (SELECT COUNT(*) FROM sc,deleted WHERE sc.sno=deleted.sno)0 begin DELETE sc FROM sc inner join deleted on sc.sno= deleted.sno End 或 CREATE TRIGGER s_delete ON s FOR DELETE AS DELETE sc FROM sc , deleted where sc.sno= deleted.sno 或CREATE TRIGGER s_delete ON s FOR DELETE AS DELETE FROM sc where sno in (select sno from deleted) select sc.sno,sna,学习情况= case WHEN AVG(Grade)=85 THEN 好