SQL SERVER數(shù)據(jù)庫綜合練習(xí)題答案

1、1、創(chuàng)建數(shù)據(jù)庫，數(shù)據(jù)庫名稱為userdb。如果數(shù)據(jù)庫userdb已存在，那么，先刪除再創(chuàng)建。userdb的存放路徑是c:data。創(chuàng)建數(shù)據(jù)文件，主邏輯名稱為userdb,物理文件名為userdb_data.mdf，初始大小為5mb，最大為30mb,增長為5%。創(chuàng)建日志文件為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、使用存儲過程查看數(shù)據(jù)庫userdb信息；sp_helpdb userdb3、刪除數(shù)據(jù)庫userdb； drop database userdb4、在userdb數(shù)據(jù)庫中按照要求創(chuàng)建以下數(shù)據(jù)表：部門表 departments字段類型名稱約束departmentid

3、int部門編號初始值為1，該字段自增，該字段為主鍵departmentnamevarchar(50)系部名稱唯一且非空notevarchar(200)部門介紹可以為空 雇員表 employees字段類型名稱約束employeeidint雇員編號初始值為1，該字段自增，該字段為主鍵departmentidint部門編號可以為空，該字段為外鍵namevarchar(20)姓名非空ageint年齡非空，年齡大于零sexchar(2)性別非空edatedatetime入職日期可以為空 員工薪水表 salary 字段類型名稱約束employeeidint雇員編號非空，該字段為主鍵incomemoney收

4、入可以為空outcomemoney支出可以為空分別創(chuàng)建三個表的結(jié)構(gòu)，如果已存在，則先刪除再創(chuàng)建該表。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、為上面的數(shù)據(jù)表添加以下約束： （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字段添加默認(rèn)約束，默認(rèn)sex為男alter table employees add constraint df_employees_sex default 男 for sex（5）為employees表edate字段添加默認(rèn)約束，默認(rèn)為當(dāng)天日期alter table employees add constraint df_employees_edat

8、e default getdate() for edate6、修改表employee中列name的數(shù)據(jù)類型為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(生產(chǎn)部, 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, 李強(qiáng),48,男, 2005-03-10);insert into employees(departmentid,name,age,sex,edate) values(2, 張揚(yáng),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（工資）的數(shù)據(jù)，為employeesid等于1的雇員增加500元工資。update salary set income=income+500 where employeeid=111、刪除雇員表中工號為5的人員記錄delet