数据库综合习题练习

数据库CompanySystemDB 中需要两个数据表，如下所示。按要求完成9个小题。

部门表：department

字段名	数据类型	约束	含义
DNO	char(4)	主键	部门编号
DNAME	varchar(20)		部门名
PHONE	varchar(20)		电话

员工表：staff

字段名	数据类型	约束	含义
SNO	char(4)	主键	员工编号
SNAME	varchar(20)		员工姓名
DNO	char(4)	外键，参照部门表的DNO	部门号
SAGE	tinyint		年龄
SDATE	date		入职日期

1写出创建staff表的SQL语句。

create table staff 
(
    SNO char(4) constraint S_Prim primary key;
    SNAME varchar(20);
    DNO char(4) comstraint S_fore foreign key references department(DNO);
    SAGE tinyint;
    SDATE date;
)

2、编写SQL语句，实现查询要求

（1）查询年龄在50岁以上的员工的员工号和姓名

select SNO,SNAME from staff where SAGE >50

（2）查询员工“张三”所在的部门名称

select DNAME from department,staff 
where SNAME='张三' and staff.DNO=department.DNO

3、编写SQL语句，将“人事部”的员工全部删除

delete from staff 
where DNO = (select DNO from department where DNAME='人事部')

4、编写SQL命令，创建视图VIEW1，查看每个部门的员工的平均年龄，标题为（部门号，平均年龄）

create view VIEW1(部门号,平均年龄)
as select DNO,AVG(SAGE) from staff 
group by DNO

5、编写SQL命令，为department表的 dname 字段创建唯一索引IX_DNAME

create unique index IX_DNAME on department(dname)

6、创建规则age_rule1,数值大于18，将规则绑定到员工表的sage字段上

create rule age_rule1 as @SAGE>18
exec sp_bindrule 'age_rule1','staff.SAGE'

7、创建函数F1,根据部门号，求部门名称，并将部门名称作为返回值。

create function F1(@Dno char(4))
returns varchar(20)
as 
begin
    declare @name varchar(20)
    select @name=DNAME from department where DNO=@Dno
    return @name
end

8、创建存储过程P1，根据部门号，删除该部门在department表中记录，如果没有这个部门，则显示“没有找到相关数据”。写一条调用存储过程P1的语句。

create procedure P1(@Dno char(4))
as 
begin
    if exists(select * from department where DNO=@Dno)
        delete from department where DNO=@Dno
    else
        print '没有找到相关数据'
end

/*调用*/
exec P1 @Dno='0001'

9、不允许删除部门信息，如执行delete命令，则显示‘不能删除部门信息’。创建触发器TR1实现这个功能。

create trigger TR1 on department
instead of delete
as
    print '不能删除部门信息'