经典oracle面试题（附有答案）

T_STU表:S_ID、S_NAME、S_SEX、S_BIRTHDAY 、S_AGE、S_MOENY、C_ID
T_CLASS表：C_ID、C_NAME
sequence序列：seq_id;
★、学生表：学号、姓名、性别、出生日期、入学年龄、缴费
★、班级表：班级编号、班级名称
1、查询入学年龄在18-20的女生或者未输入性别的,实际年龄小的要排在后面
select * from T_STU
where (S_AGE between 18 and 20 and S_SEX='女')or(S_SEX is null)
order by S_BIRTHDAY

或者：
order by (sysdate-S_BIRTHDAY) desc

2、查询班级名称、学生姓名、性别、缴费(要求显示单位：元),相同班级的要放在一起,姓名根据字典顺序排列。
select c.C_NAME,s.S_NAME,s.S_SEX,s.S_MOENY||'元' as S_MOENY
from T_STU s,T_CLASS C
where s.C_ID=c.c_id
order by c.c_id,s.S_NAME

3、查询各班名称和人数
select c.C_NAME,count(*) as rs
from T_STU s,T_CLASS C
where s.C_ID=c.c_id
group by c.C_ID,c.C_NAME
或者：
select c.C_ID,c.C_NAME,count(*) as rs
from T_STU s,T_CLASS C
where s.C_ID=c.c_id
group by c.C_ID,c.C_NAME

4、★★查询各班名称和人数,但人数必须不少于2,人数多的放在前面
select c.C_NAME,count(*) as rs
from T_STU s,T_CLASS C
where s.C_ID=c.c_id
group by c.C_ID,c.C_NAME
having count(*)>=2
order by count(*) desc

5.1、查询1980年出生的有哪些学生。
select * from t_stu s
where to_char(s.S_BIRTHDAY,'yyyy')='1980'

5.2、查询男生和女生人数,没有输入性别的当作男
select nvl(s.s_sex,1) as x,count(*)
from t_stu s
group by nvl(s.s_sex,1) --必须把null值当作1来分组

易错：
select nvl(s.s_sex,1) as x,count(*) --将null当作1来显示
from t_stu s
group by s.s_sex


6.1、查询没有人员的班级
效率低：
select * from T_CLASS c
where c.c_id not in
(
select distinct s.C_ID from t_stu s
)

优化：
select * from T_CLASS c
where not exists
(
select * from t_stu s
where c.C_ID=s.C_ID
)
或者：
select * from T_CLASS c
where not exists
(
select 'x' from t_stu s
where c.C_ID=s.C_ID
)
理解：查询班级表不存在这种情况：班级编号与学生表中班级编号相等的情况。

或者：用左连接(效率低),只有对等连接,效率最高。

6.2、查询入学年龄在20以上的同学信息
select * from T_STU where S_AGE>20

7、查询班级平均入学年龄在20及以上的班级
select c.C_NAME,avg(s.s_age)
from T_STU s,T_CLASS C
where s.C_ID=c.c_id
group by c.C_ID,c.C_NAME
having avg(s.s_age)>=20


8、★★有工资表salary(e_id,e_date,e_money),求本月发了2笔以上工资的员工信息。
select e_id,count(*)
from salary
where to_char(e_date,'yyyy.mm')=to_char(sysdate,'yyyy.mm')
group by e_id
having count(*)>=2
或者：
select e_id
from salary
where to_char(e_date,'yyyy.mm')=to_char(sysdate,'yyyy.mm')
group by e_id
having count(*)>=2

有部门表、人员表、工资表。
1、查询：人员名称、部门名称、个人总工资
首先要自己定义表名和字段名,如：
bm 部门表：bid,bname
ry 人员表:rid,rname,bid
gz 工资表:rid,money,rq,memo

select bname,rname,sum(gz.money)
from bm,ry,gz
where bm.bid=ry.bid
and gz.rid=ry.rid
group by bid,bname,rid,rname


2、查询所有部门的总工资
select bm.bid,sum(gz.money)
from bm,ry,gz
where bm.bid=ry.bid
and gz.rid=ry.rid
group by bm.bid

或者：
select bm.bid,bm.bname,sum(gz.money)
from bm,ry,gz
where bm.bid=ry.bid
and gz.rid=ry.rid
group by bm.bid,bm.bname

或者：
select bm.bid,sum(gz.money)
from bm,ry,gz
where bm.bid=ry.bid
and gz.rid=ry.rid
group by bm.bid,bm.bname

3、查询2008年8月份各部门工资最高的员工信息：部门名称、员工姓名、员工总工资
分析：
1.先找出2008年8月份各部门最高的工资
部门 最高工资
a 12000
b 8000
2.找出哪些员工的工资等于这些工资
找：a部门谁的工资=12000
b部门谁的工资=8000

执行：
1.先找出2008年8月份各部门最高的工资

select bm.bid,bm.bname,max(gz.money)
from bm,ry,gz
where bm.bid=ry.bid
and gz.rid=ry.rid
and to_char(gz.rq,'yyyy.mm')='2008.08'
group by bm.bid,bm.bname

类似：
部门 名称 最高工资
a xx1 12000
b xx2 8000

执行：
2.找出哪些员工的工资等于这些工资

select bm.bid,bm.bname,gz.money
from bm,ry,gz
where bm.bid=ry.bid
and gz.rid=ry.rid
and to_char(gz.rq,'yyyy.mm')='2008.08'
and (bm.bid,gz.money) in
(
select bm.bid,max(gz.money)
from bm,ry,gz
where bm.bid=ry.bid
and gz.rid=ry.rid
and to_char(gz.rq,'yyyy.mm')='2008.08'
group by bm.bid,bm.bname
)