create database company
go
use company
go

create table sectionInfo
(
sectionID int    primary key,
sectionName varchar(10) not null
)

create table userInfo
(
userNo int  primary key not null,
userName varchar(10) unique not null,
userSex  varchar(2) not null check(userSex='男' or userSex='女'),
userAge int  not null,
userCity varchar(50) default('福建省龙岩市'),
userSextion int references sectionInfo(sectionID),--references链接主外键约束词
userSalary  decimal(6,2) default(0)
)

create table workInfo
(
workId int  primary key not null,
userId int references userInfo(userNo),
workTime datetime not null,
workDescription varchar(40) not null check(workDescription='迟到' or workDescription='早退'or workDescription='旷工'or workDescription='病假'or workDescription='事假'or workDescription='出勤')
)

insert into sectionInfo (sectionID,sectionName)
                 values(1,'人事部'),
				       (2,'研发部'),
				       (3,'财务部'),
				       (4,'销售部'),
				       (5,'采购部')
select * from sectionInfo

insert into userInfo(userNo,userName,userSex,userAge,userCity,userSextion,userSalary)
            values(1,'张百忍','男',21,'深圳',1,6000.00),
			      (2,'刘苏红','女',20,'四川',4,7500.00),
				  (3,'王明贵','男',20,'四川',3,5000.00),
				  (4,'张平川','男',25,'上海',2,1200.00),
				  (5,'雷双天','男',24,'四川',3,6500.00),

				  (6,'萧战东','男',21,'甘肃',4,6000.00),
				  (7,'雷富麟','男',22,'山东',2,1000.00),
				  (8,'李亦龙','男',26,'四川',3,7500.00),
				  (9,'王桂林','男',22,'广西',2,8000.00),
				  (10,'张宝书','男',21,'广东',4,6000.00),

				  (11,'刘思宇','女',22,'四川',5,8000.00),
				  (12,'黄源','女',21,'四川',5,7600.00),
				  (13,'黄东倪','女',22,'广东',4,7000.00),
				  (14,'萧伊晓','女',21,'广东',2,8500.00),
				  (15,'单永信','男',26,'广西',4,1100.00),

				  (16,'赵万倪','男',22,'上海',2,1200.00),
				  (17,'苏万念','男',22,'重庆',3,1100.00),
				  (18,'赵嵩山','男',21,'上海',4,1100.00),
				  (19,'秦战','男',20,'河南',4,8000.00),
				  (20,'张闵文','男',21,'上海',2,6300.00)
select * from userInfo
insert into workInfo(workId,userId,workTime,workDescription)
              values(1,2501,2002-3-12,'迟到'),
			        (2,2502,2001-6-11,'早退'),
		 	        (3,2503,2001-2-16,'病假'),
		       	    (4,2504,2001-10-6,'迟到'),
			        (5,2505,2001-3-10,'矿工'),

			        (6,2506,2001-2-20,'病假'),
			        (7,2507,2001-5-10,'事假'),
			        (8,2508,2001-3-15,'矿工'),
			        (9,2509,2001-10-3,'事假'),
			        (10,2510,2002-5-10,'迟到'),

			        (11,2511,2002-10-2,'迟到'),
			        (12,2512,2002-10-10,'事假'),
			        (13,2513,2002-3-12,'迟到'),
			        (14,2514,2002-6-6,'矿工'),
			        (15,2515,2002-1-10,'事假'),

			        (16,2516,2002-3-3,'迟到'),
			        (17,2517,2002-5-10,'事假'),
			        (18,2518,2002-10-13,'迟到'),
			        (19,2519,2002-10-2,'事假'),
			        (20,2520,2002-10-16,'迟到')

select * from workInfo
--1.查询公司的部门数量
select count(*) 总部门 from sectionInfo 
--2	查询公司的员工数量
select count(*)总员工 from userInfo
--3	查询所有部门的员工数量和工资平均值
select count(*)部门人数,avg(userSalary)平均工资 from userInfo
--4	查询每个年龄的男女生人数
select userAge 年龄,userSex 性别,count(userAge)总数 from userInfo
group by userAge,userSex
--5	查询所有部门员工的平均薪资
select avg(userSextion)from userInfo
--6	查询员工最高工资和最低工资的差距
select max(userSextion)-min(userSextion)from userInfo
--7	查询平均工资高于 8000 的部门 id 和它的平均工资. 
select userSextion 部门,avg(userSalary) from userInfo 
group by userSextion
having avg(userSalary)>8000
--8	查询公司员工工资的最大值，最小值，平均值，总和
select max(userSalary)最大值,min(userSalary)最小值,avg(userSalary)平均值,sum(userSalary)总和 from userInfo
--9(有员工的城市)各个城市的平均工资
select avg(userSalary)from userInfo
group by userCity
--10查询每个员工本月的出勤情况信息
select * from workInfo