ORACLE基础练习（一）

找了一些SQL题目来练习一些当年所学的SQL语句些：

--CREATE TABLE EMP AS SELECT * FROM scott.emp;
--插入新数据到EMP中 
INSERT INTO EMP
VALUES
  (102, 'EricHu', 'Developer', 1455, DATE '2011-5-26', 5500.00, 14.00, 10);
INSERT INTO EMP
VALUES
  (104, 'huyong', 'PM', 1455, DATE '2011-5-26', 5500.00, 14.00, 10);
INSERT INTO EMP
VALUES
  (106,
   'WANGJING',
   'Developer',
   1455,
   DATE '2011-5-26',
   5500.00,
   14.00,
   50);
COMMIT;
SELECT * FROM SCOTT.EMP;

表数据：

--插入新数据 
INSERT INTO DEPT VALUES (50, '50abc', '50def');
INSERT INTO DEPT VALUES (60, 'Developer', 'HaiKou');

--列出至少有一个员工的所有部门 --关于取唯一值 oracle distinct 数据 Group解决唯一性问题
 SELECT A.DEPTNO, A.DNAME, A.LOC
  FROM SCOTT.DEPT A, SCOTT.EMP B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY A.DEPTNO, A.DNAME, A.LOC;

SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP);

--列出薪金比“ SMITH ” 多的所有员工。
--SMITH的薪金
 SELECT SAL
  FROM SCOTT.EMP
 WHERE ENAME = 'SMITH'

  SELECT *
          FROM SCOTT.EMP A
         WHERE A.SAL > (SELECT SAL FROM SCOTT.EMP WHERE ENAME = 'SMITH');

 --列出所有员工的姓名及其直接上级的姓名。
 SELECT EMPNO, ENAME,
  FROM EMP
 WHERE MGR IN (SELECT EMP.EMPNO FROM EMP)

  SELECT A.EMPNO, A.ENAME, B.ENAME
          FROM EMP A, EMP B
         WHERE A.MGR = B.EMPNO
        
          SELECT EMP.EMPNO, EMP.ENAME, MGR.ENAME AS MGRENAME
                  FROM EMP
                  LEFT OUTER JOIN (SELECT EMPNO, ENAME FROM EMP) MGR
                    ON EMP.MGR = MGR.EMPNO;

 --列出受雇日期早于其直接上级的所有员工
 SELECT A.EMPNO, A.ENAME
  FROM EMP A
  JOIN EMP MGRHIREDATE
    ON MGRHIREDATE.EMPNO = A.MGR
 WHERE A.HIREDATE < MGRHIREDATE.HIREDATE;

 --列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT A.DEPTNO, A.DNAME, B.EMPNO, B.ENAME
  FROM SCOTT.DEPT A
  LEFT JOIN (SELECT * FROM SCOTT.EMP) B
    ON A.DEPTNO = B.DEPTNO;

 --列出所有“ CLERK ” （办事员）的姓名及其部门名称。
 SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
  FROM EMP A, DEPT B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.JOB = 'CLERK';

 --列出最低薪金大于1500 的各种工作
 SELECT DISTINCT (A.JOB)
  FROM SCOTT.EMP A
 GROUP BY A.JOB
HAVING MIN(A.SAL) > 1500;

 --列出在部门“ SALES ” （销售部）工作的员工的姓名，假定不知道销售部的部门编号。
 SELECT B.EMPNO, B.ENAME
  FROM SCOTT.EMP B, SCOTT.DEPT A
 WHERE B.DEPTNO = A.DEPTNO
   AND A.DNAME = 'SALES';

SELECT *
  FROM EMP
 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');

 --列出薪金高于公司平均薪金的所有员工
 SELECT *
  FROM SCOTT.EMP A
 WHERE A.SAL > (SELECT AVG(B.SAL) FROM SCOTT.EMP B);

 --列出与“ SCOTT ” 从事相同工作的所有员工。
 SELECT *
  FROM SCOTT.EMP A
 WHERE A.JOB = (SELECT B.JOB FROM SCOTT.EMP B WHERE B.ENAME = 'SCOTT');

 --列出与部门30 中员工的工作相同的所有员工的姓名和薪金。
 SELECT *
  FROM SCOTT.EMP A
 WHERE A.JOB IN (SELECT B.JOB FROM SCOTT.EMP B WHERE B.DEPTNO = 30)
      --不包括部门30的员工  
      -- != <>
   AND A.DEPTNO <> 30;
 --只有部门为30 =
 SELECT *
  FROM EMP
 WHERE SAL = ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30)

 --列出薪金高于在部门30 工作的所有员工的薪金的员工姓名和薪金。
  SELECT *
          FROM SCOTT.EMP A
         WHERE A.SAL > ALL
         (SELECT B.SAL FROM SCOTT.EMP B WHERE B.DEPTNO = 30);


SELECT *
  FROM SCOTT.EMP A
 WHERE A.SAL > (SELECT MAX(B.SAL) FROM SCOTT.EMP B WHERE B.DEPTNO = 30);

