---
# System prepended metadata

title: SQL 30題作業&解答
tags: [SQL]

---

# SQL 30題作業&解答
###### tags: `SQL`

```clike=
1. 顯示每一位員工的職務是XXXX並給予一個名稱Jobs
SELECT CAST(EMPNO AS NVARCHAR)+' '+ENAME EMP, ' IS '+JOB JOB
  FROM EMP;

	EMP                                       JOB
	----------------------------------------- -------------
	7369 SMITH                                 IS CLERK
	7499 ALLEN                                 IS SALESMAN
	7521 WARD                                  IS SALESMAN
	7566 JONES                                 IS MANAGER
	7654 MARTIN                                IS SALESMAN
	7698 BLAKE                                 IS MANAGER
	7782 CLARK                                 IS MANAGER
	7788 SCOTT                                 IS ANALYST
	7839 KING                                  IS PRESIDENT
	7844 TURNER                                IS SALESMAN
	7876 ADAMS                                 IS CLERK
	7900 JAMES                                 IS CLERK
	7902 FORD                                  IS ANALYST
	7934 MILLER                                IS CLERK


2. 列出各部門中現有的職務分類
SELECT DISTINCT DEPTNO, JOB
  FROM EMP
 ORDER BY DEPTNO;

	DEPTNO      JOB
	----------- ---------
	10          CLERK
	10          MANAGER
	10          PRESIDENT
	20          ANALYST
	20          CLERK
	20          MANAGER
	30          CLERK
	30          MANAGER
	30          SALESMAN

3. 列出下表
SELECT EMPNO 編號, ENAME 姓名, JOB 職務, SAL 薪資
  FROM EMP;

	編號          姓名         職務        薪資
	----------- ---------- --------- ---------------------------------------
	7369        SMITH      CLERK     800.00
	7499        ALLEN      SALESMAN  1600.00
	7521        WARD       SALESMAN  1250.00
	7566        JONES      MANAGER   2975.00
	7654        MARTIN     SALESMAN  1250.00
	7698        BLAKE      MANAGER   2850.00
	7782        CLARK      MANAGER   2450.00
	7788        SCOTT      ANALYST   3000.00
	7839        KING       PRESIDENT 5000.00
	7844        TURNER     SALESMAN  1500.00
	7876        ADAMS      CLERK     1100.00
	7900        JAMES      CLERK     950.00
	7902        FORD       ANALYST   3000.00
	7934        MILLER     CLERK     1300.00


4. 列出姓名中含有M與A字母的員工
SELECT *
  FROM EMP
 WHERE ENAME LIKE '%M%'
   AND ENAME LIKE '%A%'

	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7654        MARTIN     SALESMAN  7698        1981-09-28 00:00:00.000 1250.00                                 1400.00                                 30
	7876        ADAMS      CLERK     7788        1987-05-23 00:00:00.000 1100.00                                 NULL                                    20
	7900        JAMES      CLERK     7698        1981-12-03 00:00:00.000 950.00                                  NULL                                    30


5. 列出職務為SALESMAN且COMM=0的員工
SELECT *
  FROM EMP
 WHERE JOB = 'SALESMAN'
   AND COMM = 0;

	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7844        TURNER     SALESMAN  7698        1981-09-08 00:00:00.000 1500.00                                 0.00                                    30


6. 列出1981年到職的員工
SELECT *
  FROM EMP
 WHERE DATEPART(YEAR,HIREDATE) = 1981
 ORDER BY EMPNO;

	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7499        ALLEN      SALESMAN  7698        1981-02-20 00:00:00.000 1600.00                                 300.00                                  30
	7521        WARD       SALESMAN  7698        1981-02-22 00:00:00.000 1250.00                                 500.00                                  30
	7566        JONES      MANAGER   7839        1981-04-02 00:00:00.000 2975.00                                 NULL                                    20
	7654        MARTIN     SALESMAN  7698        1981-09-28 00:00:00.000 1250.00                                 1400.00                                 30
	7698        BLAKE      MANAGER   7839        1981-05-01 00:00:00.000 2850.00                                 NULL                                    30
	7782        CLARK      MANAGER   7839        1981-06-09 00:00:00.000 2450.00                                 NULL                                    10
	7839        KING       PRESIDENT NULL        1981-11-17 00:00:00.000 5000.00                                 NULL                                    10
	7844        TURNER     SALESMAN  7698        1981-09-08 00:00:00.000 1500.00                                 0.00                                    30
	7900        JAMES      CLERK     7698        1981-12-03 00:00:00.000 950.00                                  NULL                                    30
	7902        FORD       ANALYST   7566        1981-12-03 00:00:00.000 3000.00                                 NULL                                    20


7. 依各職務類別及薪資降冪排序
SELECT EMPNO, ENAME, JOB, SAL
  FROM EMP
 ORDER BY JOB, SAL DESC;
	EMPNO       ENAME      JOB       SAL
	----------- ---------- --------- -----------
	7788        SCOTT      ANALYST   3000.00
	7902        FORD       ANALYST   3000.00
	7934        MILLER     CLERK     1300.00
	7876        ADAMS      CLERK     1100.00
	7900        JAMES      CLERK     950.00
	7369        SMITH      CLERK     800.00
	7566        JONES      MANAGER   2975.00
	7698        BLAKE      MANAGER   2850.00
	7782        CLARK      MANAGER   2450.00
	7839        KING       PRESIDENT 5000.00
	7499        ALLEN      SALESMAN  1600.00
	7844        TURNER     SALESMAN  1500.00
	7521        WARD       SALESMAN  1250.00
	7654        MARTIN     SALESMAN  1250.00

8. 顯示員工的資訊並依下列職務的順序排列
   PRESIDENT
   MANAGER
   ANALYST
   CLERK
   SALESMAN

SELECT EMPNO, ENAME, JOB
  FROM EMP
 ORDER BY CASE JOB
            WHEN 'PRESIDENT' THEN 1
            WHEN 'MANAGER'   THEN 2
            WHEN 'ANALYST'   THEN 3
            WHEN 'CLERK'     THEN 4
            WHEN 'SALESMAN'  THEN 5
            ELSE 6
          END;

	EMPNO       ENAME      JOB
	----------- ---------- ---------
	7839        KING       PRESIDENT
	7698        BLAKE      MANAGER
	7782        CLARK      MANAGER
	7566        JONES      MANAGER
	7788        SCOTT      ANALYST
	7902        FORD       ANALYST
	7934        MILLER     CLERK
	7876        ADAMS      CLERK
	7900        JAMES      CLERK
	7369        SMITH      CLERK
	7499        ALLEN      SALESMAN
	7521        WARD       SALESMAN
	7654        MARTIN     SALESMAN
	7844        TURNER     SALESMAN


9. 依據職務計算出員工調整後的薪資
   SALESMAN 調5%
   MANAGER  調7%
   PRESIDENT調10%
   其餘的不變

SELECT EMPNO, ENAME, JOB, SAL, 
       CASE JOB
         WHEN 'SALESMAN'  THEN SAL*1.05
         WHEN 'MANAGER'   THEN SAL*1.07
         WHEN 'PRESIDENT' THEN SAL*1.1
         ELSE SAL
       END NEW_SAL
  FROM EMP
 ORDER BY JOB;
 
	EMPNO       ENAME      JOB       SAL                                     NEW_SAL
	----------- ---------- --------- --------------------------------------- ---------------------------------------
	7788        SCOTT      ANALYST   3000.00                                 3000.0000
	7902        FORD       ANALYST   3000.00                                 3000.0000
	7934        MILLER     CLERK     1300.00                                 1300.0000
	7876        ADAMS      CLERK     1100.00                                 1100.0000
	7900        JAMES      CLERK     950.00                                  950.0000
	7369        SMITH      CLERK     800.00                                  800.0000
	7566        JONES      MANAGER   2975.00                                 3183.2500
	7698        BLAKE      MANAGER   2850.00                                 3049.5000
	7782        CLARK      MANAGER   2450.00                                 2621.5000
	7839        KING       PRESIDENT 5000.00                                 5500.0000
	7844        TURNER     SALESMAN  1500.00                                 1575.0000
	7654        MARTIN     SALESMAN  1250.00                                 1312.5000
	7499        ALLEN      SALESMAN  1600.00                                 1680.0000
	7521        WARD       SALESMAN  1250.00                                 1312.5000
 

10 .列出部門=10,各員工的年資
SELECT EMPNO, ENAME,  DATEDIFF ( YEAR , HIREDATE , GETDATE() )  年資
  FROM EMP
 WHERE DEPTNO = 10;
 
	EMPNO       ENAME      年資
	----------- ---------- -----------
	7782        CLARK      39
	7839        KING       39
	7934        MILLER     38

11.列出下面的結果
SELECT EMPNO, ENAME, RIGHT(REPLICATE('*',10)+'$'+CAST(CAST(SAL AS INT) AS NVARCHAR),10), DEPTNO
  FROM EMP
 WHERE DEPTNO = 10;

	EMPNO       ENAME                 DEPTNO
	----------- ---------- ---------- -----------
	7782        CLARK      *****$2450 10
	7839        KING       *****$5000 10
	7934        MILLER     *****$1300 10

12.依到職日排列下面的結果
--SET LANGUAGE 繁體中文
SET LANGUAGE us_english;
SELECT EMPNO, ENAME, HIREDATE, DATENAME(weekday, HIREDATE) DAYNAME
  FROM EMP
 ORDER BY CASE DATEPART(weekday, HIREDATE)
            WHEN 1 THEN 8
            ELSE DATEPART(weekday, HIREDATE)
          END;

	Changed language setting to us_english.
	EMPNO       ENAME      HIREDATE                DAYNAME
	----------- ---------- ----------------------- ------------------------------
	7654        MARTIN     1981-09-28 00:00:00.000 Monday
	7782        CLARK      1981-06-09 00:00:00.000 Tuesday
	7839        KING       1981-11-17 00:00:00.000 Tuesday
	7844        TURNER     1981-09-08 00:00:00.000 Tuesday
	7369        SMITH      1980-12-17 00:00:00.000 Wednesday
	7900        JAMES      1981-12-03 00:00:00.000 Thursday
	7902        FORD       1981-12-03 00:00:00.000 Thursday
	7566        JONES      1981-04-02 00:00:00.000 Thursday
	7499        ALLEN      1981-02-20 00:00:00.000 Friday
	7698        BLAKE      1981-05-01 00:00:00.000 Friday
	7876        ADAMS      1987-05-23 00:00:00.000 Saturday
	7934        MILLER     1982-01-23 00:00:00.000 Saturday
	7788        SCOTT      1987-04-19 00:00:00.000 Sunday
	7521        WARD       1981-02-22 00:00:00.000 Sunday

13.列出當月生日的員工
SELECT *
  FROM EMP
 WHERE DATEPART(MONTH,HIREDATE) = DATEPART(MONTH,GETDATE())
 ORDER BY EMPNO;

14.計算各職務的人員人數及薪資總和
SELECT JOB, COUNT(*) CNT, SUM(SAL) COST
  FROM EMP
 GROUP BY JOB
 ORDER BY 1;
 
	JOB       CNT         COST
	--------- ----------- ---------------------------------------
	ANALYST   2           6000.00
	CLERK     4           4150.00
	MANAGER   3           8275.00
	PRESIDENT 1           5000.00
	SALESMAN  4           5600.00

15.計算各職務的平均薪資且>2000,依平均值排序列出
SELECT JOB, AVG(SAL)
  FROM EMP
 GROUP BY JOB
 HAVING AVG(SAL)>2000
 ORDER BY 2;

	JOB       
	--------- ---------------------------------------
	MANAGER   2758.333333
	ANALYST   3000.000000
	PRESIDENT 5000.000000


16.列出各年度僱用的人數,如下表
SELECT SUM(CASE WHEN DATEPART(YEAR,HIREDATE)=1980 THEN 1 ELSE 0 END) YY1980,
       SUM(CASE WHEN DATEPART(YEAR,HIREDATE)=1981 THEN 1 ELSE 0 END) YY1981,
       SUM(CASE WHEN DATEPART(YEAR,HIREDATE)=1982 THEN 1 ELSE 0 END) YY1982,
       SUM(CASE WHEN DATEPART(YEAR,HIREDATE)=1983 THEN 1 ELSE 0 END) YY1983
  FROM EMP;

	YY1980      YY1981      YY1982      YY1983
	----------- ----------- ----------- -----------
	1           10          1           0

17.列出下表
SELECT JOB,
       SUM(CASE WHEN DEPTNO=10 THEN 1 ELSE 0 END) DEPT10,
       SUM(CASE WHEN DEPTNO=20 THEN 1 ELSE 0 END) DEPT20,
       SUM(CASE WHEN DEPTNO=30 THEN 1 ELSE 0 END) DEPT30
  FROM EMP
 GROUP BY JOB;

	JOB       DEPT10      DEPT20      DEPT30
	--------- ----------- ----------- -----------
	ANALYST   0           2           0
	CLERK     1           2           1
	MANAGER   1           1           1
	PRESIDENT 1           0           0
	SALESMAN  0           0           4


18.統計各級數的員工人數, 並依級數排序	人數
	薪資    0 ~ 1200    A級
	薪資 1201 ~ 2000    B級
	薪資 2001 ~ 2800    C級
	薪資 2801 ~ 3500    D級
	其他                E級

SELECT A.TYPE, COUNT(*) CNT
  FROM (SELECT CASE
                 WHEN SAL BETWEEN 0    AND 1200 THEN 'A'
                 WHEN SAL BETWEEN 1201 AND 2000 THEN 'B'
                 WHEN SAL BETWEEN 2001 AND 2800 THEN 'C'
                 WHEN SAL BETWEEN 2801 AND 3500 THEN 'D'
                 ELSE 'E'
               END TYPE
          FROM EMP) A
GROUP BY A.TYPE;

	TYPE CNT
	---- -----------
	A    3
	B    5
	C    1
	D    4
	E    1

19.列出各MANAGER中配置的員工人數
SELECT MGR, COUNT(*) CNT
  FROM EMP
 GROUP BY MGR;

	MGR         CNT
	----------- -----------
	NULL        1
	7566        2
	7698        5
	7782        1
	7788        1
	7839        3
	7902        1

20.列出各部門的人數
SELECT DEPTNO, COUNT(*) CNT
  FROM EMP
 GROUP BY DEPTNO;

	DEPTNO      CNT
	----------- -----------
	10          3
	20          5
	30          6

21.列出所有客戶的總交易金額
SELECT A.CUSTOMER_ID, A.CUST_FIRST_NAME, A.CUST_LAST_NAME,
       SUM(B.ORDER_TOTAL) TOTAL
  FROM CUSTOMERS A LEFT JOIN ORDERS B
    ON (A.CUSTOMER_ID = B.CUSTOMER_ID)
 GROUP BY A.CUSTOMER_ID, A.CUST_FIRST_NAME, A.CUST_LAST_NAME
 ORDER BY A.CUSTOMER_ID, A.CUST_FIRST_NAME, A.CUST_LAST_NAME;

	CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       TOTAL
	----------- -------------------- -------------------- ---------------------------------------
	101         Constantin           Welles               190395.10
	102         Harrison             Pacino               69211.40
	103         Manisha              Taylor               20591.40
	104         Harrison             Sutherland           146605.50
	105         Matthias             MacGraw              61376.50
	106         Matthias             Hannah               36199.50
	107         Matthias             Cruise               155613.20
	108         Meenakshi            Mason                213399.70
	109         Christian            Cage                 265255.60
	110         Charlie              Sutherland           NULL
	111         Charlie              Pacino               NULL
	...


22.列出所有商品的銷售量
SELECT A.PRODUCT_ID, A.PRODUCT_NAME, SUM(B.QUANTITY) QTY
  FROM PRODUCT_INFORMATION A LEFT JOIN ORDER_ITEMS B
    ON (A.PRODUCT_ID = B.PRODUCT_ID)
 GROUP BY A.PRODUCT_ID, A.PRODUCT_NAME
 ORDER BY A.PRODUCT_ID, A.PRODUCT_NAME;

	...
	1778        C for SPNIX3.3 - 1 Seat                            NULL
	1779        C for SPNIX3.3 - Doc                               NULL
	1780        C for SPNIX3.3 - Sys                               NULL
	1781        CDW 20/48/E                                        9
	1782        Compact 400/DQ                                     4
	1787        CPU D300                                           5
	1788        CPU D600                                           NULL
	1791        Industrial 700/HD                                  3
	1792        Industrial 600/DQ                                  NULL
	1794        OSI 8-16/IL                                        NULL
	1797        Inkjet C/8/HQ                                      28
	...

23.列出所有員工的銷售業績
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, SUM(B.ORDER_TOTAL) TOTAL
  FROM EMPLOYEES A LEFT JOIN ORDERS B
    ON (A.EMPLOYEE_ID = B.SALES_REP_ID)
 WHERE A.JOB_ID LIKE 'SA%'
 GROUP BY A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME
 ORDER BY A.EMPLOYEE_ID;
 
	EMPLOYEE_ID FIRST_NAME           LAST_NAME                 TOTAL
	----------- -------------------- ------------------------- ---------------------------------------
	145         John                 Russell                   NULL
	146         Karen                Partners                  NULL
	147         Alberto              Errazuriz                 NULL
	148         Gerald               Cambrault                 NULL
	149         Eleni                Zlotkey                   NULL
	150         Peter                Tucker                    NULL
	151         David                Bernstein                 NULL
	152         Peter                Hall                      NULL
	153         Christopher          Olsen                     114215.70
	154         Nanette              Cambrault                 171973.10
	155         Oliver               Tuvault                   134415.20
	156         Janette              King                      202617.60
	157         Patrick              Sully                     NULL


24.列出所有部門下的員工
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.EMPLOYEE_ID, B.FIRST_NAME, B.LAST_NAME
  FROM DEPARTMENTS A LEFT JOIN EMPLOYEES B
    ON (A.DEPARTMENT_ID = B.DEPARTMENT_ID)
 ORDER BY A.DEPARTMENT_ID;

	DEPARTMENT_ID DEPARTMENT_NAME                EMPLOYEE_ID FIRST_NAME           LAST_NAME
	------------- ------------------------------ ----------- -------------------- -------------------------
	10            Administration                 200         Jennifer             Whalen
	20            Marketing                      201         Michael              Hartstein
	20            Marketing                      202         Pat                  Fay
	30            Purchasing                     114         Den                  Raphaely
	30            Purchasing                     115         Alexander            Khoo
	30            Purchasing                     116         Shelli               Baida
	30            Purchasing                     117         Sigal                Tobias
	30            Purchasing                     118         Guy                  Himuro
	30            Purchasing                     301         NULL                 A
	40            Human Resources                203         Susan                Mavris
	50            Shipping                       180         Winston              Taylor
	50            Shipping                       181         Jean                 Fleaur
	50            Shipping                       182         Martha               Sullivan


25.列出薪資比員工編號7900高的員工
SELECT *
  FROM EMP
 WHERE SAL > (SELECT SAL 
                FROM EMP 
               WHERE EMPNO=7900)
 ORDER BY EMPNO;
 
	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7499        ALLEN      SALESMAN  7698        1981-02-20 00:00:00.000 1600.00                                 300.00                                  30
	7521        WARD       SALESMAN  7698        1981-02-22 00:00:00.000 1250.00                                 500.00                                  30
	7566        JONES      MANAGER   7839        1981-04-02 00:00:00.000 2975.00                                 NULL                                    20
	7654        MARTIN     SALESMAN  7698        1981-09-28 00:00:00.000 1250.00                                 1400.00                                 30
	7698        BLAKE      MANAGER   7839        1981-05-01 00:00:00.000 2850.00                                 NULL                                    30
	7782        CLARK      MANAGER   7839        1981-06-09 00:00:00.000 2450.00                                 NULL                                    10
	7788        SCOTT      ANALYST   7566        1987-04-19 00:00:00.000 3000.00                                 NULL                                    20
	7839        KING       PRESIDENT NULL        1981-11-17 00:00:00.000 5000.00                                 NULL                                    10
	7844        TURNER     SALESMAN  7698        1981-09-08 00:00:00.000 1500.00                                 0.00                                    30
	7876        ADAMS      CLERK     7788        1987-05-23 00:00:00.000 1100.00                                 NULL                                    20
	7902        FORD       ANALYST   7566        1981-12-03 00:00:00.000 3000.00                                 NULL                                    20
	7934        MILLER     CLERK     7782        1982-01-23 00:00:00.000 1300.00                                 NULL                                    10


26.列出各員工薪資佔該部門薪資成本的百分比
SELECT A.EMPNO, A.ENAME, A.DEPTNO, A.SAL, A.SAL*100/B.COST PCT
  FROM EMP A, (SELECT DEPTNO, SUM(SAL) COST
                 FROM EMP
                GROUP BY DEPTNO) B
 WHERE A.DEPTNO = B.DEPTNO
 ORDER BY A.DEPTNO;

	EMPNO       ENAME      DEPTNO      SAL                                     PCT
	----------- ---------- ----------- --------------------------------------- ---------------------------------------
	7782        CLARK      10          2450.00                                 28.000000000000000000000000000
	7839        KING       10          5000.00                                 57.142857142857142857142857142
	7934        MILLER     10          1300.00                                 14.857142857142857142857142857
	7902        FORD       20          3000.00                                 27.586206896551724137931034482
	7876        ADAMS      20          1100.00                                 10.114942528735632183908045977
	7788        SCOTT      20          3000.00                                 27.586206896551724137931034482
	7369        SMITH      20          800.00                                  7.356321839080459770114942528
	7566        JONES      20          2975.00                                 27.356321839080459770114942528
	7654        MARTIN     30          1250.00                                 13.297872340425531914893617021
	7698        BLAKE      30          2850.00                                 30.319148936170212765957446808
	7499        ALLEN      30          1600.00                                 17.021276595744680851063829787
	7521        WARD       30          1250.00                                 13.297872340425531914893617021
	7844        TURNER     30          1500.00                                 15.957446808510638297872340425
	7900        JAMES      30          950.00                                  10.106382978723404255319148936


27.列出各部門薪資最高的員工
SELECT A.*
  FROM EMP A, (SELECT DEPTNO, MAX(SAL) MAXSAL
                 FROM EMP
                GROUP BY DEPTNO) B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.SAL = B.MAXSAL
 ORDER BY A.DEPTNO;

EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
7839        KING       PRESIDENT NULL        1981-11-17 00:00:00.000 5000.00                                 NULL                                    10
7902        FORD       ANALYST   7566        1981-12-03 00:00:00.000 3000.00                                 NULL                                    20
7788        SCOTT      ANALYST   7566        1987-04-19 00:00:00.000 3000.00                                 NULL                                    20
7698        BLAKE      MANAGER   7839        1981-05-01 00:00:00.000 2850.00                                 NULL                                    30


28.列出各部門薪資成本佔全公司薪資總成本的百分比
SELECT A.EMPNO, A.ENAME, A.SAL, A.SAL*100/B.TOTAL PCT
  FROM EMP A, (SELECT SUM(SAL) TOTAL FROM EMP) B
 ORDER BY A.EMPNO;

	EMPNO       ENAME      SAL                                     PCT
	----------- ---------- --------------------------------------- ---------------------------------------
	7369        SMITH      800.00                                  2.756244616709732988802756244
	7499        ALLEN      1600.00                                 5.512489233419465977605512489
	7521        WARD       1250.00                                 4.306632213608957795004306632
	7566        JONES      2975.00                                 10.249784668389319552110249784
	7654        MARTIN     1250.00                                 4.306632213608957795004306632
	7698        BLAKE      2850.00                                 9.819121447028423772609819121
	7782        CLARK      2450.00                                 8.440999138673557278208440999
	7788        SCOTT      3000.00                                 10.335917312661498708010335917
	7839        KING       5000.00                                 17.226528854435831180017226528
	7844        TURNER     1500.00                                 5.167958656330749354005167958
	7876        ADAMS      1100.00                                 3.789836347975882859603789836
	7900        JAMES      950.00                                  3.273040482342807924203273040
	7902        FORD       3000.00                                 10.335917312661498708010335917
	7934        MILLER     1300.00                                 4.478897502153316106804478897



29.列出與JAMES員工相同部門的所有員工
SELECT *
  FROM EMP
 WHERE DEPTNO = (SELECT DEPTNO
                   FROM EMP
                  WHERE ENAME = 'JAMES')
 ORDER BY EMPNO;
 
	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7499        ALLEN      SALESMAN  7698        1981-02-20 00:00:00.000 1600.00                                 300.00                                  30
	7521        WARD       SALESMAN  7698        1981-02-22 00:00:00.000 1250.00                                 500.00                                  30
	7654        MARTIN     SALESMAN  7698        1981-09-28 00:00:00.000 1250.00                                 1400.00                                 30
	7698        BLAKE      MANAGER   7839        1981-05-01 00:00:00.000 2850.00                                 NULL                                    30
	7844        TURNER     SALESMAN  7698        1981-09-08 00:00:00.000 1500.00                                 0.00                                    30
	7900        JAMES      CLERK     7698        1981-12-03 00:00:00.000 950.00                                  NULL                                    30


30.列出員工資訊與該部門薪資的成本
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO, B.COST
  FROM EMP A, (SELECT DEPTNO, SUM(SAL) COST
                 FROM EMP
                GROUP BY DEPTNO) B
 WHERE A.DEPTNO = B.DEPTNO
 ORDER BY A.DEPTNO;

	EMPNO       ENAME      SAL                                     DEPTNO      COST
	----------- ---------- --------------------------------------- ----------- ---------------------------------------
	7782        CLARK      2450.00                                 10          8750.00
	7839        KING       5000.00                                 10          8750.00
	7934        MILLER     1300.00                                 10          8750.00
	7902        FORD       3000.00                                 20          10875.00
	7876        ADAMS      1100.00                                 20          10875.00
	7788        SCOTT      3000.00                                 20          10875.00
	7369        SMITH      800.00                                  20          10875.00
	7566        JONES      2975.00                                 20          10875.00
	7654        MARTIN     1250.00                                 30          9400.00
	7698        BLAKE      2850.00                                 30          9400.00
	7499        ALLEN      1600.00                                 30          9400.00
	7521        WARD       1250.00                                 30          9400.00
	7844        TURNER     1500.00                                 30          9400.00
	7900        JAMES      950.00                                  30          9400.00


```

