sql 面试题
题目一、idsnamesmoneysprovince1zhangsan2098A2lisi3000B3wangwu6789C4liumazi4587C5dongjiu3298B6shiga4567A
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
classnamesex一班张三M一班李四M一班王五M一班ccF一班mmF二班小明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
要求这么显示：
科目语文数学英语王五80NULLNULL李四908070张三808090
答案：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;">
页:
[1]