--列出在每个部门工作的员工数量、平均工资
 SELECT A.DEPTNO,
       A.DNAME,
       (SELECT COUNT(*) FROM SCOTT.EMP B WHERE A.DEPTNO = B.DEPTNO),
       (SELECT AVG(C.SAL) FROM SCOTT.EMP C WHERE C.DEPTNO = A.DEPTNO)
  FROM SCOTT.DEPT A;

 --列出在每个部门工作平均服务期限。 
 SELECT DEPTNO, AVG(TRUNC((SYSDATE - HIREDATE) / 365)) AS YEAR
  FROM EMP
 GROUP BY DEPTNO;

 --参考：截断，取整函数  
 SELECT TRUNC(75.444) FROM DUAL;
 --列出所有员工的姓名、部门名称和工资
 SELECT A.ENAME, B.DNAME, TRUNC(A.SAL)
  FROM SCOTT.EMP A, SCOTT.DEPT B
 WHERE A.DEPTNO = B.DEPTNO

 --列出所有部门的详细信息和部门人数。
  SELECT A.DEPTNO,
         A.DNAME,
         A.LOC,
         (SELECT COUNT(*) FROM SCOTT.EMP B WHERE B.DEPTNO = A.DEPTNO) AS COUNT
          FROM SCOTT.DEPT A;

 --列出各种工作的最低工资。
 SELECT A.JOB, MIN(A.SAL) FROM SCOTT.EMP A GROUP BY A.JOB;

 --列出各个部门的MANAGER （经理）的最低薪金
 SELECT A.DEPTNO,
       A.DNAME,
       (SELECT MIN(B.SAL)
          FROM SCOTT.EMP B
         WHERE B.JOB = 'MANAGER'
           AND A.DEPTNO = B.DEPTNO) AS MIN_SALARY
  FROM SCOTT.DEPT A;
--
SELECT DEPTNO, MIN(SAL) FROM EMP WHERE JOB = 'MANAGER' GROUP BY DEPTNO;

 --列出所有员工的年工资, 按年薪从低到高排序。
 SELECT A.EMPNO, A.ENAME, (A.SAL * 12) AS YEAR_SALARY
  FROM SCOTT.EMP A
 ORDER BY A.SAL; --default asc !!

 --19. 求各种工作工资最低的员工。 
--方法一：  
 SELECT *
  FROM EMP A
 WHERE SAL = (SELECT MIN(SAL) FROM EMP B WHERE B.JOB = A.JOB);
 --方法二：
 SELECT A.*
  FROM EMP A, (SELECT JOB, MIN(SAL) MIN_SAL FROM EMP GROUP BY JOB) B
 WHERE A.JOB = B.JOB
   AND A.SAL = B.MIN_SAL;

 /*用一条sql 语句查询出scott.emp 表中每个部门工资前三位的数据，显示结果如下：
                    DEPTNO SAL1 SAL2 SAL3
                    ------ ------- -------- ---------
                    10 5500 5500 5500
                    20 4000 3000 2975
                    30 2850 1600 1500*/
--answer 1   ？？？？？
 WITH T AS
 (SELECT SAL, DEPTNO, 'SAL' || T FLAG
    FROM (SELECT A.*,
                 ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) T
            FROM EMP A) E
   WHERE E.T <= 3)
SELECT * FROM T PIVOT (SUM(SAL) FOR FLAG IN('SAL1', 'SAL2', 'SAL3'));
 --answer 2
 SELECT DEPTNO,
       MAX(SAL) SAL1,
       MAX(DECODE(T, 2, SAL)) SAL2,
       MAX(DECODE(T, 3, SAL)) SAL3
  FROM (SELECT SAL, DEPTNO, T
          FROM (SELECT SAL,
                       ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) T,
                       DEPTNO
                  FROM EMP) E
         WHERE E.T <= 3)
 GROUP BY DEPTNO;
 --answer 3
 SELECT A.DEPTNO,
       MAX(A.SAL) AS SAL1,
       (MAX(DECODE(A.Y, 2, SAL))) AS SAL2,
       (MAX(DECODE(A.Y, 3, SAL))) AS SAL3
  FROM (SELECT C.DEPTNO AS DEPTNO, C.Y AS Y, C.SAL AS SAL
          FROM (SELECT B.DEPTNO,
                       B.SAL,
                       DENSE_RANK() OVER(PARTITION BY B.DEPTNO ORDER BY SAL DESC) AS Y
                  FROM EMP B) C
         WHERE C.Y <= 3) A
 GROUP BY A.DEPTNO;

 -- 列出各种工作工资前3名的员工
 SELECT *
  FROM (SELECT EMPNO,
               ENAME,
               SAL,
               JOB,
               DENSE_RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) AS 名次
          FROM EMP) A
 WHERE A.名次 <= 3
 ORDER BY JOB;


 --说明：用到了Oracle强大的“分区排名技术”，其中“DENSE_RANK()”是Oracle的解析函数。