代码如下
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;