Oracle select 笔记

emp , dept 均为Oracle内置表

--授权语句

conn sys/12346 as sysdba; 连接sysdba
grant create table , create view to scott; 授权

--求部门中哪些人的薪水最高
select ename , sal from emp
join (select max(sal) max_sal , deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno )

--求部门平均薪水的等级
select deptno , avg_sal , grade from
(select avg(sal) avg_sal , deptno from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)

--求部门平均的薪水等级
select deptno , avg(grade) from
(select deptno , ename , grade from emp
join salgrade s on (emp.sal between s.losal and s.hisal)) t
group by deptno

--雇员中有哪些人是经理人
select empno , ename  from emp
where empno in (select distinct mgr from emp)

--不准用组函数，求薪水的最高值(面试题)
select sal from emp where sal not in
(select e1.sal from emp e1
join emp e2 on (e1.sal < e2.sal) )

--求平均薪水最高的部门的部门编号
select deptno , avg_sal from
( select avg(sal) avg_sal , deptno from emp group by deptno)
where avg_sal =
( select max(avg_sal) from
( select avg(sal) avg_sal from emp group by deptno ) )

--求平均薪水最高的部门的部门名称
select dname from dept where deptno =
( select deptno from
( select avg(sal) avg_sal , deptno from emp group by deptno)
where avg_sal =
( select max(avg_sal) from
( select avg(sal) avg_sal from emp group by deptno ) )
)

--求平均薪水的等级最低的部门的部门名称
第一种方法
select t1.deptno , dname , avg_sal , grade from
(
 select deptno , avg_sal , grade from
 (
 (select deptno , avg(sal) avg_sal from emp group by deptno) t
 join salgrade s on (t.avg_sal between s.losal and s.hisal)
 )
)t1
join dept d on (d.deptno = t1.deptno)
where t1.grade =
(
select min(grade) from
 (
 select deptno , avg(sal) avg_sal  from emp group by deptno
 ) t
 join salgrade s on (t.avg_sal between s.losal and s.hisal)
)
第二种方法 创建视图
conn sys/12346 as sysdba; 连接sysdba
grant create table , create view to scott; 授权

create view v$_dept_avg_sal_info as
select deptno , avg_sal , grade from
(select deptno , avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);

select dname , t1.deptno , avg_sal , grade from
v$_dept_avg_sal_info t1
join dept d on (d.deptno = t1.deptno)
where t1.grade =
 (
   select min(grade) from v$_dept_avg_sal_info
 );

--求部门经理人中平均薪水最低的部门名称


--求比普通员工的最高薪水还要高的经理人名称
第一种方法
 select ename from emp
 where empno in (select distinct mgr from emp where mgr is not null)
 and
 sal >
 (
   select max(sal) from emp where empno not in
     (select distinct mgr from emp where mgr is not null )
 )
第二种方法

 select mgr , sal ,ename from emp
 join
 (select max(sal) max_sal from emp where empno not in
 (select distinct mgr from emp where mgr is not null))t
 on (t.max_sal < emp.sal)

--求薪水最高的前5名雇员
select ename , sal , rownum  from
 (
 select ename , sal from emp order by sal desc
 )
where rownum <= 5

--求薪水最高的第6到第10名雇员(重点掌握)
select ename , sal , r from
  (
  select ename , sal , rownum r from
     ( select ename , sal from emp order by sal desc )
  )
where r >= 6 and r <= 10
--练习 : 求最后入职的5名员工
 select ename , to_char(hiredate , 'YYYY-MM-DD HH24:MI:SS') from
 (
 select ename , hiredate from emp order by hiredate desc
 )
 where rownum <= 5

--面试题 : 比较效率
  select * from emp where deptno = 10 and ename like '%A%';
  select * from emp where ename like '%A%' and deptno = 10;
(理论上上面的语句比下面的语句效率高，但实际当中不一定，如果Oracle对语句
做了优化的话，就可能会把第二条语句中的deptno = 10提到ename前面)