create database company
go
use company
--创建部门信息表
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 references sectionInfo(sectionID),  --员工部门
userSalary decimal(5,2) default(0)  --员工月薪
)

--创建员工考勤表
create table workInfo
(
workId int identity(1,1) primary key not null,  --考勤编号
userId  int references userInfo(userNo),  --考勤员工
workTime datetime not null,  --考勤时间
workDescription  varchar(40) check(workDescription='迟到' or workDescription= '早退' or workDescription= '旷工' or workDescription='病假' or workDescription='事假' or workDescription='出勤'),  --考勤说明
)
--部门信息
insert into sectionInfo(sectionName)
values('IT部'),
('运营部'),
('电子信息部'),
('推销部'),
('生产部')
select * from sectionInfo

--员工信息
insert into userInfo(userName,userSex,userAge,userCity,userSection,userSalary)
values('A1','男',20,'湖北武汉',1,150 ),('A2','女',41,'江西赣州',2,800 ),
       ('B1','男',28,'湖南长沙',5,250 ),('B2','男',48,'浙江杭州',4, 800),
	   ('C1','男',50,'广州深圳',5,300 ),('C2','女',42,'',1, 999),
	   ('D1','女',60,'广州广东',3, 380),('D2','女',38,'辽宁沈阳',3, 200),
	   ('E1','女',43,'浙江杭州',3,580 ),('E2','女',36,'江西赣州',5,280 ),
       ('F1','女',35,'吉林长春',5,590 ),('F2','男',31,'广州广东',4, 380),
	   ('G1','男',46,'黑龙江哈尔滨',4, 900),('G2','男',22,'',1,480 ),
	   ('H1','女',28,'辽宁沈阳',1,570),('H2','男',33,'湖北武汉',1,350 ),
	   ('I1','女',64,'河北石家庄',2,490 ),('I2','女',34,'福建龙岩',1,666 ),
       ('J1','男',51,'山西太原',5, 570),('J2','男',63,'湖北武汉',5,360 )
select * from userInfo

--员工考勤
insert into workInfo(userId,workTime,workDescription)
values ('1',8,'出勤'),('2',0,'旷工'),
       ('3',9,'出勤'),('4',11,'出勤'),
	   ('5',4,'早退'),('6',5,'早退'),
	   ('7',6,'早退'),('8',0,'病假'),
	   ('9',0,'事假'),('10',8,'出勤'),
	   ('11',0,'事假'),('12',2,'早退'),
	   ('13',10,'出勤'),('14',8,'出勤'),
	   ('15',9,'出勤'),('16',0,'旷工'),
	   ('17',0,'病假'),('18',6,'早退'),
	   ('19',7,'早退'),('20',8,'出勤')
select * from workInfo

--1.查询公司的部门数量
select count(*) 部门数量 from sectionInfo
--2.查询公司的员工数量
select count(*) 员工数量 from userInfo
--3.查询所有部门的员工数量和工资平均值
select userSection 部门, count(*) 员工数量,avg(userSalary) 工资平均值 from userInfo
group by userSection
--4.查询每个年龄的男女生人数
select userSex 性别, count(*) 人数 from userInfo
group by userSex
--5.查询所有部门员工的平均薪资
select avg(userSalary) 平均薪资 from userInfo
--6.查询员工最高工资和最低工资的差距
select max(userSalary)-min(userSalary) 最高工资和最低工资的差距 from userInfo
--7.查询平均工资高于 400 的部门 id 和它的平均工资.
select userSection 部门,avg(userSalary) 平均工资 from userInfo
group by userSection
having avg(userSalary)>400
--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 员工,workTime 工作时间,workDescription  出勤情况 from workInfo
group by userId,workTime,workDescription