[210924] SUBQUERY 연습문제

 JOIN 을 사용하지 않고 SUBQUERY 를 사용한다. (사실 JOIN 권장, 성능이 더 좋다)

 

SELECT * FROM EMPLOYEES; --EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE

                         --,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID

SELECT * FROM DEPARTMENTS; --DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID

SELECT * FROM JOBS; --JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY

SELECT * FROM COUNTRIES; --COUNTRY_ID, COUNTRY_NAME, REGION_ID

SELECT * FROM REGIONS; --REGION_ID, REGION_NAME(유럽,아메리카)

SELECT * FROM LOCATIONS;  --LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY(로마,베니스),STATE_PROVINCE,COUNTRY_ID

-- 모든 사원의 부서명과 직무명이 코드가 아닌 이름(명칭)으로 조회되도록 한다.

 

-- 모든 부서의 소재지를 조회하도록 한다.

 

-- 모든 사원의 급여가 직무에 정해진 범위에 맞게 받고 있는지 조회하도록 한다. (2가지 방법)

 

-- 부서의 매니저(관리자, 사원의 담당 매니저 아님)로 지정된 사원이 누구인지 알 수 있도록 조회한다. (2가지 방법 = 부서이름, 부서 ID)

 

-- 개별 사원의 담당 매니저가 누구인지 알 수 있도록 조회한다. 

 

 

--INSERT ALL

/*

별도의 두개의 테이블을 만들어서 각각의 테이블에 사원번호, 이름, 급여, 커미션퍼센트 정보가 저장될 수 있게 한다.

단, 커미션이 없는 사원용 테이블에는 커미션퍼센트를 만들지 않는다.

*/

 

 

 

 

 

 

 

답

SELECT EMPLOYEE_ID      , FIRST_NAME      , LAST_NAME      , DEPARTMENT_ID      , JOB_ID      , (SELECT DEPARTMENT_NAME FROM DEPARTMENTS B WHERE DEPARTMENT_ID = A.DEPARTMENT_ID) AS 부서명      , (SELECT JOB_TITLE FROM JOBS C WHERE JOB_ID = A.JOB_ID) AS 직무명   FROM EMPLOYEES A; -- 테이블 별칭으로 컬럼 구별 해 주어야 한다.

SELECT DEPARTMENT_NAME      , (SELECT STATE_PROVINCE || ' ' || CITY || ' ' || STREET_ADDRESS           FROM LOCATIONS B WHERE B.LOCATION_ID = A.LOCATION_ID) AS 소재지   FROM DEPARTMENTS A;

SELECT A.EMPLOYEE_ID      , A.FIRST_NAME      , A.LAST_NAME      , A.SALARY      , DECODE((SELECT COUNT(*) FROM JOBS B       -- 1개 나오면 적합한 급여(카운트), 0 과 1 값을 디코드로 변환          WHERE B.JOB_ID = A.JOB_ID               AND A.SALARY BETWEEN B.MIN_SALARY AND B.MAX_SALARY), 1, '적합', '부적합') AS "적합/부적합"      -- 표현식이 1이면 적합 아니면 부적합, DECODE(표현식, 조건1, 결과1, 조건2, 결과2...)   FROM EMPLOYEES A; --내가 SELECT EMPLOYEE_ID       , FIRST_NAME || LAST_NAME AS 이름       , SALARY       , CASE WHEN SALARY BETWEEN (SELECT MIN_SALARY FROM JOBS B WHERE A.JOB_ID = B.JOB_ID)                             AND (SELECT MAX_SALARY FROM JOBS B WHERE A.JOB_ID = B.JOB_ID)              THEN '적정한 급여'             ELSE '부적정 급여'         END AS 급여평가   FROM EMPLOYEES A;

SELECT DEPARTMENT_NAME      , (SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES B WHERE A.MANAGER_ID = B.EMPLOYEE_ID)   FROM DEPARTMENTS A; SELECT DEPARTMENT_ID      , FIRST_NAME || ' ' || LAST_NAME   FROM EMPLOYEES  WHERE EMPLOYEE_ID IN (SELECT MANAGER_ID FROM DEPARTMENTS WHERE MANAGER_ID IS NOT NULL);

SELECT EMPLOYEE_ID      , FIRST_NAME || ' ' || LAST_NAME AS 사원이름      , MANAGER_ID      , (SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES B WHERE B.EMPLOYEE_ID = A.MANAGER_ID) AS 매니저이름   FROM EMPLOYEES A;

INSERT ALL   WHEN 커미션퍼센트 IS NOT NULL THEN       INTO 커미션이있는사원 VALUES(사원번호, 이름, 급여, 커미션퍼센트)   WHEN 커미션퍼센트 IS NULL THEN       INTO 커미션이없는사원 VALUES(사원번호, 이름, 급여) SELECT EMPLOYEE_ID AS 사원번호      , FIRST_NAME || ' ' || LAST_NAME AS 이름      , SALARY AS 급여      , COMMISSION_PCT AS 커미션퍼센트   FROM EMPLOYEES; -- 다 별칭이라 헷갈린다면 보기 INSERT ALL   WHEN COMMISSION_PCT IS NOT NULL THEN       INTO 커미션이있는사원 VALUES(EMPLOYEE_ID, 이름, SALARY, COMMISSION_PCT)   WHEN COMMISSION_PCT IS NULL THEN       INTO 커미션이없는사원 VALUES(EMPLOYEE_ID, 이름, SALARY) SELECT EMPLOYEE_ID      , FIRST_NAME || ' ' || LAST_NAME AS 이름      , SALARY      , COMMISSION_PCT   FROM EMPLOYEES;