1. 顯示每一位員工的職務是XXXX並給予一個名稱Jobs
cast t or n 值
SELECT CAST(EMPNO AS NVARCHAR)+' '+ENAME EMP, ' IS '+JOB JOB
  FROM EMP;

	EMP                                       JOB
	----------------------------------------- -------------
	7369 SMITH                                 IS CLERK
	7499 ALLEN                                 IS SALESMAN
	7521 WARD                                  IS SALESMAN
	7566 JONES                                 IS MANAGER
	7654 MARTIN                                IS SALESMAN
	7698 BLAKE                                 IS MANAGER
	7782 CLARK                                 IS MANAGER
	7788 SCOTT                                 IS ANALYST
	7839 KING                                  IS PRESIDENT
	7844 TURNER                                IS SALESMAN
	7876 ADAMS                                 IS CLERK
	7900 JAMES                                 IS CLERK
	7902 FORD                                  IS ANALYST
	7934 MILLER                                IS CLERK


2. 列出各部門中現有的職務分類
distinct不重複
SELECT DISTINCT DEPTNO, JOB
  FROM EMP
 ORDER BY DEPTNO;

	DEPTNO      JOB
	----------- ---------
	10          CLERK
	10          MANAGER
	10          PRESIDENT
	20          ANALYST
	20          CLERK
	20          MANAGER
	30          CLERK
	30          MANAGER
	30          SALESMAN

