oracle 一些经典sql（转自网络整理）

第一篇
--------1．列出至少有一个员工的所有部门。--------- 
SQL> select dname from dept where deptno in(select deptno from emp);
SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1); 
--------2．列出薪金比“SMITH”多的所有员工。---------- 
SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH'); 
--------3．列出所有员工的姓名及其直接上级的姓名。---------- 
SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;  
--------4．列出受雇日期早于其直接上级的所有员工。---------- 
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);  
--------5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门---------- 
SQL> 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”（办事员）的姓名及其部门名称。---------- 
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK'; 
--------7．列出最低薪金大于1500的各种工作。---------- 
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;  
--------8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。---------- 
SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');  
--------9．列出薪金高于公司平均薪金的所有员工。---------- 
SQL> select ename from emp where sal>(select avg(sal) from emp);  
--------10．列出与“SCOTT”从事相同工作的所有员工。-------- 
SQL> select ename from emp where job=(select job from emp where ename='SCOTT'); 

--------11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。--------- 
SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal 
     from emp b where b.deptno=30) and a.deptno<>30;  
--------12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。--------- 
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);  

--------13．列出在每个部门工作的员工数量、平均工资和平均服务期限。--------- 
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal 
    from emp a group by deptno;    
--------14．列出所有员工的姓名、部门名称和工资。--------- 
SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a;
--------15．列出所有部门的详细信息和部门人数。--------- 
SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;  
  --------16．列出各种工作的最低工资。--------- 
SQL> select job,avg(sal) from emp group by job; 
--------17．列出各个部门的MANAGER（经理）的最低薪金。-------- 
SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;
--------18．列出所有员工的年工资,按年薪从低到高排序。--------- 
SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal; 

第二篇
--------1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。--------- 
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%'; 
-------2. 找出EMP表员工名字中含有A 和N的员工姓名。---------- 
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';
--------3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。----------
SQL> SELECT ENAME,SAL + COMM AS WAGE,COMM 
    FROM SCOTT.EMP 
    ORDER BY WAGE,COMM DESC; 
-------4. 列出部门编号为20的所有职位。---------- 
SQL> SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20; 
-------5. 列出不属于SALES 的部门。---------- 
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME <> 'SALES'; 
或者
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME != 'SALES'; 
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT IN('SALES'); 
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT LIKE 'SALES';
---6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。--------- 
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP 
    WHERE SAL + COMM NOT BETWEEN 1000 AND 1500 
    ORDER BY WAGE DESC; 
--或者 
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP 
  2  WHERE SAL + COMM < 1000 OR SAL + COMM > 1500 
  3  ORDER BY WAGE DESC; 
 
  /*----- 7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。----------*/ 
SQL> SELECT ENAME 姓名,JOB 职位,(SAL + COMM) * 12 AS 年薪 
  2  FROM SCOTT.EMP 
  3  WHERE (SAL + COMM) * 12 BETWEEN 15000 AND 20000 
  4  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。

第三篇

/*--------1、改变NLS_LANG 的值，让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。---------*/ 
-----在没有设置NLS_LANG的情况下： 

SQL> SELECT TO_CHAR(SALARY,'L99,999.99') 
     FROM HR.EMPLOYEES 
     WHERE ROWNUM < 5; 
  
   /*--------2、列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。---------*/ 

SQL> SELECT FIRST_NAME,SALARY,ROUND(SALARY * 1.08) FROM HR.EMPLOYEES 
     WHERE ROWNUM <=5; 
    
/*--------3、找出谁是最高领导，将名字按大写形式显示。---------*/ 
SQL> SELECT UPPER(FIRST_NAME || ' ' || LAST_NAME) AS NAME
     FROM HR.EMPLOYEES 
     WHERE MANAGER_ID IS NULL; 
/*--------4、找出David 的直接领导的名字。---------*/ 
SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME
     FROM HR.EMPLOYEES 
     WHERE EMPLOYEE_ID IN( 
     SELECT MANAGER_ID FROM HR.EMPLOYEES 
     WHERE FIRST_NAME = 'David' AND LAST_NAME = 'Austin');       
