learn_oracle_notebook/course/02-practice.sql

/*
    练习2
*/

-- 创建数据表
CREATE TABLE USER_SK (
    ID NUMBER(4),
    PASSWORD CHAR(4),
    NAME CHAR(20),
    PHONE CHAR(20),
    EMAIL VARCHAR(50)
);

-- 为表添加数据
INSERT INTO USER_SK VALUES(
    1001,
    '1234',
    'liucs',
    '13600000000',
    'LIUCS@SINA.COM'
);

-- 查询数据
SELECT
    *
FROM
    USER_SK;

/* 
    案例-1
*/
-- 删除表
DROP TABLE DEPT_SK;

CREATE TABLE DEPT_SK(
    DEPTNO NUMBER(2),
    DNAME CHAR(20),
    LOCATION CHAR(20)
);

INSERT INTO DEPT_SK VALUES(
    10,
    'developer',
    'beijing'
);

INSERT INTO DEPT_SK VALUES(
    20,
    'account',
    'shanghai'
);

INSERT INTO DEPT_SK VALUES(
    30,
    'sales',
    'guangzhou'
);

INSERT INTO DEPT_SK VALUES(
    40,
    'operations',
    'tianjin'
);

COMMIT;

-- 查询创建的 DEPT_SK 表
SELECT
    *
FROM
    DEPT_SK;

/* 
    案例-1
        创建表: EMP
 */
-- 创建表 EMP_SK
CREATE TABLE EMP_SK(
    EMPNO NUMBER(4),
    ENAME VARCHAR2(20),
    JOB VARCHAR2(15),
    SALARY NUMBER(7, 2),
    BONUS NUMBER(7, 2),
    HIREDATE DATE,
    MGR NUMBER(4),
    DEPTNO NUMBER(10)
);

-- 插入数据
INSERT INTO EMP_SK VALUES(
    1001,
    '张无忌',
    'Manager',
    10000,
    2000,
    '',
    1005,
    10
);

INSERT INTO EMP_SK VALUES(
    1002,
    '刘芒松',
    'Analyst',
    8000,
    1000,
    '',
    NULL,
    10
);

INSERT INTO EMP_SK VALUES(
    1003,
    '李翔',
    'Analyst',
    9000,
    1000,
    '',
    1001,
    10
);

INSERT INTO EMP_SK VALUES(
    1004,
    '郭英莹',
    'Programmer',
    5000,
    NULL,
    '',
    1001,
    10
);

INSERT INTO EMP_SK VALUES(
    1005,
    '张三丰',
    'President',
    15000,
    NULL,
    '',
    NULL,
    20
);

INSERT INTO EMP_SK VALUES(
    1006,
    '燕小六',
    'Manager',
    5000,
    400,
    '',
    1005,
    20
);

INSERT INTO EMP_SK VALUES(
    1007,
    '陆无双',
    'Clerk',
    3000,
    500,
    '',
    1006,
    20
);

INSERT INTO EMP_SK VALUES(
    1008,
    '黄莺',
    'Manager',
    5000,
    500,
    '',
    1005,
    30
);

INSERT INTO EMP_SK VALUES(
    1009,
    '乔小宝',
    'Salesman',
    4000,
    NULL,
    '',
    1008,
    30
);

INSERT INTO EMP_SK VALUES(
    1010,
    '郭琦',
    'Salesman',
    4500,
    500,
    '',
    1008,
    30
);

-- 设置显示格式
set linesize 150;

col empno for 9999;

col mgr for 9999;

col deptno for 99;

col salary for 99999.99;

-- 查询表
SELECT
    *
FROM
    EMP_SK;

-- 插入一条ID 为1011, 姓名为 '余泽成', 其余字段为NULL 的数据
INSERT INTO EMP_SK(
    EMPNO,
    ENAME
) VALUES(
    1011,
    '余泽成'
);

-- 查询EMP_XXX 表, 如果没有职位, 显示'no position', 如果有职位, 显示员工的职位
SELECT
    ENAME,
    NVL(JOB, '无职位')
FROM
    EMP_SK;

/* 
    案例2-关联子查询
 */

-- 哪些员工的薪水比公司的平均薪水低？
SELECT
    ENAME,
    SALARY
FROM
    EMP_SK
WHERE
    SALARY < (
        SELECT
            AVG(NVL(SALARY, 0))
        FROM
            EMP_SK
    );

-- 哪些员工的薪水比本部门的平均薪水低？不再和整个部门的平均薪水比较。
SELECT
    E.ENAME,
    E.SALARY,
    E.DEPTNO
FROM
    EMP_SK E
    JOIN (
        SELECT
            AVG(NVL(SALARY, 0)) NEW_SALARY,
            DEPTNO
        FROM
            EMP_SK
        WHERE
            SALARY IS NOT NULL
        GROUP BY
            DEPTNO
    ) NE
    ON E.DEPTNO=NE.DEPTNO
WHERE
    E.SALARY < NE.NEW_SALARY
ORDER BY
    DEPTNO;

-- 只查询当前时间
SELECT
    SYSDATE
FROM
    DUAL;

/* 
    案例3-关联子查询
 */
-- 哪些人是其他人的经理？( 查找有下属的员工 )
SELECT
    ENAME,
    EMPNO
FROM
    EMP_SK E
WHERE
    EXISTS (
        SELECT
            1
        FROM
            EMP_SK
        WHERE
            MGR=E.EMPNO
    );