create?table?yuangong2(
YG_ID?int?primary?key?identity(1,1),
YG_name?nvarchar(50)?not?null?unique,
YG_sex?nvarchar(20)?check(YG_sex='男'?or?YG_sex='女'),
YG_ruzhishijian?datetime?default?getdate(),
zhiwei_id?int,
YG_age?int?check(YG_age>=18?and?YG_age<=60),
YG_gongzi?decimal(18,2)?check(YG_gongzi>2000)
)
create?table?zhiwei2(
zhiwei_id?int?primary?key?identity(1,1),
zhiwei_name?nvarchar(50)?not?null?unique
)
alter?table?yuangong2?add?foreign?key(zhiwei_id)?references?zhiwei2(zhiwei_id)
insert?into?zhiwei2?values('銷售部')
insert?into?zhiwei2?values('人事部')
insert?into?zhiwei2?values('行政部')
insert?into?zhiwei2?values('保安部')
insert?into?zhiwei2?values('后勤部')
insert?into?zhiwei2?values('經(jīng)理')
insert?into?zhiwei2?values('財務(wù)部')
insert?into?yuangong2?values('張三','男','1999-9-9',1,22,8888)
insert?into?yuangong2?values('李四','男','1998-2-9',2,22,7888)
insert?into?yuangong2?values('王五','男','1997-9-9',3,22,6888)
insert?into?yuangong2?values('劉備','男','1996-8-9',4,22,5888)
insert?into?yuangong2?values('孫尚香','女','1995-7-9',5,22,4888)
insert?into?yuangong2?values('張飛','男','1994-9-9',4,22,3888)
insert?into?yuangong2?values('關(guān)羽','男','1999-4-9',3,22,2888)
insert?into?yuangong2?values('孫策','男','1999-6-9',2,22,1888)
drop?table?yuangong2
select?*?from?yuangong2
select?*?from?zhiwei2
1、統(tǒng)計“銷售部（salesman）”員工人員，（?where?yuangong2.ZHIWEI_ID=1）

select?count(*)?from?yuangong2?where?yuangong2.ZHIWEI_ID=1
2、統(tǒng)計“人事部”和“行政部”的員工人數(shù)（where?ZHIWEI_ID=2?or?zhiwei_id=3
group?by?zhiwei_id，select?*?from?yuangong2）

select?zhiwei_id,count(*)?from?yuangong2?where?ZHIWEI_ID=2?or?zhiwei_id=3
group?by?zhiwei_id
select?*?from?yuangong2
3、統(tǒng)計“經(jīng)理”的人數(shù)（where?zhiwei_id=7，id為7對應(yīng)“經(jīng)理”? 因為?YG_ID?int?primary?key?identity(1,1)?）
select?COUNT(*)?from?yuangong2?where?zhiwei_id=7
4、查詢是4月份入職的員工的人數(shù)
select?COUNT(*)?from?yuangong2?where?month(YG_ruzhishijian)=4
5、查詢"BLAKE"的下屬的人數(shù)(提示：mgr列里面的數(shù)據(jù)表示此員工直接領(lǐng)導(dǎo)的員工號)
6、查詢所有員工的平均工資
select?avg(yg_gongzi)?from?yuangong2
7、查詢所有員工的工資和
select?sum(yg_gongzi)?from?yuangong2
8、查詢員工的最高工資
select?max(yg_gongzi)?from?yuangong2
9、查詢員工的最低工資
select?min(yg_gongzi)?from?yuangong2
10、查詢員工的最早和最晚入職時間(要求在一個結(jié)果集合中顯示)
select?max(yg_ruzhishijian),min(yg_ruzhishijian)?from?yuangong2
11、統(tǒng)計每個部門的人數(shù)
select?zhiwei_id,count(*)?from?yuangong2?group?by?zhiwei_id
12、統(tǒng)計每個部門的平均工資
select?zhiwei_id,avg(yg_gongzi)?from?yuangong2?group?by?zhiwei_id
13、統(tǒng)計每個部門的最高工資
select?zhiwei_id,max(yg_gongzi)?from?yuangong2?group?by?zhiwei_id
14、統(tǒng)計每個部門的最低工資
select?zhiwei_id,min(yg_gongzi)?from?yuangong2?group?by?zhiwei_id
15、統(tǒng)計每個部門的最高工資，最低工資
select?zhiwei_id,min(yg_gongzi),max(yg_gongzi)?from?yuangong2?group?by?zhiwei_id
16、統(tǒng)計每個部門的最高工資與最低工資之差小于3000的信息（顯示部門號,最高工資,最低工資）
select?zhiwei_id,max(yg_gongzi),min(yg_gongzi)?from?yuangong2?group?by?zhiwei_id
having?(max(yg_gongzi)-min(yg_gongzi))>3000
17、統(tǒng)計不同職位的在崗人數(shù)
select?zhiwei_id,count(*)?from?yuangong2?group?by?zhiwei_id
18、統(tǒng)計不同年份出生的人的平均年齡
select?year(yg_ruzhishijian),avg(year(getdate())-year(yg_ruzhishijian))?from?yuangong2
group?by?year(yg_ruzhishijian)
19、統(tǒng)計不同職位的工資和
select?zhiwei_id,sum(yg_gongzi)?from?yuangong2?group?by?zhiwei_id