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';

实验三 答案 (2) 在 SQL Server 企业管理器中为自己建立一个服务器用户、数据库用户和数据库角色。并将自己 创建的学生选课库的所有操作权赋予它们,将设计数据库的操作权赋予新建的数据库用户。 use student grant all on stu to [PC2010120120UTT\db1] go use master grant create database to [PC2010120120UTT\db1] go

(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、对“学生—课程数据库”编写存储过程,完成下面功能: (1)统计数据库原理课程的成绩几个人数; create procedure grade_tj as select count(*) from sc where cno='1' and grade>60

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

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

create procedure grade_xg as update sc set grade=1 where grade<60 update sc set grade=2 where grade>=60 and grade<70 update sc set grade=3 where grade>=70 and grade<80 update sc set grade=4 where grade>=80 and grade<90 update sc set grade=5 where grade>=90


赞助商链接
推荐相关:

实验2-4答案

实验2-4答案 dtdt隐藏>> 实验二 实验二、 选择结构【上机内容】 上机内容】 1.修改下列程序,使之满足当 x 为 10 时输出“= =” ,否则输出“!=”的条件...


实验2-4 答案

实验2-4 答案 隐藏>> 实验二 答案 1. select * from s order by city,sname desc; 2. select * from p order by pname,weight desc; 3. select * fr...


实验教材2-4答案

2财富值如要投诉违规内容,请到百度文库投诉中心;如要提出功能问题或意见建议,请点击此处进行反馈。 实验教材2-4答案 答案答案隐藏>> 实验二、 选择结构 【上机内容...


实验四答案2

3页 2财富值如要投诉违规内容,请到百度文库投诉中心;如要提出功能问题或意见建议,请点击此处进行反馈。 实验四答案2 隐藏>> 数据库技术与应用实验 1. 定义基本表...


微机实验第2、4题答案

6页 2财富值如要投诉违规内容,请到百度文库投诉中心;如要提出功能问题或意见建议,请点击此处进行反馈。 微机实验24答案 隐藏>> 2.冒泡法 DATA SEGMENT W...


实验四 (2)

15页 2财富值如要投诉违规内容,请到百度文库投诉中心;如要提出功能问题或意见建议,请点击此处进行反馈。 实验四 (2) 数据库 实验题目答案数据库 实验题目答案隐藏...


上机实验4(E-R图2)参考答案

上​机​实​验​4​(​E​-​R​图​2​)​参​考...第三周上机实验参考答案 1、E-R 图:店址 店名 参加工作时间 1 店号 商店 ...


分析化学(二)含实验4-2

《分析化学Ⅱ(含实验)》《分析化学Ⅱ(含实验)》P052 第 4-2 次作业(122) 此次作业属主观题,没有正确答案,请自行翻书解答主观题(分数:73 分) 1、光学分析...


实验二 基尔霍夫定律和叠加原理的验证(实验报告答案)

实验二 基尔霍夫定律和叠加原理的验证(实验报告答案)_物理_自然科学_专业资料。...三、实验设备与器件 1.直流稳压电源 2.直流数字电压表 3.直流数字毫安表 4....


东华大学MATLAB数学实验第二版答案(胡良剑)

东华大学MATLAB数学实验答案(胡良剑) - 数学实验答案 Chapter 1 Page20,ex1 (5) 等于[exp(1),exp(2);exp(3),exp(4)] (7) 3=...

网站首页 | 网站地图
All rights reserved Powered by 学霸学习网 www.tceic.com
copyright ©right 2010-2021。
文档资料库内容来自网络,如有侵犯请联系客服。zhit325@126.com