[推荐]ORACLE SQL：经典查询练手第二篇(不懂装懂，永世饭桶！)

[推荐]ORACLE SQL：

经典查询练手第二篇(不懂装懂，永世饭桶！)

——通过知识共享树立个人品牌。

 

 本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询，欢迎大家补充，同时你认为有那些经典的也可分享出来。在本文中，对每一个问题，你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛，感谢！

 接上一篇:[推荐]ORACLE SQL：经典查询练手第一篇

本篇相对上篇来说比较简单，如果你对本篇的各测试做得不称心如意的话，我想你是时候给自己充下电了！

 

---

 

本文使用的实例表结构与表的数据如下：

scott.emp员工表结构如下：

 

SQL >   DESC  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部门表 

SQL >   DESC  SCOTT.DEPT;
Name   Type         Nullable  Default  Comments 
-- ---- ------------ -------- ------- -------- 
DEPTNO  NUMBER ( 3 )                     部门编号  
DNAME   VARCHAR2 ( 14 ) Y                部门名称  
LOC     VARCHAR2 ( 13 ) Y                地点

 

 

scott.emp表的现有数据如下：

 

SQL >   SELECT   *   FROM  SCOTT.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  SCOTT.DEPT;
 
DEPTNO DNAME          LOC
-- ---- -------------- -------------
    110  信息科         海口
     10  ACCOUNTING     NEW YORK
     20  RESEARCH       DALLAS
     30  SALES          CHICAGO
     40  OPERATIONS     BOSTON
     50  50abc          50def
     60  Developer      HaiKou
 
7  rows selected

 

 

用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 ' 是否抱错，为什么？

 

 

各试题解答如下（欢迎大家指出不同的方法或建议！）：

 

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

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

/* --------3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，
佣金从大到小。---------- */
SQL >   SELECT  ENAME,SAL  +  COMM  AS  WAGE,COMM
   2    FROM  SCOTT.EMP
   3    ORDER   BY  WAGE,COMM  DESC ;
 
ENAME            WAGE      COMM
-- -------- ---------- ---------
TURNER            1500        0.00
WARD              1750      500.00
ALLEN             1900      300.00
MARTIN            2650     1400.00
EricHu            5514       14.00
WANGJING          5514       14.00
huyong            5514       14.00
SMITH                 
JONES                 
JAMES                 
MILLER                
FORD                  
ADAMS                 
BLAKE                 
CLARK                 
SCOTT                 
KING                  
 
17  rows selected

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

-- -----5. 列出不属于SALES 的部门。----------
SQL >   SELECT   DISTINCT   *   FROM  SCOTT.DEPT  WHERE  DNAME  <>   ' SALES ' ;
 
DEPTNO DNAME          LOC
-- ---- -------------- -------------
     10  ACCOUNTING     NEW YORK
     20  RESEARCH       DALLAS
     40  OPERATIONS     BOSTON
     50  50abc          50def
     60  Developer      HaiKou
    110  信息科         海口
 
6  rows selected

-- 或者：
SQL >   SELECT   DISTINCT   *   FROM  SCOTT.DEPT  WHERE  DNAME  !=   ' SALES ' ;
SQL >   SELECT   DISTINCT   *   FROM  SCOTT.DEPT  WHERE  DNAME  NOT   IN ( ' SALES ' );
SQL >   SELECT   DISTINCT   *   FROM  SCOTT.DEPT  WHERE  DNAME  NOT   LIKE   ' SALES ' ;

-- -6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。---------
SQL >   SELECT  ENAME,SAL  +  COMM  AS  WAGE  FROM  SCOTT.EMP
   2    WHERE  SAL  +  COMM  NOT   BETWEEN   1000   AND   1500
   3    ORDER   BY  WAGE  DESC ;
 
ENAME            WAGE
-- -------- ----------
EricHu            5514
huyong            5514
WANGJING          5514
MARTIN            2650
ALLEN             1900
WARD              1750
 
6  rows selected
-- 或者
SQL >   SELECT  ENAME,SAL  +  COMM  AS  WAGE  FROM  SCOTT.EMP
   2    WHERE  SAL  +  COMM  <   1000   OR  SAL  +  COMM  >   1500
   3    ORDER   BY  WAGE  DESC ;
 
ENAME            WAGE
-- -------- ----------
EricHu            5514
huyong            5514
WANGJING          5514
MARTIN            2650
ALLEN             1900
WARD              1750
 
6  rows selected

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

/* ----- 8. 说明以下两条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 ;
 
EMPNO      COMM
-- --- ---------

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

/* -----9. 让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  BONUS;
SELECT   *   FROM  EMP;
SELECT   *   FROM  DEPT;
-- ......等等，在此不列出。

-- -10. 语句SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错？---------
SQL >   SELECT  ENAME,SAL  FROM  EMP  WHERE  SAL  >   ' 1500 ' ;
 
ENAME            SAL
-- -------- ---------
ALLEN         1600.00
JONES         2975.00
BLAKE         2850.00
CLARK         2450.00
SCOTT         4000.00
KING          5000.00
FORD          3000.00
EricHu        5500.00
huyong        5500.00
WANGJING      5500.00
 
10  rows selected
 
SQL >   SELECT  ENAME,SAL  FROM  EMP  WHERE  SAL  >   1500 ;
 
ENAME            SAL
-- -------- ---------
ALLEN         1600.00
JONES         2975.00
BLAKE         2850.00
CLARK         2450.00
SCOTT         4000.00
KING          5000.00
FORD          3000.00
EricHu        5500.00
huyong        5500.00
WANGJING      5500.00
 
10  rows selected
-- 说明不会抱错，这儿存在隐式数据类型的。