# 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
Sign in with Wallet
Connect another wallet