EMPLOYEES 테이블에서 20 부서의 세부사항을 포한하는 EMP_20 VIEW를 생성하라
CREATE OR REPLACE VIEW EMP_20 AS SELECT * FROM EMPLOYEES;
SELECT * FROM EMP_20;
EMPLOYEES 테이블에서 30번 부서만 EMPLOYEE_ID 를 EMP_NO로 LAST_NAME을 NAME으로 SALARY를 SAL로 바꾸어 EMP_30을 생성하라
DESC EMPLOYEES;
CREATE OR REPLACE VIEW EMP_30(
EMP_NO,
FIRST_NAME,
NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SAL,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID
) AS (SELECT * FROM EMPLOYEES);
부서별로 부서명, 최소 급여, 최대 급여, 부서의 평균 급여를 포함하는 DEPT_SUM VIEW를 생성하라
CREATE OR REPLACE VIEW DEPT_SUM(
DEPARTMENT_NAME,
MIN_SAL,
MAX_SAL,
SUM_SAL,
AVG_SAL
)
AS (
SELECT D.DEPARTMENT_NAME, MIN(E.SALARY), MAX(E.SALARY), SUM(E.SALARY), AVG(E.SALARY)
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
);
Comments