3. 列出下表
SELECT EMPNO 編號, ENAME 姓名, JOB 職務, SAL 薪資
  FROM EMP;

	編號          姓名         職務        薪資
	----------- ---------- --------- ---------------------------------------
	7369        SMITH      CLERK     800.00
	7499        ALLEN      SALESMAN  1600.00
	7521        WARD       SALESMAN  1250.00
	7566        JONES      MANAGER   2975.00
	7654        MARTIN     SALESMAN  1250.00
	7698        BLAKE      MANAGER   2850.00
	7782        CLARK      MANAGER   2450.00
	7788        SCOTT      ANALYST   3000.00
	7839        KING       PRESIDENT 5000.00
	7844        TURNER     SALESMAN  1500.00
	7876        ADAMS      CLERK     1100.00
	7900        JAMES      CLERK     950.00
	7902        FORD       ANALYST   3000.00
	7934        MILLER     CLERK     1300.00


4. 列出姓名中含有M與A字母的員工
SELECT *
  FROM EMP
 WHERE ENAME LIKE '%M%'
   AND ENAME LIKE '%A%'

	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7654        MARTIN     SALESMAN  7698        1981-09-28 00:00:00.000 1250.00                                 1400.00                                 30
	7876        ADAMS      CLERK     7788        1987-05-23 00:00:00.000 1100.00                                 NULL                                    20
	7900        JAMES      CLERK     7698        1981-12-03 00:00:00.000 950.00                                  NULL                                    30


