mysql查询代码记录

代码如下

1
select ename,grade from (select ename,sal from emp,dept where emp.deptno=dept.deptno and dname='ACCOUNTING') table1,salgrade where sal BETWEEN losal and hisal;


2
select AVG(table1.leadersal),dept.dname from
            (select e2.deptno as leaderdeptno, e2.ename as leader,e2.sal as leadersal from 
                            (select DISTINCT mgr from emp where mgr is not null) 
                            as t1,emp e2 where t1.mgr=e2.empno) table1,dept 
                            where table1.leaderdeptno=dept.deptno 
                            group by table1.leaderdeptno desc limit 0,1;
3
SELECT grade from (select AVG(sal) as avgsal from emp ,dept where emp.deptno=dept.deptno AND dname='SALES') as t1,salgrade 
where t1.avgsal BETWEEN losal and hisal;

4
select ename from emp where ename not in(select  e1.ename  from emp e1,emp e2 where e1.mgr=e2.empno);


5
SELECT ename from ( SELECT MAX(sal) as maxsal from emp ) t1,emp where emp.sal=t1.maxsal;


6
select ename,empno,sal,sal*12 as annual from emp where sal*12>10000;


7
SELECT (TO_DAYS(NOW())-TO_DAYS(hiredate))/7 from (SELECT hiredate from emp WHERE ename='SMITH') t1;

8
select * from (select * from emp limit 1,6) t1 ORDER BY t1.sal desc;

9
select * from emp ORDER BY hiredate desc LIMIT 0,5;

10
select * from (select AVG(sal) as avgsal,emp.deptno  from emp ,dept where emp.deptno=dept.deptno GROUP BY dept.deptno) t1,emp where emp.sal>t1.avgsal;