국비 TIL(Today I Learned) 20220712

OT 계정생성

sys계정에서 다른사용자 왼쪽클릭 사용자 생성

 

CONNECT 가 있어야 외부에서 붙음

RESOURCE도 체크

 

 

ANY는 다른 계정 접근, 지금은 신경안써도 되

CUBE 테이블을 2차원으로 만드는데, 행 열 이렇게 CUBE은 3차원으로 만들어줌

CUBE가 모여 마트? 마트가 모여 웨어?

CUBE 이상은 데이터통계업체에서만 쓴데

 

물질화된 뷰, 오라클만 기능있음

뷰를 테이블처럼 저장해놓고 사용할수 있음, 

 

반복문 돌리고, 변수에 값 넣어주고 이럴수 있데

 

SEQUENCE랑 TABLE 권한주기

 

 

 

+ 버튼 누르고 새로 만들기

 

계정 생성되고, OT파일 가져오기(구글 드라이브)

 

맨 아래 COMMIT;  넣어주기

 

빨간테두리 부분 Local_OT 선택하고 아무데나 클릭해서 F5 누르면 알아서 넣어줌

 

 

 

낼 셤 문제

하: 집계 또는 순차함수 1개

중: 집계 또는 순차함수 또는 조인 또는 서브쿼리 2개

상: 집계 또는 순차함수 또는 조인 또는 서브쿼리 3개 이상 수준이래

 

