[图文]考试

奥帕文库
当前位置:首页 » 考试 » 正文

[图文]考试

大学,钢笔,教室,教育,考试,铅笔,人,手写,书房,书桌,水平构图,速记
大学,钢笔,教室,教育,考试,铅笔,人,手写,书房,书桌,水平构图,速记

2.在销售主表 sales 中添加字段 “发票号码” invoice_no, char(10), NOT NULL。

alter table sales add invoice_no char(10) alter table sales alter column invoice_no char(10) not null3.添加外键约束: a) 在销售主表 sales 的业务员编号字段 sale_id 上添加外键约束,参照字段 为员工表 employee 中的字段员工编号 emp_no,约束名为 FK_sale_id。

b) 在销售主表 sales 的客户号字段 cust_id 上添加外键约束,参照字段为客 户表 customer 中的字段客户号 cust_id,约束名为 FK_cust_id。

c) 在销售明细表 sale_item 的订单编号字段 order_no 上添加外键约束, 参照 字 段 为 销 售 主 表 sales 中 的 字 段 订 单 编 号 order_no , 约 束 名 为 FK_order_no。

d) 在销售明细表 sale_item 的产品编号字段 prod_id 上添加外键约束,参照 字 段 为 产 品 名 称 表 product 中 的 产 品 编 号 字 段 prod_id , 约 束 名 为 FK_prod_id。

4.添加核查约束: a) 将员工表 employee 中的薪水字段 salary 的值限定在 1000 至 10000 间,约 束名为 CK_salary。

alter table employee add constraint CK_salary check(salary between 1000 and 10000)b) 将员工表 employee 中的员工编号字段 emp_no 设定为以“E”字母开头, 后 面跟 4 位数的编号,约束名为 CK_emp_no。

alter table employee add constraint CK_emp_no check(emp_no like 'E____') c) 将员工表 employee 中的性别字段设定这取值只能是“男”和“女”。

约束名 为 CK_sex。

alter table employee add constraint CK_sex check(sex in ('男','女'))d) 将销售主表 sales 中的发票号码字段 invoice_no 设定为以“I”字母开头, 后

面跟 9 位数的编号,约束名为 CK_inno。

alter table sales add constraint CK_inno check (invoice_no like 'I_________') 5.为销售主表 sales 中的字段发票号码 invoice_no 设置为唯一约束,约束名为 UN_inno。

alter table sales add constraint UN_inno unique(invoice_no)1.查找表中所有姓刘的职工的工号,姓名,部门,薪水 select emp_no,emp_name,dept,salary from employee where emp_name like '刘%' 2.查找所有定单金额高于 20000 的所有客户编号 select cust_id from sales where tot_amt>20000 3.查找业务部或会计部的女员工的基本信息。

select emp_no,emp_name,dept from employee where sex='女' and dept in ('业务','会计') 4.选取编号界于„C0001‟和„C0004‟的客户编号、客户名称、客户地址。

select cust_id,cust_name,addr from customer where cust_id between 'C0001' AND 'C0004' 5.选取销售数量最多的前 5 条订单订单号、数量。

select top 5 order_no,qty from sale_item order by qty desc

1、模糊查询 create procedure proc_empname @E_name varchar(10) as select a.emp_name,a.dept,b.tot_amt from employee a inner join sales b on a.emp_no=b.sale_id where a.emp_name like @E_name go exec proc_empname '陈%' 2、利用存储过程计算出‟E0014‟业务员的销售总金额。

create procedure proc_saletot @E_no char(5), @p_tot int as select @p_tot=sum(tot_amt) from sales where sale_id=@E_no go declare @tot_amt int exec proc_saletot E0014, @tot_amt output select @tot_amt 3、创建一带返回值的存储过程,返回某一部门的平均工资 create proc proc_avg_salary @Dept char(4) as declare @avg_salary int select @avg_salary=avg(salary) from employee where dept=@Dept return @avg_salary declare @avg int exec @avg=proc_avg_salary '人事' print '返回值='+cast(@avg as char(10))output

1、利用存储过程,给 employee 表添加一条业务部门员工的信息。

create proc emp_in ( @emp_no char(5) , @emp_name varchar(10) , @sex char(2) , @dept varchar(10) , @title varchar(10), y datetime , @salary int, @telephone varchar(20) , @addr varchar(50) ) as insert into employee values(@emp_no ,@emp_name , @sex , @dept ,@title ,@date_hired ,@birthday , @salary , @telephone,@addr) //执行存储过程 exec emp_in 'E10015','大王','女','计算机系','学生 ','1991-09-05','1998-12-19','5000','18974554130','珊瑚海' 2、利用存储过程从 employee、sales、customer 表的连接中返回所有业务员的姓 名、客户姓名、销售金额。

create proc find as select emp_name,cust_name,tot_amt from employee ,sales, customer where sales.sale_id=employee.emp_no and sales.cust_id = customer.cust_id // 执 行存储过程 exec find 3、创建带一个输入参数的存储过程,实现按员工姓名进行模糊查找,查找员工 编号、订单编号、销售金额。

create proc mohu (@emp_name varchar(10) ) as select emp_no, order_no , tot_amt from employee , sales where employee.emp_no = sales.sale_id and (employee.emp_name like @emp_na me) //执行存储过程 declare @emp_name exec mohu @emp_name='刘%' 4、创建带两个输入参数的存储过程,查找姓“李”并且职称为“职员”的员工的员 工编号、订单编号、销售金额。

