tceic.com

# 实验2-4 答案

1. select * from s order by city,sname desc; 2. select * from p order by pname,weight desc; 3. select * from j where jname like '%厂%'; 4. select * from s where sname like '_方%'; 5. select max(weight),min(weight),avg(weight) from p; 6. select count(pno),avg(weight) from p where pname='螺丝刀'; 7. select pname,sum(qty) from spj,p where spj.pno=p.pno and sno='s1' group by pname; 8. select pname,sum(qty) from spj,p where spj.pno=p.pno and jno='j1' group by pname; 9. select jname from j where not exists (select * from spj,p where spj.pno=p.pno and spj.jno=j.jno and color='红色' and pname='螺丝刀'); 10. select sname from s where not exists (select * from spj,p where spj.pno=p.pno and spj.sno=s.sno and color='红色' and pname='螺丝刀'); 11. select jno from spj group by jno having sum(qty)>500; 12. select jno from spj group by jno having count(*)>3; 13. select jname from j where not exists (select * from p where not exists (select * from spj where j.jno=spj.jno and p.pno=spj.pno)); 14. select sname from s where not exists (select * from spj x where x.jno='j1' and not exists (select * from spj y where s.sno=y.sno and x.pno=y.pno)); 15. select sname,pname,jname,qty from s,p,j,spj where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno; 16. select pname,jname,qty from s,p,j,spj where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno and sname='东方红';

create view jcc as select sno, pno,qty from spj,j where j.jno=spj.jno and jname='机车厂' select pno,sum(qty) from jcc group by pno; select * from jcc where sno='s2';

(3) 定义图书表出版社属性为“希望出版社”的缺省，并捆绑缺省到图书表中。 CREATE TRIGGER pub ON [dbo].[publish] FOR INSERT, UPDATE AS update 图书 set 出版社="希望出版社" where 书号 =(select 书号 from inserted where 出版社 is null) or 书号=(select 书号 from deleted where 出版社 is null) 利用触发器来保证学生选课库中选课表的参照完整性，以维护其外码与参照表中的主码一致。 CREATE TRIGGER check_for ON [dbo].[sc] FOR INSERT AS delete from sc where sno=(select sno from inserted a where a.sno not in (select b.sno from stu b)) or cno=(select cno from inserted e where e.cno not in (select f.cno from course f)) (4) 为图书读者库中的借阅表建立一个保证参照完整性，以维护外码与被参照表中的主码一致的触 发器。 CREATE TRIGGER check_wai ON [dbo].[借阅] FOR INSERT, UPDATE AS delete from 借阅 where 书号=(select 书号 from inserted a where a.书号 not in (select b.书号 from 图书 b)) or 编号=(select 编号 from inserted e where e.编号 not in (select f.编号 from 读者 f))

(5) 建立一个课程与选课表间来维护参照完整性而使用的级联删除触发器、级联修改触发器和 受限插入触发器。 建立一个学生与选课表间来维护参照完整性而使用的级联删除触发器、级联修改触发器和受限 插入触发器。 CREATE TRIGGER check_cno ON [dbo].[course] FOR DELETE AS delete from sc where cno=(select cno from deleted ) CREATE TRIGGER update_cno ON [dbo].[course] FOR update AS update sc set cno=(select cno from inserted ) where cno=(select cno from deleted ) CREATE TRIGGER check_for ON [dbo].[sc] FOR INSERT AS delete from sc where sno=(select sno from inserted a where a.sno not in (select b.sno from stu b)) or cno=(select cno from inserted e where e.cno not in (select f.cno from course f))

（2）统计任意一门课程的平均成绩； create procedure grade_tj as select avg(grade) from sc where cno='1'

（3）将学生选课成绩从百分制改为等级制（5、4、3、2、1） 。

### 2016--2017年度(上)九年级模拟测试卷(二) 数学第二次月考及答案 ...

2016--2017年度(上)九年级模拟测试卷() 数学第次月考及答案 实验中学 出题 1 B 2 D 3 B 4 C 5 B 6 A 7 A 8 C 9 D 10 C 11 5.05×10 ...

### ...滕州市实验高中高三4月模拟考试地理试题及答案 (2)

2018届山东省滕州市实验高中高三4月模拟考试地理试题及答案 (2)_政史地_高中教育_教育专区。2018 届山东省滕州市实验高中高三 4 月模拟考试 地理试题 本试卷分...