ORACLE SQL 经典查询练手系列文章实践笔记

前言

原作者@EricHu写的Oracle SQL经典查询联手系列文章已经很详细了，我根据Oracle 11g的实践过程重新做了梳理，之后会在扩展阅读中增加一部分Oracle官方培训教材供大家参考学习。

不懂装懂，永世饭桶

---

更新记录

2015年03月13日 - 撰写初稿

阅读原文 - https://wsgzao.github.io/post/oracle-sql/

扩展阅读

• ORACLE SQL 经典查询练手系列文章收尾(目录篇) - http://www.cnblogs.com/huyong/archive/2011/06/29/2092124.html

---

基础说明

set

--输出每页行数，缺省为24，为了避免分页，可设定为0
set pagesize 0
    
--输出一行字符个数，缺省为80
set linesize  300

column format

--如你的EMPNO，是整型的数据，输入以下命令，数字截为四位数显示
column EMPNO format 9999;
    
--如你的ENAME为字符型，输入以下命令，字符串截为10位显示
column ENAME format a10;
    
--重置为默认值
clear columns;

---

ORACLE SQL：经典查询练手第一篇

Oracle11g创建scott用户emp表

su - oracle
cd $ORACLE_HOME/rdbms/admin
sqlplus '/as sysdba'

@utlsampl.sql
    
conn scott/tiger

---

实例表结构与表的数据

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 17-DEC-80           800                    20
    7499 ALLEN      SALESMAN         7698 20-FEB-81          1600        300         30
    7521 WARD       SALESMAN         7698 22-FEB-81          1250        500         30
    7566 JONES      MANAGER          7839 02-APR-81          2975                    20
    7654 MARTIN     SALESMAN         7698 28-SEP-81          1250       1400         30
    7698 BLAKE      MANAGER          7839 01-MAY-81          2850                    30
    7782 CLARK      MANAGER          7839 09-JUN-81          2450                    10
    7788 SCOTT      ANALYST          7566 19-APR-87          3000                    20
    7839 KING       PRESIDENT             17-NOV-81          5000                    10
    7844 TURNER     SALESMAN         7698 08-SEP-81          1500          0         30
    7876 ADAMS      CLERK            7788 23-MAY-87          1100                    20
    7900 JAMES      CLERK            7698 03-DEC-81           950                    30
    7902 FORD       ANALYST          7566 03-DEC-81          3000                    20
    7934 MILLER     CLERK            7782 23-JAN-82          1300                    10

14 rows selected.

scott.dept表的现有数据

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON

---

SQL问题列表

1. 列出至少有一个员工的所有部门。
2. 列出薪金比“SMITH”多的所有员工。
3. 列出所有员工的姓名及其直接上级的姓名。
4. 列出受雇日期早于其直接上级的所有员工。
5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
6. 列出所有“CLERK”（办事员）的姓名及其部门名称。
7. 列出最低薪金大于1500的各种工作。
8. 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
9. 列出薪金高于公司平均薪金的所有员工。
10. 列出与“SCOTT”从事相同工作的所有员工。
11. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13. 列出在每个部门工作的员工数量、平均工资和平均服务期限。
14. 列出所有员工的姓名、部门名称和工资。
15. 列出所有部门的详细信息和部门人数。
16. 列出各种工作的最低工资。
17. 列出各个部门的MANAGER（经理）的最低薪金。
18. 列出所有员工的年工资,按年薪从低到高排序。

---

题目解答

--列出至少有一个员工的所有部门
SQL> select dname from dept where deptno in(select deptno from emp); 

DNAME
----------------------------
ACCOUNTING
RESEARCH
SALES

    
--列出薪金比“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 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN         7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER          7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN         7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER          7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER          7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST          7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT             17-NOV-81          5000                    10
      7844 TURNER     SALESMAN         7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK            7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK            7698 03-DEC-81           950                    30
      7902 FORD       ANALYST          7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK            7782 23-JAN-82          1300                    10

