连接及分组查询-练习题.doc

1、 查询雇员的编号、姓名、部门名称及部门位置SELECT e.empno,e.ename,d.dname,d.loc FROM emp e,dept dWHERE e.deptno=d.deptno2、 查询每个雇员的姓名、工作、雇员的直接上级领导的姓名SELECT e.ename,e.job,c.ename FROM emp e, emp cWHERE e.mgr=c.empno(+)3、查询每个雇员的姓名、工作、雇员的直接上级领导的姓名及所在部门名称SELECT e.ename,e.job,c.ename,d.dname FROM emp e, emp c,dept dWHERE e.mgr=c.empno AND c.deptno=d.deptnoSELECT e.ename,e.job,c.ename,d.dname FROM emp e, emp c,dept d WHERE e.mgr=c.empno(+) AND e.deptno=d.deptno(+)说明：为什么使用e.deptno=d.deptno(+)而不使用c.deptno=d.deptno(+)，因为e.mgr=c.empno(+) 左外连接将“KING”这条记录的拼好后，“KING”这条记录的c.deptno字段为NULL，所以不能使用4、查询出每个雇员的姓名、工资、部门名称、工资在公司的等级及其领导的姓名及其工资所在公司的等级l 选确定工资等级表的内容SELECT * FROM salgradel 分解：查询出每个雇员的姓名、工资、部门名称、工资所在公司的等级SELECT e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade sWHERE e.deptno=d.deptno(+) AND e.sal BETWEEN s.losal AND s.hisall 分解：其领导的姓名及工资所在公司的等级SELECT e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade FROM emp e,dept d,salgrade s,emp m,salgrade msWHERE e.deptno=d.deptno(+)AND e.sal BETWEEN s.losal AND s.hisalAND (e.mgr=m.empno(+) AND m.sal BETWEEN ms.losal AND ms.hisal缺点：没有显示KING的记录信息改进：显示全部14条记录SELECT e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno) LEFT OUTER JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal) LEFT OUTER JOIN emp m ON (e.mgr=m.empno)这里使用m表，是因为m表表示的是领导表 LEFT OUTER JOIN salgrade ms ON(m.sal BETWEEN ms.losal AND ms.hisal)5、在4题的基础上，按照如下的样式显示工资等级1：第五等级2：第四等级3：第三等级4：第二等级5：第一等级SELECT e.ename,e.sal,d.dname, DECODE(s.grade,1,第5等级,2,第4等级,3,第3等级,4,第2等级,5,第1等级) sgrade,m.ename,m.sal, DECODE(ms.grade,1,第5等级,2,第4等级,3,第3等级,4,第2等级,5,第1等级) mgrade FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno) LEFT OUTER JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal) LEFT OUTER JOIN emp m ON (e.mgr=m.empno) LEFT OUTER JOIN salgrade ms ON(m.sal BETWEEN ms.losal AND ms.hisal)6、查询所有员工的最低工资SELECT MIN(sal) FROM emp7、查询所有员工的最高工资SELECT MAX(sal) FROM emp8、查询所有员工的平均工资SELECT AVG(sal) FROM emp9、查询部门20的总工资SELECT SUM(sal) FROM emp WHERE deptno=2010、查询每个部门的员工数量SELECT deptno,COUNT(ename) FROM emp e GROUP BY deptno11、求出每个部门的平均工资SELECT deptno,AVG(sal) FROM emp GROUP BY deptno12、查询部门名称及每个部门的员工数量SELECT d.dname,COUNT(ename) FROM emp e,dept dWHERE e.deptno=d.deptnoGROUP BY d.dname13、查询平均工资大于2000的部门编号及平均工资SELECT deptno,AVG(sal)FROM empGROUP BY deptnoHAVING AVG(sal) 200014、查询非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且满足从事同一工作的雇员的月工资合计大于5000，并按月工资的合计升序排列l 显示全部的非销售人员：jobSALESMANSELECT * FROM emp WHERE jobSALESMANl 按工作分组，求出工资的总和SELECT job,SUM(sal) FROM emp WHERE jobSALESMANGROUP BY jobl 对分组的条件进行限制，工资总和大于5000SELECT job,SUM(sal) FROM emp WHERE jobSALES