[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也不会执行。
使用连接查询加分组查询,注意分组中没有用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也不会执行。