--创建数据库
create database company 

go

use company 

go
 


--创建数据表

--创建部门信息表
create table sectionInfo
(
	sectionID int identity(1,1) primary key,  --部门编号
	sectionName  varchar(10) not null ,  --部门名称，不为空 
)

--创建员工信息表
create table userInfo
(
	userNo   int identity(1,1) primary key not null,  --员工编号
	userName  varchar(10) unique not null, --员工姓名
	userSex   varchar(2) not null check(userSex='男'or userSex='女'),  --员工性别
	userAge  int not null check(userAge>=1 and userAge<=100) , --员工年龄
	userCity  varchar(50) default ('福建省龙岩市'),  --所在城市
	userSection  int foreign key (userSection) references  sectionInfo (sectionID),  --员工部门
	userSalary decimal(5,2) default ('0'),  --员工月薪
)

--创建员工考勤表
create table workInfo
(
	workId int identity(1,1) primary key ,  --考勤编号
	userId  int foreign key (userId)  references userInfo(userNo),  --考勤员工
	workTime  datetime  not null,  --考勤时间
	workDescription  varchar(40) not null check(workDescription='迟到'or workDescription='早退'or
												 workDescription='旷工'or workDescription='病假'or 
												 workDescription='事假' or workDescription='出勤')   --考勤说明

)

--1.	为部门信息表添加5条记录
	insert into sectionInfo (sectionName)
	values ('运营部')

	insert into sectionInfo (sectionName)
	values ('人事部')

	insert into sectionInfo (sectionName)
	values ('营销部')

	insert into sectionInfo (sectionName)
	values ('事务管理部')

	insert into sectionInfo (sectionName)
	values ('宣传部')

	select * from sectionInfo

--2.	为员工信息表添加20条记录

	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('杨戬','女',20,default,1,'300')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('范跑跑','男',25,'北京',3,'600')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('谢老板','女',18,'广东广州',4,'500.25')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('杨天','男',22,'上海',2,'900')

	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('唐一一','女',17,'湖北武汉',5,'800.85')

	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('张树根','男',30,'湖南长沙',3,'600')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('金毛','男',50,'浙江杭州',1,'400')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('孙尚香','女',19,'黑龙江',3,'700.55')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('狄仁杰','男',20,'山东济南',1,'300')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('孙悟空','男',28,'海南三亚',5,'600.88')

	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('诸葛亮','男',35,'贵州贵阳',3,'998.88')

	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('项羽','男',40,'河北石家庄',1,'300')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('关羽','男',45,'辽宁沈阳',4,'752.22')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('马超','男',23,'江苏南京',3,'888.88')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('苏妲己','女',20,'江西南昌',3,'600.25')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('虞姬','女',21,'四川成都',2,'589.66')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('王昭君','女',26,'陕西西安',5,'594.25')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('周瑜','男',23,'宁夏银川',3,'654.25')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('刘禅','男',15,'云南昆明',2,'286.22')
	insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
	values ('钟馗','男',25,'广西南宁',3,'951.85')

	select * from userInfo

--3.	为员工考勤表添加20条记录

	insert into workInfo(userId,workTime,workDescription)
	values (1,'2019-01-08','旷工')
	insert into workInfo(userId,workTime,workDescription)
	values (3,'2019-01-09','出勤')
	insert into workInfo(userId,workTime,workDescription)
	values (4,'2019-01-10','旷工')
	insert into workInfo(userId,workTime,workDescription)
	values (5,'2019-01-11','病假')
	insert into workInfo(userId,workTime,workDescription)
	values (2,'2019-01-12','早退')

	insert into workInfo(userId,workTime,workDescription)
	values (6,'2019-01-13','出勤')
	insert into workInfo(userId,workTime,workDescription)
	values (3,'2019-01-14','病假')
	insert into workInfo(userId,workTime,workDescription)
	values (10,'2019-01-15','事假')
	insert into workInfo(userId,workTime,workDescription)
	values (15,'2019-01-16','旷工')
	insert into workInfo(userId,workTime,workDescription)
	values (20,'2019-01-17','迟到')

	insert into workInfo(userId,workTime,workDescription)
	values (18,'2019-01-18','旷工')
	insert into workInfo(userId,workTime,workDescription)
	values (11,'2019-01-19','旷工')
	insert into workInfo(userId,workTime,workDescription)
	values (12,'2019-01-20','旷工')
	insert into workInfo(userId,workTime,workDescription)
	values (15,'2019-01-21','事假')
	insert into workInfo(userId,workTime,workDescription)
	values (16,'2019-01-21','早退')

	insert into workInfo(userId,workTime,workDescription)
	values (5,'2019-01-22','旷工')
	insert into workInfo(userId,workTime,workDescription)
	values (10,'2019-01-23','早退')
	insert into workInfo(userId,workTime,workDescription)
	values (13,'2019-01-24','病假')
	insert into workInfo(userId,workTime,workDescription)
	values (17,'2019-01-25','旷工')
	insert into workInfo(userId,workTime,workDescription)
	values (19,'2019-01-26','迟到')

	select * from workInfo

--查询数据
--1.	查询公司的部门数量

	select count(*)部门数量 from sectionInfo

--2.	查询公司的员工数量

	select count(*) 员工数量  from userInfo

--3.	查询所有部门的员工数量和工资平均值

	select avg(userSalary)平均工资,count(*)员工数量 from userInfo

--4.查询每个年龄的男女生人数

	select  年龄 =userAge ,count(*) 人数 from   userInfo group by userSex ,userAge

--5.查询所有部门员工的平均薪资
	
	select avg(userSalary)平均工资 from userInfo

--6.查询员工最高工资和最低工资的差距

	select max(userSalary) - min(userSalary) 工资差距  from userInfo 

--7.查询平均工资高于 8000 的部门 id 和它的平均工资.

	select userSection 部门id ,avg(userSalary)平均工资 from userInfo 
	 group by userSection
	  having avg(userSalary)>8000
	 
--8.查询公司员工工资的最大值，最小值，平均值，总和

	select max(userSalary)最高工资 ,min(userSalary) 最低工资,avg(userSalary)平均工资,sum(userSalary)工资总和 from userInfo 

--9. (有员工的城市)各个城市的平均工资

	select userCity 城市, avg(userSalary)平均工资 from userInfo group by userCity

--10.查询每个员工本月的出勤情况信息

select userId 考勤员工编号, (case when workDescription='出勤'then 1 else 0 end )出勤次数,
							(case when workDescription='迟到'then 1 else 0 end )迟到次数,
							(case when workDescription='早退'then 1 else 0 end )早退次数,
							(case when workDescription='旷工'then 1 else 0 end )旷工次数,
							(case when workDescription='事假'then 1 else 0 end )事假次数,
							(case when workDescription='病假'then 1 else 0 end )病假次数
 from workInfo