Oracle数据库查询训练
Oracle查询语句最后的练习
范围是scott用户专属自带的emp、dept和salgrade三张表
题目：
1.查询薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数
SELECT e.ename,e.sal,d.dname,temp.count
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) COUNT
FROM emp
GROUP BY deptno
) temp
WHERE e.sal >ALL (SELECT sal FROM emp WHERE deptno = 30)
AND (e.deptno = temp.dno)
AND (e.deptno = d.deptno);
或者
SELECT e.ename,e.sal,d.dname,temp.count
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) COUNT
FROM emp
GROUP BY deptno
) temp
WHERE e.sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30)
AND (e.deptno = temp.dno)
AND (e.deptno = d.deptno);
2.查询与 Scott 从事相同工作的所有员工姓名、职位、工资及部门名称、部门人数、领导姓名
SELECT e.ename,e.job,e.sal,d.dname,temp.count,m.ename
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp
GROUP BY deptno
) temp,emp m
WHERE (e.job IN (SELECT job FROM emp WHERE ename = 'SCOTT'))
AND (e.ename != 'SCOTT')
AND (e.deptno = d.deptno)
AND (e.deptno = temp.dno)
AND (e.mgr(+) = m.empno);
3. 查询薪金比 Smith 或 Allen 多的所有员工的编号、姓名、部门名称、领导姓名、部门人数、平均工资、最高和最低工资
SELECT e.empno,e.ename,d.dname,m.ename,temp.count,temp.avg,temp.max,temp.min
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) count,AVG(sal) avg,MAX(sal) max,MIN(sal) min
FROM emp
GROUP BY deptno
) temp,emp m
WHERE e.sal >ANY (SELECT sal FROM emp WHERE ename IN ('SMITH','ALLEN'))
AND (e.deptno = d.deptno)
AND (e.deptno = temp.dno)
AND (e.mgr(+) = m.empno);
4. 查询雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
SELECT e.empno,e.ename,d.dname,d.loc,temp.count
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp
GROUP BY deptno
) temp,emp m
WHERE (e.mgr = m.empno)
AND (e.hiredate < m.hiredate)
AND (e.deptno = d.deptno)
AND (e.deptno = temp.dno);
5. 查询所有办事员的姓名及其部门名称、部门的人数，工资等级
SELECT e.ename,d.dname,temp.count,s.grade
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp
GROUP BY deptno
) temp,salgrade s
WHERE (e.job = 'CLERK')
AND (e.deptno = d.deptno)
AND (e.deptno = temp.dno)
AND (e.sal BETWEEN s.losal AND s.hisal);
6. 查询所有部门的部门名称，部门位置和部门人数
SELECT d.dname,d.loc,temp.count
FROM dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp
GROUP BY deptno
) temp
WHERE (d.deptno = temp.dno(+));
7. 查询雇员姓名中带 ‘K’ 字符的雇员姓名及其所在部门的人数
SELECT e.ename,temp.count
FROM emp e,(
SELECT deptno dno,COUNT(empno) count
FROM emp
GROUP BY deptno
) temp
WHERE (e.ename LIKE '%K%')
AND (e.deptno = temp.dno);
8. 查询每个工资等级雇员的人数、最高工资，最低工资，平均工资
SELECT s.grade,COUNT(e.empno) count,MAX(e.sal) max,MIN(e.sal) min,TRUNC(AVG(e.sal)) avg
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade
ORDER BY grade;
9. 查询会计部的所有雇员姓名、基本工资、雇佣日期、部门位置，工资等级，部门人数
SELECT e.ename,e.sal,e.hiredate,d.loc,s.grade,temp.count
FROM emp e,dept d,salgrade s,(
SELECT deptno dno,COUNT(empno) count
FROM emp
GROUP BY deptno
) temp
WHERE (e.deptno = (SELECT deptno FROM dept WHERE dname = 'ACCOUNTING'))
AND (e.deptno = d.deptno)
AND (e.deptno = temp.dno)
AND (e.sal BETWEEN s.losal AND s.hisal)
ORDER BY s.grade;
10. 在 Oracle数据库 中有表 ’ test '，它包含属性 “ test_code”，” test_category ” 和 ” test_name”查询 test_category 取值为 ” MySQL ” 或 ” SQLServer ” 的行
SELECT *
FROM test
WHERE (test_category = 'MySQL') OR (test_category = 'SQLServer');
鉴于自己没这张表，所以没图，但是要会敲
11. 查询雇佣日期早于领导的员工的姓名、部门名称、部门人数
SELECT e.ename,d.dname,temp.count
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp
GROUP BY deptno
) temp,emp m
WHERE (e.mgr = m.empno)
AND (e.hiredate < m.hiredate)
AND (e.deptno = d.deptno)
AND (e.deptno = temp.dno);
12. 统计有领导和没有领导的员工的人数和平均工资
SELECT COUNT(empno),TRUNC(AVG(sal)) FROM emp WHERE (mgr IS NOT NULL)
union
SELECT COUNT(empno),TRUNC(AVG(sal)) FROM emp WHERE (mgr IS NULL);
13. 列出薪金比部门30都多的所有员工的姓名、基本工资、部门位置、领导姓名、所在部门的人数及最高工资
SELECT e.ename,e.sal,d.loc,m.ename,temp.count,temp.max
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) count,MAX(sal) max
FROM emp
GROUP BY deptno
) temp,emp m
WHERE (e.sal >ALL (SELECT sal FROM emp WHERE deptno = 30))
AND (e.deptno = d.deptno)
AND (e.deptno = temp.dno)
AND (e.mgr = m.empno);
14. 查询部门20中，雇佣年限超过37年而工资在2000以下的雇员信息
SELECT *
FROM emp
WHERE (deptno = 20)
AND (TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) > 37)
AND (sal < 2000);
15. 查询至少有一个员工的所有部门
SELECT deptno,COUNT(empno)
FROM emp e
HAVING COUNT(empno) > 0
GROUP BY deptno;
16. 查询基本工资比 “ Smith ” 多的所有员工信息
SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH')
ORDER BY sal,deptno;
17. 查询所有员工的姓名及其领导的姓名
SELECT e.ename,m.ename lead
FROM emp e,emp m
WHERE e.mgr = m.empno(+);
18. 查询雇佣日期早于其领导的所有员工信息
SELECT *
FROM emp e,emp m
WHERE (e.mgr = m.empno)
AND (e.hiredate < m.hiredate);
19.查询最高基本工资低于5000的所有岗位名称及从事此工作的雇员姓名，其所在的部门名称、部门人数、部门平均工资
SELECT e.ename,d.dname,temp.count,temp.avg
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) count,TRUNC(AVG(sal)) avg
FROM emp
GROUP BY deptno
)temp
WHERE (e.job IN (SELECT job FROM emp GROUP BY job HAVING COUNT(empno) < 5000 ))
AND (e.deptno = d.deptno)
AND (e.deptno = temp.dno);
oracle数据库告一段落