5. 列出職務為SALESMAN且COMM=0的員工
SELECT *
  FROM EMP
 WHERE JOB = 'SALESMAN'
   AND COMM = 0;

	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7844        TURNER     SALESMAN  7698        1981-09-08 00:00:00.000 1500.00                                 0.00                                    30


6. 列出1981年到職的員工
DATEPART=取出年月日時..
SELECT *
  FROM EMP
 WHERE DATEPART(YEAR,HIREDATE) = 1981
 ORDER BY EMPNO;

	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7499        ALLEN      SALESMAN  7698        1981-02-20 00:00:00.000 1600.00                                 300.00                                  30
	7521        WARD       SALESMAN  7698        1981-02-22 00:00:00.000 1250.00                                 500.00                                  30
	7566        JONES      MANAGER   7839        1981-04-02 00:00:00.000 2975.00                                 NULL                                    20
	7654        MARTIN     SALESMAN  7698        1981-09-28 00:00:00.000 1250.00                                 1400.00                                 30
	7698        BLAKE      MANAGER   7839        1981-05-01 00:00:00.000 2850.00                                 NULL                                    30
	7782        CLARK      MANAGER   7839        1981-06-09 00:00:00.000 2450.00                                 NULL                                    10
	7839        KING       PRESIDENT NULL        1981-11-17 00:00:00.000 5000.00                                 NULL                                    10
	7844        TURNER     SALESMAN  7698        1981-09-08 00:00:00.000 1500.00                                 0.00                                    30
	7900        JAMES      CLERK     7698        1981-12-03 00:00:00.000 950.00                                  NULL                                    30
	7902        FORD       ANALYST   7566        1981-12-03 00:00:00.000 3000.00                                 NULL                                    20


