实验一：表及约束的创建
1.1 实验目的与内容 目的：创建数据表、添加和删除列、实现所创建表的完整性约束。 内容：11-2、11-26~33。 注：实验内容编号均取自《数据库原理及设计（第 3 版）》第 11 章的实验！即：实验内容以第 3 版教材为准！ 报告：以 11-2、11-27、11-30、11-33 作为实验一的报告。 1.2 实验代码及结果 注：仅附有实际意义的结果。运行代码得到结果后拷屏，用 Windows 画图工具切下有意义的部分，然后粘贴到此处。
1.2.1 11-2 实验代码及执行结果 CREATETABLE person ( P_no char(6)PRIMARYKEY, P_name varchar(10)NOTNULL, Sex char(2)NOTNULL, Birthdate datetime NULL, Date_hired datetime NOTNULL, Deptname varchar(10) NOTNULL, P_boss char(6) NULL, CONSTRAINT birth_hire_check CHECK (Birthdate<Date_hired)
)
1.2.211-27 实验代码及执行结果 Createrule Discount_rule AS @Discount between 0.50 and 1.00 sp_bindrule 'Discount_rule', 'customer.Discount'
Createrule Sex_rule as @Sex in('男','女') sp_bindrule 'Sex_rule','person.Sex'
2017-2018(1) 《数据库原理实验》课程实验报告
第 4 页共 24 页
sp_unbindrule 'person.Sex' droprule Sex_rule sp_help Sex_rule
1.2.311-30 实验代码及执行结果 Altertable person Addconstraint unique_pno_pname unique (P_no,P_name) sp_help unique_pno_pname
1.2.411-33 实验代码及执行结果 Insert orderdetail values('CX88','000009','000010','120000','2005-7-17')
update person set P_no='000010' where exists (select*from orderdetail where Order_no='cx2222' and person.P_no=orderdetail.P_no)
Delete orderdetail where order_no='AS0058'
2017-2018(1) 《数据库原理实验》课程实验报告
第 5 页共 24 页
实验二：SQL 更新语句
2.1 实验目的与内容 目的：update、delete、insert 语句的练习。 内容：11-6~8。 报告：以 11-6、11-8 作为实验二的报告。 2.2 实验代码及结果
2.2.1 11-6 实验代码及执行结果 Insert person values('000007',' 肖家庆',' 男 ','1963-07-14','1998-06-06',' 销售部 ',NULL) Insert person values('000008',' 李浩然',' 男 ','1975-01-30','2002-04-12',' 培训部 ','000005') Insert person values('000009',' 赵文龙',' 男 ','1969-04-20','1996-08-12',' 销售部 ','000007') Insert salary values('000001',2100,300) Insert salary values('000002',1800,300) Insert salary values('000003',2800,280) Insert salary values('000004',2500,250) Insert salary values('000005',2300,275) Insert salary values('000006',1750,130) Insert salary values('000007',2400,210) Insert salary values('000008',1800,235) Insert salary values('000009',2150,210) Insert person values('000001','林国平','男','1985-08-14','成都','0.85') Insert customer values('000002','林国平','男','1985-08-14','成都','0.85') Insert customer values('000003','郑洋','女','1973-04-07','成都','1.00') Insert customer values('000004','张雨洁','女','1983-09-06','北京','1.00') Insert customer values('000005','刘菁','女','1971-08-20','北京','0.95') Insert customer values('000007','顾培铭','男','1973-07-23','上海','1.00') Insert orderdetail values('AS0058','000006','000002','150000','2011-04-05') Insert orderdetail values('AS0043','000005','000005','90000','2011-03-25') Insert orderdetail values('AS0030','000003','000001','70000','2011-02-14') Insert orderdetail values('AS0012','000002','000005','85000','2010-11-11') Insert orderdetail values('AS0011','000007','000009','130000','2010-08-13') Insert orderdetail values('AS0008','000001','000007','43000','2010-06-06') Insert orderdetail values('AS0005','000001','000007','72000','2010-05-12') Insert orderdetail values('BU1167','000007','000003','110000','2010-03-08') Insert orderdetail values('BU1143','000004','000008','70000','2009-12-25') Insert orderdetail values('BU1139','000002','000005','90000','2009-10-12') Insert orderdetail values('BU1132','000006','000002','32000','2009-08-08') Insert orderdetail values('BU1121','000004','000006','66000','2009-04-01') Insert orderdetail values('CX2244','000007','000009','80000','2008-12-12') Insert orderdetail values('CX2232','000003','000001','35000','2008-09-18')
2017-2018(1) 《数据库原理实验》课程实验报告
第 6 页共 24 页
Insert orderdetail values('CX2225','000002','000003','90000','2008-05-02') Insert orderdetail values('CX2222','000001','000007','66000','2007-12-04')
2017-2018(1) 《数据库原理实验》课程实验报告
第 7 页共 24 页
2.2.211-8 实验代码及执行结果 Delete person whereP_no='000010'
2017-2018(1) 《数据库原理实验》课程实验报告
第 8 页共 24 页
实验三：SQL 查询语句
3.1 实验目的与内容 目的：select 语句中各种查询条件的实验。 内容：11-12~18。 报告：以 11-14、11-15、11-16、11-17 作为实验三的报告。 3.2 实验代码及结果
3.2.1 11-14 实验代码及执行结果 Select Deptname from salary join person on salary.P_no=person.P_no Groupby Deptname Having AVG(Bonus)>200 Orderby AVG(Bonus)DESC
Select count(*),sum(Order_total) From orderdetail,customer Where orderdetail.Cust_no=customer.Cust_no and City='上海'
3.2.211-15 实验代码及执行结果 Select p.P_no,count(*),SUM(Order_total) From orderdetailo,personp Where o.P_no=p.P_no and deptname='培训部' Groupby p.P_no
Select p.P_no,p.P_name From personp,personm Where p.P_boss=m.P_no and p.Date_hired<m.Date_hired
2017-2018(1) 《数据库原理实验》课程实验报告
第 9 页共 24 页
Select distinct c.Cust_no,Cust_name,Sex,Discount From customer c,orderdetail o1,orderdetail o2 Where c.Cust_no=o1.Cust_no and c.Cust_no=o2.Cust_no and o1.Order_no<>o2.Order_no
Select p1.P_no,p1.P_name,p1.Sex,p1.Deptname From person p1,person p2 Where p1.P_name=p2.P_name and p1.P_no<>p2.P_no
3.2.311-16 实验代码及执行结果 Select p.P_no,P_name,Fact From personp,salarys Where p.P_NO=s.p_no And s.fact>(selectfactfromsalarywhereP_no='000005')
Select order_no,cust_name,P_name,Order_total,Order_date From orderdetail o,person p,customer c Where o.Cust_no=c.Cust_no and o.p_no=p.P_no and order_total>(select avg(order_total) from orderdetail)
Select distinct p.P_no,P_name From orderdetail o,person p Where p.p_no=o.P_no and Cust_no in (select Cust_no from customer where city='成都')
2017-2018(1) 《数据库原理实验》课程实验报告
第 10 页共 24 页
selectCust_no,Cust_name,Sex,Discount fromcustomer whereCust_nonotin(selectdistinctcust_no fromorderdetail whereorder_date>=getdate()-365)
3.2.411-17 实验代码及执行结果 selectdistinctc.Cust_no,Cust_name,Sex,Discount fromcustomerc,orderdetailo1 wherec.Cust_no=o1.Cust_noand o1.cust_noin(selectcust_nofromorderdetailo2whereo1.order_no<>o2.order_no)
selectdistinctc.cust_no,cust_name,sex,discount fromcustomerc whereexists(select*fromorderdetail wherecust_no=c.cust_noandorder_total>100000 orexists(selectsum(order_total)fromorderdetailwhereCust_no=c.Cust_no havingsum(order_total)>200000))
2017-2018(1) 《数据库原理实验》课程实验报告
第 11 页共 24 页
2017-2018(1) 《数据库原理实验》课程实验报告
第 12 页共 24 页
实验四：视图及索引的建立和维护
4.1 实验目的与内容
目的：创建表的视图，修改和删除表的视图，并利用视图完成表的查询，创建表的索引、修改和删 除表的索引。 内容：11-3~5、11-9~11。 报告：以 11-3、11-4、11-10 作为实验四的报告。 4.2 实验代码及结果
4.2.1 11-3 实验代码及执行结果 createviewcustomreviewas selectcust_no,Cust_name,Sex,discountfromcustomerwherecity='北京)
因为没有一年内的结果所以改用七年内的 selectp2.p_no,p_name,sex,deptname,p3.uu frompersonp2, (selectp1.P_no,SUM(order_total)uu frompersonp1,orderdetailo wherep1.p_no=o.P_noandp_bossisnotnull andorder_date>=getdate()-365*7 groupbyp1.p_no)p3 wherep2.p_no=p3.p_no
4.2.211-4 实验代码及执行结果 createindexname_sortonperson(P_name)
createindexbirth_nameonperson(Birthdate,P_name)
2017-2018(1) 《数据库原理实验》课程实验报告
第 13 页共 24 页
createuniqueindexu_name_sortonperson(P_name) 消息1505，级别16，状态1，第1 行 CREATE UNIQUE INDEX 终止，因为发现了索引ID 9 的重复键。最重要的主键为'李浩然@。 因为列表里有两个李浩然重名，所以不能创建unique index createindexfact_idxonsalary(factdesc)
4.2.311-10 实验代码及执行结果 insertcustomerview(Cust_no,Cust_name,Sex)values ('000008','刘美萍','女')
2017-2018(1) 《数据库原理实验》课程实验报告
第 14 页共 24 页
实验五：存储过程及游标的建立和维护
5.1 实验目的与内容 目的：（1）创建用户的存储过程，修改和删除存储过程、执行存储过程。（2）定义并使用游标。 内容：11-22~24。 报告：以 11-22~24 作为实验五的报告。 5.2 实验代码及结果
5.2.1 11-22 实验代码及执行结果 createprocproc_age(@codeCHAR(6),@ageintoutput) as declare@birthvarchar(4),@todayvarchar(4) select@birth=datename(yy,BirthDate) fromperson whereP_no=@code select@today=datename(yy,getdate()) select@age=convert(int,@today)-convert(int,@birth)
5.2.211-23 实验代码及执行结果 declare@resultint execproc_age'000003',@resultoutput select@result
5.2.311-24 实验代码及执行结果
createprocproc_addbonus (@P_nochar(6),@adddec(5,1)output) as declare@Order_totalint declarecur_addbonus_checkscursorfor selectorder_total
2017-2018(1) 《数据库原理实验》课程实验报告
第 15 页共 24 页
fromorderdetail whereP_no=@P_no select@add=0 opencur_addbonus_checks fetchcur_addbonus_checksinto@order_total if (@@fetch_status<>0) begin closecur_addbonus_checks deallocatecur_addbonus_checks return end setnocounton while (@@fetch_status=0) begin if@order_total<=100000 set@add=@add+20 elseset@add=@add+@order_total/100000*30 fetchcur_addbonus_checksinto@order_total end closecur_addbonus_checks deallocatecur_addbonus_checks
declare@retdec(5,1) execproc_addbonus'000002',@add=@retoutput select@ret
2017-2018(1) 《数据库原理实验》课程实验报告
第 16 页共 24 页
实验六：触发器的建立和维护
6.1 实验目的与内容 目的：创建触发器，修改和删除触发器，测试触发器的效果。 内容：11-34。 报告：以 11-34 作为实验六的报告。 6.2 实验代码及结果
6.2.1 实验代码 (1) createtriggerchange_P onperson fordelete as if@@rowcount=0 return if@@rowcount!=1 rollback deletesalarywheresalary.p_no=(selectp_nofromdeleted) deletepersonwhereperson.p_no=(selectp_nofromdeleted) return (2) createTRIGGERCHECK_S ONsalary forupdate,insert as declare@num_rowsint select@num_rows=@@rowcount if@num_rows=0 return if (selectcount(*) frompersonp,insertedi wherep.p_no=i.p_no)!=@num_rows begin raiserror 53334 '' rollbacktransaction return end return (3) createTRIGGERCHECK_S ONsalary forupdate,insert as declare@num_rowsint select@num_rows=@@rowcount if@num_rows=0 return
2017-2018(1) 《数据库原理实验》课程实验报告
第 17 页共 24 页
if (selectcount(*) frompersonp,insertedi wherep.p_no=i.p_no)!=@num_rows begin raiserror 53334 '' rollbacktransaction return end return
6.2.2 实验结果 (1)
(2) USE[RJ2015] GO /****** Object: Trigger [U2015114466].[CHECK_S] Script Date: 11/20/2017 16:09:20 ******/ SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO ALTERTRIGGER[U2015114466].[CHECK_S] ON[U2015114466].[salary] forupdate,insert as declare@num_rowsint select@num_rows=@@rowcount if@num_rows=0 return if (selectcount(*) frompersonp,insertedi
2017-2018(1) 《数据库原理实验》课程实验报告
第 18 页共 24 页
wherep.p_no=i.p_no)!=@num_rows begin raiserror 53334 '' rollbacktransaction return end return (3) USE[RJ2015] GO /****** Object: Trigger [U2015114466].[CHECK_S] Script Date: 11/20/2017 16:09:20 ******/ SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO ALTERTRIGGER[U2015114466].[CHECK_S] ON[U2015114466].[salary] forupdate,insert as declare@num_rowsint select@num_rows=@@rowcount if@num_rows=0 return if (selectcount(*) frompersonp,insertedi wherep.p_no=i.p_no)!=@num_rows begin raiserror 53334 '' rollbacktransaction return end return
2017-2018(1) 《数据库原理实验》课程实验报告
第 19 页共 24 页
实验七：综合设计实验
7.1 实验目的、题目与实验项目 目的：综合练习 ER 图设计、ER 模型到关系模型转换、创建表、表的增删改，以及表的查询。 题目：分自拟题目与给定题目；如自拟，给分上将考虑放宽。 题目选择：自拟；给定。 注：如选自拟，则需填写：7.1.1、7.1.5、7.2、7.3、7.4、7.5 和 7.7，其它部分不用管也不必删；如 选给定，则需填写 7.2、7.3、7.4、7.5 和 7.6，其它部分不用管也不必删。 7.1.1 自拟题目
注：①如选自拟，可对照“7.1.2 给定题目”的形式，在此处描述自拟题目。 ②不能用第 3 章作业中的题目作为自拟题目。 ③至少有 6 个实体、5 个联系。 有 6 个实体：“职员（E）”，“发票（T）”，“销售记录（RS）”，“报销记录（RT）”，“财务记录（WR）”，“财 务职员（WE）”。“职员”的属性是身份证号职员 ID（EId），部门 ID（DId），姓名（ EName），职位（ EPosition）； “发票”的属性是发票 ID（TId），发票内容（TContent）；“销售记录”的属性是记录 ID（ＳId），销售 额（SNumber），日期时间（ＳDate），客户ＩＤ（ＣＩｄ）；“报销记录”的属性是记录ＩＤ（ＲＩｄ）， 职员ＩＤ（ＥＩｄ），报销金额（ＲＮｕｍｂｅｒ），日期时间（ＲＤａｔｅ）；“财务记录”的属性是记 录ＩＤ（ＷＩｄ），记录人员（ＷＰｅｒｓｏｎ），日期时间（ＷＤａｔｅ），内容（ＷＣｏｎｔｅｎｔ）； “财务专员”的属性是职员ＩＤ（ＷＥＩｄ）。职员产生发票，发票报销产生报销记录；财务职员会计审 核发票，财务职员可以预算和审核财务记录可以查看销售记录。
7.1.2 给定题目 有四个实体：“团长”（T），“代表团”（D），“运动员”（Y），“比赛项目”（B）。“团长”的属性是身 份证号（Tid），姓名（Tname），性别（Tgender），年龄（Tage），电话（Ttel）；“代表团”的属性有团编 号（Did），来自地区（Darea）， 住所（Dplace）；“运动员”的属性有编号（Yid），姓名（Yname），性别 （Ygender），年龄（Yage）；“比赛项目”的属性有项目编号（Bid），项目名（Bname），级别（Blevel）。 这些实体间的联系及它们的属性有：团长“管理”（G）代表团；代表团由运动员“组成”（Z）；运 动员“参加”（C）比赛项目。“参加”的属性是比赛时间（Ctime）和名次（Crank）。 7.1.3 通用实验项目
注：不论自拟还是给定，以下实验必做。 (1) 画出 ER 图，要求标注联系的约束类型和键约束。 (2) 将 ER 图转换为关系模型（用已给出的字母编码表示），要求标出各关系的主键。 (3) 用 SQL 创建各个关系表，并设置各表的主键约束和外键约束。 (4) 自己完成向各表插入数据、删除数据和修改数据的实验。 7.1.4 给定题目特定的查询
注：以下为针对给定题目特定的实验项目。如选给定题目，则必做；如选自拟则勿需做。 (5) 写出下列查询的 SQL 语句及执行后的结果： ①查询获得“男子”、“链球”、“ 冠军”的运动员编号及来自的地区。 ②检索获得“女子”、“乒乓球单打”、“冠军”的运动员编号及所在代表团的团长姓名。 ③查询“赵康”团长所管理代表团的运动员参加马拉松比赛的运动员姓名与名次。 ④查询“成都市”代表团的运动员“吴迪”参加的比赛项目名和比赛时间。
2017-2018(1) 《数据库原理实验》课程实验报告
第 20 页共 24 页
(6) 自拟至少两个查询，并写相应的 SQL 查询及执行结果！ 7.1.5 自拟题目的查询设定
注：如选自拟，则需先对照“7.1.4 针对给定题目特定的实验项目”(5)的形式，自己设定 4 个查询（尽量不要太简单， 否则得分上会失去优势）。然后，再完成这 4 个设定的查询。 写出下列查询的 SQL 语句及执行后的结果： ① 查询获得销售部的所有职员的 Id 和姓名 ② 查询一年内有报销记录的职员的 Id 和姓名 ③ 查询一年内各个客户的消费总额 ④ 查询报销记录总额超过 10000 元的职员 Id 和姓名
7.2 ER 图
职员E
发票T 财务记录WR
财务职员WE
EId 职员ID
Did 部门ID
EName 姓名
EPosition 职位
TId 发票ID
TContent 发票内容
SId 记录ＩＤ
ＳＮｕｍｂｒｅ 销售额
日期时间 ＳＤａｔｅ
记录ＩＤ
ＲＩｄ
报销金额 ＲＮｕｍｂｅｒ
日期时间 ＲＤａｔｅ
记录ＩＤ ＷＩｄ
记录人员 ＷＰｅｒｓｏｎ
日期时间 ＷＤａｔｅ
内容 ＷＣｏｎｔｅｎ ｔ
产生
１
１
职员ＩＤ ＷＥＩｄ
会计审核
１
ｎ
预算
审核
ｎ
１
ｎ
１
客户customer
客户Id CId
客户姓名 CName
性别 Sex
生日 birthdate
城市 City
折扣 Discount
销售 m n
报销
n
1
7.3 ER 模型向关系模型的转换结果 职员(职员 ID（EId），部门 ID（DId），姓名（EName），职位（EPosition）) 发票（发票 ID（TId），发票内容（TContent），职员 ID（EId），专员ＩＤ（WEId））职员 ID，专员 ID 是 外键 报销记录（记录 ID（RId），报销金额（RNumber），日期时间（RDate），发票 ID（TId），职员 ID（EId）） 发票ＩＤ,职员 ID 是外键 财务专员（专员 ID（WEId）） 财务记录（记录 ID（WId），记录人员（WPerson），日期时间（ WDate），内容（ WContent），专员 ID（WEId）） 专员 ID 为外键 销售记录（记录 ID（SId），销售额（SNumber），日期时间（SDate），客户 ID（CId），职员 ID（EId）） 职员 Id，客户 Id 为外键
2017-2018(1) 《数据库原理实验》课程实验报告
第 21 页共 24 页
客户（客户 Id（CId），客户姓名（CName），性别（Sex），生日（Birth），城市（ City），折扣（Discount））
7.4 各表的创建 Create table Employee (EId char(9) primary key, Did char(10) not null, EName char(10) not null, EPosition char(10) not null ) Create table Ticket ( TId char(9) primary key, TContent char(50) not null default'无', EId char(9) not null, WEId char(9) not null constraint EId_Check foreign key(EId) references Employee (EId) on DELETE CASCADE on update cascade, constraint WEId_Check foreign key (WEId) references WEmployee(WEId) on DELETE CASCADE on update cascade, )
Create table Record ( Rid char(9) primary key, RNumber int not null, RDate datetime not null, TId char(9) not null, Eid char(9) not null, Constraint TId_Check Foreign key(TId) References Ticket (TId) on delete cascade on update cascade, Constraint EId_Check Foreign key(EId) References Employee (EId) on delete cascade on update cascade,
2017-2018(1) 《数据库原理实验》课程实验报告
第 22 页共 24 页
) create table WEmployee ( WEId char(9) primary key ) create table Wealth ( WId char(9) Primary Key, WPerson char(10) not null, WDate datetime not null, WContent char(50) not null, WEId char(9) not null, foreign key(WEId) references WEmployee (WEId) on delete cascade on update cascade, ) Create table Sales ( SId char(9) Primary Key, SNumber int not null, SDate datetime not null, CId char(9) not null, Eid char(9) not null, Constraint CId_Check Foreign key(CId) References Customer (CId) on delete cascade on update cascade, Constraint EId_Check Foreign key(EId) References Employee (EId) on delete cascade on update cascade,
) Create table Customer (
CId char(9) Primary Key, CName char(9) not null, Sex char(1) not null, Birth datetime not null, City char(10) not null,
2017-2018(1) 《数据库原理实验》课程实验报告
第 23 页共 24 页
Discount float not null default 1.0, ) 7.5 各表数据的增删改实验 insert into Employee values('000000001','销售部','王虎','职员') insert into Employee values('000000002','销售部','李华','职员') insert into Employee values('000000003','销售部','高琴','助理') insert into Employee values('000000004','销售部','高品','主管') insert into Employee values('000000005','研发部','李浩','职员') insert into Employee values('000000006','研发部','王宇','职员') insert into Employee values('000000007','研发部','辛达','总管') insert into WEmployee values('000000003') insert Ticket values('100000001','医疗报销','000000001','000000003') insert Ticket values('100000002','出行报销','000000001','000000003') insert Ticket values('100000003','宴请报销','000000001','000000003') insert Ticket values('100000004','医疗报销','000000002','000000003') insert Ticket values('100000005','出行报销','000000004','000000003') insert Record values('200000001',2000,'2017-05-23','100000001', '000000001') insert Record values('200000002',3000,'2017-05-23','100000001', '000000001') insert Record values('200000003',1000,'2017-05-23','100000001','000000001') insert Record values('200000004',2000,'2017-05-23','100000002','000000001') insert Record values('200000005',2000,'2017-05-23','100000004', '000000002') insert Record values('200000006',2000,'2017-05-23','100000005', '000000004') delete Record where RId='200000001' insert Wealth values('300000001','Peter','2017-08-19','IPO风投','000000003') insert Sales values('400000001',200000,'2017-08-23','500000001', '000000001') insert Sales values('400000002',100000,'2017-08-23','500000001', '000000001') insert Sales values('400000003',300000,'2017-08-23','500000003', '000000002') insert Customer values ('500000001',’Peter’,’男’,’1997-02-07’,’南京’,1.0) insert Customer values ('500000003',’Wendy,’女’,’1997-02-07’,’上海’,1.0)
7.6 给定题目的特定查询
7.6.1 表的查询及执行结果（第 5 个问题的 4 个查询）
7.6.2 自拟表的查询及执行结果（第 6 个问题的 2 个查询）
注：每位同学的查询均不得相同，否则以“抄袭”论处。
(1) select EId,EName from Employee where Did='销售部' and EPosition='职员'
2017-2018(1) 《数据库原理实验》课程实验报告
第 24 页共 24 页
(2) select distinct e.EId,e.EName from Employee e,Record r where r.RDate>=getdate()-365 and r.EId=e.EId
(3) select CId,sum(SNumber) from Sales s where s.SDate>=getdate()-365 group by Cid
(4) select e.EId,e.EName from Employee e,(select EId,sum(RNumber)as ans from Record where RDate>=getdate()-365 group by EId having sum(RNumber)>4000) x where x.Eid=e.Tid
1.1 实验目的与内容 目的：创建数据表、添加和删除列、实现所创建表的完整性约束。 内容：11-2、11-26~33。 注：实验内容编号均取自《数据库原理及设计（第 3 版）》第 11 章的实验！即：实验内容以第 3 版教材为准！ 报告：以 11-2、11-27、11-30、11-33 作为实验一的报告。 1.2 实验代码及结果 注：仅附有实际意义的结果。运行代码得到结果后拷屏，用 Windows 画图工具切下有意义的部分，然后粘贴到此处。
1.2.1 11-2 实验代码及执行结果 CREATETABLE person ( P_no char(6)PRIMARYKEY, P_name varchar(10)NOTNULL, Sex char(2)NOTNULL, Birthdate datetime NULL, Date_hired datetime NOTNULL, Deptname varchar(10) NOTNULL, P_boss char(6) NULL, CONSTRAINT birth_hire_check CHECK (Birthdate<Date_hired)
)
1.2.211-27 实验代码及执行结果 Createrule Discount_rule AS @Discount between 0.50 and 1.00 sp_bindrule 'Discount_rule', 'customer.Discount'
Createrule Sex_rule as @Sex in('男','女') sp_bindrule 'Sex_rule','person.Sex'
2017-2018(1) 《数据库原理实验》课程实验报告
第 4 页共 24 页
sp_unbindrule 'person.Sex' droprule Sex_rule sp_help Sex_rule
1.2.311-30 实验代码及执行结果 Altertable person Addconstraint unique_pno_pname unique (P_no,P_name) sp_help unique_pno_pname
1.2.411-33 实验代码及执行结果 Insert orderdetail values('CX88','000009','000010','120000','2005-7-17')
update person set P_no='000010' where exists (select*from orderdetail where Order_no='cx2222' and person.P_no=orderdetail.P_no)
Delete orderdetail where order_no='AS0058'
2017-2018(1) 《数据库原理实验》课程实验报告
第 5 页共 24 页
实验二：SQL 更新语句
2.1 实验目的与内容 目的：update、delete、insert 语句的练习。 内容：11-6~8。 报告：以 11-6、11-8 作为实验二的报告。 2.2 实验代码及结果
2.2.1 11-6 实验代码及执行结果 Insert person values('000007',' 肖家庆',' 男 ','1963-07-14','1998-06-06',' 销售部 ',NULL) Insert person values('000008',' 李浩然',' 男 ','1975-01-30','2002-04-12',' 培训部 ','000005') Insert person values('000009',' 赵文龙',' 男 ','1969-04-20','1996-08-12',' 销售部 ','000007') Insert salary values('000001',2100,300) Insert salary values('000002',1800,300) Insert salary values('000003',2800,280) Insert salary values('000004',2500,250) Insert salary values('000005',2300,275) Insert salary values('000006',1750,130) Insert salary values('000007',2400,210) Insert salary values('000008',1800,235) Insert salary values('000009',2150,210) Insert person values('000001','林国平','男','1985-08-14','成都','0.85') Insert customer values('000002','林国平','男','1985-08-14','成都','0.85') Insert customer values('000003','郑洋','女','1973-04-07','成都','1.00') Insert customer values('000004','张雨洁','女','1983-09-06','北京','1.00') Insert customer values('000005','刘菁','女','1971-08-20','北京','0.95') Insert customer values('000007','顾培铭','男','1973-07-23','上海','1.00') Insert orderdetail values('AS0058','000006','000002','150000','2011-04-05') Insert orderdetail values('AS0043','000005','000005','90000','2011-03-25') Insert orderdetail values('AS0030','000003','000001','70000','2011-02-14') Insert orderdetail values('AS0012','000002','000005','85000','2010-11-11') Insert orderdetail values('AS0011','000007','000009','130000','2010-08-13') Insert orderdetail values('AS0008','000001','000007','43000','2010-06-06') Insert orderdetail values('AS0005','000001','000007','72000','2010-05-12') Insert orderdetail values('BU1167','000007','000003','110000','2010-03-08') Insert orderdetail values('BU1143','000004','000008','70000','2009-12-25') Insert orderdetail values('BU1139','000002','000005','90000','2009-10-12') Insert orderdetail values('BU1132','000006','000002','32000','2009-08-08') Insert orderdetail values('BU1121','000004','000006','66000','2009-04-01') Insert orderdetail values('CX2244','000007','000009','80000','2008-12-12') Insert orderdetail values('CX2232','000003','000001','35000','2008-09-18')
2017-2018(1) 《数据库原理实验》课程实验报告
第 6 页共 24 页
Insert orderdetail values('CX2225','000002','000003','90000','2008-05-02') Insert orderdetail values('CX2222','000001','000007','66000','2007-12-04')
2017-2018(1) 《数据库原理实验》课程实验报告
第 7 页共 24 页
2.2.211-8 实验代码及执行结果 Delete person whereP_no='000010'
2017-2018(1) 《数据库原理实验》课程实验报告
第 8 页共 24 页
实验三：SQL 查询语句
3.1 实验目的与内容 目的：select 语句中各种查询条件的实验。 内容：11-12~18。 报告：以 11-14、11-15、11-16、11-17 作为实验三的报告。 3.2 实验代码及结果
3.2.1 11-14 实验代码及执行结果 Select Deptname from salary join person on salary.P_no=person.P_no Groupby Deptname Having AVG(Bonus)>200 Orderby AVG(Bonus)DESC
Select count(*),sum(Order_total) From orderdetail,customer Where orderdetail.Cust_no=customer.Cust_no and City='上海'
3.2.211-15 实验代码及执行结果 Select p.P_no,count(*),SUM(Order_total) From orderdetailo,personp Where o.P_no=p.P_no and deptname='培训部' Groupby p.P_no
Select p.P_no,p.P_name From personp,personm Where p.P_boss=m.P_no and p.Date_hired<m.Date_hired
2017-2018(1) 《数据库原理实验》课程实验报告
第 9 页共 24 页
Select distinct c.Cust_no,Cust_name,Sex,Discount From customer c,orderdetail o1,orderdetail o2 Where c.Cust_no=o1.Cust_no and c.Cust_no=o2.Cust_no and o1.Order_no<>o2.Order_no
Select p1.P_no,p1.P_name,p1.Sex,p1.Deptname From person p1,person p2 Where p1.P_name=p2.P_name and p1.P_no<>p2.P_no
3.2.311-16 实验代码及执行结果 Select p.P_no,P_name,Fact From personp,salarys Where p.P_NO=s.p_no And s.fact>(selectfactfromsalarywhereP_no='000005')
Select order_no,cust_name,P_name,Order_total,Order_date From orderdetail o,person p,customer c Where o.Cust_no=c.Cust_no and o.p_no=p.P_no and order_total>(select avg(order_total) from orderdetail)
Select distinct p.P_no,P_name From orderdetail o,person p Where p.p_no=o.P_no and Cust_no in (select Cust_no from customer where city='成都')
2017-2018(1) 《数据库原理实验》课程实验报告
第 10 页共 24 页
selectCust_no,Cust_name,Sex,Discount fromcustomer whereCust_nonotin(selectdistinctcust_no fromorderdetail whereorder_date>=getdate()-365)
3.2.411-17 实验代码及执行结果 selectdistinctc.Cust_no,Cust_name,Sex,Discount fromcustomerc,orderdetailo1 wherec.Cust_no=o1.Cust_noand o1.cust_noin(selectcust_nofromorderdetailo2whereo1.order_no<>o2.order_no)
selectdistinctc.cust_no,cust_name,sex,discount fromcustomerc whereexists(select*fromorderdetail wherecust_no=c.cust_noandorder_total>100000 orexists(selectsum(order_total)fromorderdetailwhereCust_no=c.Cust_no havingsum(order_total)>200000))
2017-2018(1) 《数据库原理实验》课程实验报告
第 11 页共 24 页
2017-2018(1) 《数据库原理实验》课程实验报告
第 12 页共 24 页
实验四：视图及索引的建立和维护
4.1 实验目的与内容
目的：创建表的视图，修改和删除表的视图，并利用视图完成表的查询，创建表的索引、修改和删 除表的索引。 内容：11-3~5、11-9~11。 报告：以 11-3、11-4、11-10 作为实验四的报告。 4.2 实验代码及结果
4.2.1 11-3 实验代码及执行结果 createviewcustomreviewas selectcust_no,Cust_name,Sex,discountfromcustomerwherecity='北京)
因为没有一年内的结果所以改用七年内的 selectp2.p_no,p_name,sex,deptname,p3.uu frompersonp2, (selectp1.P_no,SUM(order_total)uu frompersonp1,orderdetailo wherep1.p_no=o.P_noandp_bossisnotnull andorder_date>=getdate()-365*7 groupbyp1.p_no)p3 wherep2.p_no=p3.p_no
4.2.211-4 实验代码及执行结果 createindexname_sortonperson(P_name)
createindexbirth_nameonperson(Birthdate,P_name)
2017-2018(1) 《数据库原理实验》课程实验报告
第 13 页共 24 页
createuniqueindexu_name_sortonperson(P_name) 消息1505，级别16，状态1，第1 行 CREATE UNIQUE INDEX 终止，因为发现了索引ID 9 的重复键。最重要的主键为'李浩然@。 因为列表里有两个李浩然重名，所以不能创建unique index createindexfact_idxonsalary(factdesc)
4.2.311-10 实验代码及执行结果 insertcustomerview(Cust_no,Cust_name,Sex)values ('000008','刘美萍','女')
2017-2018(1) 《数据库原理实验》课程实验报告
第 14 页共 24 页
实验五：存储过程及游标的建立和维护
5.1 实验目的与内容 目的：（1）创建用户的存储过程，修改和删除存储过程、执行存储过程。（2）定义并使用游标。 内容：11-22~24。 报告：以 11-22~24 作为实验五的报告。 5.2 实验代码及结果
5.2.1 11-22 实验代码及执行结果 createprocproc_age(@codeCHAR(6),@ageintoutput) as declare@birthvarchar(4),@todayvarchar(4) select@birth=datename(yy,BirthDate) fromperson whereP_no=@code select@today=datename(yy,getdate()) select@age=convert(int,@today)-convert(int,@birth)
5.2.211-23 实验代码及执行结果 declare@resultint execproc_age'000003',@resultoutput select@result
5.2.311-24 实验代码及执行结果
createprocproc_addbonus (@P_nochar(6),@adddec(5,1)output) as declare@Order_totalint declarecur_addbonus_checkscursorfor selectorder_total
2017-2018(1) 《数据库原理实验》课程实验报告
第 15 页共 24 页
fromorderdetail whereP_no=@P_no select@add=0 opencur_addbonus_checks fetchcur_addbonus_checksinto@order_total if (@@fetch_status<>0) begin closecur_addbonus_checks deallocatecur_addbonus_checks return end setnocounton while (@@fetch_status=0) begin if@order_total<=100000 set@add=@add+20 elseset@add=@add+@order_total/100000*30 fetchcur_addbonus_checksinto@order_total end closecur_addbonus_checks deallocatecur_addbonus_checks
declare@retdec(5,1) execproc_addbonus'000002',@add=@retoutput select@ret
2017-2018(1) 《数据库原理实验》课程实验报告
第 16 页共 24 页
实验六：触发器的建立和维护
6.1 实验目的与内容 目的：创建触发器，修改和删除触发器，测试触发器的效果。 内容：11-34。 报告：以 11-34 作为实验六的报告。 6.2 实验代码及结果
6.2.1 实验代码 (1) createtriggerchange_P onperson fordelete as if@@rowcount=0 return if@@rowcount!=1 rollback deletesalarywheresalary.p_no=(selectp_nofromdeleted) deletepersonwhereperson.p_no=(selectp_nofromdeleted) return (2) createTRIGGERCHECK_S ONsalary forupdate,insert as declare@num_rowsint select@num_rows=@@rowcount if@num_rows=0 return if (selectcount(*) frompersonp,insertedi wherep.p_no=i.p_no)!=@num_rows begin raiserror 53334 '' rollbacktransaction return end return (3) createTRIGGERCHECK_S ONsalary forupdate,insert as declare@num_rowsint select@num_rows=@@rowcount if@num_rows=0 return
2017-2018(1) 《数据库原理实验》课程实验报告
第 17 页共 24 页
if (selectcount(*) frompersonp,insertedi wherep.p_no=i.p_no)!=@num_rows begin raiserror 53334 '' rollbacktransaction return end return
6.2.2 实验结果 (1)
(2) USE[RJ2015] GO /****** Object: Trigger [U2015114466].[CHECK_S] Script Date: 11/20/2017 16:09:20 ******/ SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO ALTERTRIGGER[U2015114466].[CHECK_S] ON[U2015114466].[salary] forupdate,insert as declare@num_rowsint select@num_rows=@@rowcount if@num_rows=0 return if (selectcount(*) frompersonp,insertedi
2017-2018(1) 《数据库原理实验》课程实验报告
第 18 页共 24 页
wherep.p_no=i.p_no)!=@num_rows begin raiserror 53334 '' rollbacktransaction return end return (3) USE[RJ2015] GO /****** Object: Trigger [U2015114466].[CHECK_S] Script Date: 11/20/2017 16:09:20 ******/ SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO ALTERTRIGGER[U2015114466].[CHECK_S] ON[U2015114466].[salary] forupdate,insert as declare@num_rowsint select@num_rows=@@rowcount if@num_rows=0 return if (selectcount(*) frompersonp,insertedi wherep.p_no=i.p_no)!=@num_rows begin raiserror 53334 '' rollbacktransaction return end return
2017-2018(1) 《数据库原理实验》课程实验报告
第 19 页共 24 页
实验七：综合设计实验
7.1 实验目的、题目与实验项目 目的：综合练习 ER 图设计、ER 模型到关系模型转换、创建表、表的增删改，以及表的查询。 题目：分自拟题目与给定题目；如自拟，给分上将考虑放宽。 题目选择：自拟；给定。 注：如选自拟，则需填写：7.1.1、7.1.5、7.2、7.3、7.4、7.5 和 7.7，其它部分不用管也不必删；如 选给定，则需填写 7.2、7.3、7.4、7.5 和 7.6，其它部分不用管也不必删。 7.1.1 自拟题目
注：①如选自拟，可对照“7.1.2 给定题目”的形式，在此处描述自拟题目。 ②不能用第 3 章作业中的题目作为自拟题目。 ③至少有 6 个实体、5 个联系。 有 6 个实体：“职员（E）”，“发票（T）”，“销售记录（RS）”，“报销记录（RT）”，“财务记录（WR）”，“财 务职员（WE）”。“职员”的属性是身份证号职员 ID（EId），部门 ID（DId），姓名（ EName），职位（ EPosition）； “发票”的属性是发票 ID（TId），发票内容（TContent）；“销售记录”的属性是记录 ID（ＳId），销售 额（SNumber），日期时间（ＳDate），客户ＩＤ（ＣＩｄ）；“报销记录”的属性是记录ＩＤ（ＲＩｄ）， 职员ＩＤ（ＥＩｄ），报销金额（ＲＮｕｍｂｅｒ），日期时间（ＲＤａｔｅ）；“财务记录”的属性是记 录ＩＤ（ＷＩｄ），记录人员（ＷＰｅｒｓｏｎ），日期时间（ＷＤａｔｅ），内容（ＷＣｏｎｔｅｎｔ）； “财务专员”的属性是职员ＩＤ（ＷＥＩｄ）。职员产生发票，发票报销产生报销记录；财务职员会计审 核发票，财务职员可以预算和审核财务记录可以查看销售记录。
7.1.2 给定题目 有四个实体：“团长”（T），“代表团”（D），“运动员”（Y），“比赛项目”（B）。“团长”的属性是身 份证号（Tid），姓名（Tname），性别（Tgender），年龄（Tage），电话（Ttel）；“代表团”的属性有团编 号（Did），来自地区（Darea）， 住所（Dplace）；“运动员”的属性有编号（Yid），姓名（Yname），性别 （Ygender），年龄（Yage）；“比赛项目”的属性有项目编号（Bid），项目名（Bname），级别（Blevel）。 这些实体间的联系及它们的属性有：团长“管理”（G）代表团；代表团由运动员“组成”（Z）；运 动员“参加”（C）比赛项目。“参加”的属性是比赛时间（Ctime）和名次（Crank）。 7.1.3 通用实验项目
注：不论自拟还是给定，以下实验必做。 (1) 画出 ER 图，要求标注联系的约束类型和键约束。 (2) 将 ER 图转换为关系模型（用已给出的字母编码表示），要求标出各关系的主键。 (3) 用 SQL 创建各个关系表，并设置各表的主键约束和外键约束。 (4) 自己完成向各表插入数据、删除数据和修改数据的实验。 7.1.4 给定题目特定的查询
注：以下为针对给定题目特定的实验项目。如选给定题目，则必做；如选自拟则勿需做。 (5) 写出下列查询的 SQL 语句及执行后的结果： ①查询获得“男子”、“链球”、“ 冠军”的运动员编号及来自的地区。 ②检索获得“女子”、“乒乓球单打”、“冠军”的运动员编号及所在代表团的团长姓名。 ③查询“赵康”团长所管理代表团的运动员参加马拉松比赛的运动员姓名与名次。 ④查询“成都市”代表团的运动员“吴迪”参加的比赛项目名和比赛时间。
2017-2018(1) 《数据库原理实验》课程实验报告
第 20 页共 24 页
(6) 自拟至少两个查询，并写相应的 SQL 查询及执行结果！ 7.1.5 自拟题目的查询设定
注：如选自拟，则需先对照“7.1.4 针对给定题目特定的实验项目”(5)的形式，自己设定 4 个查询（尽量不要太简单， 否则得分上会失去优势）。然后，再完成这 4 个设定的查询。 写出下列查询的 SQL 语句及执行后的结果： ① 查询获得销售部的所有职员的 Id 和姓名 ② 查询一年内有报销记录的职员的 Id 和姓名 ③ 查询一年内各个客户的消费总额 ④ 查询报销记录总额超过 10000 元的职员 Id 和姓名
7.2 ER 图
职员E
发票T 财务记录WR
财务职员WE
EId 职员ID
Did 部门ID
EName 姓名
EPosition 职位
TId 发票ID
TContent 发票内容
SId 记录ＩＤ
ＳＮｕｍｂｒｅ 销售额
日期时间 ＳＤａｔｅ
记录ＩＤ
ＲＩｄ
报销金额 ＲＮｕｍｂｅｒ
日期时间 ＲＤａｔｅ
记录ＩＤ ＷＩｄ
记录人员 ＷＰｅｒｓｏｎ
日期时间 ＷＤａｔｅ
内容 ＷＣｏｎｔｅｎ ｔ
产生
１
１
职员ＩＤ ＷＥＩｄ
会计审核
１
ｎ
预算
审核
ｎ
１
ｎ
１
客户customer
客户Id CId
客户姓名 CName
性别 Sex
生日 birthdate
城市 City
折扣 Discount
销售 m n
报销
n
1
7.3 ER 模型向关系模型的转换结果 职员(职员 ID（EId），部门 ID（DId），姓名（EName），职位（EPosition）) 发票（发票 ID（TId），发票内容（TContent），职员 ID（EId），专员ＩＤ（WEId））职员 ID，专员 ID 是 外键 报销记录（记录 ID（RId），报销金额（RNumber），日期时间（RDate），发票 ID（TId），职员 ID（EId）） 发票ＩＤ,职员 ID 是外键 财务专员（专员 ID（WEId）） 财务记录（记录 ID（WId），记录人员（WPerson），日期时间（ WDate），内容（ WContent），专员 ID（WEId）） 专员 ID 为外键 销售记录（记录 ID（SId），销售额（SNumber），日期时间（SDate），客户 ID（CId），职员 ID（EId）） 职员 Id，客户 Id 为外键
2017-2018(1) 《数据库原理实验》课程实验报告
第 21 页共 24 页
客户（客户 Id（CId），客户姓名（CName），性别（Sex），生日（Birth），城市（ City），折扣（Discount））
7.4 各表的创建 Create table Employee (EId char(9) primary key, Did char(10) not null, EName char(10) not null, EPosition char(10) not null ) Create table Ticket ( TId char(9) primary key, TContent char(50) not null default'无', EId char(9) not null, WEId char(9) not null constraint EId_Check foreign key(EId) references Employee (EId) on DELETE CASCADE on update cascade, constraint WEId_Check foreign key (WEId) references WEmployee(WEId) on DELETE CASCADE on update cascade, )
Create table Record ( Rid char(9) primary key, RNumber int not null, RDate datetime not null, TId char(9) not null, Eid char(9) not null, Constraint TId_Check Foreign key(TId) References Ticket (TId) on delete cascade on update cascade, Constraint EId_Check Foreign key(EId) References Employee (EId) on delete cascade on update cascade,
2017-2018(1) 《数据库原理实验》课程实验报告
第 22 页共 24 页
) create table WEmployee ( WEId char(9) primary key ) create table Wealth ( WId char(9) Primary Key, WPerson char(10) not null, WDate datetime not null, WContent char(50) not null, WEId char(9) not null, foreign key(WEId) references WEmployee (WEId) on delete cascade on update cascade, ) Create table Sales ( SId char(9) Primary Key, SNumber int not null, SDate datetime not null, CId char(9) not null, Eid char(9) not null, Constraint CId_Check Foreign key(CId) References Customer (CId) on delete cascade on update cascade, Constraint EId_Check Foreign key(EId) References Employee (EId) on delete cascade on update cascade,
) Create table Customer (
CId char(9) Primary Key, CName char(9) not null, Sex char(1) not null, Birth datetime not null, City char(10) not null,
2017-2018(1) 《数据库原理实验》课程实验报告
第 23 页共 24 页
Discount float not null default 1.0, ) 7.5 各表数据的增删改实验 insert into Employee values('000000001','销售部','王虎','职员') insert into Employee values('000000002','销售部','李华','职员') insert into Employee values('000000003','销售部','高琴','助理') insert into Employee values('000000004','销售部','高品','主管') insert into Employee values('000000005','研发部','李浩','职员') insert into Employee values('000000006','研发部','王宇','职员') insert into Employee values('000000007','研发部','辛达','总管') insert into WEmployee values('000000003') insert Ticket values('100000001','医疗报销','000000001','000000003') insert Ticket values('100000002','出行报销','000000001','000000003') insert Ticket values('100000003','宴请报销','000000001','000000003') insert Ticket values('100000004','医疗报销','000000002','000000003') insert Ticket values('100000005','出行报销','000000004','000000003') insert Record values('200000001',2000,'2017-05-23','100000001', '000000001') insert Record values('200000002',3000,'2017-05-23','100000001', '000000001') insert Record values('200000003',1000,'2017-05-23','100000001','000000001') insert Record values('200000004',2000,'2017-05-23','100000002','000000001') insert Record values('200000005',2000,'2017-05-23','100000004', '000000002') insert Record values('200000006',2000,'2017-05-23','100000005', '000000004') delete Record where RId='200000001' insert Wealth values('300000001','Peter','2017-08-19','IPO风投','000000003') insert Sales values('400000001',200000,'2017-08-23','500000001', '000000001') insert Sales values('400000002',100000,'2017-08-23','500000001', '000000001') insert Sales values('400000003',300000,'2017-08-23','500000003', '000000002') insert Customer values ('500000001',’Peter’,’男’,’1997-02-07’,’南京’,1.0) insert Customer values ('500000003',’Wendy,’女’,’1997-02-07’,’上海’,1.0)
7.6 给定题目的特定查询
7.6.1 表的查询及执行结果（第 5 个问题的 4 个查询）
7.6.2 自拟表的查询及执行结果（第 6 个问题的 2 个查询）
注：每位同学的查询均不得相同，否则以“抄袭”论处。
(1) select EId,EName from Employee where Did='销售部' and EPosition='职员'
2017-2018(1) 《数据库原理实验》课程实验报告
第 24 页共 24 页
(2) select distinct e.EId,e.EName from Employee e,Record r where r.RDate>=getdate()-365 and r.EId=e.EId
(3) select CId,sum(SNumber) from Sales s where s.SDate>=getdate()-365 group by Cid
(4) select e.EId,e.EName from Employee e,(select EId,sum(RNumber)as ans from Record where RDate>=getdate()-365 group by EId having sum(RNumber)>4000) x where x.Eid=e.Tid
4285
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
