2018年12月24日

第七章课后题

1.查询部门 平均工资在2500元以上的 部门名称 及 平均工资。

SELECT dname,AVG(sal)
FROM emp e
NATURAL JOIN dept 
GROUP BY dname
HAVING AVG(sal )>2500;

2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

 SELECT job,AVG(sal)
 FROM emp 
 WHERE job NOT LIKE 'SA%'
 GROUP BY job
 HAVING AVG(sal)>2500
 ORDER BY AVG( sal) DESC

3.查询部门人数在2人以上 的部门名称、最低工资、最高工资, 并对求得的工资进行四舍五入到整数位。

 SELECT d.dname,ROUND(MIN(e.sal)),ROUND(MAX(e.sal))
 FROM dept d JOIN emp e ON d.deptno= e.deptno
 GROUP BY d.dname
 HAVING COUNT(e.ename)>2

4.查询岗位不为SALESMAN，工资和大于等于2500的岗位 及每种岗位的工资和。

SELECT job,SUM(sal)
FROM emp
WHERE job != 'SALESMAN'
GROUP BY job
HAVING SUM(sal)>=2500

5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

SELECT m.ename,m.empno,MIN(e.sal)
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno
GROUP BY e.ename,e.empno
HAVING MIN(e.sal)>3000
ORDER BY MIN(e.sal) DESC

6.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

SELECT empno,ename,sal
FROM emp 
WHERE sal>(SELECT sal FROM emp WHERE empno=7782)
AND job=(SELECT job FROM emp WHERE empno=7369)

7.查询工资最高的员工姓名和工资。

SELECT ename,sal
FROM emp
WHERE sal=(SELECT MAX(sal) FROM emp)

8.查询部门最低工资 高于10号部门 最低工资的部门的编号、名称及部门最低工资。

SELECT e.deptno,d.dname,MIN(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY deptno,dname
HAVING MIN(e.sal)>(SELECT MIN(sal) from emp WHERE deptno=10)

9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

SELECT empno,ename,sal
FROM emp

10.显示经理是KING的员工姓名，工资。

SELECT e.ename,e.sal
FROM emp e
WHERE mgr=(SELECT empno FROM emp WHERE ename='KING')

11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

SELECT ename,sal,hiredate
FROM emp
WHERE hiredate>(SELECT hiredate FROM emp WHERE ename='SMITH')

12.使用子查询的方式查询哪些职员在NEW YORK工作。

SELECT *
FROM emp
WHERE deptno=(SELECT deptno from dept WHERE loc='NEW YORK')

13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。

SELECT ename,hiredate
FROM emp
WHERE deptno=(SELECT deptno from emp WHERE ename='SMITH' AND ename!='SMITH')

14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。

SELECT deptno,ename
FROM emp
WHERE sal>(SELECT AVG(sal) from emp)

15.显示部门名称和人数。

SELECT dname,COUNT(dept.deptno)
FROM emp
NATURAL JOIN  dept
GROUP BY dept.deptno

16.显示每个部门的最高工资的员工。

select a.*
from emp a,(  select max(sal) maxsal,deptno
from emp
group by deptno) b
where a.deptno=b.deptno and a.sal=maxsal
SELECT *
from emp

17.显示出和员工号7369部门相同的员工姓名，工资。

SELECT ename,sal
FROM emp
WHERE deptno=(SELECT deptno from emp WHERE empno='7369')

18.显示出和姓名中包含“W”的员工相同部门的员工姓名。

SELECT ename
FROM emp
WHERE deptno=(SELECT deptno FROM emp WHERE ename LIKE '%W%')