7. 依各職務類別及薪資降冪排序
SELECT EMPNO, ENAME, JOB, SAL
  FROM EMP
 ORDER BY JOB, SAL DESC;
	EMPNO       ENAME      JOB       SAL
	----------- ---------- --------- -----------
	7788        SCOTT      ANALYST   3000.00
	7902        FORD       ANALYST   3000.00
	7934        MILLER     CLERK     1300.00
	7876        ADAMS      CLERK     1100.00
	7900        JAMES      CLERK     950.00
	7369        SMITH      CLERK     800.00
	7566        JONES      MANAGER   2975.00
	7698        BLAKE      MANAGER   2850.00
	7782        CLARK      MANAGER   2450.00
	7839        KING       PRESIDENT 5000.00
	7499        ALLEN      SALESMAN  1600.00
	7844        TURNER     SALESMAN  1500.00
	7521        WARD       SALESMAN  1250.00
	7654        MARTIN     SALESMAN  1250.00

8. 顯示員工的資訊並依下列職務的順序排列
   PRESIDENT
   MANAGER
   ANALYST
   CLERK
   SALESMAN

SELECT EMPNO, ENAME, JOB
  FROM EMP
 ORDER BY CASE JOB
            WHEN 'PRESIDENT' THEN 1
            WHEN 'MANAGER'   THEN 2
            WHEN 'ANALYST'   THEN 3
            WHEN 'CLERK'     THEN 4
            WHEN 'SALESMAN'  THEN 5
            ELSE 6
          END;

	EMPNO       ENAME      JOB
	----------- ---------- ---------
	7839        KING       PRESIDENT
	7698        BLAKE      MANAGER
	7782        CLARK      MANAGER
	7566        JONES      MANAGER
	7788        SCOTT      ANALYST
	7902        FORD       ANALYST
	7934        MILLER     CLERK
	7876        ADAMS      CLERK
	7900        JAMES      CLERK
	7369        SMITH      CLERK
	7499        ALLEN      SALESMAN
	7521        WARD       SALESMAN
	7654        MARTIN     SALESMAN
	7844        TURNER     SALESMAN


9. 依據職務計算出員工調整後的薪資
   SALESMAN 調5%
   MANAGER  調7%
   PRESIDENT調10%
   其餘的不變

SELECT EMPNO, ENAME, JOB, SAL, 
       CASE JOB
         WHEN 'SALESMAN'  THEN SAL*1.05
         WHEN 'MANAGER'   THEN SAL*1.07
         WHEN 'PRESIDENT' THEN SAL*1.1
         ELSE SAL
       END NEW_SAL
  FROM EMP
 ORDER BY JOB;
 
	EMPNO       ENAME      JOB       SAL                                     NEW_SAL
	----------- ---------- --------- --------------------------------------- ---------------------------------------
	7788        SCOTT      ANALYST   3000.00                                 3000.0000
	7902        FORD       ANALYST   3000.00                                 3000.0000
	7934        MILLER     CLERK     1300.00                                 1300.0000
	7876        ADAMS      CLERK     1100.00                                 1100.0000
	7900        JAMES      CLERK     950.00                                  950.0000
	7369        SMITH      CLERK     800.00                                  800.0000
	7566        JONES      MANAGER   2975.00                                 3183.2500
	7698        BLAKE      MANAGER   2850.00                                 3049.5000
	7782        CLARK      MANAGER   2450.00                                 2621.5000
	7839        KING       PRESIDENT 5000.00                                 5500.0000
	7844        TURNER     SALESMAN  1500.00                                 1575.0000
	7654        MARTIN     SALESMAN  1250.00                                 1312.5000
	7499        ALLEN      SALESMAN  1600.00                                 1680.0000
	7521        WARD       SALESMAN  1250.00                                 1312.5000
 

