Oracle数据库查询训练

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 ” 的行

1
2
3

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. 统计有领导和没有领导的员工的人数和平均工资

1
2
3

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. 查询所有员工的姓名及其领导的姓名

1
2
3

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数据库告一段落

数据库

Oracle

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

永恒之蓝ms17_010复现 Previous

Bugku_login 1 Next