sql练习

select e.*, d.dname, m.ename, s.grade

from emp e,dept d, emp m,salgrade s

where e.sal > (select avg(sal) from emp)

and e.deptno =d.deptno

and e.mgr = m.mgr

and e.sal between s.losal and s.hisal

 

select  e.*, d.dname, m.ename, s.grade

from emp e

 left outer join dept d on e.deptno = d.deptno

left outer join emp m on e.mgr = m.empno

left outer join salgrade s on e.sal between s.losal and s.hisal

where e.sal > (select avg(sal)from emp)

 

select e.*, d.dname

from emp e, dept d

where e.deptno = d.deptno and job = (select job from emp where ename = '庞统')

 

select e.ename, e.sal, d.dname

from emp e, dept d

where e.deptno = d.deptno and sal > all (select sal from emp where deptno =30)

 

select y1.*, ifnull(concat((y1.zz = y2.zz)/y2.zz*100,'%'),'0%') 增长比

from tb_year y1 left outer join tb_year y2

on y1.year = y2.year + 1;