sql面试

引用

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

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;

引用

原题大致是这样 合同表 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

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)

引用

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

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;

评论

1 楼 makemyownlife 2010-08-27  

呵呵，这样的面试题sql 原来也做过