1、(1)创建表时将orderdetail的Cust_no列和P_no列定义为外键,并分别参考表customer的列Cust_no和表person的列P_nocreate table orderdetail20082333(Order_no char(6) not null primary keyconstraint Order_no_constraintcheck (Order_no like A-ZA-Z0-90-9),Cust_no char(6) not null,P_no char(6) not null,Order_total int not null,Order_date datet
2、ime not null,constraint person_contrforeign key(P_no)references person20082333(P_no)on delete cascadeon update cascade,constraint customer_contrforeign key(Cust_no)references customer20082333(Cust_no)on update cascade)(2)将salary表中的P_no设为外键,并使其参照表person中的P_noalter table salary20082333add constraint p
3、_no_FK foreign key(p_no) references person20082333(p_no)实验二:SQL更新语句update、delete、insert 语句的练习。11-68。以11-7、11-8作为实验二报告的典型。11-7(1)将salary表中工号为000006的员工工资增加为1800,奖金增加为160UPDATE salary20082333 set Base=1800,Bonus=160 where p_no=000006(2)利用SQL语句将两年内没有签订单的员工奖金下调25%。UPDATE salary20082333 set Bonus=bonus*.7
4、5 where not exists (select * from orderdetail20082333 where salary20082333.p_no=orderdetail20082333.p_no and order_date=getdate()-730)11-8删除person表中工号为000001的员工数据。delete from person20082333 where p_no=000001实验三:SQL查询语句select语句中各种查询条件的实验。11-1218。以11-13、11-14作为实验三报告的典型。11-13(1)查询person表中所有不重复的部门select
5、 distinct Deptname from person20082333(2)查询person表中部门女经理的数据select * from person20082333 where p_boss is null and sex=女(3)查询person表中姓名为林峰、谢志文和罗向东的员工数据select * from person20082333 where p_name in(林峰,谢志文罗向东(4)利用SQL语句将工号为000003000008的员工的月收入按实发工资升序排序select * from salary20082333 where p_no between 000003
6、and 000008order by Fact ASC(5)查询工号为000002的员工基本工资增加2倍,奖金增加1.5倍后的实际收入。select p_no 工号,2*base+1.5*bonus 实际收入 from salary20082333 where p_no=00000211-14(1)利用SQL语句查询一月份发放奖金平均数大于180元的部门,并从高到低排序。select Deptname 部门,avg(bonus) 平均奖金FROM salary20082333 A join person20082333 B on A.p_no=B.p_nogroup by deptname h
7、aving avg(bonus)180 order by avg(bonus) desc(2)查询居住城市在上海的顾客订单总数和订单总额。select count(*) 订单总数,sum(order_total) 订单总额from orderdetail20082333,customer20082333where orderdetail20082333.Cust_no=customer20082333.Cust_no and City=上海实验四:视图及索引的建立和维护创建表的视图,修改和删除表的视图,并利用视图完成表的查询,创建表的索引、修改和删除表的索引。11-35、11-911。以11-
8、3、11-4、11-9作为实验四报告的典型。11-3 (1)在表customer上创建“顾客”视图CustomerView,其中包括居住在北京的顾客的基本信息,并显示“顾客号”、“姓名”、“性别”和“购买折扣”等字段create view CustomerView AS select Cust_no,Cust_name,Sex,Discount from customer20082333 where City=北京 (2)基于表customer和表orderdetail创建“培训员工”视图TrainingView,其中包括培训部所有员工(不含部门经理)的员工号、姓名、性别、所属部门和最近一年内
9、的总销售业绩create view TrainingView AS select person20082333.p_no,p_name,Sex,Deptname,SUM(order_total) AS Achievement from person20082333,orderdetail20082333 where person20082333.p_no=orderdetail20082333.p_no and deptname=培训部 and p_boss is not null and order_date=getdate()-365 group by person20082333.p_n
10、o,p_name,Sex,Deptname11-4 (1)在“人员”表的“姓名”列上创建一个单列索引name_sortcreate index name_sort on person20082333(p_name) (2)在“人员”表的“出生日期”列和姓名列上创建一个组合索引birth_name create index birth_name on person20082333(birthdate,p_name) (3)在“人员”表的“姓名”列上创建一个唯一索引u_name_sort create unique index u_name_sort on person20082333(p_nam
11、e) (4)“月薪”表的“实发”列上创建一个聚簇索引fact_idx,并使系统按降序索引create clustered index fact_idx on salary20082333(Fact DESC)11-9 将“顾客”视图CustomerView中姓名为“刘菁”的顾客的购买折扣改为0.85update CustomerView set discount=0.85 where cust_name=刘菁实验五:存储过程的建立和维护创建用户的存储过程,修改和删除存储过程、执行存储过程。11-2224。以11-24作为实验五报告的典型。创建使用游标的存储过程要求:根据各员工在orderdet
12、ail表中的销售业绩计算其总的奖金增额;员工每签订一份订单额小于100000的订单,其奖金增额为20;若订单额高于100000,则奖金增额为(order_total/100000*30)create proc proc proc_addbonus(p_no char(6),add dec(5,1) output)AS declare order_total int declare cur_addbonus_checks from orderdetail20082333 where p_no=p_no select add=0 open cur_addbonus_checks fetch cur
13、_addbonus_checks into order_tatal if(fetch_status0) begin close cur_addbonus_checks deallocate cur_addbonus_checks return end set nocount on while(fetch_status=0) if order_total=100000 set add=add+20 else set add=add+order_total/100000*30 fetch cur_addbonus_checks into order_totalreturn实验六:触发器的建立和维护
14、创建触发器,修改和删除触发器,测试触发器的效果。11-34。以11-34作为实验六的报告典型。创建DELETE类型、UPDATE类型及INSERT类型的触发器并测试其作用(1)在person表上创建一个触发器,并删除表person中的员工信息时,级联删除表salary中该员工的信息 创建触发器create trigger delete20082333on person20082333after deleteasif rowcount=0 return delete salary20082333 from salary20082333 t,deleted d where t.p_no=d.p_n
15、o 删除person表中的员工信息delete from person20082333 where p_no=000003 此时salary表中显示为 (2)在salary表上创建一个触发器,检查在修改该表时是否有不存在于person表中的职工代码出现create trigger update20082333on salary20082333for update,insertdeclare num_rows intselect num_rows=rowcountif num_rows=0 returnif (select count (*) from person20082333 p,inse
16、rted i where p.p_no=i.p_no)!=num_rowsbegin raiserror 53334 试图插入或修改非法的p_no值到salary表中 rollback transaction end 满足条件时,成功插入insert into salary20082333 values(2100300 不满足条件时,提示出错(下面两句分别是插入、修改)2800280update salary20082333 set p_no=011 where p_no=01 (3)在salary表上创建一个触发器,向该表插入数据时必须参考表person中的P_no创建触发器实验七:函数创建
17、与调用创建函数,调用函数。11-20、11-21。以11-20、11-21作为实验七报告的典型。11-20创建函数创建一个函数Check_Pno,检测给定的员工号是否存在,如果存在返回0,否则返回-1create function Check_Pno20082333(p_no char(6)returns integer asbegin declare num int if exists (select p_no from person20082333 where p_no=p_no) select num=0 else select num=-1 return numend11-21调用函数调用函数Check_Pno,如果返回0,则向salary表中插入一行该员工的工资记录declare num intexec num=Check_Pno20082333 000007if num=0 insert salary20082333 values(,2200,280)插入的P_no在person表中存在,在salary表中不存在的时候,就会在salary表中添加进数据
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1