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))

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

### java实验4 业务类的设计2 - 答案.doc

java实验4 业务类的设计2 - 答案 - 实验四 业务类设计(二) 一、实验时间: 二、 实验目的 1、 掌握业务类的作用; 2、 理解成员变量为引用变量的类的设计;...

### VisualBasic程序设计教程(第4版)实验答案[2].doc

VisualBasic程序设计教程(第4版)实验答案[2] - ※声明: 1. 实验所属课本原件为 《Visual Basic 程序设计教程 (第 4 版) (□主编 龚沛曾 □编者 杨志强 陆...

### DCNE2-四套模拟题+实验+答案.doc

DCNE2-四套模拟题+实验+答案_IT认证_资格考试/认证_教育专区。DCNE2-四套模拟题+实验练习题加实验,带答案 神州数码网络工程师考试模拟题说明:本复习题包含两部分...

### 化学实验基础4新题赏析2.doc

4 第 -3- 页 化学实验基础 2014 新题赏析 讲义参考答案新题赏析题一:A 题六:C 题二:D 题七:AD 题三:C 题八:B 题四:A 题五:B 第 -4- ...

### H09-2017广东实验中考总复习数学专题2方程与不等式....doc

H09-2017广东实验中考总复习数学专题2方程与不等式(4页,答案6)(已变尾注)_中考_初中教育_教育专区。H09 2017 广东中考专题( )方程与不等式一、选择...

### 实验四 验证牛顿第二定律.doc

(4)保持小车的质量不变,改变砝码的质量,按上面步骤再做 5 次实验. (5)在...(x3+x4)-(x1+x2) 答案: (1) 4T2 或摩擦力平衡不够 M (2)B (3)①...

### 2014秋实验四_MATLAB的符号计算二答案.doc

2014秋实验四_MATLAB的符号计算二答案_数学_自然科学_专业资料 暂无评价|0人阅读|0次下载|举报文档 2014秋实验四_MATLAB的符号计算二答案_数学_自然科学_专业资料...