oracle查询

–得到工资大于自己部门平均工资的员工的信息

SQL> select ename,sal,a_sal from (select avg(sal) a_sal,deptno from emp group by deptno) t,emp e where t.deptno=e.deptno and e.sal>t.a_sal;

ENAME SAL A_SAL

---

ALLEN 1600.00 1566.66666
JONES 2975.00 2175
BLAKE 2850.00 1566.66666
SCOTT 3000.00 2175
KING 5000.00 2916.66666
FORD 3000.00 2175
6 rows selected
– 如何显示部门为 10 的部门名、员工名和工资

SQL> select d.dname,e.ename,e.sal from emp e,dept d where e.deptno=d.deptno and e.deptno=10;

DNAME ENAME SAL

---

ACCOUNTING CLARK 2450.00
ACCOUNTING KING 5000.00
ACCOUNTING MILLER 1300.00
–显示各个员工的姓名、工资及其工资的级别

SQL> select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

ENAME SAL GRADE

---

SMITH 800.00 1
JAMES 950.00 1
ADAMS 1100.00 1
WARD 1250.00 2
MARTIN 1250.00 2
MILLER 1300.00 2
TURNER 1500.00 3
ALLEN 1600.00 3
CLARK 2450.00 4
BLAKE 2850.00 4
JONES 2975.00 4
SCOTT 3000.00 4
FORD 3000.00 4
KING 5000.00 5
14 rows selected
–显示雇员名，雇员工资及所在部门的名字，并按部门排序。

SQL> select e.ename,e.sal,d.dname from emp e,dept d where d.deptno=e.deptno order by d.deptno;

ENAME SAL DNAME

---

CLARK 2450.00 ACCOUNTING
KING 5000.00 ACCOUNTING
MILLER 1300.00 ACCOUNTING
JONES 2975.00 RESEARCH
FORD 3000.00 RESEARCH
ADAMS 1100.00 RESEARCH
SMITH 800.00 RESEARCH
SCOTT 3000.00 RESEARCH
WARD 1250.00 SALES
TURNER 1500.00 SALES
ALLEN 1600.00 SALES
JAMES 950.00 SALES
BLAKE 2850.00 SALES
MARTIN 1250.00 SALES
14 rows selected
–显示员工的上级领导的姓名

SQL> select e1.ename 员工, e2.ename 领导 from emp e1,emp e2 where e1.mgr=e2.empno;

员工 领导

---

FORD JONES
SCOTT JONES
TURNER BLAKE
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
JONES KING
CLARK KING
SMITH FORD
13 rows selected
–比如显示’FORD’的上

SQL> select e1.ename 员工, e2.ename 领导 from emp e1,emp e2 where e1.mgr=e2.empno and e1.ename='FORD';

员工 领导

---

FORD JONES
– 如何查询前五名员工的姓名和工资？

SQL> select ename,sal,rownum from emp where rownum<=5;

ENAME SAL ROWNUM

---

SMITH 800.00 1
ALLEN 1600.00 2
WARD 1250.00 3
JONES 2975.00 4
MARTIN 1250.00 5
–如何查询员工的姓名和工资，并按照部门和岗位进行降序排列

`SQL> select ename,sal ,deptno,job from emp order by deptno,job` desc;

ENAME SAL DEPTNO JOB

---

KING 5000.00 10 PRESIDENT
CLARK 2450.00 10 MANAGER
MILLER 1300.00 10 CLERK
JONES 2975.00 20 MANAGER
ADAMS 1100.00 20 CLERK
SMITH 800.00 20 CLERK
SCOTT 3000.00 20 ANALYST
FORD 3000.00 20 ANALYST
MARTIN 1250.00 30 SALESMAN
TURNER 1500.00 30 SALESMAN
WARD 1250.00 30 SALESMAN
ALLEN 1600.00 30 SALESMAN
BLAKE 2850.00 30 MANAGER
JAMES 950.00 30 CLERK
14 rows selected
–如何查询工资前五名员工的姓名和工资

SQL> select t.*,rownum from (select ename,sal from emp order by sal desc) t where rownum <=5;

ENAME SAL ROWNUM

