Oracle SQL：经典查询练手第一篇(1)

Oracle自带的表两张：

1） DEPT表

2）EMP表

习题：

列出至少有一个员工的所有部门。

select D.DEPTNO from DEPT D 

left join EMP E on D.DEPTNO=E.DEPTNO 

where E.EMPNO is null

列出薪金比“SMITH”多的所有员工

select EMP.EMPNO, EMP.SAL

from EMP

where EMP.SAL> (SELECT EMP.SAL

from EMP

where EMP.ENAME='SMITH')

列出所有员工的姓名及其直接上级的姓名。

select e.ENAME, (select EMP.ENAME from emp where EMP.EMPNO=e.MGR) from EMP e

列出受雇日期早于其直接上级的所有员工。

select e.ENAME

from EMP e

where e.hiredate < (select EMP.HIREDATE from emp where EMP.EMPNO=e.MGR)

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select * 

from DEPT d left join EMP e

on d.deptno=e.deptno

列出所有“CLERK”(办事员)的姓名及其部门名称。

SELECT e.ENAME, (SELECT DEPT.DNAME from DEPT where e.deptno = DEPT.DEPTNO) 

from EMP e

where e.job='CLERK'

列出最低薪金大于1500的各种工作。

select e.job 

from EMP e

group by e.job

having min(e.sal)>1500

列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。

select EMP.ENAME

from EMP

where EMP.DEPTNO = (select DEPT.DEPTNO from DEPT where DEPT.DNAME='SALES')

列出薪金高于公司平均薪金的所有员工。

SELECT *

FROM EMP

WHERE EMP.SAL>(SELECT AVG(EMP.SAL) FROM EMP)

列出与“SCOTT”从事相同工作的所有员工。

SELECT * 

FROM EMP

WHERE EMP.JOB=(SELECT EMP.JOB FROM EMP WHERE EMP.ENAME='SCOTT')

列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

SELECT e.ENAME, e.SAL

FROM EMP e

WHERE e.SAL IN (SELECT EMP.SAL FROM EMP WHERE EMP.DEPTNO=30)

列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

SELECT e.ENAME, e.SAL

FROM EMP e

WHERE e.SAL > (SELECT MAX(EMP.SAL) FROM EMP WHERE EMP.DEPTNO=30)

列出在每个部门工作的员工数量、平均工资和平均服务期限。

SELECT count(EMP.EMPNO) num, sum(EMP.SAL) sumsal, avg(round(TO_NUMBER(SYSDATE-EMP.HIREDATE))) 

from emp GROUP BY EMP.DEPTNO

列出所有员工的姓名、部门名称和工资。

SELECT e.ename, e. sal, d. dname

from emp e LEFT JOIN dept d

on e.deptno=d.deptno

列出所有部门的详细信息和部门人数。

SELECT D.* , COUNT(E.ENAME) 

FROM DEPT D LEFT JOIN EMP E 

ON D.DEPTNO=E.DEPTNO 

GROUP BY D.DEPTNO, D.DNAME, D.LOC 

列出各种工作的最低工资。

select min(sal), job from emp group by job

列出各个部门的MANAGER(经理)的最低薪金。

select MIN(sal), DEPTNO 

from emp where job='MANAGER' GROUP BY DEPTNO

列出所有员工的年工资,按年薪从低到高排序。

SELECT EMP.ENAME, sal from EMP order by sal