＼(^o^)／

--서브쿼리에 대한 개념. selet절 서브쿼리 활용
SELECT TO_CHAR(A.DT1,'YYYYMMDD') DATE1 --상위쿼리
FROM (   --하위쿼리
select to_date('20210101','YYYY-MM-DD') DT1
    ,TO_DATE('20220101','YYYY-MM-DD') DT2
    FROM DUAL
    ) A
    ;
    --먼저 SEELCT FROM문으로 컬럼 두개가 만들어졌고 이 부분을 묶어서 테이블에 저장한 테이블이라고 생각.
    --만든 SELECT문을 테이블 하나라고 생각하고 FROM절 안에 넣고 A라고 테이블에 별칭 선언. 
    --=== > 지금 FROM절에 들어가있는데 테이블이 아닌 SELECT문으로 작성한 데이터라고 생각하면됨. (안쪽에 작성되어있기때문에 "하위쿼리"라 부름)
    --여기서 A.DT만 출력하면 A라는테이블의 DT1만 출력하면 FROM절에서 데이터 꺼내온거랑 똑같다. 
    
/*SELECT절의 서브쿼리*/
--전체급여 합산한거를 모든열에 넣어서 쓰고싶음. 
SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES; --여기서 표시된 전체급여 합산액을

SELECT EMPLOYEES.*
    , (SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES) AS 전체급여 -- *,SALARY 하면 에러남. 결과를 반환할수잇는방법능ㄴ *앞에 테이블명칭을 붙여줘야함.
    --이렇게 셀렉트문으로 계산한 데이터를 그대로 가져오게되면 로우데이터를 건들지않고 구간별비중이나 특별한 계산을 해서 파생컬럼을 만들수있는 장점이 존재. 
    , (SELECT MAX(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최대급여
    , (SELECT MIN(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최소급여
    , (SELECT COUNT(SALARY) AS 전체급여 FROM EMPLOYEES) AS 직원수
    , (SELECT ROUND(AVG(SALARY),2) AS 전체급여 FROM EMPLOYEES) AS 평균급여  --ROUND를 AVG바로앞에서 제어할수도있고 최상단에서 제어할수도있음.
FROM EMPLOYEES;

--셀렉트절의 서브쿼리는 서브쿼리가 셀렉트절로 확장되었다고 해서 스칼럿서브쿼리라고 블림
--출력되는 행수만큼 반복되어 실행된다
--같은 SQL이 반복되어 실행되면서 데이터를 반복해서 출력하므로 성능을 위해 첫번째 행인 100번사원의 데이터를 출력할때 나머지 출력한 데이터를 메모리에 올려놓고
--두번째행부터는메모리에 올려놓은 데이터를 출력하게된다 -- 이러한행위를 서브쿼리캐싱 이라고함.

--이와같은결과를 WINDOW함수를 통해서 출력할수있음.
--윈도우 함수 : 그룹합수 뒤에 OVER라는 함수를 통해 집계하게 됨. 
--OVER 정리 : 쿼리 결과 집합 내의 윈도우 또는 사용자 지정 행 집합을 정의한다. 
SELECT EMPLOYEES.*
    , (SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES) AS 전체급여 -- *,SALARY 하면 에러남. 결과를 반환할수잇는방법능ㄴ *앞에 테이블명칭을 붙여줘야함.
    --이렇게 셀렉트문으로 계산한 데이터를 그대로 가져오게되면 로우데이터를 건들지않고 구간별비중이나 특별한 계산을 해서 파생컬럼을 만들수있는 장점이 존재. 
    , (SELECT MAX(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최대급여
    , (SELECT MIN(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최소급여
    , (SELECT COUNT(SALARY) AS 전체급여 FROM EMPLOYEES) AS 직원수
    , (SELECT ROUND(AVG(SALARY),2) AS 전체급여 FROM EMPLOYEES) AS 평균급여  --ROUND를 AVG바로앞에서 제어할수도있고 최상단에서 제어할수도있음.
    ,  SUM(SALARY) OVER () AS 전체급여1 --전체직원의 누적함수를 구할수있음.
    ,  MAX(SALARY) OVER () AS 최대급여1 
    ,  MIN(SALARY) OVER () AS 최소급여1
    ,  COUNT(SALARY) OVER () AS 직원수
    ,  ROUND(AVG(SALARY) OVER (),2) AS 평균급여1    
FROM EMPLOYEES;

--부서별 금액합계를 구했을때 JOIN또는 서브쿼리를 통해서 출력할수도 있지만 윈도우함수로도 출력할수있음. 
SELECT DEPARTMENT_ID, SUM(SALARY) AS 부서별금액
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
;

--기준을 주게되면 기준별 집계의 결과를 확인할수도 있음. PARTITIONN BY 사용. 
--PARTITION BY 정리 : 특정 열을 기준으로 데이터를 나눈다. PARTITION_BY를 지정하지 않으면 쿼리 결과 집합의 모든 행이 단일 그룹으로 취급된다-모두 같은 값을 표시. 
--SUM/MAX/MIN/COUNT/AVG의 파티션을 지정해주면 그 파티션 그룹내의 합산/최대/최소/갯수/평균 값들을 표시해줄수 있다는것. 
--이때 단점은 같은 동선에 GROUPBY가 쓰일수 없다는 점. 
--윈도우함수를 사용해서 상위쿼리로 올라가서 누적집계를 했다면 그룹핑을하거나 다른 작업에 필요한 순간에 상위쿼리로 올라가서 작업을 해야한다는거. 
--누적집계를 구할때는 셀렉트절 서브쿼리보다 윈도우함수를 쓰는게 유연하고 쿼리도 깔끔함. 
--누적집계만 구할수있는건 아니고, 다른테이블의 컬럼과 정보를 조회해서 가져올수도 있음!! 일종의 SELECT 절에서 조인하는거라고 생각할수있음. 
SELECT EMPLOYEES.*
    , (SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES) AS 전체급여 -- *,SALARY 하면 에러남. 결과를 반환할수잇는방법능ㄴ *앞에 테이블명칭을 붙여줘야함.
    --이렇게 셀렉트문으로 계산한 데이터를 그대로 가져오게되면 로우데이터를 건들지않고 구간별비중이나 특별한 계산을 해서 파생컬럼을 만들수있는 장점이 존재. 
    , (SELECT MAX(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최대급여
    , (SELECT MIN(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최소급여
    , (SELECT COUNT(SALARY) AS 전체급여 FROM EMPLOYEES) AS 직원수
    , (SELECT ROUND(AVG(SALARY),2) AS 전체급여 FROM EMPLOYEES) AS 평균급여  --ROUND를 AVG바로앞에서 제어할수도있고 최상단에서 제어할수도있음.
    ,  SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS 전체급여1
    ,  MAX(SALARY) OVER () AS 최대급여1 
    ,  MIN(SALARY) OVER () AS 최소급여1
    ,  COUNT(SALARY) OVER () AS 직원수
    ,  ROUND(AVG(SALARY) OVER (),2) AS 평균급여1    
FROM EMPLOYEES;


--누적집계만 구할수있는건 아니고, 다른테이블의 컬럼과 정보를 조회해서 가져올수도 있음!! 일종의 SELECT 절에서 조인하는거라고 생각할수있음. 
--
SELECT (SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES) AS 전체급여 -- *,SALARY 하면 에러남. 결과를 반환할수잇는방법능ㄴ *앞에 테이블명칭을 붙여줘야함.
    --이렇게 셀렉트문으로 계산한 데이터를 그대로 가져오게되면 로우데이터를 건들지않고 구간별비중이나 특별한 계산을 해서 파생컬럼을 만들수있는 장점이 존재. 
    , (SELECT MAX(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최대급여
    , (SELECT MIN(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최소급여
    , (SELECT COUNT(SALARY) AS 전체급여 FROM EMPLOYEES) AS 직원수
    , (SELECT ROUND(AVG(SALARY),2) AS 전체급여 FROM EMPLOYEES) AS 평균급여  --ROUND를 AVG바로앞에서 제어할수도있고 최상단에서 제어할수도있음.
    ,  SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS 전체급여1
    ,  MAX(SALARY) OVER () AS 최대급여1 
    ,  MIN(SALARY) OVER () AS 최소급여1
    ,  COUNT(SALARY) OVER () AS 직원수
    ,  ROUND(AVG(SALARY) OVER (),2) AS 평균급여1   
    , (SELECT DEPARTMENT_NAME FROM DEPARTMENTS B WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) 부서명
    --다른테이블과 메인테이블의 셀렉트절을 통해서 조인을통한 가벽적인 정보도 출력이 가능하다. 
FROM EMPLOYEES A;

--21강에서 만들었던 sql문을 테이블ㄹ ㅗ만ㄷ르어줄거임. 
create table cctv설치현황 as
--묶은 지역을 기준으로(group by) 전체설치되어있는 지역별 카메라설치수와  + 각 목적구분별로 설치되어있는 카메라의 수를 구할거임. sum과 사용자정의함수 
SELECT trim(replace(replace(replace(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구')) as 지역 --중복제거,정제,trim사용하여 공백제거
    ,sum(카메라대수) as 전체카메라설치수
    ,sum(CASE WHEN 설치목적구분 = '교통단속' THEN 카메라대수 ELSE 0 END) AS 교통단속
    ,sum(CASE WHEN 설치목적구분 = '교통정보수집' THEN 카메라대수 ELSE 0 END) AS 교통정보수집
    ,sum(CASE WHEN 설치목적구분 = '기타' THEN 카메라대수 ELSE 0 END) AS 기타
    ,sum(CASE WHEN 설치목적구분 = '생활방범' THEN 카메라대수 ELSE 0 END) AS 생활방범
    ,sum(CASE WHEN 설치목적구분 = '시설물관리' THEN 카메라대수 ELSE 0 END) AS 시설물관리
    ,sum(CASE WHEN 설치목적구분 = '쓰레기단속' THEN 카메라대수 ELSE 0 END) AS 쓰레기단속
    ,sum(CASE WHEN 설치목적구분 = '어린이보호' THEN 카메라대수 ELSE 0 END) AS 어린이보호
    ,sum(CASE WHEN 설치목적구분 = '재난재해' THEN 카메라대수 ELSE 0 END) AS 재난재해
    ,sum(CASE WHEN 설치목적구분 = '차량방범' THEN 카메라대수 ELSE 0 END) AS 차량방범
    
FROM cctv
group by  trim(replace(replace(replace(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구'))
;



/* 비상벨 설치현황*/
--지역별 비상벨대수
create table 비상벨설치현황 as
SELECT CASE WHEN INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구') > 1 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 가져오기*/
            ELSE TRIM(REPLACE(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'청','')) END AS 지역/*구로 시작하는 경우는 정제*/
            ,COUNT(*) AS 설치대수
            ,SUM(CASE WHEN 설치장소유형 = '가로변' THEN 1 ELSE 0 END) AS 설치위치_가로변
            ,SUM(CASE WHEN 설치장소유형 = '기타' THEN 1 ELSE 0 END) AS 설치위치_기타
            ,SUM(CASE WHEN 설치장소유형 = '화장실' THEN 1 ELSE 0 END) AS 설치위치_화장실
            ,SUM(CASE WHEN 설치장소유형 = '주차장' THEN 1 ELSE 0 END) AS 설치위치_주차장
            ,SUM(CASE WHEN 설치장소유형 = '건물' THEN 1 ELSE 0 END) AS 설치위치_건물
            ,SUM(CASE WHEN 설치장소유형 = '공원' THEN 1 ELSE 0 END) AS 설치위치_공원
FROM 비상벨
GROUP BY CASE WHEN INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구') > 1 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 자르기*/
            ELSE TRIM(REPLACE(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'청','')) END /*구로 시작하는 경우는 정제*/
            ;


select *
from 비상벨설치현황;

select *
from cctv설치현황;

--두개 조인해서 지역벌로 어디가 가장많이 설치되어 있는지 확인할것임. 각 지역별 카메라설치율,비상벨설치율 구하기.
select A.지역
    , A.전체카메라설치수
    , B.설치대수
    , ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 전체카메라설치율
    , ROUND(B.설치대수 / (A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 비상벨설치율
from cctv설치현황 A full outer join 비상벨설치현황 B
 on A.지역 = B.지역
 ;
 
 --위에꺼 카메라설치비율 가지고 구간나눠서 해당되는 평균설치 카메라수 구할거임. 
select CASE WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 50 THEN '설치미비'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 80 THEN '설치보통'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 95 THEN '설치우수'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) > 95 THEN '완전설치'
    ELSE 'CHK' END AS 설치구분
    ,TO_CHAR(SUM(A.전체카메라설치수+B.설치대수),'999,999') AS 전체설치건수
    ,TO_CHAR(ROUND(AVG(A.전체카메라설치수+B.설치대수)),'999,999') AS 평균건수
    ,COUNT(*) AS 지역수
    ,TO_CHAR(SUM(A.전체카메라설치수),'999,999') AS 카메라수
    ,TO_CHAR(SUM(B.설치대수),'999,999') AS 비상벨수
    ,ROUND(SUM(A.전체카메라설치수) / SUM(A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 카메라설치비율
    ,ROUND(SUM(B.설치대수) / SUM(A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 비상벨설치비율
from cctv설치현황 A INNER join 비상벨설치현황 B
 on A.지역 = B.지역
 GROUP BY CASE WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 50 THEN '설치미비'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 80 THEN '설치보통'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 95 THEN '설치우수'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) > 95 THEN '완전설치'
    ELSE 'CHK' END
ORDER BY 카메라설치비율 DESC
 ;

이해될듯말듯한 상태라서 따로 찾아보면서 추가적으로 이해해두자

/*기타 조인의 종류
1. natural join
2. cross join
3. 셀프조인
*/

/* natural join - join 조건 주지않고 eq조인하는것*/

select *
from employees natural join departments
;

select *
from employees
;
select *
from departments
;

--employees와 departments에서 중복되는값은 daprtment_id와 manager_id 두개임. 이두개 컬럼이 키로 활용
--기존에넌 innerjoin 해서 전체출력하게되면 해당되는 각각의테이블에 중복되는 컬럼또한 _1붙어서 출력이 되었으나,
--natural join에서는 중복되는 컬럼도 제거해서 한가지만 보여줌, 중복되는 컬럼이 복합키로 활용되어 조인이 되었다고 생각하면됨. 


-- inner join으로 출력했을때 > _1 제외하고는 natural join과 같은 값이 나온다. 
select *
from employees inner join departments
on employees.department_id = departments.department_id
and employees.manager_id = departments.manager_id;

--cross join도 작성할때는 natural join처럼 키를 연결해주지는 않는다. 
select *
from employees cross join departments
;

select count(*)
from employees cross join departments
;

select 107*27 from dual;
--이렇게 데이터가 부풀려지는 이유는 조인에대한 키값을 알지못하기 때문에. 
--cross join은 join이라 할순없지만 시스템 테스트하거나 테스트예제 생성할때 사용되는편임.


--오라클방식 join 테이블선언해주고 조인키 걸어주지않으면 모든 경우의 수에대한 행의수가 생성되게되니 오라클방식 join사용할경우 반드시 키값 걸어주어야 한다. 
select count(*)
from employees, departments
;

--self join 자기자신과 조인할때
--employee 테이블을 두번 join할거임 , 하나는 관리자id로, 하나는 사원정보 기준으로 join할거임.
--첫번째임플로이. 두번째임플로이 테이블 각각 이름 지정하고/ 임플로이테이블의 피관리인과 관리인을 결합하여 내가속한 관리자가 누구인지 출력
select emp.employee_id as 사원ID
        , emp.last_name||' '||emp.first_name 사원이름
        , emp.job_id 사원직업
        , man.last_name||' '||man.first_name 관리자이름
        , man.job_id 관리자직업
        , man.employee_id 관리자번호
from employees emp INNER JOIN employees man
on emp.manager_id = man.employee_id
;
--최고관리자는 관리자가 없기때문에 조인할값(관리자번호)가 NULL이므로 출력에서 빠짐.
--mployee.manager_id기준으로 employee_id가 있는 것을 출력하려고했기때문.
--ㅡMANAGER 피관리인/ EMPLOYEE 관리인 결합, 내가속한 관리자가 누구인지 출력. 

--그래서 1건을더 출력하려면 left outer join으로 바꿔 주어야 한다. >그러면 최고관리자도 출력됨
select emp.employee_id as 사원ID
        , emp.last_name||' '||emp.first_name 사원이름
        , emp.job_id 사원직업
        , man.last_name||' '||man.first_name 관리자이름
        , man.job_id 관리자직업
        , man.employee_id 관리자번호
from employees emp left outer JOIN employees man
on emp.manager_id = man.employee_id
;
--내가 누구에게 속해있는지 자기자신 테이블을 참조하여 JOIN하여 결과출력이 가능함.

--3개이상 조인해서 결과출력 / 사원/직업/부서테이블 조인하여 직업명과 부서타이틀을 가져오겠다. 
SELECT a.last_name||' '||a.first_name as 사원이름
    , to_char(a.hire_date, 'YYYY-MM-DD') AS 입사일
    , b.department_name as 부서명
    , a.salary as 급여
FROM  employees a left outer join departments b
 on a.department_id = b.department_id
 ;
--사원테이블 부서테이블 조인해서 부서명 먼저 출력. 한번더 조인하려면 from join절 on뒤에 한번더 left outer join선언
SELECT a.last_name||' '||a.first_name as 사원이름
    , to_char(a.hire_date, 'YYYY-MM-DD') AS 입사일
    , b.department_name as 부서명
    , c.job_title as 직업명
    , a.salary as 급여
FROM  employees a left outer join departments b
 on a.department_id = b.department_id
 left outer join jobs c    ---직업정보가 들어있는 테이블을 한번더 조인한다. 
 on a.job_id = c.job_id
 ;
 --vlookup을 a시트와 b시트 결과를 만들어놓고 한번더 추가된 시크에서 c 시트에있는 또다른 정보를 가져온다고 생각하는것과 같다. 
 
 
 --oracle표준방식의 경우 
 SELECT a.last_name||' '||a.first_name as 사원이름
    , to_char(a.hire_date, 'YYYY-MM-DD') AS 입사일
    , b.department_name as 부서명
    , c.job_title as 직업명
    , a.salary as 급여
FROM  employees a , departments b, jobs c
where a.department_id = b.department_id
    and a.job_id = c.job_id
 ;

--outer join 배우는날 /엑셀에서 vlookup 사용해서 값 가져오는거랑 동일
--내가 찾으려고 하는 값이 존재하지않으면 보여주지않는다. 왼쪽 정보를 기준으로 정보를 가져온다. 
--값이 존재하지 않더라도 행의수는 살아잇음.  기준 테이블이 어디에 있는지에 따라 LEFT/RIGHTJOIN으로 나뉜다. 

--<ANSI JOIN 방식>
/* SELECT 필드명
FROM 테이블명1 LEFT/RIGHT OUTER JOIN 테이블명2
    ON 테이블명1.필드명1 = 테이블명2.필드명2
WHERE 조건식
*/

SELECT *
 FROM EMPLOYEES left outer join departments
 on employees.department_id = departments.department_id
 ;
SELECT *
 FROM EMPLOYEES right outer join departments
 on employees.department_id = departments.department_id
 ;
SELECT *
 FROM departments left outer join employees
 on employees.department_id = departments.department_id
 ;
--outer join은 leftjoin 하나만 있는것으로 알고 기준테이블을 뭘로할지만 명확하게 알고있다면 한가지로만으로도 사용할수 있겠다. 

--<ORACLE JOIN 방식>  >> join사인, 표시를 해줘야함. 
SELECT *
 FROM employees, departments
 WHERE employees.department_id = departments.department_id(+)  --기준테이블 반대쪽에 조인사인을 주면, 반대테이블이 기준이 되고, 이 기준으로 left조인 된다고 생각.
 --이경우 employees.department_id 기준으로 departments.department_id를 가져와서 조인해준거임
 ;
 
 
 SELECT *
 FROM employees, departments
 WHERE employees.department_id(+) = departments.department_id
 --이경우 departments를 기준테이블로 하고싶어서 employeedp (+)조인 사인을 줌.
 ;
 
 --각각 테이블에 존재하지않는 값을 모두 출력하고 싶을때! > 양쪽에 조인사인(+)을 주는게 아니다! 
 --이때는 ANSI표준 방식인 FULL OUTER JOIN을 사용하면 된다.
 SELECT *
 FROM employees FULL OUTER JOIN departments
 ON employees.department_id = departments.department_id
 --이경우 departments를 기준테이블로 하고싶어서 employeedp (+)조인 사인을 줌.
 ;
 
 

inner join 사용할때 방식 혼용하지않도록 주의하기

/*SELECT 필드명
FROM 테이블명1 INNER JOIN 테이블명2
    ON 테이블명1.필드명1 = 테이블명2.필드명2
WHERE 조건식
*/

SELECT COUNT(*)
FROM employees; --107건

SELECT COUNT(*)
FROM departments;  --27건

--부서id 기준으로 inner join
FROM INNER JOIN 테이블명2         --(1)테이블명+ 2()기준테이블의 참조테이블이 될 테이블
    ON 테이블명1.필드명1 = 테이블명2.필드명2   --join방식에 대한 키값 연결
WHERE 조건식
;

select*
 from employees inner join departments
    on employees.department_id = departments.department_id

    ;--이때 전체 행의 갯수가 107>106건으로 줄었음>employees.department_id에 null값이 한건 존재하여 join할수 없었음. 교집합에대한 정보만 표시.

--<ANSI표준 JOIN방식>
select a.employee_id
    , a.salary
    , a.department_id
    , b.department_name
    from employees a inner join departments b 
    on a.department_id = b.department_id
    where a.department_id = 30
    ;
----<ORACLE JOIN방식> join- inner join을 ,로 대체(JOIN할 테이블을 나열)하고 on대신에 where 조건을 걸어주면
select a.employee_id
    , a.salary
    , a.department_id
    , b.department_name
    from employees a, departments b 
 where a.department_id = b.department_id
    and a.department_id = 30

 ;

데이터 적재  cctv, 비상벨 

@저장파일경로/파일명.sql        +f5 전체스크립트 실행

@C:\study_db/cctv.sql;
@C:\study_db/비상벨.sql;
--f5누르면 전체실행

--묶은 지역을 기준으로(group by) 전체설치되어있는 지역별 카메라설치수와  + 각 목적구분별로 설치되어있는 카메라의 수를 구할거임. sum과 사용자정의함수 
SELECT distinct
    trim(replace(replace(replace(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구')) as 지역 --중복제거,정제,trim사용하여 공백제거
    ,sum(카메라대수) as 전체카메라설치수
    ,sum(CASE WHEN 설치목적구분 = '교통단속' THEN 카메라대수 ELSE 0 END) AS 교통단속
    ,sum(CASE WHEN 설치목적구분 = '교통정보수집' THEN 카메라대수 ELSE 0 END) AS 교통정보수집
    ,sum(CASE WHEN 설치목적구분 = '기타' THEN 카메라대수 ELSE 0 END) AS 기타
    ,sum(CASE WHEN 설치목적구분 = '생활방범' THEN 카메라대수 ELSE 0 END) AS 생활방범
    ,sum(CASE WHEN 설치목적구분 = '시설물관리' THEN 카메라대수 ELSE 0 END) AS 시설물관리
    ,sum(CASE WHEN 설치목적구분 = '쓰레기단속' THEN 카메라대수 ELSE 0 END) AS 쓰레기단속
    ,sum(CASE WHEN 설치목적구분 = '어린이보호' THEN 카메라대수 ELSE 0 END) AS 어린이보호
    ,sum(CASE WHEN 설치목적구분 = '재난재해' THEN 카메라대수 ELSE 0 END) AS 재난재해
    ,sum(CASE WHEN 설치목적구분 = '차량방범' THEN 카메라대수 ELSE 0 END) AS 차량방범
    
FROM cctv
group by  trim(replace(replace(replace(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구'))
;

/*
설치목적구분 어떤거있는지 확인
select distinct설치목적구분
from cctv;

교통단속
교통정보수집
기타
다목적
생활방범
시설물관리
쓰레기단속
어린이보호
재난재해
차량방범
*/
/*
SELECT DISTINCT 설치장소유형
FROM 비상벨
GROUP BY 설치장소유형;
*/

--지역별 비상벨대수
SELECT CASE WHEN INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구') > 1 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 가져오기*/
            ELSE TRIM(REPLACE(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'청','')) END AS 지역/*구로 시작하는 경우는 정제*/
            ,COUNT(*) AS 설치대수
            ,SUM(CASE WHEN 설치장소유형 = '가로변' THEN 1 ELSE 0 END) AS 설치위치_가로변
            ,SUM(CASE WHEN 설치장소유형 = '기타' THEN 1 ELSE 0 END) AS 설치위치_기타
            ,SUM(CASE WHEN 설치장소유형 = '화장실' THEN 1 ELSE 0 END) AS 설치위치_화장실
            ,SUM(CASE WHEN 설치장소유형 = '주차장' THEN 1 ELSE 0 END) AS 설치위치_주차장
            ,SUM(CASE WHEN 설치장소유형 = '건물' THEN 1 ELSE 0 END) AS 설치위치_건물
            ,SUM(CASE WHEN 설치장소유형 = '공원' THEN 1 ELSE 0 END) AS 설치위치_공원
FROM 비상벨
GROUP BY CASE WHEN INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구') > 1 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 자르기*/
            ELSE TRIM(REPLACE(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'청','')) END /*구로 시작하는 경우는 정제*/
            ;

--복습꼭하기

폼롤러 위에서 플랭크자세하고 한쪽씩 무릎차기 15번씩 4세트 뒷발은 뻗어있어야 하고 찰때 앞으로 살짝 나와야함

렛풀다운 올라갈때 어깨고정했다가 광배근늘려주고 힘안놓친상태에서 당기기 시작자세  허벅지안쪽긴장+ 고관절접고 시작하기 + 올라갈때 엉덩이 붙이는 힘!! + 견갑골 움직임 신경쓰면서 할것

원암덤벨프레스? 했음 드는팔 반대쪽 복근에 균형 힘주고 12번씩인가 4세트한거같당 직각보다 더 내려와야 어깨를 쓰는거고 덜접으면 승모근을 잘못쓰게 된다

목위쪽근육 안써서 목 젖히는 가동범위안나와서 샘이도와줌 목 앞쪽가운데쪽 근육을 써줘야 한다

누워서 목밑에 손대고 아주천천히올라왓다가 아주천천히 내려가기 목 가운데근육에 힘들어가야하고 빨리올라와서 목 옆쪽 근육에 힘들어가면 틀린거임! 이거 집에서 연습하고 오라고햇음
누워서 렛풀다운하는 자세로 팔놓고 견갑골 긴장주고 목 끝까지 젖히고 돌아오기할때 목앞쪽 자극 왕왕옴

원레그데드리프트 자세 발바닥안정화운동 플레이트 두개 두고 위에서 했는데 중심1도안잡히고 개난장판이었다 아직멀었다 나는 ㅠㅠ내려다가 발로 밀어서 올라온다는 느낌으로

--복함함수 사용하여 데이터 다뤄보기
--

/* SQL 작성순서 및 실행순서
        1  SELECT   5
        2  FROM     1
        3  WHERE    2
        4  GROUP BY 3
        5  HAVING   4
        6  ORDER BY 6
    */
    --lpad 활용하여 급여가 가장적은사람부터 많은사람까지 출력. 시각화 구현
SELECT last_name||' '||first_name as emp_name
    , to_char(hire_date,'yyyymmdd') as hire_dt
    , salary
    , department_id
    , lpad('■',round(salary/1000,0),'■') as sal_vi
FROM employees
order BY salary desc;

--연도별 입사자 수와 평균 급여 출력하기
SELECT substr(to_char(hire_date,'yyyymmdd'), 1, 4) as 년도
    , count(*) as 입사자수
    , round(avg(salary),2) as 평균연봉
        , lpad('■',count(*),'■') as 입사자_vi
from employees
group by substr(to_char(hire_date,'yyyymmdd'),1,4)
order by 입사자_vi
;

--직원들의 이름, 급여, 부서번호, 직업정보, 근속년수
SELECT last_name||' '||first_name as 이름
    , salary as 급여
    , department_id as 부서번호
    , job_id as 직업정보
    , round(months_between(sysdate, hire_date)/12) as 근속년수
from employees
where round(months_between(sysdate, hire_date)/12) >= 10
;


-- 직업정보 기준으로 직원수들이 몇명이고 평균급여는 얼마이고 근속년수가 가장적은대상자의 근속년수를 출력하는 sql
--jobid기준 직원수/평균급여/최소근속/최대근속년수- 직원수가 가장많은 직무별로 정렬
--이때 직원수가 1명인건 출력하지 않도록 하기위해서는,where절이 아닌 having 을 사용해야 함. 직원수가 한명을 초과하는것들. 
--원본데이터가 아닌 그룹핑된 데이터(count)기준으로 조건을 거는거기떄문에
SELECT job_id
    , count(*) as 직원수
    , round(avg(salary),2) as 평균급여
    , min(round(months_between(SYSDATE, hire_date)/12)) as 최소근속년수
    , max(round(months_between(sysdate, hire_date)/12)) as 최대근속년수
from employees
where round(months_between(sysdate, hire_date)/12) >= 10
group by job_id
having count(*) > 1
order by 직원수 desc;
;

★where 과 group by의 차이점 숙지. 

select 5 필수   마지막에수행 일련의 작업이 끝나고 난후 사용자가 확인할수있는 결과 출력물에대한 결과를 어떻게 정의할지에 대해 명령을 내려주는 부분. 
from      1 필수   
where     2 전체데이터에서 일부데이터를 선택할경우 사용하는것 / 전체로작업할지, 부분으로작업할지 체크하는 부분. 버릴 데이터가 있는지 없는지체크.
group by  3 where로 줄어든 결과를 가지고 일련의 집계, 그룹핑을 할지말지 체크하는 부분 
having   4 그룹핑된 결과에 대해 필터링 할지말지 추가적으로 사용하는 부분.
order by  6 select 후 특정부분 기준으로 정렬을 할지말지 결정한다. 

--소계를 낼수있는 rollup기능 : 합계도출- groupby에서 사용할수있음! / null값 처리하고 rollup 기능 사용
--주의할점 select절에서 옵션이 들어가고 함수가 적용되어서 결과가 바뀌었다는건, groupby에도 똑같이 들어가야한다는것!!
--rollup 사용한후에 셀렉트절에 가서 한번더 nvl사용해서 null값을 총합계로 바꿔야함 
--rollup이 실행되고 나면 새로운 값이 생성이 되는거기때문에 이결과컬럼에대한 제어는 select에서 할수있다. 

---law데이터 기준으로 필터링할떄는 where / 집계된 결과에서 필터링해서 표시하려면 having by 
SELECT department_id, count(*) as 건수
FROM employees
GROUP BY department_id
having count(*) > 5  -- 집계된 결과에서 필터링 해줄거기때문에 having절에는 집계 결과+조건이 나와야한다
ORDER BY 건수 desc
;

/* select 5 필수   마지막에수행 일련의 작업이 끝나고 난후 사용자가 확인할수있는 결과 출력물에대한 결과를 어떻게 정의할지에 대해 명령을 내려주는 부분. 
from      1 필수   
where     2 전체데이터에서 일부데이터를 선택할경우 사용하는것 / 전체로작업할지, 부분으로작업할지 체크하는 부분. 버릴 데이터가 있는지 없는지체크.
group by  3 where로 줄어든 결과를 가지고 일련의 집계, 그룹핑을 할지말지 체크하는 부분 
hav'ing   4 그룹핑된 결과에 대해 필터링 할지말지 추가적으로 사용하는 부분.
order by  6 select 후 특정부분 기준으로 정렬을 할지말지 결정한다. 

*/

SELECT *
FROM employees
WHERE department_id in(40, 50, 60); --Where절은 원본데이터를 바로 가지고 온후에 행을 일부만 선택할지 여부 선택.

SELECT job_id
    , count(*) as 직원수    --- jobid 별 직원수
    , round(avg(salary), 2) as 평균급여 ----- 그룹핑된 jobid별 급여의 평균
FROM employees
WHERE department_id in(40, 50, 60) 
group by job_id
having round(avg(salary) ,2) > 5000   ---직원들의 평균급여가 5000이상인 사람들만 표시
order by 3 desc
;

--소계를 낼수있는 rollup기능 합계도출- groupby에서 사용할수있음! / null값 처리하고 rollup 기능 사용
--주의할점 select절에서 옵션이 들어가고 함수가 적용되어서 결과가 바뀌었다는건, groupby에도 똑같이 들어가야한다는것!!
--rollup 사용한후에 셀렉트절에 가서 한번더 nvl사용해서 null값을 총합계로 바꿔야함 
--rollup이 실행되고 나면 새로운 값이 생성이 되는거기때문에 이결과컬럼에대한 제어는 select에서 할수있다. 
SELECT nvl(nvl(to_char(department_id),'대기'),'총합계') as 부서
       , count(*) as 건수   ---null값은 대기발령이라는 값을 넣어준다. nvl과 to_char활용
FROM employees
group by rollup(nvl(to_char(department_id),'대기'))   ---맨위에 null로 총합계건수가 표시된다. 
order by 건수 desc
;

/*GROUP BY 활용
--사원테이블의 부서ID 기준 대상자수출력
 SELECT department_id, count(*) as 건수
  from employees         
 GROUP BY department_id   
 ;
 from으로 테이블 가져오고/ group by로 행에대한 기준을 잡고/ 이후에 select에서 집계함수의 출력결과를 출력.
 내가 원하는 값을 별도의 파생컬럼으로 만들어서 집계해볼수 있음.
 */
 
 --CASE WHEN 사용자정의 함수를 사용하면 정말 다양한 조건과 컬럼들을 만들어서 다양한 관점으로 정보를 확인할수 있다! ! 
 SELECT CASE WHEN SALARY <= 7000 THEN '저소득자'
             WHEN SALARY <=15000 THEN '중위소득자'
             ELSE '고소득자' END AS 소득그룹
             ,ROUND(AVG(SALARY),2) AS 평균급여
FROM EMPLOYEES
GROUP BY CASE WHEN SALARY <= 7000 THEN '저소득자'
             WHEN SALARY <=15000 THEN '중위소득자'
             ELSE '고소득자' END ---소득그룹에 대한 정의가 그룹바이에 나와줘야 함.(컬럼명 X)
             ;

--직원들의 이름가지고 최대최소함수 사용
SELECT first_name||' ' ||last_name as name
from employees
ORDER BY name; 
-- 오름차순 정렬(디폴트 or asc/ 내림차순은 desc)
--name 자리에 해당하는 연산의 결과로 직접 넣어도되고 , 컬럼의 순서를 넣어줘도 됨 (name/ first_name||' ' ||last_name/ 1 모두 같은 결과)

--이름 기준으로 최대최소값
SELECT min(first_name||' '||last_name) as col1 --- 오름차순의 맨처음이름
      , max(first_name||' '||last_name) as col2     -----내림차순의 첫번째이름
from employees;

----부서기준으로  이름 최대최소값
SELECT department_id
      , min(first_name||' '||last_name) as col1 --- 오름차순의 맨처음이름
      , max(first_name||' '||last_name) as col2     -----내림차순의 첫번째이름
from employees
GROUP BY department_id;
 
--연습문제

--직원의 근속연수 구간별 직원수, 평균급여, 근속연수 구간별 최대 근속년수를 출력해보자
--2021년 1월 1일 기준으로 사원들의 근속연수를 구해보자
-- 근속연수구간(5년이하, 10년이하, 15년이핟, 20년이하, 20년초과) -- 18강 7분부터
 
--사용자 지정함수로 구간을 나타내고, 그룹바이로 구간을 묶고 그 그룹에대한 집계함수에대한 출력결과를 SELECT를 출력. 
SELECT CASE WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 5 THEN '5년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 10 THEN '10년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 15 THEN '15년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 20 THEN '20년이하' ELSE '25년초과' END AS 근속연수구간
        , COUNT(*) AS 직원수
        , ROUND(AVG(SALARY),2) AS 평균급여
        , MAX(ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0)) AS 구간별최대근속년수
FROM employees
GROUP BY CASE WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 5 THEN '5년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 10 THEN '10년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 15 THEN '15년이하'
            WHEN ROUND(MONTHS_BETWEEN(TO_DATE('20210101','YYYYMMDD'),HIRE_DATE)/12,0) <= 20 THEN '20년이하' ELSE '25년초과' END ;