員工表 emp
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) 員工號
ENAME VARCHAR2(10) Y 員工姓名
JOB VARCHAR2(9) Y 工作
MGR NUMBER(4) Y 上級編號
HIREDATE DATE Y 雇傭日期
SAL NUMBER(7,2) Y 薪金
COMM NUMBER(7,2) Y 傭金
DEPTNO NUMBER(2) Y 部門編號
提示：工資 ＝ 薪金 ＋ 傭金
部門表 dept
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2) 部門編號
DNAME VARCHAR2(14) Y 部門名稱
LOC VARCHAR2(13) Y 地點
select * from dept;
select * from emp;
1．列出至少有一個員工的所有部門。
select dname from dept where deptno in
(select deptno from emp)
2．列出薪金比“SMITH”多的所有員工。
select * from emp where sal>(select sal from emp where ename='SMITH')
3．列出所有員工的姓名及其直接上級的姓名。
select a.ename ,(select ename from emp b where b.empno= a.mgr )as boss
from emp a
4．列出受雇日期早於其直接上級的所有員工。
select ename from emp a where a.hiredate<
(select hiredate from emp b
where b.empno=a.mgr )
5．列出部門名稱和這些部門的員工信息，
同時列出那些沒有員工的部門
select a.dname,b.empno,b.ename,b.job,
b.mgr,b.hiredate,b.sal,b.deptno
from dept a left join emp b on a.deptno=b.deptno
6．列出所有“CLERK”（辦事員）的姓名及其部門名稱。
select a.ename ,b.dname from emp a
join dept b on a.deptno=b.deptno
where job='CLERK'
7．列出最低薪金大於1500的各種工作
select distinct job from emp
group by job having min(sal)>1500
8．列出在部門“SALES”（銷售部）工作的員工的姓名，
假定不知道銷售部的部門編號。
select ename from emp where deptno =
(select deptno from dept
where dname='SALES ')
9．列出薪金高於公司平均薪金的所有員工。
select * from emp where sal >(select avg(sal) from emp)
10．列出與“SCOTT”從事相同工作的所有員工。
select * from emp where job=
(select job from emp where ename='SCOTT')
11．列出薪金等於部門30中員工的薪金的所有員工的姓名
和薪金。
select ename,sal from emp where sal in
( select sal from emp where deptno=30)
and deptno not in 30
12．列出薪金高於在部門30工作的所有員工的薪金的
員工姓名
和薪金。
select ename,sal from emp where sal >
( select max(sal) from emp where deptno=30)
and deptno not in 30
select ename,sal from emp where sal>
(select max(sal) from emp where deptno=30);
13．列出在每個部門工作的員工數量、平均工資
select deptno , count(*) ,avg(sal)
from emp group by deptno
select (select b.dname from dept b where b.deptno=a.deptno) as dname ,
count(deptno) as deptcount,avg(sal) from emp a group by deptno
14．列出所有員工的姓名、部門名稱和工資。
select ename,dname,sal from emp a
left join dept b on a.deptno=b.deptno
15．列出所有部門的詳細信息和部門人數。
select a.deptno,a.dname,a.loc,
(select count(deptno) from emp b
where a.deptno=b.deptno group by deptno )
as deptcount from dept a
16．列出各種工作的最低工資。
select job,min(sal) from emp group by job
17．列出各個部門的MANAGER（經理）的最低薪金。
select deptno,min(sal) from emp where
job='MANAGER' group by deptno
18．列出所有員工的年工資,按年薪從低到高排序。
select ename,(sal+nvl(comm,0))*12 as salpersal
from emp order by salpersal asc
-- nul 函數 格式為：NVL( string1, replace_with) 功能：如果string1為NULL，則NVL函數返回replace_with的值，否則返回string1的值。
查詢第二講
1. 找出EMP表中的姓名（ENAME）第三個字母是A 的員工姓名。
SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';
sub
2. 找出EMP表員工名字中含有A 和N的員工姓名。
SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';
SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%N%';
3. 找出所有有傭金的員工，列出姓名、工資、傭金，
顯示結果按工資從小到大，
傭金從大到小。
select * from emp where comm is null
SELECT ENAME,SAL + COMM AS WAGE,COMM FROM SCOTT.EMP
ORDER BY WAGE,COMM DESC;
4. 列出部門編號為20的所有職位。
SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20;
5. 列出不屬於SALES 的部門。
SELECT DISTINCT * FROM DEPT WHERE DNAME <> 'SALES';
SELECT DISTINCT * FROM DEPT WHERE DNAME not in
( 'SALES');
6. 顯示工資不在1000 到1500 之間的員工信息
：名字、工資，按工資從大到小排序。
SELECT ENAME,(SAL + COMM) AS WAGE FROM SCOTT.EMP
WHERE SAL + COMM NOT BETWEEN 1000 AND 1500
ORDER BY WAGE DESC;
SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP
WHERE WAGE < 1000 OR WAGE > 1500
ORDER BY WAGE DESC;
7. 顯示職位為MANAGER 和SALESMAN，
年薪在15000 和20000 之間的員工的信息：
名字、職位、年薪。
SELECT ENAME 姓名,JOB 職位,(SAL + COMM) * 12 AS 年薪
FROM EMP
WHERE (SAL + COMM) * 12
BETWEEN 15000 AND 20000
AND JOB IN('MANAGER','SALESMAN');
8. 說明以下兩條SQL語句的輸出結果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
--說明：IS NULL是判斷某個字段是否為空，為空並不等價於為空字符串或為數字0；
--而 =NULL 是判斷某個值是否等於 NULL，NULL = NULL和NULL <> NULL都為 FALSE。
9. 讓SELECT 語句的輸出結果為
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出當前用戶有多少張數據表，結果集中存在多少條記錄。
SELECT 'SELECT * FROM '||TABLE_NAME||';'
FROM USER_TABLES;
10. 判斷SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'
是否抱錯，為什麼？
SELECT ENAME,SAL FROM EMP WHERE SAL > '1500';
SELECT ENAME,SAL FROM EMP WHERE SAL > 1500;
--不會抱錯，這兒存在隱式數據類型的。
查詢第三講
1. 讓SELECT LTRIM(TO_CHAR(sal,'L99,999.99'))
FROM emp WHERE ROWNUM < 5
輸出結果的貨幣單位是￥和$。
LTRIM和RTRIM
--LTRIM 刪除左邊出現的字符串
---RTRIM 刪除右邊出現的字符串
SELECT TO_CHAR(sal,'L99,999.99') FROM emp WHERE ROWNUM < 5;
SELECT TO_CHAR(sal,'$99,999.99') FROM emp WHERE ROWNUM < 5;
/*--說明：對於'$99,999.99'格式符：
L：表示強制顯示當地貨幣符號
$: 表示顯示美元符號
9: 表示一個數字
0: 表示強制0顯示
.: 表示一個小數點
,: 表示一個千位分隔符
--------------*/
2. 列出前五位每個員工的名字，工資、漲薪後的的工資
（漲幅為8%），
以“元”為單位進行四捨五入。
SELECT ename,sal,ROUND(sal * 1.08,2) FROM emp
WHERE ROWNUM <=5;
3. 找出誰是最高領導，將名字按大寫形式顯示。
SELECT UPPER(ename) AS NAME FROM emp
WHERE mgr is null;
4. 找出SMITH 的直接領導名字。
SELECT ename AS NAME FROM emp where empno in
(select mgr from emp where ename ='SMITH')
6. 哪些員工的工資高於他直接上司的工資，
列出員工的名字和工資，上司的名字和工資。
select a.ename,a.sal, b.ename,b.sal
from emp a,emp b where a.mgr=b.empno
and a.sal>b.sal
--SELECT E.ENAME,E.SAL,M.ENAME,M.SAL FROM EMP E,EMP M
--WHERE E.EMPNO = M.EMPNO AND E.SAL > M.SAL;
--SELECT E.ENAME,E.SAL FROM EMP E WHERE E.SAL >
--(SELECT M.SAL FROM EMP M WHERE E.EMPNO = M.EMPNO);
7. 哪些員工和SMITH同部門。
SELECT ENAME FROM EMP WHERE DEPTNO IN
(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH')
8. 哪些員工跟SMITH做一樣職位。
SELECT ENAME FROM EMP WHERE JOB=
(SELECT JOB FROM EMP WHERE ENAME='SMITH')
9. 哪些員工跟SMITH不在同一個部門。
SELECT ENAME FROM EMP WHERE DEPTNO NOT IN
(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH')
10. 哪些員工跟SMITH做不一樣的職位。
SELECT ENAME FROM EMP WHERE JOB !=
(SELECT JOB FROM EMP WHERE ENAME='SMITH')
11. 顯示有提成的員工的信息：
名字、提成、所在部門名稱、所在地區的名稱。
SELECT E.ENAME, E.COMM,D.DNAME,D.LOC FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO AND
(E.COMM IS NOT NULL AND E.COMM >0) ;
12. 顯示 RESEARCH部門有哪些職位。
SELECT DISTINCT E.JOB FROM EMP E,DEPT D WHERE
D.DEPTNO = E.DEPTNO AND D.DNAME = ' RESEARCH';
13. 整個公司中，最高工資和最低工資相差多少。
SELECT MAX(SAL) - MIN(SAL) FROM EMP
14. 提成大於0 的人數。
SELECT COUNT(*) AS 提成大於0的人數
FROM emp WHERE comm > 0;
15. 顯示整個公司的最高工資、
最低工資、工資總和、
平均工資保留到整數位。
SELECT MAX(sal) AS 最高工資, MIN(sal) AS 最低工資,
SUM(sal) AS 工資總和,
ROUND(AVG(sal)) AS 平均工資 FROM emp
16. 整個公司有多少個領導。
SELECT COUNT(DISTINCT(mgr))
FROM emp WHERE mgr IS NOT NULL
17. 列出在同一部門入職日期晚但工資高於其他同事的員工：
名字、工資、入職日期。
SELECT DISTINCT E1.ename AS 姓名, E1.sal AS 工資,
E1.hiredate AS 入職日期
FROM emp E1,emp E2 WHERE
e1.empno = E2.empno AND E1.hiredate > E2.hiredate
AND E1.sal > E2.sal ORDER BY 工資 DESC;
查詢 第四講
1. 各個部門平均、最大、最小工資、人數，
按照部門號升序排列。
SELECT deptno AS 部門號,AVG(sal) AS 平均工資 ,
MAX(sal) AS 最高工資,MIN(sal)
AS 最低工資 ,COUNT(*) AS 人數
FROM emp GROUP BY deptno
ORDER BY deptno ASC;
2. 各個部門中工資大於5000的員工人數。
SELECT deptno,COUNT(*) FROM emp WHERE
sal > 5000 GROUP BY deptno;
3. 各個部門平均工資和人數，按照部門名字升序排列。
SELECT DNAME,AVG(SAL),COUNT(*) FROM
(SELECT
(SELECT DEPT.DNAME FROM DEPT WHERE
DEPT.DEPTNO = EMP.DEPTNO)
DNAME,EMP.SAL FROM EMP)
GROUP BY DNAME ORDER BY DNAME;
select dname,sala,num from
(select deptno ,avg(sal) as sala,count(*) as num from emp
group by deptno
order by deptno
)a
join dept on dept.deptno= a.deptno
order by dname
4. 列出每個部門中有同樣工資的員工的統計信息，
列出他們的部門號，工資，人數。
SELECT EMP1.DEPTNO,EMP1.SAL,COUNT(*)
FROM EMP EMP1,EMP EMP2
WHERE EMP1.DEPTNO = EMP2.DEPTNO
AND EMP1.SAL = EMP2.SAL
AND EMP1.EMPNO <> EMP2.EMPNO
GROUP BY EMP1.DEPTNO,EMP1.SAL;
5. 列出同部門中工資高於1000 的員工數量超過2 人的部門，
顯示部門名字、地區名稱。
SELECT
D.DNAME,D.LOC,COUNT(*)
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO AND
E.SAL > 1000
GROUP BY D.DNAME,D.LOC
HAVING COUNT(*) > 2;
6. 哪些員工的工資，高於整個公司的平均工資，
列出員工的名字和工資（降序）。
SELECT ENAME,SAL
FROM EMP
WHERE SAL> (
SELECT AVG(SAL)
FROM EMP
)
ORDER BY SAL DESC;
7. 哪些員工的工資，介於10號 和30號部門平均工資之間。
SELECT ENAME,SAL
FROM EMP
WHERE SAL
BETWEEN
(SELECT AVG(SAL) FROM EMP
WHERE DEPTNO = 10)
AND (SELECT AVG(SAL) FROM EMP
WHERE DEPTNO = 80);
8. 所在部門平均工資高於5000 的員工名字。
SELECT ENAME,SAL
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > 5000);
9. 列出各個部門中工資最高的員工的信息：
名字、部門號、工資。
SELECT ENAME
,SAL ,DEPTNO
FROM EMP
WHERE (DEPTNO,SAL ) IN
(SELECT DEPTNO,MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
10. 最高的部門平均工資是多少。
SELECT MAX(AVGSALARY)
FROM(SELECT DEPTNO,AVG(SAL) AVGSALARY
FROM EMP
GROUP BY DEPTNO);
查詢 第五講
1. 哪些部門的人數比20 號部門的人數多。
SELECT DEPTNO,COUNT(*) FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) >
(SELECT COUNT(*) FROM EMP
WHERE DEPTNO = 20
);
2. SMITH的領導是誰（非關聯子查詢）。
SELECT ENAME
FROM EMP
WHERE EMPNO in
(SELECT MGR FROM EMP
WHERE ENAME='SMITH'
);
3.FORD 領導誰（非關聯子查詢）。
SELECT ENAME
FROM EMP
WHERE MGR IN
(SELECT EMPNO FROM EMP
WHERE ENAME='FORD'
);
5. FORD 領導誰（關聯子查詢）。
SELECT ENAME
FROM EMP E1
WHERE EXISTS (
SELECT 1 FROM EMP E2
WHERE ENAME='FORD'
AND E2.EMPNO = E1.MGR);
4. SMITH 的領導是誰（關聯子查詢）。
SELECT ENAME
FROM EMP E1
WHERE EXISTS (
SELECT 1 FROM EMP E2
WHERE ENAME='SMITH'
AND E2.MGR = E1.EMPNO);
5. FORD 領導誰（關聯子查詢）。
SELECT ENAME
FROM EMP E1
WHERE EXISTS (
SELECT 1 FROM EMP E2
WHERE ENAME='FORD'
AND E2.EMPNO = E1.MGR);
6. 列出在同一部門共事，入職日期晚但工資高於其他同事的員工：
名字、工資、入職日期
（關聯子查詢）。
SELECT ename 姓名,
SAL AS 工資,HIREDATE AS 入職日期
FROM EMP E1
WHERE EXISTS (
SELECT 1 FROM EMP E2
WHERE E2.DEPTNO = E1.DEPTNO
AND E1.HIREDATE > E2.HIREDATE
AND E1.SAL > E2.SAL
);
7. 哪些員工跟SMITH不在同一個部門（非關聯子查詢）。
SELECT ENAME
FROM EMP a
WHERE
not EXISTS
(SELECT 1 FROM EMP b
WHERE b.ENAME='SMITH'
and a.deptno=b.deptno
);
SELECT ENAME
FROM EMP E1
WHERE EXISTS (
SELECT 1 FROM EMP E2
WHERE ENAME='FORD'
AND E2.EMPNO = E1.MGR);
8. 哪些員工跟SMITH不在同一個部門（關聯子查詢）。
SELECT ENAME
FROM EMP E1
WHERE NOT EXISTS (
SELECT 1 FROM EMP E2
WHERE E1.DEPTNO = E2.DEPTNO
AND E2.ENAME='SMITH'
);
9. RESEARCH部門有哪些職位（非關聯子查詢）。
SELECT DISTINCT JOB FROM EMP
WHERE DEPTNO = (
SELECT DEPTNO FROM DEPT
WHERE DNAME = ' RESEARCH');
10. RESEARCH部門有哪些職位（關聯子查詢）。
SELECT DISTINCT JOB FROM EMP
WHERE EXISTS(
SELECT 1 FROM DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND DEPT.DNAME = ' RESEARCH');
