一些数据库面试题

1.查询平均工资大于2000的部门及其员工信息。
   使用连接查询加分组查询，注意分组中没有用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)
2.查询平均工资高于本部门工资的员工信息。
select * from emp e1 where sal>( select avg(sal) from emp e2 where e2.deptno=e1.deptno )
3.rank()和desen_rank()
   两者是排序函数。如:
　　数学，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.
4.一张表中如何删除冗余数据。
    主要用到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);
5.如何使索引生效。
    此处不是问你索引创建是否能利用，索引创建后是马上能用的。这里要考的是sql如何利用索引。
答：一般可按照where中加索引列等隐式调用索引，如果要强制索引，可以用hints，hints是oracle强制索引的一组规则。比如可以用注释说明,注释是/*+索引函数*/这种形式，注意有个加号，如：
    /*+INDEX_JOIN(TABLE INDEX_NAME)*/
　　提示明确命令优化器使用索引作为访问路径.
　　例如:
　　SELECT /*+INDEX_JOIN(EMP EMPID)*/ SAL,HIREDATE
　　FROM EMP WHERE SAL<60000;
最后需要注意的是索引列最好不要为null,因为有null值的话，即使有hints也不会执行。