面试题29 30

21.显示部门名和每个部门的累计薪资，要求每个部门的累计薪资大于3000元。

答案
SELECT dname,SUM(sal)
FROM emp e,dept d
WHERE e.deptno = d.deptno
GROUP BY dname
HAVING SUM(sal)>3000;

解题思路
查询dname、sal来自于两个表，需要多表连接，关联条件为e.deptno = d.deptno;
每个部门应表示为部门分组，语法为GROUP BY deptno,dname;
分组的限制条件是累计薪资大于3000元，表示为HAVING SUM(sal)>3000。
22.如何只显示重复数据，或不显示重复数据？

答案
只显示重复数据：
SELECT * FROM tablename GROUP BY id HAVING COUNT(*)>1
不显示重复数据：
SELECT * FROM tablename GROUP BY id HAVING COUNT(*)=1

解题思路
count（*）表示多个不同记录的统计，不重复等于1，重复大于1。

子查询

1.显示工资比ALLEN高的所有员工的姓名和工资。

答案
SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE ename = 'ALLEN');

解题思路
主查询的限制条件是比ALLEN薪资高，所以首先要清楚ALLEN的薪资是多少，就可以通过子查询对ALLEN的薪资进行检索。
2.显示与SCOTT从事相同工作的员工的详细信息。

答案
SELECT * FROM emp
WHERE job = (SELECT job FROM emp WHERE ename = 'ALLEN');

解题思路
主查询的限制条件job与SCOTT职业相同，子查询应检索出SCOTT的职位，然后把结果当做主查询的查询条件。
3.显示销售部（SALES）员工姓名

答案
SELECT ename FROM emp e,dept d
WHERE e.deptno AND d.dname = 'SALES';

解题思路
查询语句中涉及部门名称和员工姓名，所以需要多表连接。
4.显示与30号部门MARTIN工资相同的员工姓名额工资。

答案
SELECT ename,sal FROM emp
WHERE sal = (SELECT sal
                         FROM emp
                         WHERE deptno = 30 AND ename = 'MAGTIN');

解题思路
主查询限制条件是薪资与30号部门并且姓名是MAGTIN的相同，所以子查询应检索30号部门并且姓名是MARTIN的薪资是多少。
5.查询所有工资高于平均工资（平均工资包括所有员工）的销售人员（SALESMAN）。

答案
SELECT * FROM emp WHERE job = 'SALESMAN'
AND sal>(SELECT AVG(sal) FROM emp);

解题思路
主查询的限制条件为职位是SALESMAN并且薪资高于平均薪资，所以子查询应检索出所有员工的平均薪资是多少，将结果作为主查询的限制条件来使用。