sql面试

[quote]
问题描述:
现有一张表结构如下:
create table tableName(
id int,
sname varchar(15),
smoney float,
sprovince varchar(15)
)
其中
id:合同id
sname:姓名
smoney :业绩
sprovince:地区
问:
[color=red]第一道：显示出 业绩大于同一地区平均值的 合同id 姓名 地区 业绩[/color]
[color=red]第二道：把同一地区的 平均业绩地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）[/color]
[/quote]

drop table tableName;
create table tableName(
	id int,
	sname varchar(15),
	smoney float,
	sprovince varchar(15)
)

delete from tableName;
insert into tableName(id,sname,smoney,sprovince)values(1,'zhangsan',2098,'A');
insert into tableName(id,sname,smoney,sprovince)values(2,'lisi',3000,'B');
insert into tableName(id,sname,smoney,sprovince)values(3,'wangwu',4587,'C');
insert into tableName(id,sname,smoney,sprovince)values(4,'liumazi',4587,'C');
insert into tableName(id,sname,smoney,sprovince)values(5,'dongjiu',3000,'B');
insert into tableName(id,sname,smoney,sprovince)values(6,'shiga',4567,'A');
insert into tableName(id,sname,smoney,sprovince)values(7,'jerry',4567,'A');

select * from tableName;

select sprovince,avg(smoney) as '平均业绩' from tableName group by sprovince;

select 
	a.id,a.sname,a.sprovince,a.smoney
from tableName as a
where smoney>(select avg(b.smoney) from tableName as b where b.sprovince=a.sprovince group by b.sprovince)
order by a.sprovince;


--此种插入方式newtable 表格必须存在
insert into newtable(sprovince,avgmoney)select sprovince,avg(smoney) as '平均业绩' from tableName group by sprovince;

drop table newtalbe;
--此种插入方式newtable自动创建
select avg(smoney) as '平均业绩',sprovince as '地区' into newtalbe from tableName group by sprovince;

[quote]
原题大致是这样 合同表 cid主键
cid Region（区域） Saler（销售员） Money(合同金额)
1 北京 杨建 100
2 上海 社长 200
3 杭州 副团 500
4 上海 社长 200
5 上海 杨建 400
6 北京 社长 300
7 北京 杨建 200
8 杭州 副团 100


1. 查询每个区域有多少个销售人员并按区域倒叙排列
2. 查询所有相同区域中合同金额最少的区域
3. 查询表中合同金额小于所在区域平均合同金额的合同id
[/quote]

drop table salerInformation;
create table salerInformation(
	id int,
	region varchar(15),
	saler varchar(15),
	salerMoney float
)
delete from salerInformation;
insert into salerInformation(id,region,saler,salerMoney)values(1,'北京','杨建',100);
insert into salerInformation(id,region,saler,salerMoney)values(2,'上海','社长',200);
insert into salerInformation(id,region,saler,salerMoney)values(3,'杭州','副团',500);
insert into salerInformation(id,region,saler,salerMoney)values(4,'上海','社长',200);
insert into salerInformation(id,region,saler,salerMoney)values(5,'上海','杨建',400);
insert into salerInformation(id,region,saler,salerMoney)values(6,'北京','社长',300);
insert into salerInformation(id,region,saler,salerMoney)values(7,'北京','杨建',200);

1. 查询每个区域有多少个销售人员并按区域倒叙排列

select region as '区域',count(saler) as '员工人数' from salerInformation group by region order by region desc;

2. 查询所有相同区域中合同金额最少的区域
select region  as '区域',min(salerMoney) as '合同金额' from salerInformation group by region;

3. 查询表中合同金额小于所在区域平均合同金额的合同id

select region as '区域',avg(b.salerMoney)as '平均' from salerInformation as b group by b.region

select a.* from  salerInformation as a 
where a.salerMoney<(select avg(b.salerMoney) from salerInformation as b where b.region=a.region group by b.region)

[quote]
表形式如下：
Year Salary
2000 1000
2001 2000
2002 3000
2003 4000
想得到如下形式的查询结果
Year Salary
2000 1000
2001 3000
2002 6000
2003 10000
sql语句怎么写?

[/quote]

drop table test;
create table test(
	years int,
	salary  int
)

insert into test(years,salary)values(2000,1000);
insert into test(years,salary)values(2001,2000);
insert into test(years,salary)values(2002,3000);
insert into test(years,salary)values(2003,4000);

SELECT 
	b.years, SUM (a.salary) salary 
FROM test a, test b 
WHERE a.years <= b.years 
GROUP BY b.years;