代码拉取完成，页面将自动刷新
同步操作将从 20级软件开发4班/SQL Server作业仓库 强制同步，此操作会覆盖自 Fork 仓库以来所做的任何修改，且无法恢复！！！
确定后同步将在后台操作，完成时将刷新页面，请耐心等待。
--创建数据库
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
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手
