数据库二

--（1）求员工的总人数
select count(*) from People

--（2）求最大值，最高工资
select max(PeopleSalary) from People

--（3）求最小值，最低工资
select min(PeopleSalary) from People

--（4）求和，求所有员工的工资总和
select sum(PeopleSalary) from People

--（5）求平均值，求所有员工的平均工资
select avg(PeopleSalary) from People

--（6）求总人数，最大值、最小值、总和、平均值、在一行显示
select count(*)总人数,max(PeopleSalary)最大值,min(PeopleSalary)最小值,
sum(PeopleSalary)总和,avg(PeopleSalary)平均值 from People

--（7）查询出上海地区的员工人数、总工资、最高工资、最低工资和平均工资
select count(*)总人数,max(PeopleSalary)最大值,min(PeopleSalary)最小值,
sum(PeopleSalary)总和,avg(PeopleSalary)平均值 from People where PeopleAddress ='上海'

--（8）求出工资比平均工资高的人员信息
select * from People where PeopleSalary > (select avg(PeopleSalary) from People)

--（9）求数量，年龄最大值，年龄最小值、年龄总和、年龄平均值、在一行显示
select COUNT(*)数量,
max(year(getdate())-year(PeopleBirth))最大年龄,
min(year(getdate())-year(PeopleBirth))最小年龄,
sum(year(getdate())-year(PeopleBirth))年龄总和,
avg(year(getdate())-year(PeopleBirth))平均年龄
from People

--（10）计算月薪在10000以上的男性员工的最大年龄，最小年龄和平均年龄
select COUNT(*)数量,
max(year(getdate())-year(PeopleBirth))最大年龄,
min(year(getdate())-year(PeopleBirth))最小年龄,
avg(year(getdate())-year(PeopleBirth))平均年龄
from People 
where PeopleSalary>=10000 and PeopleSex='男'

--（11）统计出所在地“武汉或上海 的所有女员工数量以及最大年龄，最小年龄和平均年龄
select '武汉或上海的女员工'描述, 
COUNT(*)数量,
max(year(getdate())-year(PeopleBirth))最大年龄,
min(year(getdate())-year(PeopleBirth))最小年龄,
sum(year(getdate())-year(PeopleBirth))年龄总和,
avg(year(getdate())-year(PeopleBirth))平均年龄
from People 
where PeopleSex='女' and PeopleAddress in('武汉','上海')

--（12）求年龄比平均年龄高的人员信息
select * from People where year(getdate())-year(PeopleBirth)>
(select avg(year(getdate())-year(PeopleBirth)) from People)