数据库程序设计与数据库编程技术练习题


数据库程序设计与数据库编程技术练习题

1、 用IF….ELSE语句实现下面功能:在员工表中,如果存在工资大于等于4000的员工,
–则输出该员工的员工号、员工名、工资、部门号和部门名。否则输出没有满足条件的员工。

if exists(select * from Employee where Wages>=4000)
select EmplID,EmplName,Wages,Department.DeptID, DeptName 
from Employee ,Department
where Employee.DeptID=Department.DeptID and Wages>=4000
else
print('没有满足条件的员工!')

2、 用while语句实现下面功能:计算1+2+3+4的值。

declare @a int,@sum int
set @a=1
set @sum=0
while(@a<=4)
begin
set @sum=@sum+@a
set @a=@a+1
end
print @sum

3.查询Employee中所有员工的员工号,姓名,年龄。

select EmplID,EmplName,year(GETDATE())-YEAR(Birthday)as '年龄' 
from Employee

4.输出字符串“abc”中每一个字符对应的ASCII值。

declare @i int
set @i=1
while(@i<=len('abc'))
begin
print ascii(SUBSTRING('abc',@i,1))
set @i=@i+1
end

5.创建一个带两个参数的存储过程,一个参数接受传递过来的员工号,
–一个参数返回该员工对应的工资。
–调用存储过程时,传递一个员工号,调用完成后能输出该员工的工资。

create procedure abc @a char(4),@b money output
as
begin
set @b=(select Wages from dbo.Employee where EmplID=@a)
end

调用存储过程:

declare @b money
exec  abc 'E001',@b output
print @b

用一种参数的写法:

create procedure abc1 @a char(4)
as
begin
(select Wages from dbo.Employee where EmplID=@a)
end

exec  abc1 'E001'#调用

6.修改第1题存储过程,用来完成根据员工号计算输出年龄。

alter  procedure abc @a char(4)
as
begin
(select 2020-year(Birthday) from dbo.Employee where EmplID=@a)
end

7.删除第1题的存储过程。

drop procedure abc

8.在StoreSales 数据库的Employee表上创建一个INSERT触发器T_Ins,向Employee表插入数据时,
–如果员工号已存在,则禁止插入,并回滚到插入操作前。

CREATE TRIGGER T_Ins                /* 创建INSERT触发器T_Ins */
  ON Employee
AFTER INSERT
AS
BEGIN
  DECLARE @ID char(4) 
SELECT @ID=EmplID FROM inserted
  IF EXISTS(SELECT EmplID FROM Employee WHERE EmplID=@ID)
    BEGIN 
      PRINT '该员工号已存在,禁止插入'
      ROLLBACK TRANSACTION      /* 回滚到插入操作之前的状态 */
    END 
END

INSERT INTO Employee VALUES('E006','李11','男','1995-04-26',NULL ,3000,'D001')

select * from Employee

delete from Employee
where EmplID='E006'

9.在StoreSales 数据库的Employee表上创建一个UPDATE触发器T_Upd,
–防止用户修改Employee表的员工号。

CREATE TRIGGER T_Upd            /* 创建UPDATE触发器T_Upd */
  ON Employee
AFTER UPDATE
AS
IF UPDATE(EmplID)
   BEGIN 
     PRINT '不能修改员工号'
     ROLLBACK TRANSACTION      /* 回滚到修改操作之前的状态 */
   END 
GO

update dbo.Employee
set EmplID='E008'
where EmplName='刘强'

10.在StoreSales 数据库的Employee表上创建一个DELETE触发器T_Del,
–防止用户删除Employee表的记录。

CREATE TRIGGER T_Del                 /* 创建DELETE触发器T_Del */
  ON Employee
AFTER DELETE
AS
  IF EXISTS(SELECT * FROM deleted )
    BEGIN   
      PRINT '不能删除Employee表的记录'
      ROLLBACK TRANSACTION          /* 回滚到删除操作之前的状态 */
    END 
GO

delete from Employee
where  EmplName='刘强'

11.删除触发器T_Ins、T_Upd和T_Del

drop trigger T_Ins,T_Upd,T_Del

文章作者: Cy_Dragon
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 Cy_Dragon !
评论
  目录