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

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=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字段类型名称约束DepartmentIDint部门编号初始值为1，该字段自增，该字段为主键DepartmentNamevarchar(50)系部名称唯一且非空Notevarchar(200)部门介绍可以为空 雇员表 Employees字段类型名称约束EmployeeIDint雇员编号初始值为1，该字段自增，该字段为主键DepartmentIDint部门编号可以为空，该字段为外键Namevarchar(20)姓名非空Ageint年龄非空，年龄大于零SexChar(2)性别非空EdateDatetime入职日期可以为空 员工薪水表 Salary 字段类型名称约束EmployeeIDint雇员编号非空，该字段为主键IncomeMoney收入可以为空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), Edate 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 Employees 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(DepartmentID)（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_edate 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,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, 李红,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 VALUES(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