本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询,欢迎大家补充,同时你认为有那些经典的也可分享出来。在本文中,对每一个问题,你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛,感谢!
本文使用的实例表结构与表的数据如下:
scott.emp员工表结构如下:
- 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表的现有数据如下:
- 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表的现有数据如下:
- 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工作的所有员工的薪金的员工姓名和薪金。
- 列出在每个部门工作的员工数量、平均工资和平均服务期限。
- 列出所有员工的姓名、部门名称和工资。
- 列出所有部门的详细信息和部门人数。
- 列出各种工作的最低工资。
- 列出各个部门的MANAGER(经理)的最低薪金。
- 列出所有员工的年工资,按年薪从低到高排序。
#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.列出在每个部门工作的员工数量、平均工资和平均服务期限。---------
- 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.列出所有部门的详细信息和部门人数。---------
- 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(经理)的最低薪金。--------
- 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
【编辑推荐】