10 .列出部門=10,各員工的年資
DATEDIFF ( datepart , startdate , enddate )
DATEADD (datepart , number , date )

SELECT EMPNO, ENAME,  DATEDIFF ( YEAR , HIREDATE , GETDATE() )  年資
  FROM EMP
 WHERE DEPTNO = 10;
 
	EMPNO       ENAME      年資
	----------- ---------- -----------
	7782        CLARK      39
	7839        KING       39
	7934        MILLER     38

11.列出下面的結果
REPLICATE=複製
CAST(SAL AS INT)=拿掉小數點
CAST(CAST(SAL AS INT) AS NVARCHAR)=nvarchar [ ( n | max ) ]
SELECT EMPNO, ENAME, RIGHT(REPLICATE('*',10)+'$'+CAST(CAST(SAL AS INT) AS NVARCHAR),10), DEPTNO
  FROM EMP
 WHERE DEPTNO = 10;

	EMPNO       ENAME                 DEPTNO
	----------- ---------- ---------- -----------
	7782        CLARK      *****$2450 10
	7839        KING       *****$5000 10
	7934        MILLER     *****$1300 10

12.依到職日排列下面的結果
datename(weekday,hidedate)=轉為星期
--SET LANGUAGE 繁體中文
SET LANGUAGE us_english;
SELECT EMPNO, ENAME, HIREDATE, DATENAME(weekday, HIREDATE) DAYNAME
  FROM EMP
 ORDER BY CASE DATEPART(weekday, HIREDATE)
            WHEN 1 THEN 8
            ELSE DATEPART(weekday, HIREDATE)
          END;

	Changed language setting to us_english.
	EMPNO       ENAME      HIREDATE                DAYNAME
	----------- ---------- ----------------------- ------------------------------
	7654        MARTIN     1981-09-28 00:00:00.000 Monday
	7782        CLARK      1981-06-09 00:00:00.000 Tuesday
	7839        KING       1981-11-17 00:00:00.000 Tuesday
	7844        TURNER     1981-09-08 00:00:00.000 Tuesday
	7369        SMITH      1980-12-17 00:00:00.000 Wednesday
	7900        JAMES      1981-12-03 00:00:00.000 Thursday
	7902        FORD       1981-12-03 00:00:00.000 Thursday
	7566        JONES      1981-04-02 00:00:00.000 Thursday
	7499        ALLEN      1981-02-20 00:00:00.000 Friday
	7698        BLAKE      1981-05-01 00:00:00.000 Friday
	7876        ADAMS      1987-05-23 00:00:00.000 Saturday
	7934        MILLER     1982-01-23 00:00:00.000 Saturday
	7788        SCOTT      1987-04-19 00:00:00.000 Sunday
	7521        WARD       1981-02-22 00:00:00.000 Sunday

13.列出當月生日的員工

select *,datename(month,hiredate) birthday 
	from emp 
where datepart(month,hiredate) = datepart(month,getdate()) 
order by 1
老師:
SELECT *
  FROM EMP
 WHERE DATEPART(MONTH,HIREDATE) = DATEPART(MONTH,GETDATE())
 ORDER BY EMPNO;

14.計算各職務的人員人數及薪資總和
COUNT(*)=計算總筆數
SUM=總和
SELECT JOB, COUNT(*) CNT, SUM(SAL) COST
  FROM EMP
 GROUP BY JOB
 ORDER BY 1;
 
	JOB       CNT         COST
	--------- ----------- ---------------------------------------
	ANALYST   2           6000.00
	CLERK     4           4150.00
	MANAGER   3           8275.00
	PRESIDENT 1           5000.00
	SALESMAN  4           5600.00

15.計算各職務的平均薪資且>2000,依平均值排序列出
SELECT JOB, AVG(SAL)
  FROM EMP
 GROUP BY JOB
 HAVING AVG(SAL)>2000
 ORDER BY 2;

	JOB       
	--------- ---------------------------------------
	MANAGER   2758.333333
	ANALYST   3000.000000
	PRESIDENT 5000.000000


16.列出各年度僱用的人數,如下表
SELECT SUM(CASE WHEN DATEPART(YEAR,HIREDATE)=1980 THEN 1 ELSE 0 END) YY1980,
       SUM(CASE WHEN DATEPART(YEAR,HIREDATE)=1981 THEN 1 ELSE 0 END) YY1981,
       SUM(CASE WHEN DATEPART(YEAR,HIREDATE)=1982 THEN 1 ELSE 0 END) YY1982,
       SUM(CASE WHEN DATEPART(YEAR,HIREDATE)=1983 THEN 1 ELSE 0 END) YY1983
  FROM EMP;

	YY1980      YY1981      YY1982      YY1983
	----------- ----------- ----------- -----------
	1           10          1           0

17.列出下表
SELECT JOB,
       SUM(CASE WHEN DEPTNO=10 THEN 1 ELSE 0 END) DEPT10,
       SUM(CASE WHEN DEPTNO=20 THEN 1 ELSE 0 END) DEPT20,
       SUM(CASE WHEN DEPTNO=30 THEN 1 ELSE 0 END) DEPT30
  FROM EMP
 GROUP BY JOB;

	JOB       DEPT10      DEPT20      DEPT30
	--------- ----------- ----------- -----------
	ANALYST   0           2           0
	CLERK     1           2           1
	MANAGER   1           1           1
	PRESIDENT 1           0           0
	SALESMAN  0           0           4


18.統計各級數的員工人數, 並依級數排序	人數
	薪資    0 ~ 1200    A級
	薪資 1201 ~ 2000    B級
	薪資 2001 ~ 2800    C級
	薪資 2801 ~ 3500    D級
	其他                E級

SELECT A.TYPE, COUNT(*) CNT
  FROM (SELECT CASE
                 WHEN SAL BETWEEN 0    AND 1200 THEN 'A'
                 WHEN SAL BETWEEN 1201 AND 2000 THEN 'B'
                 WHEN SAL BETWEEN 2001 AND 2800 THEN 'C'
                 WHEN SAL BETWEEN 2801 AND 3500 THEN 'D'
                 ELSE 'E'
               END TYPE
          FROM EMP) A
