sql查询

第三次 面试Sql问题记录

第一题：列出所有工资等级为2的员工
SELECT e.ename FROM emp e WHERE sal>(SELECT LO_SAL FROM GRADE WHERE GRADE.GRADE=2) AND sal<(SELECT HI_SAL FROM GRADE WHERE GRADE.GRADE=2)
第二题：列车sales部门最高的工资等级是哪级
SELECT g.GRADE FROM GRADE g WHERE g.LO_SAL<(SELECT MAX(sal) FROM emp e,dept d WHERE e.DEPTNO=d.deptno AND d.dname=‘SALES’)
AND (SELECT MAX(sal) FROM emp e,dept d WHERE e.DEPTNO=d.deptno AND d.dname=‘SALES’) <g.HI_SAL
第三题：列出部门最高的工资等级
SELECT c.*,g.GRADE FROM grade g,
(SELECT d.deptno,d.dname ,MAX(e.sal) maxsal FROM dept d , emp e WHERE e.DEPTNO=d.deptno GROUP BY e.DEPTNO ) c
WHERE g.LO_SAL<=c.maxsal AND g.HI_SAL>=c.maxsal