12.24日作业

– 01查询部门平均工资在2500元以上的部门名称及平均工资。
– SELECT DNAME,avg
– FROM dept d JOIN
– (SELECT AVG(SAL) avg,DEPTNO
– FROM emp GROUP BY DEPTNO) a
– ON d.DEPTNO=a.DEPTNO
– WHERE avg > 2500

– 02查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
– SELECT JOB,avg
– FROM
– (SELECT JOB,AVG(SAL) avg
– FROM emp
– GROUP BY JOB) a
– WHERE avg > 2500
– AND a.JOB NOT LIKE (‘SA%’)
– ORDER BY avg DESC

– 03查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
– SELECT DNAME,n,x
– FROM dept e JOIN
– (SELECT DEPTNO,COUNT(*) s,
– MAX(SAL) x,MIN(SAL) n
– FROM emp GROUP BY DEPTNO) a
– ON e.DEPTNO=a.DEPTNO
– WHERE s>2

– 04查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
– SELECT a.JOB,s
– FROM (SELECT JOB,SUM(SAL) s FROM emp GROUP BY JOB) a
– WHERE a.JOB NOT LIKE (‘SALESMAN’)
– AND s>2500

– 05显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，
– 不包括最低工资小于3000的，按最低工资由高到低排序。
– SELECT m.EMPNO,m.ENAME
– FROM emp e LEFT JOIN emp m
– ON e.MGR= m.EMPNO
– GROUP BY m.EMPNO,m.ENAME
– HAVING MIN(e.SAL)<3000
– ORDER BY MIN(e.SAL) DESC

– 06查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
– SELECT EMPNO,ENAME,SAL
– FROM emp
– WHERE SAL>(SELECT sal FROM emp WHERE EMPNO=7782)
– AND JOB in(SELECT JOB FROM emp WHERE EMPNO=7369)

– 07查询工资最高的员工姓名和工资。
– SELECT ENAME,MAX(sal)
– FROM emp

– 08查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
– SELECT a.DEPTNO,d.DNAME,a.q
– FROM (SELECT MIN(SAL) q,DEPTNO FROM emp GROUP BY DEPTNO) a,
– (SELECT MIN(SAL) w FROM emp WHERE DEPTNO=10) b,
– dept d
– WHERE a.deptno=d.deptno
– AND q>w

– 09查询员工工资为其部门最低工资的员工的编号和姓名及工资。
– SELECT e.EMPNO,e.ENAME,e.SAL
– FROM emp e JOIN (SELECT MIN(SAL) c FROM emp GROUP BY DEPTNO) a
– ON e.SAL=a.c

– 10显示经理是KING的员工姓名，工资。
– SELECT e.ENAME,e.SAL
– FROM emp e JOIN emp m
– ON e.MGR=m.EMPNO
– WHERE m.ENAME=‘KING’

– 11显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
– SELECT ENAME,SAL,HIREDATE
– FROM emp
– WHERE HIREDATE>
– (SELECT HIREDATE h FROM emp WHERE ENAME=‘SMITH’)

– 12使用子查询的方式查询哪些职员在NEW YORK工作。
– SELECT ENAME
– FROM emp e JOIN dept d
– ON e.DEPTNO=d.DEPTNO
– WHERE d.LOC=‘NEW YORK’

– 13写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
– SELECT e.ENAME,e.HIREDATE
– FROM emp e JOIN (SELECT DEPTNO FROM emp WHERE ENAME=‘SMITH’) d
– ON e.DEPTNO=d.DEPTNO
– AND e.ENAME <>‘SMITH’

– 14写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
– SELECT e.EMPNO,e.ENAME
– FROM emp e JOIN (SELECT AVG(SAL) a FROM emp ) d
– ON e.sal>d.a

– 15显示部门名称和人数
– SELECT DNAME,COUNT(*)
– FROM emp e JOIN dept d
– ON e.DEPTNO=d.DEPTNO
– GROUP BY e.DEPTNO

– 16显示每个部门的最高工资的员工
– SELECT ENAME
– FROM emp e JOIN (SELECT MAX(SAL) a FROM emp GROUP BY DEPTNO) d
– ON e.SAL=d.a

– 17显示出和员工号7369部门相同的员工姓名，工资
– SELECT ENAME,SAL
– FROM emp e JOIN (SELECT DEPTNO a FROM emp WHERE EMPNO=7369) d
– ON DEPTNO=a

– 18显示出和姓名中包含“W”的员工相同部门的员工姓名
– SELECT ENAME
– FROM emp e JOIN (SELECT DEPTNO a FROM emp WHERE ENAME LIKE ‘%W%’) d
– ON DEPTNO=a