sql笔记

(1)显示各部门最高薪人员的姓名、部门和薪水
/*
select b.dname, max(sal)
from emp a
left join dept b
on a.deptno = b.deptno
group by b.dname
*/

SELECT e.ename, d.dname, e.sal
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE (e.deptno, e.sal) IN
(SELECT deptno, MAX(sal) FROM emp GROUP BY deptno);

(2) 显示各部门的平均薪水
select b.dname, avg(sal)
from emp a
left join dept b
on a.deptno = b.deptno
group by b.dname
//having dname = ‘SALES’;
(3)显示各个部门多少人
select count(*),dname from emp a
left join dept b
on a.deptno = b.deptno
group by dname

having count(*)>3