GROUP BY A.TYPE;

	TYPE CNT
	---- -----------
	A    3
	B    5
	C    1
	D    4
	E    1

19.列出各MANAGER中配置的員工人數
SELECT MGR, COUNT(*) CNT
  FROM EMP
 GROUP BY MGR;

	MGR         CNT
	----------- -----------
	NULL        1
	7566        2
	7698        5
	7782        1
	7788        1
	7839        3
	7902        1

20.列出各部門的人數
SELECT DEPTNO, COUNT(*) CNT
  FROM EMP
 GROUP BY DEPTNO;

	DEPTNO      CNT
	----------- -----------
	10          3
	20          5
	30          6

21.列出所有客戶的總交易金額
SELECT A.CUSTOMER_ID, A.CUST_FIRST_NAME, A.CUST_LAST_NAME,
       SUM(B.ORDER_TOTAL) TOTAL
  FROM CUSTOMERS A LEFT JOIN ORDERS B
    ON (A.CUSTOMER_ID = B.CUSTOMER_ID)
 GROUP BY A.CUSTOMER_ID, A.CUST_FIRST_NAME, A.CUST_LAST_NAME
 ORDER BY A.CUSTOMER_ID, A.CUST_FIRST_NAME, A.CUST_LAST_NAME;

	CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       TOTAL
	----------- -------------------- -------------------- ---------------------------------------
	101         Constantin           Welles               190395.10
	102         Harrison             Pacino               69211.40
	103         Manisha              Taylor               20591.40
	104         Harrison             Sutherland           146605.50
	105         Matthias             MacGraw              61376.50
	106         Matthias             Hannah               36199.50
	107         Matthias             Cruise               155613.20
	108         Meenakshi            Mason                213399.70
	109         Christian            Cage                 265255.60
	110         Charlie              Sutherland           NULL
	111         Charlie              Pacino               NULL
	...


22.列出所有商品的銷售量
SELECT A.PRODUCT_ID, A.PRODUCT_NAME, SUM(B.QUANTITY) QTY
  FROM PRODUCT_INFORMATION A LEFT JOIN ORDER_ITEMS B
    ON (A.PRODUCT_ID = B.PRODUCT_ID)
 GROUP BY A.PRODUCT_ID, A.PRODUCT_NAME
 ORDER BY A.PRODUCT_ID, A.PRODUCT_NAME;

	...
	1778        C for SPNIX3.3 - 1 Seat                            NULL
	1779        C for SPNIX3.3 - Doc                               NULL
	1780        C for SPNIX3.3 - Sys                               NULL
	1781        CDW 20/48/E                                        9
	1782        Compact 400/DQ                                     4
	1787        CPU D300                                           5
	1788        CPU D600                                           NULL
	1791        Industrial 700/HD                                  3
	1792        Industrial 600/DQ                                  NULL
	1794        OSI 8-16/IL                                        NULL
	1797        Inkjet C/8/HQ                                      28
	...

23.列出所有員工的銷售業績
老師:只找出SA開頭JOB
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, SUM(B.ORDER_TOTAL) TOTAL
  FROM EMPLOYEES A LEFT JOIN ORDERS B
    ON (A.EMPLOYEE_ID = B.SALES_REP_ID)
 WHERE A.JOB_ID LIKE 'SA%'
 GROUP BY A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME
 ORDER BY A.EMPLOYEE_ID;
 

SELECT a.EMPLOYEE_ID,a.FIRST_NAME,a.LAST_NAME,SUM(b.ORDER_TOTAL)[TOTAL]
FROM EMPLOYEES a left join ORDERS b
ON(a.EMPLOYEE_ID = b.ORDER_TOTAL)
group by a.EMPLOYEE_ID,a.FIRST_NAME,a.LAST_NAME;

	EMPLOYEE_ID FIRST_NAME           LAST_NAME                 TOTAL
	----------- -------------------- ------------------------- ---------------------------------------
	145         John                 Russell                   NULL
	146         Karen                Partners                  NULL
	147         Alberto              Errazuriz                 NULL
	148         Gerald               Cambrault                 NULL
	149         Eleni                Zlotkey                   NULL
	150         Peter                Tucker                    NULL
	151         David                Bernstein                 NULL
	152         Peter                Hall                      NULL
	153         Christopher          Olsen                     114215.70
	154         Nanette              Cambrault                 171973.10
	155         Oliver               Tuvault                   134415.20
	156         Janette              King                      202617.60
	157         Patrick              Sully                     NULL


24.列出所有部門下的員工
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.EMPLOYEE_ID, B.FIRST_NAME, B.LAST_NAME
  FROM DEPARTMENTS A LEFT JOIN EMPLOYEES B
    ON (A.DEPARTMENT_ID = B.DEPARTMENT_ID)
 ORDER BY A.DEPARTMENT_ID;

	DEPARTMENT_ID DEPARTMENT_NAME                EMPLOYEE_ID FIRST_NAME           LAST_NAME
	------------- ------------------------------ ----------- -------------------- -------------------------
	10            Administration                 200         Jennifer             Whalen
	20            Marketing                      201         Michael              Hartstein
	20            Marketing                      202         Pat                  Fay
	30            Purchasing                     114         Den                  Raphaely
	30            Purchasing                     115         Alexander            Khoo
	30            Purchasing                     116         Shelli               Baida
	30            Purchasing                     117         Sigal                Tobias
	30            Purchasing                     118         Guy                  Himuro
	30            Purchasing                     301         NULL                 A
	40            Human Resources                203         Susan                Mavris
	50            Shipping                       180         Winston              Taylor
	50            Shipping                       181         Jean                 Fleaur
	50            Shipping                       182         Martha               Sullivan


25.列出薪資比員工編號7900高的員工
SELECT *
  FROM EMP
 WHERE SAL > (SELECT SAL 
                FROM EMP */-------------/////////////////////////////////////////////////////////////////////////
               WHERE EMPNO=7900)
 ORDER BY EMPNO;
 
	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7499        ALLEN      SALESMAN  7698        1981-02-20 00:00:00.000 1600.00                                 300.00                                  30
	7521        WARD       SALESMAN  7698        1981-02-22 00:00:00.000 1250.00                                 500.00                                  30
	7566        JONES      MANAGER   7839        1981-04-02 00:00:00.000 2975.00                                 NULL                                    20
	7654        MARTIN     SALESMAN  7698        1981-09-28 00:00:00.000 1250.00                                 1400.00                                 30
	7698        BLAKE      MANAGER   7839        1981-05-01 00:00:00.000 2850.00                                 NULL                                    30
	7782        CLARK      MANAGER   7839        1981-06-09 00:00:00.000 2450.00                                 NULL                                    10
	7788        SCOTT      ANALYST   7566        1987-04-19 00:00:00.000 3000.00                                 NULL                                    20
	7839        KING       PRESIDENT NULL        1981-11-17 00:00:00.000 5000.00                                 NULL                                    10
	7844        TURNER     SALESMAN  7698        1981-09-08 00:00:00.000 1500.00                                 0.00                                    30
	7876        ADAMS      CLERK     7788        1987-05-23 00:00:00.000 1100.00                                 NULL                                    20
	7902        FORD       ANALYST   7566        1981-12-03 00:00:00.000 3000.00                                 NULL                                    20
	7934        MILLER     CLERK     7782        1982-01-23 00:00:00.000 1300.00                                 NULL                                    10