---

KING 5000.00 1
SCOTT 3000.00 2
FORD 3000.00 3
JONES 2975.00 4
BLAKE 2850.00 5
–如果显示与 smith 同一部门的所有员工？

SQL> select * from emp where deptno=(select deptno from emp where ename='SMITH');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

7369 SMITH CLERK 7902 1980/12/17 800.00 20
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
– 如何查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号

SQL> select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10);

ENAME JOB SAL DEPTNO

---

CLARK MANAGER 2450.00 10
BLAKE MANAGER 2850.00 30
JONES MANAGER 2975.00 20
KING PRESIDENT 5000.00 10
MILLER CLERK 1300.00 10
JAMES CLERK 950.00 30
ADAMS CLERK 1100.00 20
SMITH CLERK 800.00 20
8 rows selected
–：如何显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号

SQL> select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30); 

ENAME SAL DEPTNO

---

JONES 2975.00 20
SCOTT 3000.00 20
FORD 3000.00 20
KING 5000.00 10
–如何显示工资比部门 30 的任意一个员工的工资高(只要比 30 号部门中任意的一个工资高就 满足条件)的员工的姓名、工资和部门号

 SQL> select ename,sal,deptno from emp
  2  where sal>any(select sal from emp where deptno=30);

ENAME SAL DEPTNO

---

KING 5000.00 10
FORD 3000.00 20
SCOTT 3000.00 20
JONES 2975.00 20
BLAKE 2850.00 30
CLARK 2450.00 10
ALLEN 1600.00 30
TURNER 1500.00 30
MILLER 1300.00 10
WARD 1250.00 30
MARTIN 1250.00 30
ADAMS 1100.00 20
12 rows selected
–如何查询与 smith 的部门和岗位完全相同的所有雇员？

SQL> select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH'); 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

7369 SMITH CLERK 7902 1980/12/17 800.00 20
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

–如何显示高于自己部门平均工资的员工的信息 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用。

  SQL> select e.ename, e.sal, t1.myavg, e.deptno
  2  from emp e, (select avg(sal) myavg, deptno from emp group by deptno) t1
  3  where e.deptno = t1.deptno and e.sal > t1.myavg
  4  order by e.deptno
  5  ;

ENAME SAL MYAVG DEPTNO

---

KING 5000.00 2916.66666 10
JONES 2975.00 2175 20
SCOTT 3000.00 2175 20
FORD 3000.00 2175 20
ALLEN 1600.00 1566.66666 30
BLAKE 2850.00 1566.66666 30
6 rows selected

– union 取并集 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

SQL> select ename,sal,job from emp where sal>2500 union
  2  select ename,sal,job from emp where job='MANAGER';

ENAME SAL JOB

---

BLAKE 2850.00 MANAGER
CLARK 2450.00 MANAGER
FORD 3000.00 ANALYST
JONES 2975.00 MANAGER
KING 5000.00 PRESIDENT
SCOTT 3000.00 ANALYST
6 rows selected
– union all 取所有 该操作符与 union 相似，但是它不会取消重复行，而且不会排序。

SQL> select ename,sal,job from emp where sal>2500 union all
  2  select ename,sal,job from emp where job='MANAGER';

ENAME SAL JOB

---

JONES 2975.00 MANAGER
BLAKE 2850.00 MANAGER
SCOTT 3000.00 ANALYST
KING 5000.00 PRESIDENT
FORD 3000.00 ANALYST
JONES 2975.00 MANAGER
BLAKE 2850.00 MANAGER
CLARK 2450.00 MANAGER
8 rows selected
– intersect 取交集 使用该操作符用于取得两个结果集的交集。

SQL> select ename,sal,job from emp where sal>2500 intersect
  2  select ename,sal,job from emp where job='MANAGER';

ENAME SAL JOB

---

BLAKE 2850.00 MANAGER
JONES 2975.00 MANAGER
– minus 取差集 使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的 数据。
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job=’MANAGER’;

SQL> select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='MANAGER'; 

ENAME SAL JOB

---

FORD 3000.00 ANALYST
KING 5000.00 PRESIDENT
SCOTT 3000.00 ANALYST