use master
if exists (select *from sysdatabases where name='Worker')
	drop database Worker
------建數據庫
create database Worker
on primary(
	Name='Worker_data',
	FileName='E:\Worker.mdf',
	size=6mb,
	Filegrowth=10%
)
log on(
	Name='Worker_log',
	FileName='E:\Worker.log',
	size=2 mb,
	Filegrowth=1mb
)
use Worker
go

-----建數據表
create table Section(
	StId int not null ,
	StName varchar(20),
	StTime varchar(20),
	StBoss int		
)
create table Employee(
	EyId int  not null,
	EyName varchar(10),
	EySex varchar(10),
	EyAge int,
	StId int,
	ZwName varchar(10),
	EyTel varchar(20)
)
create table Zhiwu(
	ZwName varchar(10) not null,
	Salary int,
	Lift float 
)
---添加主鍵，外鍵和檢查約束以及默認約束
alter table Section add constraint pk_Section_StId primary key(StId)
alter table Employee add constraint pk_Employee_EyId primary key(EyId)
alter table Zhiwu add constraint pk_zhiwu_ZwName primary key(ZwName)
alter table Zhiwu add constraint ck_Zhiwu_Salary check(Salary>1000 and Salary <5000)
alter table Zhiwu add constraint ck_Zhiwu_Lift check(Lift<=10)
alter table Employee add constraint fk_Employee_Stid foreign key(Stid) references Section(Stid)
alter table Employee add constraint fk_Employee_ZwName foreign key(ZwName) references Zhiwu(ZwName)
alter table Section add constraint DF_Section_StTime default(getdate()) for StTime


select *from Section 
select *from Employee
select *from Zhiwu

-----------向數據表Section中插入數據
insert into Section values(1001,'企劃部',default,200601)
insert into Section values(1002,'財務部',default,200612)
insert into Section values(1003,'生產部',default,200617)
insert into Section values(1004,'銷售部',default,200609)
insert into Section values(1005,'宣傳部',default,200605)
insert into Section values(1006,'后勤部',default,200620)

-----------向數據表Zhiwu中插入數據
insert into ZhiWu values('企劃師',3000,2)
insert into ZhiWu values('會計師',1500,3)
insert into ZhiWu values('項目經理',4500,6)
insert into ZhiWu values('初級程序員',4500,4)
insert into ZhiWu values('銷售員',2000,5)
insert into ZhiWu values('企宣主管',2500,7)
insert into ZhiWu values('后勤主管',3500,3)

-----------向數據表Employee中插入數據
insert  into Employee values(20060009,'陶偉','男',35,1001,'企劃師',13100245602)
insert  into Employee values(20060012,'鄧斌','男',35,1003,'初級程序員',13774143026)
insert  into Employee values(20060013,'鄧巧云','女',25,1002,'會計師',13474149984)
insert  into Employee values(20060008,'王倩','女',29,1006,'后勤主管',13360507216)
insert  into Employee values(20060005,'李玲','女',22,1005,'企宣主管',13900263548)
insert  into Employee values(20060003,'周金','男',42,1003,'項目經理',13811253462)
insert  into Employee values(20060018,'楊玲','女',35,1004,'銷售員',13265003202)


select *from Section 
select *from Employee
if exists (select *from dbo.sysobjects where name='view_zhiwu')
drop view view_zhiwu
go
create View view_zhiwu
as
	select ZwName,Salary,convert(varchar(2),lift) +'%'as 提成率 from Zhiwu
go
select *from  view_zhiwu

--查詢

-->1	統計所有員工的人數
	select count(*) as 員工人數 from Employee

-->2	計算所有員工的平均提成率
	 select avg(Lift)as 平均提成率 from zhiwu where ZwName in(select ZwName from Employee)

-->3 計算所有員工的底薪的總和
	select sum(Salary) as 底薪總和 from view_zhiwu where ZwName in(select ZwName from Employee)

-->4	顯示所有部門的名稱和底薪，顯示字段名必須是中文，按照底薪排降序	 
	select a.StName,a.StId,b.ZwName into Newtable from Section a,Employee b where a.StId=b.StId
	select *from Newtable
	select n.StName,z.Salary into New from Newtable n,view_zhiwu z where n.ZwName=z.ZwName 	
	select *from New
	select distinct StName as 部門名稱,Salary as 底薪 from New order by Salary desc	
	drop table Newtable
	drop table New


-->5	顯示所有員工的姓名，年齡，職務，底薪，提成率
--		使用連接查詢
--		字段名顯示成中文（使用=）
--		首先按部門排降序，然后按年齡排升序
	select *from Section 
	select *From Employee
	select *from view_zhiwu
	select e.EyName as 姓名,e.EyAge as 年齡, e.ZwName as 職務,z.Salary as 底薪,z.Lift as 提成率 into EmployeeNew from Employee e,view_zhiwu z where e.ZwName =z.ZwName order by e.StId desc,e.EyAge  
	select *from EmployeeNew
	drop table EmployeeNew

------刪除數據庫
	drop database Worker