13 rows selected.


--列出所有员工的姓名及其直接上级的姓名
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

14 rows selected.


--列出受雇日期早于其直接上级的所有员工
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.


--列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno from dept a left join emp b on a.deptno=b.deptno;

DNAME           EMPNO ENAME      JOB               MGR HIREDATE            SAL     DEPTNO
---------- ---------- ---------- ---------- ---------- ------------ ---------- ----------
ACCOUNTING       7782 CLARK      MANAGER          7839 09-JUN-81          2450         10
ACCOUNTING       7839 KING       PRESIDENT             17-NOV-81          5000         10
ACCOUNTING       7934 MILLER     CLERK            7782 23-JAN-82          1300         10
RESEARCH         7566 JONES      MANAGER          7839 02-APR-81          2975         20
RESEARCH         7902 FORD       ANALYST          7566 03-DEC-81          3000         20
RESEARCH         7876 ADAMS      CLERK            7788 23-MAY-87          1100         20
RESEARCH         7369 SMITH      CLERK            7902 17-DEC-80           800         20
RESEARCH         7788 SCOTT      ANALYST          7566 19-APR-87          3000         20
SALES            7521 WARD       SALESMAN         7698 22-FEB-81          1250         30
SALES            7844 TURNER     SALESMAN         7698 08-SEP-81          1500         30
SALES            7499 ALLEN      SALESMAN         7698 20-FEB-81          1600         30
SALES            7900 JAMES      CLERK            7698 03-DEC-81           950         30
SALES            7698 BLAKE      MANAGER          7839 01-MAY-81          2850         30
SALES            7654 MARTIN     SALESMAN         7698 28-SEP-81          1250         30
OPERATIONS

15 rows selected.


--列出所有“CLERK”（办事员）的姓名及其部门名称
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK'; 

ENAME      DNAME
---------- ----------
MILLER     ACCOUNTING
SMITH      RESEARCH
ADAMS      RESEARCH
JAMES      SALES


--列出最低薪金大于1500的各种工作
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500; 

HIGHSALJOB
------------------
PRESIDENT
MANAGER
ANALYST


--列出在部门“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.


--列出薪金高于公司平均薪金的所有员工
SQL> select ename from emp where sal>(select avg(sal) from emp); 

ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD

6 rows selected.


--列出与“SCOTT”从事相同工作的所有员工
SQL> select ename from emp where job=(select job from emp where ename='SCOTT');

ENAME
----------
SCOTT
FORD


--列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal from emp b where b.deptno=30) and a.deptno<>30; 

no rows selected


--列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30); 

ENAME             SAL
---------- ----------
JONES            2975
SCOTT            3000
KING             5000
FORD             3000


--列出在每个部门工作的员工数量、平均工资和平均服务期限
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal from emp a group by deptno; 

DEPTNAME                      DEPTCOUNT DEPTAVGSAL
---------------------------- ---------- ----------
SALES                                 6 1566.66667
RESEARCH                              5       2175
ACCOUNTING                            3 2916.66667


--列出所有员工的姓名、部门名称和工资
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
ALLEN      SALES                              1600
WARD       SALES                              1250
JONES      RESEARCH                           2975
MARTIN     SALES                              1250
BLAKE      SALES                              2850
CLARK      ACCOUNTING                         2450
SCOTT      RESEARCH                           3000
KING       ACCOUNTING                         5000
TURNER     SALES                              1500
ADAMS      RESEARCH                           1100
JAMES      SALES                               950
FORD       RESEARCH                           3000
MILLER     ACCOUNTING                         1300

14 rows selected.


--列出所有部门的详细信息和部门人数
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                            3
        20 RESEARCH   DALLAS                              5
        30 SALES      CHICAGO                             6
        40 OPERATIONS BOSTON


--列出各种工作的最低工资
SQL> select job,avg(sal) from emp group by job;

