ORACLE基础练习（一）

来源：互联网发布：淘宝茶叶有多少人竞争编辑：程序博客网时间：2024/05/16 19:26

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

--CREATE TABLE EMP AS SELECT * FROM scott.emp;--插入新数据到EMP中 INSERT INTO EMPVALUES  (102, 'EricHu', 'Developer', 1455, DATE '2011-5-26', 5500.00, 14.00, 10);INSERT INTO EMPVALUES  (104, 'huyong', 'PM', 1455, DATE '2011-5-26', 5500.00, 14.00, 10);INSERT INTO EMPVALUES  (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.JOBHAVING 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的解析函数。

0 0