# 瑪亞資訊面試SQL題目
Table：dept 部門主檔表格
| deptno 部門編號| dname 部門名稱| loc 區域|
|:-------|:-----------|:---------|
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
Table：emp 員工主檔表格
| empno 員工編號| ename 姓名| job 職位| mgr 上級| hiredate 到職日| sal 薪資| comm 佣金| deptno 部門|
|:------|:------|:---------|:-----|:-----------|:-----|:-----|:-------|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
職位：CLERK 秘書、SALESMAN 售貨員、MANAGER 經理、ANALYST 分析師、PRESIDENT 負責人
Table：SALGRADE 薪水等級表格
| grade 等級| losal 最低| hisal 最高|
|:------|:------|:------|
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
註1：所有員工＝所有僱員(包含經理,負責人)
註2：辦事員：不含負責人、經理
1. 列出薪資SAL低於1000的所有員工(包含經理,負責人)
```sql
SELECT * FROM emp
WHERE sal <1000;
```
2. 列出部門30中的所有雇員(包含經理,負責人)
```sql
SELECT * FROM emp
WHERE deptno=30;
```
3. 列出所有辦事員 (不含負責人PRESIDENT、經理MANAGER )
顯示欄位：姓名ENAME, 員工編號EMPNO, 部門名稱DNAME
```sql
SELECT ename 姓名, empno 員工編號, dept.dname 部門名稱 FROM emp
LEFT JOIN dept on emp.deptno=dept.deptno
WHERE job NOT IN ('PRESIDENT','MANAGER');
```
4. 找出佣金COMM高於薪資SAL的雇員
```sql
SELECT * FROM emp
WHERE comm > sal;
```
5. 找出佣金COMM高於(達到)薪資SAL的60%之雇員
```sql
SELECT * FROM emp
WHERE comm > 0.6*sal;
```
6. 找出部門10中所有經理MANAGER
```sql
SELECT * FROM emp
WHERE deptno=10 and job ='MANAGER';
```
```sql
SELECT * FROM emp
WHERE job <> 'MANAGER' and deptno=10;
```sql
7. 找出部門20中不是經理MANAGER 但是薪資大於2000的所有僱員資料
```sql
SELECT * FROM emp
WHERE deptno=20 and sal > 2000 and job NOT in('MANAGER');
```
8. 找出有收到佣金COMM的雇員及其職務JOB
顯示欄位：姓名ENAME, 職務JOB, 傭金COMM
```sql
SELECT ename 姓名,job 職務,comm 佣金 FROM emp
WHERE comm >0;
```
9. 找出不收取佣金COMM或者佣金小於300的僱員 (*)
```sql
SELECT ename 姓名,job 職務,comm 佣金 FROM emp
WHERE comm <300;
```
10. 找出到職日大於41年以上僱員
```sql
SELECT ename,hiredate,hiredate+interval 41 year old FROM emp WHERE (hiredate+interval 41 year) < now();
```
11. 找出早於1982年以前到職員工
```sql
SELECT ename,hiredate FROM emp WHERE hiredate < '1982-01-01';
```
12. 找出姓名ENAME為五個字的員工
```sql
SELECT ename,length(ename) FROM emp WHERE length(ename)=5;
```
13. 找出僱員姓名ENAME中不帶有"A"的員工
顯示欄位：員工編號EMPNO, 姓名不帶'A'
```sql
SELECT empno,ename FROM emp WHERE ename NOT like '%A%';
```
14.
(1)顯示所有僱員姓名的前三個字元
```sql
SELECT ename,LEFT(ename,3) FROM emp;
```
(2) 顯示所有僱員姓名的前三個字元，其餘字數使用’*’替代，以符合個資保護作法。
顯示欄位：姓名, 前3字元
```sql
SELECT ename,concat(LEFT(ename,3),repeat('*',length(ename)-3)) nname FROM emp;
```
15. 顯示"SALESMAN"銷售員的姓名，第一個字替換成"B"
顯示欄位：原姓名, 調整後姓名, 職務
```sql
SELECT ename 原姓名,insert(ename,1,1,'B') 調整後姓名,job 職務 FROM emp WHERE job='SALESMAN';
```
16. 顯示僱員的詳細資料，按照姓名排序 (由小而大)
```sql
SELECT * FROM emp
order by ename;
```
17. 顯示僱員姓名，按照到職日進行排序，將最老的僱員排列在最前面
顯示欄位：姓名, 到職日
```sql
SELECT ename,hiredate FROM emp order by hiredate desc;
```
18. 顯示所有僱員的姓名，職務，跟薪資，按照職務降序排列，職務相同時按薪資升序排列
顯示欄位：姓名, 職務, 薪資
```sql
SELECT ename,job,sal FROM emp order by job desc,sal;
```
19. 顯示所有僱員一個月以30天為情況下計算僱員日薪(小數1位)。
顯示欄位：姓名, 薪資, 日薪
```sql
SELECT ename,sal,round(sal/30,1) 日薪 FROM emp;
```
20. 找出不含傭金(不考量傭金欄位,不管是否有傭金)最高薪資員工名稱。
顯示欄位：姓名, SAL 薪資
```sql
SELECT ename,sal FROM emp order by sal desc limit 1;
```
21. 找出不限制年份，二月受聘的所有僱員
顯示欄位：姓名, 到職日
```sql
SELECT ename,hiredate,month(hiredate) 受聘月 FROM emp WHERE month(hiredate)=2;
```
22. 顯示所有僱員，從到職日至現在的天數
顯示欄位：姓名, 到職日, 到職天數
```sql
SELECT ename,hiredate,timestampdiff(day,hiredate,curdate())到職天數 FROM emp order by hiredate;
```
23. 查詢姓名中有C的僱員
顯示欄位：員工編號, 姓名有C僱員
```sql
SELECT empno,ename FROM emp
WHERE ename like '%C%';
```
24. 請列出最少有一個僱員的所有部門
顯示欄位：部門編號, 部門名稱, 人數
```sql
SELECT emp.deptno 部門編號,dept.dname 部門名稱,count(*) 人數 FROM emp LEFT JOIN dept on emp.deptno=dept.deptno group by dname;
```
25. 列出薪資比SMITH還多的所有僱員；按照部門編號排序 (由小而大)
顯示欄位：部門編號, 姓名 , 薪資
```sql
SELECT deptno,ename,sal FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename ='SMITH')
order by deptno;
```
26. 列出僱員的姓名與上級主管姓名
顯示欄位：姓名, 主管姓名
```sql
SELECT ename,mgr FROM emp;
```
27. 列出到職日早於上級主管的所有僱員
顯示欄位：僱員, 到職日, 主管, 到職日
```sql
SELECT a.empno,a.ename,a.hiredate,a.mgr,b.ename,b.hiredate
FROM emp a LEFT JOIN emp b on a.mgr=b.empno
WHERE a.hiredate < b.hiredate;
```
28. 列出所有職務為"CLERK"的姓名與其部門名稱
顯示欄位：姓名, 職務, 部門名稱
```sql
SELECT ename,job,dname FROM emp LEFT JOIN dept on emp.deptno=dept.deptno
WHERE job='CLERK';
```
29.
1\. 列出各種工作類別的最低薪資；顯示欄位：職務, 最低薪(按照職務排序 由小而大)
```sql
SELECT job,min(sal) FROM emp group by job order by job;
```
2\. 列出各種工作類別最低薪資的僱員；顯示欄位：職務, 僱員, 最低薪(排序同上)
```sql
```
30. 列出跟"SCOTT"員工做相同工作的所有僱員(不含"SCOTT")
顯示欄位：僱員, 職務
```sql
SELECT ename,job FROM emp WHERE ename <> 'SCOTT' and job =(SELECT job FROM emp WHERE ename='SCOTT');
```
31. 列出某些雇員的姓名和薪資，條件是他們的薪資等於部門30中任何一個僱員的薪資
顯示欄位：僱員, 薪資
```sql
SELECT ename,sal,deptno FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30);
```
32. 列出有雇員的部門之訊息與該部門中共有多少僱員數量
顯示欄位：部門編號, 部門名稱, 區域, 人數
```sql
SELECT emp.deptno 部門編號,dname 部門名稱,loc 區域, count(*) 人數 FROM emp LEFT JOIN dept on emp.deptno=dept.deptno group by emp.deptno;
```
33. 列出所有僱員的僱員名稱，部門名稱，跟薪資。
顯示欄位：僱員, 部門名稱, 薪資
```sql
SELECT ename 僱員,dname 部門名稱,sal 薪資 FROM emp LEFT JOIN dept on emp.deptno=dept.deptno;
```
34. 列出分配所有僱員數量的部門詳細訊息，即使該部門沒有僱員(沒有僱員者為0)。
顯示欄位：部門編號, 部門名稱, 區域, 人數
```sql
SELECT dept.deptno,dname,loc,count(emp.deptno) FROM dept LEFT JOIN emp on dept.deptno=emp.deptno group by emp.deptno order by dept.deptno;
```
35. 列出各種工作類別的最高薪資。(按照職務排序 由小而大)
(1)顯示欄位：職務, 最高薪
```sql
SELECT job,max(sal) FROM emp group by job order by job;
```
(2)顯示欄位：職務, 姓名, 最高薪
```sql
```
36. 列出各部門的經理最低薪資。
顯示欄位：經理, 職務, 最低薪
```sql
SELECT deptno,ename,job,sal,grade,losal FROM emp LEFT JOIN salgrade on sal between salgrade.losal and salgrade.hisal WHERE job = 'MANAGER';
```
37. 列出按照年薪降冪排序所有僱員的年薪
顯示欄位：僱員, 職務, 年薪
```sql
SELECT ename,job,sal FROM emp order by sal desc;
```
38. 列出薪資等級為4的所有員工
顯示欄位：僱員, 職務, 薪資, 等級
```sql
SELECT ename,job,sal,grade
FROM emp LEFT JOIN salgrade on sal between salgrade.losal and salgrade.hisal
WHERE grade = 4;
```
39. 列出含傭金+薪資等級為2的員工。
顯示欄位：僱員, 職務, [薪資+傭金], 等級
```sql
SELECT ename,job,sal,comm,sal+comm,grade
FROM emp LEFT JOIN salgrade on sal+comm between salgrade.losal and salgrade.hisal
WHERE grade = 2;
```
40. 列出每個部門薪資水平為第二的員工。
顯示欄位：部門編號, 僱員, 職務, 薪資
- 方法1
```sql
select DEPTNO 部門編號, Ename 僱員, JOB 職務, SAL 薪資
from emp E1
where (select Count(DISTINCT SAL) from emp E2 where E1.DEPTNO=DEPTNO and SAL>=E1.SAL) =2
order by 1;
```
- 方法2
```sql
select 部門編號, 僱員, 職務, 薪資, 部門排名
from (
select DEPTNO 部門編號, Ename 僱員, JOB 職務, SAL 薪資, DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO,SAL DESC) AS 部門排名
from emp
) sub
where 部門排名=2
order by 1;
```
- 方法3
```sql
select distinct 部門編號, Ename 僱員, job 職務, 薪資, 部門排名
from
emp E,
(
select DEPTNO 部門編號, SAL 薪資, DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO,SAL DESC) AS 部門排名
from emp
) sub
where E.Deptno=部門編號 and sal = 薪資 and 部門排名=1
order by 2;
```
- 方法4
```sql
SELECT DEPTNO 部門編號,ENAME 僱員,JOB 職務, max(SAL) 薪資
FROM emp
where (SAL not in (SELECT Max(SAL) FROM emp group by DEPTNO) )
group by DEPTNO
order by DEPTNO;
```
- 方法5
```sql
select deptno as '部門編號', ename as '僱員', job as '職務', Max(sal) as '薪資'
from emp inner join (select deptno,MAX(sal) as Max from emp group by deptno )sub using(deptno)
where sal != Max
group by deptno ;
```
Sign in with Wallet
Connect another wallet