/*--------5、First_Name 为Alexander，LAST_NAME为Hunold领导谁。（谁向David 报告）。---------*/ 
SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME
     FROM HR.EMPLOYEES 
     WHERE MANAGER_ID IN( 
     SELECT EMPLOYEE_ID FROM HR.EMPLOYEES 
     WHERE FIRST_NAME = 'Alexander' AND LAST_NAME = 'Hunold'); 
    
/*--------6、哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。---------*/ 

SQL> SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY 
    FROM EMPLOYEES E,EMPLOYEES M 
    WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY > M.SALARY; 

--要是只列出员工的名字与工资的话，还可以这样： 

SQL> SELECT E.FIRST_NAME,E.SALARY 
   FROM EMPLOYEES E WHERE E.SALARY > 
  (SELECT M.SALARY FROM EMPLOYEES M  
   WHERE E.MANAGER_ID = M.EMPLOYEE_ID);
  /*--------7、哪些员工和Chen(LAST_NAME)同部门。---------*/ 

SQL> SELECT FIRST_NAME FROM EMPLOYEES 
  WHERE DEPARTMENT_ID IN
  (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Chen') 
  AND LAST_NAME <> 'Chen'; 
  --或者-- 

SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2 
    WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID 
    AND E2.LAST_NAME = 'Chen' AND E1.LAST_NAME <> 'Chen'; 
  /*--------8、哪些员工跟De Haan(LAST_NAME)做一样职位。---------*/ 
SQL> SELECT FIRST_NAME FROM EMPLOYEES 
    WHERE JOB_ID IN
    (SELECT JOB_ID FROM EMPLOYEES 
    WHERE LAST_NAME = 'De Haan') 
    AND LAST_NAME <> 'De Haan'; 
   --或者-- 

SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2 
    WHERE E1.JOB_ID = E2.JOB_ID  
    AND E2.LAST_NAME = 'De Haan' AND E1.LAST_NAME <> 'De Haan'; 
  /*--------9、哪些员工跟Hall(LAST_NAME)不在同一个部门。---------*/ 

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES 
    WHERE DEPARTMENT_ID NOT IN( 
    SELECT DEPARTMENT_ID FROM HR.EMPLOYEES 
    WHERE LAST_NAME = 'Hall'); 
  
   --或者： 

SQL> SELECT e1.FIRST_NAME FROM EMPLOYEES e1,EMPLOYEES e2 
    WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+) 
    and e2.LAST_NAME(+) = 'Hall'
    and e2.LAST_NAME IS NULL; 
  /*-------10、哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。--------*/ 

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES 
    WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES 
    WHERE FIRST_NAME = 'William' AND LAST_NAME = 'Smith');  
/*--------11、显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。---------*/ 

SQL> SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME, 
  E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY 
  FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L 
  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
  AND D.LOCATION_ID = L.LOCATION_ID 
  AND E.COMMISSION_PCT IS NOT NULL; 

/*--------12、显示Executive部门有哪些职位。---------*/ 
SQL> SELECT DISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D 
    WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID 
    AND D.DEPARTMENT_NAME = 'Executive'; 
  /*--------13、整个公司中，最高工资和最低工资相差多少。---------*/ 
SQL> SELECT MAX(SALARY) - MIN(SALARY) FROM HR.EMPLOYEES; 
/*--------14、提成大于0 的人数。---------*/ 
SQL> SELECT COUNT(*) AS 提成大小0的人数 FROM HR.EMPLOYEES 
    WHERE COMMISSION_PCT > 0; 
  --或者 

SQL> SELECT COUNT(COMMISSION_PCT) AS 提成大小0的人数   
     FROM HR.EMPLOYEES 
     WHERE COMMISSION_PCT > 0; 
  /*--------15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。---------*/ 

SQL> SELECT MAX(NVL(SALARY,0)) AS 最高工资, 
             MIN(NVL(SALARY,0)) AS 最低工资, 
             SUM(NVL(SALARY,0)) AS 工资总和, 
             ROUND(AVG(NVL(SALARY,0))) AS 平均工资 
    FROM HR.EMPLOYEES; 
  /*--------16、整个公司有多少个领导。---------*/ 
SQL> SELECT COUNT(DISTINCT(MANAGER_ID))  FROM HR.EMPLOYEES 
   2  WHERE MANAGER_ID IS NOT NULL; 
   /*--------17、列出在同一部门入职日期晚但工资高于其他同事的员工： 
名字、工资、入职日期。---------*/ 

