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完成以下問題列表：
- 找出EMP表中的姓名（ENAME）第三個字母是A 的員工姓名。
- 找出EMP表員工名字中含有A 和N的員工姓名。
- 找出所有有傭金的員工，列出姓名、工資、傭金，顯示結果按工資從小到大，傭金從大到小。
- 列出部門編號為20的所有職位。
- 列出不屬于SALES 的部門。
- 顯示工資不在1000 到1500 之間的員工信息：名字、工資，按工資從大到小排序。
- 顯示職位為MANAGER 和SALESMAN，年薪在15000 和20000 之間的員工的信息：名字、職位、年薪。
- 說明以下兩條SQL語句的輸出結果：
- SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
- SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
- 讓SELECT 語句的輸出結果為
- SELECT * FROM SALGRADE;
- SELECT * FROM BONUS;
- SELECT * FROM EMP;
- SELECT * FROM DEPT;
- ……
列出當前用戶有多少張數據表，結果集中存在多少條記錄。
- 判斷SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱錯，為什么？
#p#
各試題解答如下（歡迎大家指出不同的方法或建議！）：
- --------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
#p#
- /*----- 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。
- ---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
- --說明不會抱錯，這兒存在隱式數據類型的。
- /*-----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;
- --......等等，在此不列出。
原文鏈接：http://www.cnblogs.com/huyong/archive/2011/06/16/2082581.html
【編輯推薦】
責任編輯：艾婧
來源：
EricHu's Tech Space
