本文使用的實例表結構與表的數據如下：
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
--說明不會抱錯，這兒存在隱式數據類型的。
posted on 2012-09-18 15:02
kxbin 閱讀(290)
評論(0) 編輯 收藏 所屬分類:
ORACLE 、
轉發