表1:emp员工信息表
表2:dept部门信息表
-- 1、按员工编号升序排列不在10号部门工作的员工信息
SELECT * FROM emp WHERE deptno !=10 ORDER BY empno;
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息,按薪水降序排列
SELECT * FROM emp WHERE ename NOT LIKE '_A%' AND sal>800 ORDER BY sal DESC;
-- 3、求每个部门的平均薪水
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno ;
-- 4、求各个部门的最高薪水
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno ;
-- 5、求每个部门每个职位的最高薪水
SELECT deptno,MAX(sal),job FROM emp GROUP BY deptno,job ;
-- 6、求平均薪水大于2000的部门编号
SELECT deptno ,AVG(sal)avg_sal FROM emp GROUP BY deptno HAVING avg_sal>2000;
SELECT deptno,AVG(sal) avgSal FROM emp GROUP BY deptno HAVING avgSal>2000;
-- 7、将员工薪水大于1200且部门平均薪水大于1500的部门编号列出来,按部门平均薪水降序排列
SELECT deptno ,AVG(sal)avg_sal FROM emp WHERE sal>1200 GROUP BY deptno HAVING avg_sal>1500 ORDER BY avg_sal DESC;
-- 8、求最高薪水的员工信息
SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
-- 9、求多于平均薪水的员工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);
-- 10、求各个部门薪水最高的员工信息 显示部门名称
SELECT * FROM dept ,
(SELECT emp.*,maxsal FROM emp ,
(SELECT MAX(sal) maxsal ,deptno FROM emp GROUP BY deptno) t
WHERE emp.deptno=t.deptno AND emp.sal=t.maxsal)tt
WHERE dept.deptno=tt.deptno