＼(^o^)／

Q1. How did you first become interested in playing badminton? Who taught you how to play? Also, tell me about the most unforgettable badminton match.

Oh, badmintion is one of my favorite activities. When I was a child, my father taught my brother and me how to play badminton step by step. My younger brother was good at badminton. So I was in a hurry to learn quickly to get to his level. Sometimes I fell down and hurt my knee when I hit the shuttlecock but I never gave up. Now I've taken my skills to a certain level and I'm the best among my friends. / last olympics, I saw a badminton match on TV to cheer for my country. The game was in team of two. In the beginning, I didn’t know who played for Korea but soon, one player was catching my eyes since he was very tall and athletic. He showed strong serve as well as perfect defense. It turned out that he was 이용대 who was the Korean national badminton player. Thanks to his amazing performance, Finally my country won the game. I became his big fan since that day. It was really interesting match. (nice)

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

 ;

Q1. Let's talk about a bank in your country. Describe a bank for me. What do you in a bank? Also, tell the difference briefly between the bank in the past and today's bank.

In the center of seoul, you can see bank branches almost on every block. Their business hours are from 9 to 4, monday through friday. As soon as you enter, you can find many ATMs where people can use simple services, like withdrawing or depositing money. I go to banks only to exchange some money before I go traveling or withdraw some cash at ATMs before shopping. / These days, there are still a lot of the elderly who are not good at handling their smartphone banking. I think there should be some kind of teaching places for them to learn how to use phone banking services. You know, bank systems have made remarkable progress over the decade. It is possoble to do almost everything with smartphones. Even pulling out a ticket for the line is possible with just a few touches of the screen on your phone as well. But on the other hand, the number of banks have been closing. and Moreover many bank employees have lost their jobs because of these automatic systems. I think digital transformation has changing our whole lives more comfortably and conveniently but I think someday all employees can be replaced with machines, which is a big problem. (nice)

데이터 적재  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
;

Q1. Please tell me about a piece of furniture you bought recently. What kind of furniture did you get? Tell me about it in detail. Also, tell me about the furniture you have in your room and describe your room. 

The desk in my room was getting old and there was not enough space for my computer. It could barely hold the computer and keyboard. it was very uncomfortable. I needed the new wide one. So, Last month, I went to Ikea with my boyfriend. Indeed, There were many kinds of desks. In the corner of the store, I found a nice white desk. Despite the fact that  it’s self-assembly furniture, I bought it without any hesitation. It was big enough to hold many things. On top of that, I thought it would really go well with other furniture in my room. As it took up a little bit more than before, I rearranged the new desk in front of the window and my bed behind it. Now, My room has very cozy, neat and warm atmonsphere with white colored furniture. I’m very satisfied with it and I'm so glad that I can study and work at a plesant environment. (nice)

/*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 ;
 

--그룹에대한이해, 그룹바이 사용법에대해 알아보기


/*
SELECT  --열 선택 
 FROM  -- 데이터 꺼내오는부분
 WHERE  -- 필터링, 행제어
 GROUP BY
 HAVING 
 ORDER BY
 
 --작성 순서/ 컴퓨터가 명령 수행하는 순서
 실질적으로 컴퓨터가 수행하는 절차는 FROM부터 이루어지고, 마지막에 내가 표시할 컬럼을 선택해서 정렬SELECT가 가장 마지막에 실행.
 
 GROUP BY> 피벗테이블 수행과 동일. 이 기능의 부분이 그룹바이의 역할임.
 
 
 
 */
 
 SELECT department_id, count(*) AS 건수
  FROM employees
  group by department_id   -- 이 기준을 잡는다면 SELECT에도 같이 보여주는 습관을 들이면 더 좋음. 어떤 건에대한 건수를 세는건지 알수없으니까.
  ;
  
SELECT 시군구명, SUM(전체카메라수) AS 카메라수
 FROM 지역별설치현황
 GROUP BY 시군구명;
 
SELECT 시군구명, COUNT(*) AS 건수
 FROM 지역별설치현황  --파일을 열고
 GROUP BY 시군구명;   --기준을 잡는다 / 그룹바이가 없으면 기준이 없는데 SUM이나 COUNT 집계를 하려고 하는건 불가능하므로 에러가 난다.
 
 --GROUP BY가 없으면 전체에대한 건수가 나옴.
 SELECT COUNT(*) AS 건수
 FROM 지역별설치현황
;
  

 SELECT 시군구명, 동리명, COUNT(*) AS 건수, SUM(전체카메라수) AS 카메라수
 FROM 지역별설치현황
 GROUP BY 시군구명, 동리명;