收藏
1 找出傭金高于薪金60%的雇員。
SELECT * FROM emp WHERE comm>sal*0.6;
2 找出部門(mén)10中所有經(jīng)理和部門(mén)20中所有辦事員的詳細資料。
SELECT * FROM emp
WHERE deptno=10 AND JOB='MANAGER' OR deptno=20 AND job='CLERK';
3 找出部門(mén)10中所有經(jīng)理，部門(mén)20中所有辦事員以及既不是經(jīng)理又不是辦事員但其薪金大于或等2000的所有雇員的詳細資料。
SELECT * FROM emp
WHERE deptno=10 AND JOB='MANAGER' OR deptno=20 AND job='CLERK' OR JOB NOT IN('MANAGER','CLERK') AND SAL>=2000;
SELECT * FROM emp
WHERE deptno=10 AND JOB='MANAGER' OR deptno=20 AND job='CLERK' OR (JOB<>'MANAGER' AND JOB<>'MANAGER' AND SAL>=2000);
4 找出收取傭金的雇員的不同工作。
SELECT DISTINCT JOB FROM EMP WHERE COMM IS NOT NULL;
5 找出不收取傭金或收取的傭金低于300的雇員。
SELECT * FROM EMP WHERE COMM IS NULL OR COMM<300;
6 找出各月最后一天受雇的所有雇員。
SELECT * FROM EMP WHERE HIREDATE=LAST_DAY(HIREDATE);
--找出各月最后受雇的所有雇員
SELECT * FROM emp WHERE hiredate IN
(SELECT maxh FROM
(SELECT MAX(HIREDATE) AS maxh,EXTRACT(MONTH FROM hiredate)
FROM EMP
GROUP BY EXTRACT(MONTH FROM hiredate)));
7 找出晚于26年之前受雇的雇員。
SELECT * FROM emp WHERE months_between(SYSDATE,hiredate)<=26*12;
8 顯示只有首字母大寫(xiě)的的所有雇員的姓名。
SELECT * FROM emp WHERE ename=initcap(ename);
9 顯示正好為5個(gè)字符的雇員的姓名。
SELECT * FROM emp WHERE length(ename)=5;
10顯示不帶有“R”的雇員姓名。
SELECT * FROM emp WHERE instr(ename,'R')=0;
SELECT * FROM emp WHERE ename NOT LIKE '%R%';
11顯示所有雇員的姓名的前三個(gè)字符。
SELECT substr(ename,1,3) AS en3 FROM emp;
12顯所有雇員的姓名，用a替換所有“A”。
SELECT REPLACE(ename,'A','a') FROM emp;
13顯示所有雇員的姓名以及滿(mǎn)10年服務(wù)年限的日期。
SELECT ENAME,HIREDATE,ADD_MONTHS(HIREDATE,120) AS H_10Y FROM EMP;
14顯示雇員的詳細資料，按姓名排序。
SELECT * FROM EMP ORDER BY ENAME;
15顯示雇員姓名，根據其服務(wù)年限，將最老的雇員排在最前面。
SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE;
16顯示所有雇員的姓名、工作和薪金，按工作內的工作的降序順序順序排序，而工作按薪金排序。
SELECT ENAME,JOB,SAL
FROM EMP
ORDER BY JOB DESC,SAL;
17顯示所有雇員的姓名和加入公司的年份和月份，按雇員受雇日所在月排序，并將最早年份的項目排在最前面。
SELECT ENAME,TO_CHAR(HIREDATE,'YYYY-MM') AS Y_M
FROM EMP
ORDER BY TO_CHAR(HIREDATE,'MM'),TO_CHAR(HIREDATE,'YYYY');
18顯示在一個(gè)月為30天的情況下所有雇員的日薪金，取整。
SELECT ROUND(SAL/30) AS SAL FROM EMP;
19找出在（任何年份的）2月受聘的所有雇員。
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'MM')='02';
20對于每個(gè)雇員，顯示其加入公司的天數。
SELECT CEIL(SYSDATE-HIREDATE) AS DY FROM EMP;
21顯示姓名字段的任何位置，包含“A”的所有雇員的姓名。
SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%';
SELECT ENAME FROM EMP WHERE INSTR(ENAME,'A')>0;
22以年、月和日顯示所有雇員的服務(wù)年限。
SELECT HIREDATE,
FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) AS Y,
MOD(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)),12) AS M,
MOD(FLOOR(SYSDATE-HIREDATE),30) AS D
FROM EMP;
23列出至少有一個(gè)雇員的所有部門(mén)。
SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP);
24列出薪金比“SMITH”多的所有雇員。
SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH');
25列出所有雇員的姓名及其上級的姓名。
SELECT ygb.ename AS empnm,sjb.ename AS mgrnm
FROM emp ygb,emp sjb
WHERE ygb.mgr=sjb.empno;
26列出入職日期早于其直接上級的所有雇員。
SELECT * FROM emp ygb WHERE hiredate<(SELECT hiredate FROM emp sjb WHERE sjb.empno=ygb.mgr);
27列出部門(mén)和這些部門(mén)的雇員，同時(shí)列出那些沒(méi)有雇員的部門(mén)。
/*
SELECT * FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp)
UNION
SELECT * FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)
*/
SELECT dname,ename
FROM dept a LEFT JOIN emp b
ON a.deptno=b.deptno;
28列出所有“CLERK”（辦事員）的姓名及其部門(mén)名稱(chēng)。
SELECT ename,dname
FROM dept a,emp b
WHERE a.deptno=b.deptno AND job='CLERK';
29列出各種類(lèi)型的最低薪金，并使最低薪金大于1500。
SELECT job,MIN(sal)
FROM emp
GROUP BY job
HAVING MIN(sal)>1500;
30列出從事“SALES”（銷(xiāo)售）工作的雇員的姓名，假定不知道銷(xiāo)售部的部門(mén)編號。
SELECT ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');
31列出薪金高于公司平均水平的所有雇員。
SELECT * FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP);
32列出與“SCOTT”從事相同工作的所有雇員。
SELECT * FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT') AND ENAME<>'SCOTT';
33列出薪金等于在部門(mén)30工作的所有雇員的姓名和薪金。
SELECT * FROM EMP WHERE SAL IN(SELECT SAL FROM EMP WHERE DEPTNO=30);
34列出薪金高于在部門(mén)30工作的所有雇員的姓名和薪金。
SELECT * FROM EMP WHERE SAL >ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);
35列出在每個(gè)部門(mén)工作的雇員的經(jīng)理以及其他信息。
SELECT A.*,B.*
FROM DEPT A,EMP B
WHERE A.DEPTNO=B.DEPTNO AND JOB='MANAGER';
/*
SELECT A.*,B.*
FROM (SELECT * FROM EMP WHERE JOB<>'MANAGER') A,(SELECT * FROM EMP WHERE JOB='MANAGER') B
WHERE A.DEPTNO=B.DEPTNO AND B.JOB='MANAGER'
ORDER BY A.DEPTNO;
*/
36列出所有雇員的雇員名稱(chēng)、部門(mén)名稱(chēng)和薪金。
SELECT ENAME,DNAME,SAL
FROM DEPT A,EMP B
WHERE A.DEPTNO=B.DEPTNO;
37列出從事同一種工作但屬于不同部門(mén)的雇員的不同組合。
SELECT * FROM emp ORDER BY job,deptno;
38列出分配有雇員數量的所有部門(mén)的詳細信息，即使是分配有0個(gè)雇員。
SELECT * FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp)
UNION
SELECT * FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)
39列出各種類(lèi)型工作的最低工資。
SELECT job,MIN(sal)
FROM emp
GROUP BY job;
40列出各個(gè)部門(mén)的MANAGER（經(jīng)理）的最低薪金。
SELECT MIN(sal) FROM emp WHERE job='MANAGER';
41列出按年薪排序的所有雇員的年薪。
SELECT sal*12 AS y_sal FROM emp ORDER BY sal*12;
42列出薪金水平處于第四位的雇員。
SELECT * FROM
(SELECT a.*,row_number() over(ORDER BY sal DESC) AS rn FROM emp a)
WHERE rn=4;
43查找EMP表中前5條記錄
SELECT * FROM emp WHERE ROWNUM<=5;
44查找EMP表中10條以后的記錄
SELECT * FROM (SELECT a.*,ROWNUM AS rn FROM emp a) WHERE rn>10;
45查找EMP表中薪水第5高的員工
SELECT * FROM
(SELECT a.*,row_number() over(ORDER BY sal DESC) AS rn FROM emp a)
WHERE rn=5;
46查找EMP表部門(mén)30中薪水第3的員工
SELECT * FROM
(SELECT a.*,row_number() over(PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp a)
WHERE rn=5 AND deptno=30;
47查找EMP表中每部門(mén)薪水第3的員工
SELECT * FROM
(SELECT a.*,row_number() over(PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp a)
WHERE rn=3;
48統計各部門(mén)的薪水總和.
SELECT deptno,SUM(sal) AS sumsal
FROM emp
GROUP BY deptno;
--每個(gè)部門(mén)員工和經(jīng)理的詳細信息:編號,姓名,薪水,入職日期,部門(mén)編號
SELECT a.empno AS eno,a.ename AS enm,a.job AS ejob,a.sal AS esal,a.hiredate AS edate,a.deptno AS edept,
b.empno AS mno,b.ename AS mnm,b.job AS mjob,b.sal AS msal,b.hiredate AS mdate,b.deptno AS mdept
FROM (SELECT * FROM emp WHERE job<>'MANAGER' AND job<>'PRESIDENT') a,(SELECT * FROM emp WHERE job='MANAGER') b
WHERE a.deptno=b.deptno;