26.列出各員工薪資佔該部門薪資成本的百分比
老師:
SELECT A.EMPNO, A.ENAME, A.DEPTNO, A.SAL, A.SAL*100/B.COST PCT
  FROM EMP A, (SELECT DEPTNO, SUM(SAL) COST
                 FROM EMP
                GROUP BY DEPTNO) B
 WHERE A.DEPTNO = B.DEPTNO
 ORDER BY A.DEPTNO;

--SELECT *,SAL*100/(SELECT SUM(SAL)FROM EMP WHERE DEPTNO=A.DEPTNO) PCT 
FROM EMP A
ORDER BY 8

	EMPNO       ENAME      DEPTNO      SAL                                     PCT
	----------- ---------- ----------- --------------------------------------- ---------------------------------------
	7782        CLARK      10          2450.00                                 28.000000000000000000000000000
	7839        KING       10          5000.00                                 57.142857142857142857142857142
	7934        MILLER     10          1300.00                                 14.857142857142857142857142857
	7902        FORD       20          3000.00                                 27.586206896551724137931034482
	7876        ADAMS      20          1100.00                                 10.114942528735632183908045977
	7788        SCOTT      20          3000.00                                 27.586206896551724137931034482
	7369        SMITH      20          800.00                                  7.356321839080459770114942528
	7566        JONES      20          2975.00                                 27.356321839080459770114942528
	7654        MARTIN     30          1250.00                                 13.297872340425531914893617021
	7698        BLAKE      30          2850.00                                 30.319148936170212765957446808
	7499        ALLEN      30          1600.00                                 17.021276595744680851063829787
	7521        WARD       30          1250.00                                 13.297872340425531914893617021
	7844        TURNER     30          1500.00                                 15.957446808510638297872340425
	7900        JAMES      30          950.00                                  10.106382978723404255319148936


27.列出各部門薪資最高的員工
SELECT A.*
  FROM EMP A, (SELECT DEPTNO, MAX(SAL) MAXSAL
                 FROM EMP
                GROUP BY DEPTNO) B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.SAL = B.MAXSAL
 ORDER BY A.DEPTNO;

EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
7839        KING       PRESIDENT NULL        1981-11-17 00:00:00.000 5000.00                                 NULL                                    10
7902        FORD       ANALYST   7566        1981-12-03 00:00:00.000 3000.00                                 NULL                                    20
7788        SCOTT      ANALYST   7566        1987-04-19 00:00:00.000 3000.00                                 NULL                                    20
7698        BLAKE      MANAGER   7839        1981-05-01 00:00:00.000 2850.00                                 NULL                                    30


28.列出各部門薪資成本佔全公司薪資總成本的百分比
SELECT A.EMPNO, A.ENAME, A.SAL, A.SAL*100/B.TOTAL PCT
  FROM EMP A, (SELECT SUM(SAL) TOTAL FROM EMP) B
 ORDER BY A.EMPNO;

	EMPNO       ENAME      SAL                                     PCT
	----------- ---------- --------------------------------------- ---------------------------------------
	7369        SMITH      800.00                                  2.756244616709732988802756244
	7499        ALLEN      1600.00                                 5.512489233419465977605512489
	7521        WARD       1250.00                                 4.306632213608957795004306632
	7566        JONES      2975.00                                 10.249784668389319552110249784
	7654        MARTIN     1250.00                                 4.306632213608957795004306632
	7698        BLAKE      2850.00                                 9.819121447028423772609819121
	7782        CLARK      2450.00                                 8.440999138673557278208440999
	7788        SCOTT      3000.00                                 10.335917312661498708010335917
	7839        KING       5000.00                                 17.226528854435831180017226528
	7844        TURNER     1500.00                                 5.167958656330749354005167958
	7876        ADAMS      1100.00                                 3.789836347975882859603789836
	7900        JAMES      950.00                                  3.273040482342807924203273040
	7902        FORD       3000.00                                 10.335917312661498708010335917
	7934        MILLER     1300.00                                 4.478897502153316106804478897



29.列出與JAMES員工相同部門的所有員工
SELECT *
  FROM EMP
 WHERE DEPTNO = (SELECT DEPTNO
                   FROM EMP
                  WHERE ENAME = 'JAMES')
 ORDER BY EMPNO;
 
	EMPNO       ENAME      JOB       MGR         HIREDATE                SAL                                     COMM                                    DEPTNO
	----------- ---------- --------- ----------- ----------------------- --------------------------------------- --------------------------------------- -----------
	7499        ALLEN      SALESMAN  7698        1981-02-20 00:00:00.000 1600.00                                 300.00                                  30
	7521        WARD       SALESMAN  7698        1981-02-22 00:00:00.000 1250.00                                 500.00                                  30
	7654        MARTIN     SALESMAN  7698        1981-09-28 00:00:00.000 1250.00                                 1400.00                                 30
	7698        BLAKE      MANAGER   7839        1981-05-01 00:00:00.000 2850.00                                 NULL                                    30
	7844        TURNER     SALESMAN  7698        1981-09-08 00:00:00.000 1500.00                                 0.00                                    30
	7900        JAMES      CLERK     7698        1981-12-03 00:00:00.000 950.00                                  NULL                                    30


30.列出員工資訊與該部門薪資的成本
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO, B.COST
  FROM EMP A, (SELECT DEPTNO, SUM(SAL) COST
                 FROM EMP
                GROUP BY DEPTNO) B
 WHERE A.DEPTNO = B.DEPTNO
 ORDER BY A.DEPTNO;

	EMPNO       ENAME      SAL                                     DEPTNO      COST
	----------- ---------- --------------------------------------- ----------- ---------------------------------------
	7782        CLARK      2450.00                                 10          8750.00
	7839        KING       5000.00                                 10          8750.00
	7934        MILLER     1300.00                                 10          8750.00
	7902        FORD       3000.00                                 20          10875.00
	7876        ADAMS      1100.00                                 20          10875.00
	7788        SCOTT      3000.00                                 20          10875.00
	7369        SMITH      800.00                                  20          10875.00
	7566        JONES      2975.00                                 20          10875.00
	7654        MARTIN     1250.00                                 30          9400.00
	7698        BLAKE      2850.00                                 30          9400.00
	7499        ALLEN      1600.00                                 30          9400.00
	7521        WARD       1250.00                                 30          9400.00
	7844        TURNER     1500.00                                 30          9400.00
	7900        JAMES      950.00                                  30          9400.00

