个人的尚学堂数据库oracle笔记（2）

select avg(sal),deptno from emp group by deptno;
 select avg(sal),deptno from emp group by deptno having avg(sal) >2000;
 
select avg(sal),deptno from emp where sal > 1200 group by deptno
having avg(sal) >1500 order by avg(sal) desc;


select ename ,sal from emp where sal = (select avg(sal) from emp);
select ename ,sal from emp where sal >(select avg(sal) from emp);

select ename ,sal ,deptno from emp where sal =(select max(sal) from emp group by deptno)

select ename ,sal ,deptno from emp where sal in (select max(sal) from emp group by deptno)

select max(sal),deptno from emp group by deptno;

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 empno ,ename ,mgr from emp;

/*求经理人*/
select e1.ename, e2.ename mgr from emp e1, emp e2 where 
e1.mgr =e2.empno;

select ename,dname ,grade from emp e, dept d ,salgrade sal
where e.deptno =d.deptno and e.sal between s.losal and s.hisal and job <>'clerk';


select ename ,dname from emp cross join dept;

select ename , dname from emp  join dept on (emp.deptno =dept.deptno) ;


select ename ,dname from emp join dept using (deptno);

select ename ,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

select ename ,dname,grade from emp e join   dept d on(e.deptno =d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%';

 select e1.ename ,e2.ename from emp e1 join emp e2 on(e1.mgr =e2.ename);
 
select ename ,dname from emp e right outer join
dept d on (e.deptno = d.deptno);/*右外联接*/
 
select ename ,dname from emp e full join
dept d on (e.deptno = d.deptno);/*全联接*/

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));

/*创建视图*/
create  view v$_dept_avg_sal_info as
select deptno,grade ,avg_sal 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);
 
 /*授权视图:建虚表完成操作*/
 grant create table ,create view to scott;
 conn scott/tiger
 
 select *from  v$_dept_avg_sal_info;
 
 
 select  empno ,ename from emp where rownum <5;
 
 select  empno ,ename from emp where rownum <=5;
 /*rownum 只能用小于和小于等于用*/
 select  ename from (select rownum r ,ename from emp ) where r >10;
 
 select   ename ,sal  from 
 (select   ename ,sal  from  emp order by sal desc) where rownum < =5;
 /*查询前top5 的销售额的名单*/
 
 
 select   ename ,sal ,rownum r from
(select ename , sal from emp order by sal desc);

select  seller_id , amt  from 
(select  seller_id , amt  from table_name order by amt desc) where rownum <=50;