公司信息管理

​ 假设你建立了一家咨询公司，为企业提供软硬件服务。你需要一个数据库以实时追踪本公司中的各个细节。考虑最简单的情况，你需要追踪客户情况，掌握公司员工信息，并维护订单信息。在公司的关系数据模式中创建了以下三个关系:

(1)客户(客户编号、姓名、住址、省市区、邮编、手机号码、固话号码)

(2)员工(员工编号、姓名、工作种类、业务部门、住址、办公室号码、入职时间、薪水、是否接受996工作制)

(3)订单(订单号、客户编号、负责员工编号、订单金额、订单交付日期)

请自行为该数据库编写测试数据，并给出实现以下各种需求的SQL语句:

(1) 创建这三个关系，注意每个属性的数据类型，以及订单关系中的参照完整性约束; (2) 查询公司客户的分布省份:

(3)查 询所有不位于四川的客户的姓名和手机号码:

(4)查询所有接受996工作制的员工的姓名:

(5) 查询员工总人数(提示:使用count聚集函数) ;

(6) 查询每个员工以及其负责订单的情况(提示:参考例3.49) ;

(7)查询负贵订单金额在100万以上的所有员工编号与姓名(提示:参考例3.51) ;

(8)查询负贵的每个订单金额都在10万以.上的员工编号与姓名(有多种实现方式，提示:参考例3.48或3.57);

(9)查询出每个员工低于自已负责订单的平均金额的订单号与客户编号(提示:参考例 3.57) ;

(10)假设公司有物流规划组(简称规划组)、智能设备组(简称设备组)、数据可视化与复杂模型(简称数模组)三个业务部门，查询非设备组中比设备组员工的平均薪水高的员工姓名与年龄(提示:参考例3.58/3.59);

(11) 由于国庆节提前上班，将所有员工的薪水修改为增加2000元后的金额(提示:参 考例3.75) ;

(12)定义一个客户详细地址 (编号、省市区、住址、邮编)的视图(提示:参考例 3.88) ;

1.数据表结构说明

1>客户信息表（cust_info）

字段名	字段值	数据类型	是否主键	可否为空	备注
客户编号	cust_num	varchar(10）	是	否
姓名	cust_name	varchar(10）	否	否
住址	address	varchar(100）	否	否
省市区	province	varchar(100）	否	否
邮编	postcode	varchar(6）	否	否
手机号码	mobile_phone	varchar(11）	否	否
固话号码	tell_phone	varchar(20）

2>员工(staff_info)

字段名	字段值	数据类型	是否主键	可否为空	备注
员工编号	staff_num	varchar(10）	是	否
姓名	staff_name	varchar(10）	否	否
工作种类	work_type	varchar(10）	否	否
业务部门	business_dept	varchar(10）	否	否
住址	address	varchar(100）	否	否
办公室号码	work_phone	varchar(20）	否	否
入职时间	entry_time	datetime	否	否
薪水	salary	decimal(10,2）	否	否
是否接受996工作制	is_accept_996	varchar(1）	否	否	0-不接受996 1-接受996

3>订单(order_info)

字段名	字段值	数据类型	是否主键	可否为空	备注
订单号	order_num	varchar(10）	是	否
客户编号	cust_num	varchar(10）	否	否	fk：cust_info表cust_num
负责员工编号	staff_num	varchar(10）	否	否	fk：staff_info表staff_num
订单金额	order_amount	decimal(10,2）	否	否
订单交付日期	due_date	datetime	否	否

2.模拟数据执行sql语句执行说明

(1) 创建这三个关系，注意每个属性的数据类型，以及订单关系中的参照完整性约束;

	# 构建order_info与cust_info的主外键关系
	alter table order_info add constraint fk_order_cust_num 
  foreign key(cust_num) references cust_info(cust_num);
  
  # 构建order_info与staff_info的主外键关系
  alter table order_info add constraint fk_order_staff_num 
  foreign key(staff_num) references staff_info(staff_num);
  

(2) 查询公司客户的分布省份:

select province "客户分布省份" 
from cust_info
group by province

(3)查 询所有不位于四川的客户的姓名和手机号码:

select CI.cust_name "客户姓名",CI.mobile_phone "手机号码" ，ci.province "省份"
from cust_info ci
where ci.province != '四川'

(4)查询所有接受996工作制的员工的姓名:

select SI.staff_name
from staff_info si
where SI.is_accept_996 = '1'

(5) 查询员工总人数(提示:使用count聚集函数) ;

select count(*)
from staff_info si

(6) 查询每个员工以及其负责订单的情况(提示:参考例3.49) ;

select OI.*,SI.STAFF_NAME
from staff_info si
left join order_info oi on OI.staff_num = SI.staff_num 
order by SI.staff_name

(7)查询负责订单金额在100万以上的所有员工编号与姓名(提示:参考例3.51) ;

select OI.ORDER_AMOUNT "订单金额",SI.STAFF_NUM,SI.STAFF_NAME
from order_info oi
left join staff_info si on SI.staff_num = OI.staff_num
where OI.ORDER_AMOUNT >= 1000000

(8)查询负责的每个订单金额都在10万以上的员工编号与姓名(有多种实现方式，提示:参考例3.48或3.57);

select tbMin."staffNum",SI.staff_name
from (
	select min(OI.ORDER_AMOUNT) "minOrderAmount",OI.staff_num "staffNum"
	from order_info oi
	group by OI.staff_num
)tbMin
left join staff_info si on SI.staff_num = tbMin."staffNum"
where tbMin."minOrderAmount" > 100000

(9)查询出每个员工低于自已负责订单的平均金额的订单号与客户编号(提示:参考例 3.57) ;

select OI.ORDER_NUM "订单号",oi.cust_num "客户编号"
from (
	select avg(OI.ORDER_AMOUNT) "avgOrderAmount",OI.staff_num "staffNum"
	from order_info oi
	group by OI.staff_num
)tbAvg
left join order_info oi on oi.staff_num = tbAvg."staffNum"
where tbAvg."avgOrderAmount" > oi.ORDER_AMOUNT

(10)假设 公司有物流规划组(简称规划组)、智能设备组(简称设备组)、数据可视化与复杂模型(简称数模组)三个业务部门，查询非设备组中比设备组员工的平均薪水高的员工姓名与年龄(提示:参考例3.58/3.59);

select SI.STAFF_NAME,SI.STAFF_AGE
from staff_info si
where SI.BUSINESS_DEPT != '设备组' 
and si.SALARY >(
	select avg(si.SALARY)
	from staff_info si
	where SI.BUSINESS_DEPT = '设备组'
)

(11) 由于国庆节提前上班，将所有员工的薪水修改为增加2000元后的金额(提示:参 考例3.75) ;

update STAFF_INFO
set SALARY = SALARY+2000

(12)定义一个客户详细地址 (编号、省市区、住址、邮编)的视图(提示:参考例 3.88) ;

create or replace view cust_view as 
(
	select CI.CUST_NUM,CI.PROVINCE,CI.ADDRESS,CI.POSTCODE
	from CUST_INFO ci
) 

select * from cust_view