create proc find1( @emp_name varchar(10),@title varchar(10) ) as select emp_no,order_no,tot_amt from employee , sales where employee.emp_no = sales.sale_id and (emp_name like @emp_name)and (t @date_hired datetime , @birthda

itle=@title) //执行存储过程 exec find1 @emp_name='刘%',@title='职员' 3、利用存储过程计算出订单编号为 10003 的订单的销售金额。

(带一输入参数和 一输出参数) (提示: sales 表中的 tot_amt 应该等于 sale_item 表中的同一张订 单的不同销售产品的 qty*unit_price 之和) create proc sum1 (@order_no int ,@summoney int output) as select @summoney=SUM(tot_amt) from sales where @order_no=order_n //执行存储过程 declare @summoney int exec sum1 10003 ,@summoney output select @summoney 4、创建一存储过程,根据给出的职称,返回该职称的所有员工的平均工资。

(带 一输入参数和返回值)create proc avg_salary @title char(4) as declare @avg_salary int select @avg_salary=avg(salary) from employee where title=@title return @avg_salary // 执 行存储过程 declare @avg int exec @avg=avg_salary '职员' print '返回值='+cast(@avg as char(10))1、写一个允许用户一次只删除一条员工记录的触发器。

create trigger tr_emp on employee for delete as declare @row_cnt int select @row_cnt=count(*) from deleted if @row_cnt>1 begin print '删除操作不允许删除多条人事表数据!!!' rollback transaction end delete from employee where sex='女'--显示'删除操作不允许删除多条人事表数据!!!'

2、创建一个触发器,只能接受女员工 create trigger employee_insert2 on employee after insert as if exists(select * from inserted where sex='男') begin print '本公司不接受男员工!' rollback transaction end else print '欢迎加入本公司!' insert employee values('E0021',' 林 中 正 ',' 男 ',' 管 理 ',' 总 经 理 ','1974-10-01','1953-05-04',100000,'13879765667',' 上海市 ') -- 显示 ' 本公司不接受 男员工!' 3、定义一触发器,保证新添加的员工的工资不能超过 5000 元 create trigger employee_insert on employee after insert as if(select salary from inserted)>5000 begin print '新添加的员工的工资不能超过 5000 元!' rollback end 4、在 customer 表上创建一触发器,用来实现级联删除、 、 。

create trigger customer_del_upd on customer instead of delete as delete sales where cust_id in(select cust_id from deleted) delete customer where cust_id in(select cust_id from deleted)1、针对 employee 表写一个 DELETE 触发器,显示删除的员工人数。

create trigger emp_delete on employee for delete as declare @num int set @num=0 select @num=count(*) from deleted print '删除的人数为:'+cast(@num as char(10))delete from employee where sex='男'drop trigger emp_delete

2、针对 employee 表写一个 UPDATE 触发器,限制每次工资额的变动不能超过 原工资的 20%。

CREATE TRIGGER tr_up ON employee AFTER update AS declare @new_salary float,@old_salary float select @old_salary=salary from deleted select @new_salary=salary from inserted if ABS(@new_salary-@old_salary)>@old_salary*0.2 BEGIN print'每次工资额的变动不能超过原工资的%' rollback transaction END GO3、定义一个触发器,保证新添加的员工的工资不能超过 5000 元create trigger upwy_emp on employee for insert as if((select salary from inserted)>5000) begin print '新添加的员工的工资不能超过 5000 元' rollback end insert employee values('E0028',' 杨 子 ',' 男 ','1980-12-1','1950-1-1',4600,'07345123596','昆明市') insert employee values('E0029',' 陆 毅 ',' 男 ','1980-12-2','1950-12-2',6200,'07343120496','上海市')',' ','业 业务 务',' ','职 职员 员

4、对 sale_item 表创建一个触发器,当插入一条销售明细记录时,如果该记录的 产品数量超过 5,则显示“欢迎成为本公司的 VIP 会员!” create trigger ins_sait on sale_item for insert as if((select qty from inserted)>5) begin print '欢迎成为本公司的 VIP 会员!' rollbackinsert sale_item values('1004','P0008','6','10000','1996-11-10') 5、针对 customer 表,定义一触发器用来保证参照完整性 create trigger del_cus on customer instead of delete as delete customer where cust_id in(select cust_id from deleted) delete sales where cust_ id in(select cust_id from deleted) delete customer where cust_name='客户乙' select * from customer select * from sales 6、针对 sales 表,定义一触发器保证参照完整性(参照 customer 表) create trigger unin_sales on sales instead of insert as if((select count(*) from customer where cust_id in(select cust_id from inserted))<0) b egin print'不允许向 sales 表中插入 customer 表中不存在的客户' rollback insert sales values (10002,'C0556','E0013',22700.00,'1996-11-10','I000000002') drop trigger unin_sales 5、针对 employee 表,定义一触发器用来保证实体完整性 create trigger unaf_emp on employee for update as declare @emp_id_i char declare @emp_id_d char select @emp_id_i=emp_no from inserted select @emp_id_d=emp_no from deleted if @emp_id_i<>@emp_id_d begin print'不能修改 employee 表中的 emp_no 列' rollbackupdate employee set emp_no='45645' where emp_no='E0008