JOB          AVG(SAL)
---------- ----------
CLERK          1037.5
SALESMAN         1400
PRESIDENT        5000
MANAGER    2758.33333
ANALYST          3000


--列出各个部门的MANAGER（经理）的最低薪金
SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;

    DEPTNO   MIN(SAL)
---------- ----------
        30       2850
        20       2975
        10       2450


--列出所有员工的年工资,按年薪从低到高排序
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           36000
KING            60000

14 rows selected.

---

ORACLE SQL：经典查询练手第二篇

实例表结构与表的数据

和第一篇相同

---

SQL问题列表

1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
2. 找出EMP表员工名字中含有A 和N的员工姓名。
3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
4. 列出部门编号为20的所有职位。
5. 列出不属于SALES 的部门。
6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
8. 说明以下两条SQL语句的输出结果：
   SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
   SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
9. 让SELECT 语句的输出结果为
   SELECT * FROM SALGRADE;
   SELECT * FROM BONUS;
   SELECT * FROM EMP;
   SELECT * FROM DEPT;
   ……
   列出当前用户有多少张数据表，结果集中存在多少条记录。
10. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > ‘1500’是否抱错，为什么？

---

题目解答

--找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';

ENAME
----------
BLAKE
CLARK
ADAMS


--找出EMP表员工名字中含有A和N的员工姓名
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';

ENAME
----------
ALLEN
MARTIN


--找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小
SQL> SELECT ENAME,SAL + COMM AS WAGE,COMM FROM SCOTT.EMP ORDER BY WAGE,COMM DESC;

ENAME            WAGE       COMM
---------- ---------- ----------
TURNER           1500          0
WARD             1750        500
ALLEN            1900        300
MARTIN           2650       1400
SCOTT
KING
ADAMS
JAMES
FORD
MILLER
BLAKE
JONES
SMITH
CLARK

14 rows selected.


--列出部门编号为20的所有职位
SQL> SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20;

JOB
----------
CLERK
MANAGER
ANALYST


--列出不属于SALES 的部门
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME <> 'SALES';

    DEPTNO DNAME      LOC
---------- ---------- --------------------------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        40 OPERATIONS BOSTON


--显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP WHERE SAL + COMM NOT BETWEEN 1000 AND 1500 ORDER BY WAGE DESC;

ENAME            WAGE
---------- ----------
MARTIN           2650
ALLEN            1900
WARD             1750


--显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪
SQL> SELECT ENAME NAME,JOB JOB,(SAL + COMM) * 12 AS YEAR_SALARY FROM SCOTT.EMP WHERE (SAL + COMM) * 12 BETWEEN 15000 AND 20000 AND JOB IN('MANAGER','SALESMAN');

NAME                 JOB        YEAR_SALARY
-------------------- ---------- -----------
TURNER               SALESMAN         18000


/*----- 说明以下两条SQL语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
----------*/
SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;

     EMPNO       COMM
---------- ----------
      7369
      7566
      7698
      7782
      7788
      7839
      7876
      7900
      7902
      7934

10 rows selected.


SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;

no rows selected

--说明：IS NULL是判断某个字段是否为空，为空并不等价于为空字符串或为数字0；
--而 =NULL 是判断某个值是否等于 NULL，NULL = NULL和NULL <> NULL都为 FALSE。


/*-----让SELECT 语句的输出结果为
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。
----------*/
SQL> SELECT 'SELECT * FROM '||TABLE_NAME||';' FROM USER_TABLES;

'SELECT*FROM'||TABLE_NAME||';'
------------------------------------------------------------------------------------------
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;


--语句SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错？
SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > '1500';

ENAME             SAL
---------- ----------
ALLEN            1600
JONES            2975
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
FORD             3000

7 rows selected.
--说明不会抱错，这儿存在隐式数据类型的。

---

ORACLE SQL：经典查询练手第三篇

---

ORACLE SQL：经典查询练手第四篇

---

ORACLE SQL：经典查询练手第五篇