Oracle SQL：經(jīng)典查詢練手第一篇
作者：胡勇
本文與大家共同討論與分享ORACLE SQL的一些常用經(jīng)典查詢，歡迎大家補充，同時你認(rèn)為有那些經(jīng)典的也可分享出來。在本文中，對每一個問題，你要是認(rèn)為有什么更好的解決方法也歡迎你及時提出。交流與分享才能共同進步嘛，感謝!
本文與大家共同討論與分享ORACLE SQL的一些常用經(jīng)典查詢，歡迎大家補充，同時你認(rèn)為有那些經(jīng)典的也可分享出來。在本文中，對每一個問題，你要是認(rèn)為有什么更好的解決方法也歡迎你及時提出。交流與分享才能共同進步嘛，感謝!
本文使用的實例表結(jié)構(gòu)與表的數(shù)據(jù)如下：
scott.emp員工表結(jié)構(gòu)如下：
- Name Type Nullable Default Comments
- -------- ------------ -------- ------- --------
- EMPNO NUMBER(4) 員工號
- ENAME VARCHAR2(10) Y 員工姓名
- JOB VARCHAR2(9) Y 工作
- MGR NUMBER(4) Y 上級編號
- HIREDATE DATE Y 雇傭日期
- SAL NUMBER(7,2) Y 薪金
- COMM NUMBER(7,2) Y 傭金
- DEPTNO NUMBER(2) Y 部門編號
scott.dept部門表：
- Name Type Nullable Default Comments
- ------ ------------ -------- ------- --------
- DEPTNO NUMBER(2) 部門編號
- DNAME VARCHAR2(14) Y 部門名稱
- LOC VARCHAR2(13) Y 地點
提示：工資 = 薪金 + 傭金
scott.emp表的現(xiàn)有數(shù)據(jù)如下：
- SQL> select * from emp;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- --------- ----- ----------- --------- --------- ------
- 7369 SMITH CLERK 7902 1980-12-17 800.00 20
- 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
- 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
- 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
- 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
- 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
- 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
- 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
- 7839 KING PRESIDENT 1981-11-17 5000.00 10
- 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
- 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
- 7900 JAMES CLERK 7698 1981-12-3 950.00 30
- 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
- 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
- 102 EricHu Developer 1455 2011-5-26 1 5500.00 14.00 10
- 104 huyong PM 1455 2011-5-26 1 5500.00 14.00 10
- 105 WANGJING Developer 1455 2011-5-26 1 5500.00 14.00 10
- 17 rows selected
Scott.dept表的現(xiàn)有數(shù)據(jù)如下：
- SQL> select * from dept;
- DEPTNO DNAME LOC
- ------ -------------- -------------
- 10 ACCOUNTING NEW YORK
- 20 RESEARCH DALLAS
- 30 SALES CHICAGO
- 40 OPERATIONS BOSTON
- 50 50abc 50def
- 60 Developer HaiKou
- 6 rows selected
用SQL完成以下問題列表：
- 列出至少有一個員工的所有部門。
- 列出薪金比“SMITH”多的所有員工。
- 列出所有員工的姓名及其直接上級的姓名。
- 列出受雇日期早于其直接上級的所有員工。
- 列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門
- 列出所有“CLERK”(辦事員)的姓名及其部門名稱。
- 列出最低薪金大于1500的各種工作。
- 列出在部門“SALES”(銷售部)工作的員工的姓名，假定不知道銷售部的部門編號。
- 列出薪金高于公司平均薪金的所有員工。
- 列出與“SCOTT”從事相同工作的所有員工。
- 列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金。
- 列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金。
- 列出在每個部門工作的員工數(shù)量、平均工資和平均服務(wù)期限。
- 列出所有員工的姓名、部門名稱和工資。
- 列出所有部門的詳細信息和部門人數(shù)。
- 列出各種工作的最低工資。
- 列出各個部門的MANAGER(經(jīng)理)的最低薪金。
- 列出所有員工的年工資,按年薪從低到高排序。
#p#
各答案如下，歡迎大家給出不出的解答方式。
- --------1．列出至少有一個員工的所有部門。---------
- SQL> select dname from dept where deptno in(select deptno from emp);
- DNAME
- --------------
- RESEARCH
- SALES
- ACCOUNTING
- --------或--------
- SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);
- DNAME
- --------------
- ACCOUNTING
- RESEARCH
- SALES
- --------2．列出薪金比“SMITH”多的所有員工。----------
- SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH');
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ----- ---------- --------- ----- ----------- --------- --------- ------
- 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
- 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
- 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
- 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
- 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
- 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
- 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
- 7839 KING PRESIDENT 1981-11-17 5000.00 10
- 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
- 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
- 7900 JAMES CLERK 7698 1981-12-3 950.00 30
- 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
- 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
- 102 EricHu Developer 1455 2011-5-26 1 5500.00 14.00 10
- 104 huyong PM 1455 2011-5-26 1 5500.00 14.00 10
- 105 WANGJING Developer 1455 2011-5-26 1 5500.00 14.00 10
- 16 rows selected
- --------3．列出所有員工的姓名及其直接上級的姓名。----------
- SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;
- ENAME BOSS_NAME
- ---------- ----------
- SMITH FORD
- ALLEN BLAKE
- WARD BLAKE
- JONES KING
- MARTIN BLAKE
- BLAKE KING
- CLARK KING
- SCOTT JONES
- KING
- TURNER BLAKE
- ADAMS SCOTT
- JAMES BLAKE
- FORD JONES
- MILLER CLARK
- EricHu
- huyong
- WANGJING
- 17 rows selected
- --------4．列出受雇日期早于其直接上級的所有員工。----------
- SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);
- ENAME
- ----------
- SMITH
- ALLEN
- WARD
- JONES
- BLAKE
- CLARK
- 6 rows selected
- --------5．列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門----------
- SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno
- 2 from dept a left join emp b on a.deptno=b.deptno;
- DNAME EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
- -------------- ----- ---------- --------- ----- ----------- --------- ------
- RESEARCH 7369 SMITH CLERK 7902 1980-12-17 800.00 20
- SALES 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 30
- SALES 7521 WARD SALESMAN 7698 1981-2-22 1250.00 30
- RESEARCH 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
- SALES 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 30
- SALES 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
- ACCOUNTING 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
- RESEARCH 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
- ACCOUNTING 7839 KING PRESIDENT 1981-11-17 5000.00 10
- SALES 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 30
- RESEARCH 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
- SALES 7900 JAMES CLERK 7698 1981-12-3 950.00 30
- RESEARCH 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
- ACCOUNTING 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
- ACCOUNTING 102 EricHu Developer 1455 2011-5-26 1 5500.00 10
- ACCOUNTING 104 huyong PM 1455 2011-5-26 1 5500.00 10
- ACCOUNTING 105 WANGJING Developer 1455 2011-5-26 1 5500.00 10
- 50abc
- OPERATIONS
- Developer
- 20 rows selected
#p#
- --------6．列出所有“CLERK”（辦事員）的姓名及其部門名稱。----------
- SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';
- ENAME DNAME
- ---------- --------------
- SMITH RESEARCH
- ADAMS RESEARCH
- JAMES SALES
- MILLER ACCOUNTING
- --------7．列出最低薪金大于1500的各種工作。----------
- SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;
- HIGHSALJOB
- ----------
- ANALYST
- Developer
- MANAGER
- PM
- PRESIDENT
- --------8．列出在部門“SALES”（銷售部）工作的員工的姓名，假定不知道銷售部的部門編號。----------
- SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');
- ENAME
- ----------
- ALLEN
- WARD
- MARTIN
- BLAKE
- TURNER
- JAMES
- 6 rows selected
- --------9．列出薪金高于公司平均薪金的所有員工。----------
- SQL> select ename from emp where sal>(select avg(sal) from emp);
- ENAME
- ----------
- JONES
- BLAKE
- SCOTT
- KING
- FORD
- EricHu
- huyong
- WANGJING
- 8 rows selected
- --------10．列出與“SCOTT”從事相同工作的所有員工。--------
- SQL> select ename from emp where job=(select job from emp where ename='SCOTT');
- ENAME
- ----------
- SCOTT
- FORD
- --------11．列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金。---------
- SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal
- 2 from emp b where b.deptno=30) and a.deptno<>30;
- ENAME SAL
- ---------- ---------
- --------12．列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金。---------
- SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
- ENAME SAL
- ---------- ---------
- JONES 2975.00
- SCOTT 4000.00
- KING 5000.00
- FORD 3000.00
- EricHu 5500.00
- huyong 5500.00
- WANGJING 5500.00
- 7 rows selected
- --------13．列出在每個部門工作的員工數(shù)量、平均工資和平均服務(wù)期限。---------
- SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal
- 2 from emp a group by deptno;
- DEPTNAME DEPTCOUNT DEPTAVGSAL
- -------------- ---------- ----------
- ACCOUNTING 6 4208.33333
- RESEARCH 5 2375
- SALES 6 1566.66666
#p#
- --------14．列出所有員工的姓名、部門名稱和工資。---------
- SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a;
- ENAME DEPTNAME SAL
- ---------- -------------- ---------
- SMITH RESEARCH 800.00
- ALLEN SALES 1600.00
- WARD SALES 1250.00
- JONES RESEARCH 2975.00
- MARTIN SALES 1250.00
- BLAKE SALES 2850.00
- CLARK ACCOUNTING 2450.00
- SCOTT RESEARCH 4000.00
- KING ACCOUNTING 5000.00
- TURNER SALES 1500.00
- ADAMS RESEARCH 1100.00
- JAMES SALES 950.00
- FORD RESEARCH 3000.00
- MILLER ACCOUNTING 1300.00
- EricHu ACCOUNTING 5500.00
- huyong ACCOUNTING 5500.00
- WANGJING ACCOUNTING 5500.00
- 17 rows selected
- --------15．列出所有部門的詳細信息和部門人數(shù)。---------
- SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;
- DEPTNO DNAME LOC DEPTCOUNT
- ------ -------------- ------------- ----------
- 10 ACCOUNTING NEW YORK 6
- 20 RESEARCH DALLAS 5
- 30 SALES CHICAGO 6
- 40 OPERATIONS BOSTON
- 50 50abc 50def
- 60 Developer HaiKou
- 6 rows selected
- --------16．列出各種工作的最低工資。---------
- SQL> select job,avg(sal) from emp group by job;
- JOB AVG(SAL)
- --------- ----------
- ANALYST 3500
- CLERK 1037.5
- Developer 5500
- MANAGER 2758.33333
- PM 5500
- PRESIDENT 5000
- SALESMAN 1400
- 7 rows selected
- --------17．列出各個部門的MANAGER（經(jīng)理）的最低薪金。--------
- SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;
- DEPTNO MIN(SAL)
- ------ ----------
- 10 2450
- 20 2975
- 30 2850
- --------18．列出所有員工的年工資,按年薪從低到高排序。---------
- SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;
- ENAME SALPERSAL
- ---------- ----------
- SMITH 9600
- JAMES 11400
- ADAMS 13200
- MILLER 15600
- TURNER 18000
- WARD 21000
- ALLEN 22800
- CLARK 29400
- MARTIN 31800
- BLAKE 34200
- JONES 35700
- FORD 36000
- SCOTT 48000
- KING 60000
- EricHu 66168
- huyong 66168
- WANGJING 66168
- 17 rows selected
原文鏈接：http://www.cnblogs.com/huyong/archive/2011/06/03/2071228.html
【編輯推薦】
責(zé)任編輯：艾婧
來源：
EricHu's Tech Space
