习题(五)

（1）      使用下表提供的值，创建数据库ClassNorthwind

参            数	值
数据库名称	ClassNorthwind_Data
数据库文件名	ClassNorthwind_Data.mdf
位置	d:\data
初始大小	25MB
文件组	Primary
文件增长	1MB
最大文件长度	100MB
日志名称	ClassNorthwind_Log
事务日志文件名称	ClassNorthwind_Log.ldf
位置	d:\data
初始大小	15MB
文件增长	1MB
最大文件长度	30MB

 （2）使用sp_helpdb存储过程浏览有关数据库ClassNorthwind的信息 

实践2：创建和修改SQL Server数据库的表

（1）      创建数据库ClassNorthwind中的雇员表Employees、雇员工资表Wage和工资税表Tax

雇员表Employees:

字  段  名  称	类      型	空      否
EmployeeID	Int，标识列,初值为1，增量为1
Name	VarChar(10)	NOT NULL
Sex	Char(2)	NOT NULL
BirthDate	Datetime	NULL
Address	Varchar(50)	NULL
Phone	Char(13)	NULL
Remark	Varchar(500)

 

雇员工资表Wage:

字  段  名  称	类      型	空      否
WageID	Int，标识值 ,初值为1，增量为1	主键
EmployeeID	Int	NOT NULL
Wage(工资)	money	NOT NULL
Putdate	DateTime	NOT NULL

 

工资税表   Tax:

字  段  名  称	类      型	空      否
TaxID	Int，标识值 ,初值为1，增量为1	主键
EmployeeID	Int	NOT NULL
Tax(税款)	money	NOT NULL
Paydate(付款时间)	DateTime	NOT NULL

实践3：实现SQL Server数据库的完整性

（1）      创建雇员表Employees的主关键字，列：EmployeeID

alter table employees

add constraint PK_EmployeeID primary key(EmployeeID)

（2）创建雇员工资表wage的外部关键字，列：EmployeeID，参考Employees的主键

alter table wage

add constraint FK_EmployeeID foreign key(EmployeeID) references Employees(EmployeeID)

（4）向雇员表Employees中添加关于以下列的DEFAULT约束：

name：’不清楚’，sex：’男’，Address：’北京’，Phone：’(000)000 000 00 ‘      如何用代码打出这些默认约束

（5）向雇员表Employees中添加BirthDate列的CHECK约束：在

BirthDate列中的值必须早于今天的日期

alter table employees

add constraint CK_BirthDate check(birthdate=getdate())

（6）向雇员表Employees中插入3个雇员信息

insert into Employees

select '张三','女',getdate(),'北京','12345678901','45' union

select '李四','男',getdate(),'唐山','12345678901','77' union

select '王五','女',getdate(),'廊坊','12345678901','90'

（7）向雇员工资表wage中插入3个雇员相应的工资发放情况信息

insert into wage

select 8,42000,2010-03-23 union

select 9,52000,2010-04-23 union

select 10,32333,2010-02-12

（8）向工资税表tax中插入3个雇员相应的交税情况 

insert into Tax

select 1,4000,2010-03-23 union

select 2,5000,2010-04-23 union

select 3,3333,2010-02-12

 

实践4：创建SQL Server数据库表的视图和索引

（1）      在雇员表Employees中，在Phone列，创建一个名字为MyEmployees的聚集索引

create clustered index myEmployees on employees(phone)

（2）      使用存储过程sp_helpindex Employees列出表的所有索引

exec sp_helpindexemployees

（3）      分别查询雇员表Employees、雇员工资表wage和工资税表tax中的行数据 

select * from Employees

select * from Wage

select * from Tax

实践5：查询SQL Server数据库表的信息

（1）列出每个雇员的工资、税款情况一览表 ??????????????????

（2）列出工资超过1000元，而未交税的雇员

select * from Employees where EmployeeID=(select EmployeeID from wage where wage>1000 and EmployeeID in(select EmployeeID from Tax where Tax=0))

（3）列出工资在1000元以下，不应该交税的雇员情况一览表

select * from Employees where EmployeeID in(select EmployeeID from wage where wage between 1 and 1000 and EmployeeID in(select EmployeeID from Tax where Tax=0)) 

实践6：SQL Server数据库的高级查询

（1）分别列出在雇员表Employees中而未领工资和未交税的男、女雇员情况 （提示:要按照性别分组） ????????????????

select COUNT(*) from Employees where EmployeeID=(select EmployeeID from Tax where Tax=0 and EmployeeID=(select EmployeeID from wage where wage=0)) group by sex

 (2)查询电话号码区号为(010)、工资大于3000的男雇员情况（提示：使用like进行模糊查询）

select * from Employees where Phone like '010%' and EmployeeID in(select EmployeeID from Wage where Wage>3000) and Sex='男'

(3)查询交税最多的六名雇员情况 （提示：要注意排序）??????????

select top 6 * from Employees em inner join Tax tx on em.EmployeeID=tx.EmployeeID order by Tax desc

实践7：创建、执行、修改SQL Server数据库的存储过程

（1）      编写存储过程，向雇员表Employees、雇员工资表wage和工资税表tax中各插入两条记录 （要使用输入参数）

(1)employees

create procedure sp_employees_insert

@name varchar(10),

@sex char(2),

@birthdate datetime,

@address varchar(50),

@phone char(13),

@remark varchar(500)

as

insert into Employees values(@name,@sex,@birthdate,@address,@phone,@remark)

go

 

exec sp_employees_insert'赵君','男',null,'北京','00000000000','55'

exec sp_employees_insert'王五','女',null,'南京','00000000000','55'

(2)wage

create procedure sp_wage_insert

@employeeID int,

@wage int,

@putdate datetime

as

insert into wage values(@employeeID,@wage,@putdate)

go

 

exec sp_wage_insert8,42000,'2012-3-3'

exec sp_wage_insert9,42000,'1902-3-3'

(3)tax

create procedure sp_Tax_insert

@employeeID int,

@Tax money,

@paydate datetime

as

insert into Tax values(@employeeID,@Tax,@paydate)

go

 

exec sp_Tax_insert8,4444,'1905-06-10'

exec sp_tax_insert9,2222,'2012-02-21'

（2）编写存储过程，列出工资最高的六名雇员情况

create procedure sp_employee_select

@employeeID int

as

select top 6 * from Employees em inner join Tax tx on em.EmployeeID=tx.EmployeeID order by Tax desc

go

（3）编写存储过程，查找雇员表Employees中Sex、Phone与指定值相匹配的雇员 （注意输入参数）

create procedure sp_wage_select

@sex char(2),

@phone char(13)

as

select * from Employees where Sex='男' and Phone='00000000000'

go

实践8：创建和使用SQL Server数据库的触发器

（1）      创建delete触发器，从雇员表Employees中一次删除的记录数应不超过2条 （注意：deleted表中的记录数目不超过2条，否则要回滚）

（2）       创建insert触发器，当向雇员工资表wage中插入记录时，同时向工资税表tax中插入记录，工资税计算为：

Wage：1000以下，不交税；1000~5000：交税工资的1%

5000以上：交税工资的3%

（3）       创建update触发器，当修改雇员工资表wage中的Wage时，同时修改工资税表tax中相应的税款。