orcale小白学习的艰辛历程

select d.dname,e.sal,e.job,e.ename from emp e,dept d where e.deptno=20and d.deptno=e.deptno;
select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
select e.mgr,m.ename from emp e,emp m where  m.empno=e.mgr and e.ename='SCOTT';
select * from emp;
select e.ename,e.comm,d.dname from emp e,dept d where e.deptno=d.deptno and comm is not null and comm<>0;
select e.ename,d.dname from emp e,dept d where e.deptno=10 or d.deptno<>10 and e.deptno=d.deptno ;
select distinct(d.dname) from emp e,dept d where e.deptno=d.deptno and e.deptno<>10; 
select e.ename,d.dname from emp e right join dept d on e.deptno=10 and d.deptno=10 order by d.dname;--重要看点
select e.ename,d.dname from emp e left join dept d on e.deptno=10 and d.deptno=10 order by d.dname;-----重要
select d.dname,e.ename from emp e full join dept d on e.deptno=10 and e.deptno=d.deptno order by d.dname;----重要
select * from emp where deptno=(select deptno from emp where ename='BLAKE')and ename<>'BLAKE';
select d.dname 部门名称,e.ename 姓名,e.sal 工资 from emp e,dept d where e.deptno=d.deptno and e.sal>1500;
select d.dname 部门名称,e.ename 姓名,e.sal 工资 from emp e,dept d where e.deptno=d.deptno and e.sal=(select max(sal)from emp);
select e.ename||'为'||m.ename||'工作' from emp e,emp m where e.mgr=m.empno;
select ename,decode(deptno,10,sal*1.1,20,sal*1.15,30,sal*1.2,sal) from emp;
select ename,(months_between(sysdate,hiredate)/100+1)*sal from emp;
select e.deptno,d.dname,e.ename from emp e,dept d where e.deptno=(select deptno from emp  where  ename='KING')and e.deptno=d.deptno;
select q.ename,g.ename from emp q,emp g  where q.mgr=g.empno and g.ename=(select ename from (select m.ename,count(e.deptno)s from emp e,emp m where e.mgr=m.empno  group by m.ename order by s desc)t where rownum=1);
select t.deptno,n.dname,t.s from (select e.deptno,sum(sal)s from emp e,dept d where e.deptno=d.deptno group by e.deptno order by s desc) t,dept n where rownum=1and t.deptno=n.deptno;
select d.dname from (select deptno,count(empno)s from emp where sal<2500 group by deptno order by s desc)t,dept d where rownum=1and t.deptno=d.deptno;
select ename,job from emp where ename not in (select m.ename from emp e,emp m where e.mgr=m.empno group by m.ename);
select t.deptno,m.dname from (select deptno,count(*)s from emp group by deptno order by s desc )t,dept m where rownum=1 and m.deptno=t.deptno;
select f.deptno,e.ename,f.dname from emp e,(select d.dname,d.deptno from (select deptno,count(*)s from emp where ename like 'A%'group by deptno order by s desc)t,dept d where rownum=1)f where f.deptno=e.deptno and f.deptno=e.deptno ;
select (trunc(months_between(sysdate,hiredate)/12))*30+sal+nvl(comm,0) 最新工资 from emp; 
select d.dname 部门名称,e.ename 员工姓名,e.sal 工资 from emp e,dept d where e.deptno=d.deptno order by e.deptno,e.sal desc;
select d.deptno,d.dname,min(sal),max(sal),avg(sal),sum(sal) from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname;
select d.dname,avg(sal) from emp e,dept d where e.deptno=(select deptno from emp where ename='SMITH')and d.deptno=e.deptno group by d.dname;
select nvl(e.ename,'不存在'),nvl(e.sal,0),d.dname from dept d full join emp e on d.deptno=e.deptno;
select * from dept where deptno not in( select deptno from emp);
select d.dname,t.s,t.deptno from (select avg(sal)s,deptno from emp group by deptno order by s desc )t,dept d where t.deptno=d.deptno and rownum=1; 
select e.deptno,d.dname,e.ename,e.sal from emp e,dept d where e.sal>(select avg(sal) from emp)and e.deptno=(select deptno from (select d.deptno,d.dname,count(*)s from emp e,dept d where e.sal>(select avg(sal) from emp)and d.deptno=e.deptno group by d.deptno,d.dname order by s desc) where rownum=1 )and d.deptno=e.deptno;