SQL SERVER数据库综合练习题答案

1、1、创建数据库，数据库名称为userdb。如果数据库userdb已存在，那么，先删除再创建。userdb的存放路径是c:data。创建数据文件，主逻辑名称为userdb,物理文件名为userdb_data.mdf，初始大小为5mb，最大为30mb,增长为5%。创建日志文件为userdblog，物理名称为userdb_log.ldf,文件大小为5mb，最大为20mb，文件增长为5mb； if exists(select * from sysdatabases where name=userdb)drop database userdbgocreate database userdbon(name

2、=userdb,filename=c:datauserdb_data.mdf,size=5mb,maxsize=30mb,filegrowth=5%)log on (name=userdblog,filename=c:datauserdb_log.ldf,size=5mb,maxsize=20mb,filegrowth=5mb)go2、使用存储过程查看数据库userdb信息；sp_helpdb userdb3、删除数据库userdb； drop database userdb4、在userdb数据库中按照要求创建以下数据表：部门表 departments字段类型名称约束departmentid

3、int部门编号初始值为1，该字段自增，该字段为主键departmentnamevarchar(50)系部名称唯一且非空notevarchar(200)部门介绍可以为空 雇员表 employees字段类型名称约束employeeidint雇员编号初始值为1，该字段自增，该字段为主键departmentidint部门编号可以为空，该字段为外键namevarchar(20)姓名非空ageint年龄非空，年龄大于零sexchar(2)性别非空edatedatetime入职日期可以为空 员工薪水表 salary 字段类型名称约束employeeidint雇员编号非空，该字段为主键incomemoney收

4、入可以为空outcomemoney支出可以为空分别创建三个表的结构，如果已存在，则先删除再创建该表。create table departments ( departmentid int identity(1,1), departmentname varchar(50) unique not null, note varchar(200);create table employees( employeeid int identity(1,1), departmentid int, name char(10) unique not null, age int, sex char(2), edat

5、e datetime );create table salary ( employeeid int not null, income money, outcome money );5、为上面的数据表添加以下约束： （1）为departments、employees、salary表添加三个主键约束，约束名分别为pk_departments_id,pk_employees_id,pk_salary_idalter table departments add constraint pk_departments_id primary key(departmentid)alter table emplo

6、yees add constraint pk_employees_id primary key(employeeid)alter table salary add constraint pk_salary_id primary key(employeeid)（2）为employees表的departmentid字段添加外键约束，依赖于departments表的departmentidalter table employees add constraint fk_employees foreign key(departmentid) references departments(departme

7、ntid)（3）为employees表age字段添加check约束，检查age大于零alter table employees add constraint ck_employees_age check(age0)（4）为employees表sex字段添加默认约束，默认sex为男alter table employees add constraint df_employees_sex default 男 for sex（5）为employees表edate字段添加默认约束，默认为当天日期alter table employees add constraint df_employees_edat

8、e default getdate() for edate6、修改表employee中列name的数据类型为varchar(30)。alter table employees alter colunm name varchar(30)7、修改表employee，在表中增加新列zip，类型为char（6）。alter table employees add zip char(6)8、从部门表employee中删除列zip。alter table employees drop column zip 9、向表中插入如下记录insert into departments(departmentname,

9、note) values(销售部, null);insert into departments(departmentname,note) values(采购部, null);insert into departments(departmentname,note) values(生产部, null);insert into employees(departmentid,name,age,sex,edate) values(1, 王林,35,男, 2000-11-10);insert into employees(departmentid,name,age,sex,edate) values(1,

10、 李红,46,女, 1995-10-11);insert into employees(departmentid,name,age,sex,edate) values(1, 李强,48,男, 2005-03-10);insert into employees(departmentid,name,age,sex,edate) values(2, 张扬,23,女, 2010-05-10);insert into employees(departmentid,name,age,sex,edate) values(2, 赵明,50,男, 2003-06-10);insert into salary v

11、alues(1,3500.00, 800.00);insert into salary values(2,4000.00, 1200.00);insert into salary values(3,7000.00, 2500.00);insert into salary values(4,1300.00, 1200.00);insert into salary values(5,5500.00, 2500.00);10、修改表salary中的列income（工资）的数据，为employeesid等于1的雇员增加500元工资。update salary set income=income+500 where employeeid=111、删除雇员表中工号为5的人员记录delet