Oracle作业3
1、查询出有3个以上下属的员工信息(子查询--分级查询-having)
SELECT * FROM emp e WHERE (SELECT count(*) FROM emp WHERE e.empno=mgr)>=3;
2、查询所有大于本部门平均工资的员工信息(子查询--分组)
SELECT job,count(*) FROM emp WHERE sal> (SELECT avg(sal) FROM emp) GROUP BY job;
3、查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN')(子查询--聚合函数)
SELECT*FROM emp WHERE job='SALESMAN' AND sal> (SELECT avg(sal) FROM emp);
4、查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位(子查询--聚合函数)
SELECT job,count(*) FROM emp WHERE sal> (SELECT avg(sal) FROM emp) GROUP BY job;
5、查看每个员工都工作在哪个城市
SELECT d.loc FROM emp e RIGHT JOIN dept d ON e.job = d.dname;
SELECT ename,loc FROM emp e,dept d WHERE e.deptno=d.deptno;
6、查看每个员工都工作在哪个城市并在结果集中显示40部门信息
SELECT ename,loc FROM emp e,dept d WHERE e.deptno=d.deptno AND e.deptno=40;
SELECT * FROM (SELECT a.deptno deptno, loc FROM emp a, dept d WHERE a.deptno = d.deptno) c WHERE c.deptno = 40;
7、查看每个员工的工资等级
SELECT e.*, s.grade FROM emp e, salgrade s WHERE sal BETWEEN losal and hisal;
8、查看每个员工的直属管理者
SELECT e.*, e2.ename FROM emp e,emp e2 WHERE e.mgr=e2.empno;
9、查询员工的基本信息，附加其上级的姓名(自连接)
SELECT e.*, e. ename FROM emp e;
10、显示所有职员的姓名及其所在部门的名称和工资(多表查询)
SELECT e.ename, d.dname, e.sal FROM emp e, dept d;
11、查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地(多表查询)
SELECT e.empno, e.ename, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = 'RESEARCH';
12、查询各个部门的名称和员工人数(分组+多表查询)
SELECT e.deptno, c FROM (SELECT COUNT(*) c, deptno FROM emp GROUP BY deptno) e, dept d WHERE e.deptno = d.deptno;
13、查询每个员工的信息及工资级别(用到表Salgrade)(多表查询)
SELECT e.*,s.grade FROM emp e,salgrade s WHERE sal BETWEEN losal AND hisal;
14、部门编号与7934相同的人有哪些?（子查询）
SELECT e.*,s.grade FROM emp e,salgrade s WHERE sal BETWEEN losal AND hisal;
15、参加工作比7788晚的人有哪些?（子查询）
SELECT e.*,s.grade FROM emp e,salgrade s WHERE sal BETWEEN losal AND hisal;
16、每个部门拿最少工资的人?（分组）
SELECT e.*,s.grade FROM emp e,salgrade s WHERE sal BETWEEN losal AND hisal;
17、查询姓名中第二个字母为L的人的工资（函数）
select ename,sal from emp where ename like '_L%';
18、列出工资前5名的人（子查询）
SELECT rownum rn, tt.* FROM (SELECT * FROM emp ORDER BY sal DESC) tt WHERE rownum < 6;
19、大于30部门最大工资的人?（子查询--分组）
SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);
20、工资高于本部门平均工资的人(拿上游工资的人)? （子查询--分组）
SELECT * FROM emp e, (SELECT AVG(sal) avgsal, deptno FROM emp GROUP BY deptno) b WHERE e.deptno = b.deptno;
21、工资相同的人有哪些？（子查询）
SELECT * FROM emp WHERE sal in (SELECT sal FROM emp GROUP BY sal HAVING COUNT(empno) > 1);
22、显示工资比'ALLEN'高的所有员工的姓名和工资（子查询）
SELECT e.ename, e.sal FROM emp e WHERE e.sal > (SELECT m.sal FROM emp m WHERE m.ename = 'ALLEN')
23、显示与'SMITH'从事相同工作的员工的详细信息（子查询）
SELECT*FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='SMITH');
24、查询工资相同的员工的工资和姓名(子查询)
SELECT*FROM emp e WHERE (SELECT count(*) FROM emp WHERE sal=e.sal GROUP BY sal)> 1;
25、按工资进行排名,排名从1 开始,工资相同排名相同(如果两人并列第1 则没有第2 名,从第三名继续排)
SELECT sal,rank () over (ORDER BY sal DESC) FROM emp;