SQL> SELECT DISTINCT E1.FIRST_NAME || ' ' || E1.LAST_NAME AS 姓名, 
          E1.SALARY AS 工资,E1.HIRE_DATE AS 入职日期 
   FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2 
   WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID 
   AND E1.HIRE_DATE > E2.HIRE_DATE 
   AND E1.SALARY > E2.SALARY 
   ORDER BY 工资 DESC; 
第四篇
/*--------1、各个部门平均、最大、最小工资、人数，按照部门号升序排列。---------*/ 
SQL> SELECT DEPARTMENT_ID AS 部门号,AVG(SALARY) AS 平均工资 
           ,MAX(SALARY) AS 最高工资,MIN(SALARY)  AS 最低工资 
           ,COUNT(*) AS 人数 
    FROM EMPLOYEES 
    GROUP BY DEPARTMENT_ID 
    ORDER BY DEPARTMENT_ID ASC; 
  /*--------2、各个部门中工资大于5000的员工人数。---------*/ 
SQL> SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES 
     WHERE SALARY > 5000 
     GROUP BY DEPARTMENT_ID; 
   /*--------3、各个部门平均工资和人数，按照部门名字升序排列。---------*/ 

SQL> SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM
          (SELECT
              (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT 
              WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME, 
              EMP.SALARY 
   FROM EMPLOYEES EMP) 
   GROUP BY DPTNAME 
   ORDER BY DPTNAME; 
  --或者-- 

SQL> SELECT DEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*) 
     FROM EMPLOYEES EMP,DEPARTMENTS DEPT 
     WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID 
     GROUP BY DEPT.DEPARTMENT_NAME 
     ORDER BY DEPT.DEPARTMENT_NAME; 
   /*--------4、列出每个部门中有同样工资的员工的统计信息， 
  列出他们的部门号，工资，人数。---------*/ 

SQL> SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT 
    FROM   EMPLOYEES EMP1,EMPLOYEES EMP2 
    WHERE  EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND
            EMP1.SALARY = EMP2.SALARY 
            AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID 
    GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY; 
   /*--------5、列出同部门中工资高于1000 的员工数量超过2 人的部门， 
显示部门名字、地区名称。---------*/ 

SQL> SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*) 
    FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L 
    WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
           D.LOCATION_ID   = L.LOCATION_ID    AND
           E.SALARY > 1000 
    GROUP BY D.DEPARTMENT_NAME,L.CITY 
    HAVING COUNT(*) > 2; 
   /*--------6、哪些员工的工资，高于整个公司的平均工资， 
  列出员工的名字和工资（降序）。---------*/ 

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY 
   FROM EMPLOYEES 
   WHERE SALARY > ( 
         SELECT AVG(SALARY) 
         FROM EMPLOYEES 
         ) 
   ORDER BY SALARY DESC;  
  
   /*--------7、哪些员工的工资，介于50号 和80号 部门平均工资之间。---------*/ 

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY 
    FROM EMPLOYEES 
    WHERE SALARY 
    BETWEEN
        (SELECT AVG(SALARY) FROM EMPLOYEES 
         WHERE DEPARTMENT_ID = 50) 
    AND (SELECT AVG(SALARY) FROM EMPLOYEES 
         WHERE DEPARTMENT_ID = 80); 
  /*--------8、所在部门平均工资高于5000 的员工名字。---------*/ 

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY 
    FROM EMPLOYEES 
    WHERE DEPARTMENT_ID IN
          (SELECT DEPARTMENT_ID FROM EMPLOYEES 
           GROUP BY DEPARTMENT_ID 
           HAVING AVG(SALARY) > 5000); 
   /*--------9、列出各个部门中工资最高的员工的信息：名字、部门号、工资。---------*/ 

SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME
          ,SALARY,DEPARTMENT_ID 
   FROM EMPLOYEES 
   WHERE (DEPARTMENT_ID,SALARY) IN
         (SELECT DEPARTMENT_ID,MAX(SALARY) 
          FROM EMPLOYEES 
          GROUP BY DEPARTMENT_ID);  
  
   /*--------10、最高的部门平均工资是多少。---------*/ 
SQL> SELECT MAX(AVGSALARY) 
   FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY 
     FROM EMPLOYEES 
     GROUP BY DEPARTMENT_ID);