1 SELECT * FROM emp WHERE hiredate IN ( SELECT last_day(hiredate)-2 FROM emp); 2 SELECT d.deptno,nvl(COUNT(e.empno),0) FROM dept d,emp e WHERE d.deptno=e.deptno(+) GROUP BY d.deptno;
select dname from (select tt.dname, rank() over(order by sal) rk from (select dept.dname, t.sal from dept, (select deptno, avg(sal) sal from emp group by deptno) t where dept.deptno = t.deptno)tt) ttt where rk=1
③ 求平均薪水的等级最低的部门的部门名称这道是压轴题,需要用到的表有emp和salgrade。做出来就结贴!
WITH t AS (SELECT v.deptno,sg.grade FROM (SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) v,salgrade sg WHERE v.avgsal BETWEEN sg.losal AND sg.hisal) SELECT dname FROM dept d,t WHERE d.deptno=t.deptno AND t.grade=(SELECT MIN(grade) FROM t);
哦 salgrade 表没见过 不要分了
嗯,所以最后一行应该是 WHERE d.deptno=t.deptno AND t.grade IN (SELECT MIN(grade) FROM t);感谢两位的关注。 结贴!
WHERE hiredate IN (
SELECT last_day(hiredate)-2 FROM emp);
2 SELECT d.deptno,nvl(COUNT(e.empno),0) FROM dept d,emp e
WHERE d.deptno=e.deptno(+)
GROUP BY d.deptno;
select dname from (select tt.dname, rank() over(order by sal) rk from (select dept.dname, t.sal
from dept, (select deptno, avg(sal) sal from emp group by deptno) t
where dept.deptno = t.deptno)tt) ttt where rk=1
(SELECT v.deptno,sg.grade FROM
(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) v,salgrade sg
WHERE v.avgsal BETWEEN sg.losal AND sg.hisal)
SELECT dname FROM dept d,t
WHERE d.deptno=t.deptno AND t.grade=(SELECT MIN(grade) FROM t);
嗯,所以最后一行应该是
WHERE d.deptno=t.deptno AND t.grade IN (SELECT MIN(grade) FROM t);感谢两位的关注。
结贴!