sql 面试题

you_java

题目一、

id	sname	smoney	sprovince
1	zhangsan	2098	A
2	lisi	3000	B
3	wangwu	6789	C
4	liumazi	4587	C
5	dongjiu	3298	B
6	shiga	4567	A

id:合同id  sname:姓名     smoney :业绩     sprovince:地区

第一道：显示出  业绩 大于同一地区平均值的 合同id  姓名 地区 业绩

第二道：把同一地区的  平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）

答案：

第一道题答案：

第一种写法，select t1.*,t2.avg_smoney from hetong t1,

(select avg(smoney) avg_smoney,sprovince from hetong group by sprovince) t2

where t1.sprovince = t2.sprovince and t1.smoney>t2.avg_smoney;

第二种写法，select t1.*,t2.avg_smoney from hetong t1 join

(select avg(smoney) avg_smoney,sprovince from hetong group by sprovince) t2

on(t1.sprovince = t2.sprovince and t1.smoney>t2.avg_smoney);

第三种写法，select a.* from hetong a 

where a.smoney > (select avg(t.smoney) from hetong t where t.sprovince = a.sprovince);

第二道题答案：

create table ht2 as

(select avg(smoney) avg_smoney,sprovince from hetong 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 

答案：

第一道题答案，select t.region,count(*) from

(select count(saler) cc,region,saler from com group by region,saler order by region desc) t

group by region;

第二道题答案，select min(money),region from com group by region;

第三道题答案，select c.* from com c join

(select avg(money) avg_money,region from com group by region) c1

on(c.region=c1.region and c.money<c1.avg_money)

 

题目三、

1、select * from schools 

 

class	name	sex
一班	张三	M
一班	李四	M
一班	王五	M
一班	cc	F
一班	mm	F
二班	小明	M
二班	小河	F

现在要查询出 男女人数相等的班级

 

答案：select tem.class from(

select class, 

(select count(sex) from schools s1 where s1.sex='M' and s1.class=s.class) as mcount,

(select count(sex) from schools s1 where s1.sex='F' and s1.class=s.class) as fcount 

from schools s group by class

) tem where tem.mcount=tem.fcount;

 

2、select * from grade

 

姓名    科目  成绩
王五     语文  80
李四     语文  90
李四     数学  80
李四     英语  70
张三     语文  80
张三     数学  80
张三     英语  90
要求这么显示：
 

科目	语文	数学	英语
王五	80	NULL	NULL
李四	90	80	70
张三	80	80	90

 
答案：select name as 科目,
(select score from grade g1 where g1.name=g.name and subject='语文') as 语文,
(select score from grade g1 where g1.name=g.name and subject='数学') as 数学,
(select score from grade g1 where g1.name=g.name and subject='英语') as 英语 from grade g group by name
 
题目四、
数据库 ORACLE
 
T表：（字段：ID，NAME，ADDRESS，PHONE，LOGDATE）
E表：（字段：NAME，ADDRESS，PHONE）
1． 将表T中的字段LOGDATE中为2001-02-11的数据更新为2003-01-01，请写出相应的SQL语句。（该字段类型为日期类型）--注意可能包含多条记录


2． 请写出将表T中NAME存在重复的记录都列出来的SQL语句（按NAME排序）


3. 请写出题目2中，只保留重复记录的第一条，删除其余记录的SQL语句（即使该表不存在重复记录）


4. 请写出将E表中的ADDRESS、PHONE更新到T表中的SQL语句（按NAME相同进行关联）


5. 请写出将T表中第3~5行数据列出来的SQL语句


答案：
1.update t set logdate=to_date('2003-01-01','YYYY-mm-dd') where logdate=to_date('2001-02-11','YYYY-mm-dd');


2.select count(*),name from t group by name having count(*)>1 order by name;
--
select * from t 
where name in (select name from t group by name having count(*)>1)
order by name;


3.select * from
(select t.*,row_number() over(partition by name order by name) rn from t)
where rn = 1;
--
delete from t where rowid not in(select min(rowid) from t group by name);


4.update t set(address,phone)=(select address,phone from e where e.name=t.name) 
where exists(select e.name from e where t.name=e.name);


update t set(address,phone)=(select address,phone from e where e.name=t.name) 
where t.name in(select name from e);


5.select tt.*,rownum n from(
select t.*,rownum rn from (select * from t order by id) t where rownum<=5
) tt where rn>=3
<div style="text-align: left;">