一些数据库面试题

[b]1.查询平均工资大于2000的部门及其员工信息。[/b]
使用连接查询加分组查询，注意分组中没有用where,因为where不能使用聚合函数avg,sum等)
select * from dept left outer join emp on dept.deptno=emp.deptno where dept.deptno in ( select deptno from emp group by deptno having avg(sal)<2000)
[b]2.查询平均工资高于本部门工资的员工信息。[/b]
select * from emp e1 where sal>( select avg(sal) from emp e2 where e2.deptno=e1.deptno )
[b]3.rank()和desen_rank()[/b]
两者是排序函数。如:
　　数学，100
　　数学，90
　　数学，80
　　语文，88
　　语文，77
　　语文，70
　　那么语句就这么写：
　　select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t where t.rk<=3;
dense_rank基本和rank()用法差不多，区别在于rank()并列第几的时候排序将跳到后面，dense_rank()则并列的时候，下面的正常排列，如1,2,2,3,并列后还是1,2,2,3.
[b]4.一张表中如何删除冗余数据。[/b]
主要用到in和group by ..having分组函数.
方法1，用in：delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
方法2,用not in：
delete from employee where rowid not in (select max(t1.rowid) from employee t1 group by t1.emp_id,t1.emp_name,t1.salary);
[b]5.如何使索引生效。[/b]
此处不是问你索引创建是否能利用，索引创建后是马上能用的。这里要考的是sql如何利用索引。
答：一般可按照where中加索引列等隐式调用索引，如果要强制索引，可以用hints，hints是oracle强制索引的一组规则。比如可以用注释说明,注释是[b]/*+索引函数*/[/b]这种形式，注意有个加号，如：
/*+INDEX_JOIN(TABLE INDEX_NAME)*/
　　提示明确命令优化器使用索引作为访问路径.
　　例如:
　　SELECT /*+INDEX_JOIN(EMP EMPID)*/ SAL,HIREDATE
　　FROM EMP WHERE SAL<60000;
最后需要注意的是索引列最好不要为null,因为有null值的话，即使有hints也不会执行。