-- 문제1
-- 도시별 2005년 입사자가 많은 업무를 구하시오.
SELECT L.CITY, E.JOB_ID, COUNT(*),
RANK() OVER(PARTITION BY L.CITY ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE TO_CHAR(HIRE_DATE,'YYYY') = '2005'
GROUP BY E.JOB_ID, L.CITY;


SELECT E.CITY, J.JOB_TITLE
FROM (
SELECT L.CITY, E.JOB_ID, COUNT(*),
RANK() OVER(PARTITION BY L.CITY ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE TO_CHAR(HIRE_DATE,'YYYY') = '2005'
GROUP BY E.JOB_ID, L.CITY ) E INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID
WHERE E.RNK = '1'
;

-- 강사님 풀이
SELECT E.CITY, J.JOB_TITLE
FROM JOBS J INNER JOIN(
SELECT L.CITY, E.JOB_ID, COUNT(*) AS CNT,
RANK() OVER(PARTITION BY L.CITY ORDER BY COUNT(*) DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE TO_CHAR(HIRE_DATE,'YYYY') = '2005'
GROUP BY L.CITY, E.JOB_ID) E ON J.JOB_ID = E.JOB_ID AND E.RNK =1
;

-- 차이점: 나는 WHRER를 써서 RNK 걸려줬고, 강사님은 AND로 



-- Seattle에서 근무하는 사원들의 부서별 급여 1위를 구하시오.
-- 출력: 부서이름, 연봉, 도시
SELECT S.DEPARTMENT_NAME ,S.SALARY, S.CITY FROM(
SELECT E.SALARY, D.DEPARTMENT_NAME, L.CITY,
RANK() OVER(PARTITION BY D.DEPARTMENT_NAME ORDER BY E.SALARY DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE L.CITY = 'Seattle') S WHERE S.RNK= '1';


-- 문제1
-- 2001년에 입사한 사람의 나라이름
-- 출력: 이름, 나라이름
SELECT FIRST_NAME, C.COUNTRY_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID
WHERE TO_CHAR(E.HIRE_DATE, 'YYYY') = '2001';

-- 문제2
-- 도시별 사원들의 급여에서 최저 급여와 최고 급여의 차이가 가장 큰 도시와 그 차이를 구하시오
SELECT S.CITY, S.CHA
FROM(
SELECT S.CITY, MAX(S.SALARY),  MIN(S.SALARY),  MAX(S.SALARY) -  MIN(S.SALARY) AS CHA,
RANK() OVER(ORDER BY  MAX(S.SALARY) -  MIN(S.SALARY) DESC) AS RNK
FROM (SELECT E.SALARY, L.CITY
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID)S
GROUP BY S.CITY ) S WHERE S.RNK='1'
;

-- 문제3
-- PHONE_NUMBER 맨 뒷자리가 4인 사원의 LAST_NAME과 COUNTRY_ID를 뽑고,
-- COUNTRY_ID가 UK인 사람은 50%인상 US인 사람은 10%인상하여 그들만의 월급 순위를 구하시오.
-- 출력 = LAST_NAME, PHONE_NUMBER, COUNTRY_ID, 원래 월급, 인상 후 월급, 랭킹
SELECT  S.LAST_NAME, S.PHONE_NUMBER, S.COUNTRY_ID, S.SALARY, S.RSAL,
RANK() OVER(ORDER BY S.RSAL DESC)
FROM (SELECT E.LAST_NAME, E.PHONE_NUMBER, C.COUNTRY_ID, E.SALARY,
          CASE WHEN C.COUNTRY_ID = 'UK'
            THEN E.SALARY * 1.5
            WHEN C.COUNTRY_ID = 'US'
            THEN E.SALARY * 1.1
            ELSE E.SALARY
        END AS RSAL
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID
WHERE PHONE_NUMBER LIKE '%4') S;

-- 문제4
-- 급여가 3000 미만의 사원들을 구하고 급여 순위를 구하세요
-- 출력 : FIRST_NAME + LAST_NAME , SALARY, COUNTRY_NAME, RNK
SELECT CONCAT(FIRST_NAME, LAST_NAME), SALARY, C.COUNTRY_NAME,
RANK() OVER(ORDER BY SALARY DESC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID 
WHERE SALARY < 3000;

-- 문제 4.5
-- 나라별 사원들의 평균 급여를 구하고, 
-- 평균 급여가 가장 낮은 1순위 나라의 평균급여를 5000인상,  
-- 2순위는 3천, 3순위는 1천 인상후, 
-- 인상전의 평균급여, 인상전 순위를 오름차순 
-- 인상후의 평균급여과 인상후의 순위를 내림차순으로 구하시오.
SELECT  D.AVG_SAL AS "인상 전 평균급여", D.RNK AS "인상 전 순위", D.RSAL AS "인상 후 평균급여",
RANK() OVER(ORDER BY D.RSAL DESC) AS "인상 후 순위", D.COUNTRY_ID AS "나라"
FROM (
SELECT S.COUNTRY_ID, S.AVG_SAL, S.RNK,
     CASE WHEN S.RNK = 1
            THEN AVG_SAL + 5000
            WHEN S.RNK = 2
            THEN AVG_SAL + 3000
            WHEN S.RNK = 3
            THEN AVG_SAL + 1000
            ELSE AVG_SAL
        END AS RSAL
FROM(
SELECT C.COUNTRY_ID, FLOOR(AVG(E.SALARY)) AS AVG_SAL, 
RANK() OVER(ORDER BY FLOOR(AVG(E.SALARY)) ASC) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID INNER JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID
GROUP BY C.COUNTRY_ID) S) D
;




-- 문제5
-- 급여가 4000에서 5000사이인 사원의 이름과 소속부서명과 주소, 급여, 순위를 출력하시오.
SELECT FIRST_NAME,D.DEPARTMENT_NAME, L.STREET_ADDRESS, SALARY,
RANK() OVER(ORDER BY SALARY DESC) AS RNK
FROM EMPLOYEES  E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID WHERE SALARY BETWEEN 4000 AND 5000;

-- 문제6
-- FIRST_NAME이 Bruce 인 사람보다 나중에 입사한 사원들의 FIRST_NAME, HIRE_DATE 를 조회해라.
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= (SELECT HIRE_DATE FROM EMPLOYEES WHERE FIRST_NAME = 'Bruce')
ORDER BY HIRE_DATE
;

​
-- 문제7
-- 도시별 사원 수와 급여 평균을 출력하세요 (사원 수 오름차순 정렬)
SELECT L.CITY, COUNT(*) AS CNT, FLOOR(AVG(E.SALARY)) AS AVG_SAL
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.CITY
ORDER BY CNT
;

-- 문제8 => 하는중
-- 유럽과 아시아에서 입사자가 가장 많은 부서의 2007년에 입사한 사원 중
-- 연봉이 가장 낮은 사원의 상급자 정보를 출력
-- 출력 : 부하직원의 이름, 상급자의 이름, 상급자의 연봉, 상급자의 업무이름


-- 문제9
-- S로 시작하는 도시별 평균급여와 그 도시에서 일하는 직원들의 개별급여를 구하고
-- 도시의 평균급여보다 낮은 직원들의 급여를 10% 인상하시오.
-- 출력: S로 시작하는 도시들의 평균급여, 각 직원 급여,도시명, 사원이름, 변경된 급여
SELECT E.FIRST_NAME, L.CITY, E.SALARY, S.AVG_SAL, 
         CASE WHEN E.SALARY  < S.AVG_SAL
            THEN E.SALARY * 1.1
            ELSE E.SALARY
        END AS RSAL
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID INNER JOIN (
SELECT L.CITY, FLOOR(AVG(E.SALARY)) AS AVG_SAL
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE L.CITY LIKE 'S%'
GROUP BY L.CITY
)S ON L.CITY= S.CITY;


-- 문제10
-- 업무 인원이 가장 많은 부서를 출력하세요
SELECT S.CITY, S.DEPARTMENT_NAME
FROM (
SELECT L.CITY, D.DEPARTMENT_NAME, COUNT(*),
RANK() OVER(PARTITION BY L.CITY ORDER BY COUNT(*) DESC ) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.CITY, D.DEPARTMENT_NAME) S
WHERE S.RNK = 1
;


-- 문제11
-- 사원수 1등 2등의 부서들의 급여 평균의 차이를 구하시오
SELECT ABS(MAX(DECODE(S.RNK, 1, S.AVG_SAL, NULL)) -MIN(DECODE(S.RNK, 2, S.AVG_SAL, NULL))) AS CHA
FROM (
SELECT D.DEPARTMENT_NAME, COUNT(*), FLOOR(AVG(E.SALARY)) AS AVG_SAL,
RANK() OVER(ORDER BY COUNT(*) DESC ) AS RNK
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
)S 
WHERE S.RNK IN(1,2)
;

-- 문제12
-- 'COUNTRY_ID'가 'US'인 2004년 입사자들의 연봉을 20% 인상시키고, 2003년 입사자들은 20% 감소시켜라. (나머지는 그대로)
-- 위를 바탕으로,부서 ID별 바뀐 연봉 순위를 매기고 1순위만 출력하여라.
-- 출력) 2004,2003년 입사자의 FIRST_NAME, 입사자들의 원래 연봉, COUNTRY_ID, 바뀐 연봉, 부서별 연봉 1순위 
SELECT M.FIRST_NAME, M.SALARY, M.COUNTRY_ID, M.DEPARTMENT_ID, M.RNK FROM (
SELECT S.FIRST_NAME, S.SALARY, S.COUNTRY_ID, S.DEPARTMENT_ID, S.HIRE_DATE,
RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY RSAL) AS RNK
FROM (
SELECT E.FIRST_NAME, E.SALARY, L.COUNTRY_ID, E.DEPARTMENT_ID, E.HIRE_DATE,
 CASE WHEN TO_CHAR(E.HIRE_DATE,'YYYY') = '2004'
            THEN E.SALARY * 1.2
            WHEN TO_CHAR(E.HIRE_DATE,'YYYY') = '2003'
            THEN E.SALARY * 0.8
            ELSE E.SALARY
        END AS RSAL
FROM EMPLOYEES  E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE L.COUNTRY_ID = 'US' AND  TO_CHAR(HIRE_DATE, 'YYYY') IN ('2004', '2003')
) S ) M WHERE M.RNK = '1';


-- 문서13 => 평균값이 답이랑 다름, 하다가 말음
-- 판매부서 직업별 최고연봉자의 연봉과, 판매부서 직원들의 평균연봉을 비교해서 그 차이를 구하시오
-- 출력 : 직업, 최고연봉자 FIRSTNAME, 최고연봉, 판매부서평균연봉, 차이 

SELECT AVG(SALARY) FROM EMPLOYEES  E INNER JOIN JOB_HISTORY JH ON E.JOB_ID = JH.JOB_ID INNER JOIN JOBS J ON JH.JOB_ID = J.JOB_ID
WHERE J.JOB_TITLE LIKE 'Sales%';


-- 문제14 
-- 소속한 부서의 평균 연봉보다 낮은 연봉인 사원들의 ENAME, SALARY, DEPARTMENT_ID, 각 부서 평균 연봉 AS AVG_SALARY 을 조회해라.
SELECT CONCAT(E.FIRST_NAME, E.LAST_NAME), E.SALARY, E.DEPARTMENT_ID, S.AVG_SAL FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
INNER JOIN (SELECT FLOOR(AVG(E.SALARY))AS AVG_SAL, D.DEPARTMENT_ID FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID GROUP BY D.DEPARTMENT_ID) S ON D.DEPARTMENT_ID=S.DEPARTMENT_ID
WHERE E.SALARY < S.AVG_SAL
ORDER BY S.AVG_SAL
;

-- 문제15, => 하다가 말음
-- 나라별 사원 평균을 구하고 1,4 순위는 20%인상 후 차이 값  2, 3 순위는 20%삭감 후 합계 값을 구하시오.
-- RANK 로 순위 구할때 PARTITION BY 사용하지 않음
-- 출력 : 1 , 2 , 3 , 4 등 ,
-- 1 , 4등의 인상 후 값  ,
-- 1 , 4등 인상후 차이 값 ,
-- 2 , 3등 삭감 값 ,
-- 2 , 3등 삭감 후 값

SELECT AVG_SAL,
RANK() OVER(ORDER BY AVG_SAL ASC) AS RNK
FROM(
SELECT L.COUNTRY_ID, FLOOR(AVG(E.SALARY)) AS AVG_SAL FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.COUNTRY_ID)
;

-- 문제16
-- Seattle'에서 근무하는 사원들이 제일 많이 하고 있는 업무의 최대, 최소 급여를 구하라
SELECT J.JOB_TITLE, J.MAX_SALARY, J.MIN_SALARY FROM JOBS J INNER JOIN 
(SELECT J.JOB_TITLE, COUNT(*),
RANK() OVER (ORDER BY COUNT(*) DESC) AS CNT
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID INNER JOIN JOBS J ON E.JOB_ID =J.JOB_ID 
WHERE L.CITY = 'Seattle'
GROUP BY J.JOB_TITLE) S ON J.JOB_TITLE = S.JOB_TITLE
WHERE S.CNT = '1'
;

-- 문제 17  => 하다가 말음
-- 2005, 2006년에 입사한 사원들의 도시별 업무들의 평균 급여를 구하고,
-- 평균 급여가 2등인 도시와 업무에 속한 사원들의 정보를 출력하세요.
-- 출력 내용 -> 입사년도, LAST_NAME, CITY , JOB_TITLE,SALARY, 2등인 평균급여
SELECT E.FIRST_NAME, E.SALARY, L.CITY FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE TO_CHAR(HIRE_DATE,'YYYY') IN ('2005', '2006');

SELECT FLOOR(AVG(E.SALARY)), L.CITY FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
WHERE TO_CHAR(HIRE_DATE,'YYYY') IN ('2005', '2006') GROUP BY L.CITY;