힘내보자

https://github.com/neverGiveUpppppp/Comento_SQL_Oracle

SQL 조회하기 위한 절차

1. 주어진 조건을 문장화
2. 문장에서 조건 추리기
3. 조건을 쿼리문으로 바꿀 수 있는 부분부분 바꾸기

1.조회 할 내용 텍스트화

--3-2) 현재 날짜 기준 재직중인 "이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.
--힌트: substr, count(*)를 사용

2.문장에서 쿼리 조건 리스트화

-- 재직중, 현재 날짜 기준, 이씨성, 자녀수

3.각 조건마다 예상되는 쿼리 짜기

-- 재직중 : RETIRE 99/12/31 조건
-- 현재 날짜 기준
-- 이씨성 : LIKE '이%'
-- 자녀수 : COUNT()

3-2) 현재 날짜 기준 재직중인 “이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.

힌트: substr , count(*)를 사용

-- 재직중 : RETIRE 99/12/31 조건
-- 현재 날짜 기준
-- 이씨성 : LIKE '이%'
-- 자녀수

-- COUNT + LIKE
SELECT E.EMP_NM 직원명, COUNT(*)
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;    

-- COUNT(SUBSTR)+ LIKE
SELECT E.EMP_NM, COUNT(SUBSTR(E.EMP_NM,2,4)) AS "자녀명수"
FROM EMP_C E
    JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;    

-- COUNT + SUBSTR
SELECT E.EMP_NM, COUNT(*) AS "자녀명수"
FROM EMP_C E
    JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND SUBSTR(E.EMP_NM, 1,1) = '이'
GROUP BY E.EMP_NM
;

3-3) 자녀 명수가 2명 이상인 직원의 리스트를 구하세요.  (직원번호 / 성명 / 자녀명수 )

힌트 having

-- 조회 컬럼 : 직원번호/성명/자녀명수
-- 자녀수 2명 이상 : REL_TYPE_CD A=27이 자녀이므로 이거 2ROW 이상

SELECT E.EMP_NO 직원번호, E.EMP_NM 성명, COUNT(*)
FROM EMP_C E 
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE F.REL_TYPE_CD = 'A27'    -- 자녀(A27) 조건 적용
GROUP BY E.EMP_NO ,E.EMP_NM    
-- 조회에 필요한 EMP_NO,EMP_NM을 조회하기 위해 GROUP BY 사용 필요. 
-- COUNT가 단일행 그룹 함수이기에 단일행 그룹함수 COUNT와 다른 행도 조회하기 위해 사용함
HAVING COUNT(F.REL_TYPE_CD) > 2;

3-4) 현재 기준 조직의 부서별 직원이 몇 명인지 구하는 sql을 작성하세요. (조직코드, 조직명, 직원수)

힌트 group by org_cd, org_nm

-- 현재 기준 : ?
-- 부서별 직원 : GROUP BY ORG_NM
-- 부서별 직원 수 : COUNT(*)

SELECT
    org_nm   조직분류,
    COUNT(*) 직원수
FROM
         emp_c e
    JOIN org_c o USING ( org_cd )
GROUP BY
    org_nm;    
;

전체 쿼리

--3)20210321 기준으로 재직중인 임직원이며,사내부부인 임직원의 직원번호를 구하는 sql을 작성하세요.
--HINT : EMP_C와 FAM_REL_C를 조인해야 합니다.
-- 조회 : 직원번호
-- 재직중 : RETIRE 99/12/31 조건
-- 사내부부 : ? // 일단 EMP_NO = EMP_REL_NO 아님


-- 직원번호와 직원가족번호 =조건 해봤으나 X
SELECT *
FROM EMP_C E
    JOIN FAM_REL_C FR ON(E.EMP_NO = FR.EMP_NO)
WHERE E.EMP_NO = FR.EMP_REL_NO  -- 직원번호와 직원가족번호 =조건 해봤으나 X
;    


SELECT *
FROM EMP_C E
    JOIN FAM_REL_C FR ON(E.EMP_NO = FR.EMP_REL_NO)
WHERE E.EMP_NO = FR.EMP_REL_NO  -- 직원번호와 직원가족번호 =조건 해봤으나 X
;    

SELECT *
FROM EMP_C E
    JOIN FAM_REL_C FR ON(E.EMP_NO = FR.EMP_REL_NO)
WHERE E.EMP_NO = FR.EMP_REL_NO 
AND REL_TYPE_CD IN('A02', 'A18')
;   

SELECT * FROM EMP_C;
SELECT * FROM FAM_C;
SELECT * FROM ORG_C;
SELECT * FROM FAM_REL_C;

SELECT * 
FROM EMP_C  
WHERE EMP_NO = 11501824;
SELECT * 
FROM FAM_C
WHERE EMP_NO = 11501824;
SELECT * 
FROM FAM_REL_C
WHERE EMP_NO = 11501824;




--3-2) 현재 날짜 기준 재직중인 "이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.
--힌트: substr, count(*)를 사용
-- 재직중 : RETIRE 99/12/31 조건
-- 현재 날짜 기준
-- 이씨성 : LIKE '이%'
-- 자녀수

-- 조건 : 재직중+이씨성
SELECT *
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
;    

-- COUNT + LIKE
SELECT E.EMP_NM 직원명, COUNT(*)
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;    

-- COUNT(SUBSTR)+ LIKE
SELECT E.EMP_NM, COUNT(SUBSTR(E.EMP_NM,2,4)) AS "자녀명수"
FROM EMP_C E
    JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND E.EMP_NM LIKE '이%'
GROUP BY E.EMP_NM
;    

-- COUNT + SUBSTR
SELECT E.EMP_NM, COUNT(*) AS "자녀명수"
FROM EMP_C E
    JOIN FAM_C F USING(EMP_NO)
WHERE E.RETIRE_YMD = '99991231'
AND SUBSTR(E.EMP_NM, 1,1) = '이'
GROUP BY E.EMP_NM
;  


--3-3) 자녀 명수가 2명 이상인 직원의 리스트를 구하세요.(직원번호?/?성명?/?자녀명수?)
--힌트 having
-- 조회 컬럼 : 직원번호/성명/자녀명수
-- 자녀수 2명 이상 : REL_TYPE_CD A=27이 자녀이므로 이거 2ROW 이상

-- A27타입인 자녀수만 조회
SELECT COUNT(F.REL_TYPE_CD)
FROM EMP_C E
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE F.REL_TYPE_CD = 'A27'    
;

SELECT E.EMP_NO 직원번호, E.EMP_NM 성명, COUNT(*)
FROM EMP_C E 
    JOIN FAM_C F ON(E.EMP_NO = F.EMP_NO)
WHERE F.REL_TYPE_CD = 'A27'    -- 자녀(A27) 조건 적용
GROUP BY E.EMP_NO ,E.EMP_NM    
-- 조회에 필요한 EMP_NO,EMP_NM을 조회하기 위해 GROUP BY 사용 필요. 
-- COUNT가 단일행 그룹 함수이기에 단일행 그룹함수 COUNT와 다른 행도 조회하기 위해 사용함
HAVING COUNT(F.REL_TYPE_CD) > 2 
-- WHERE절에서 자녀있는 직원만 골라낸 후, HAVING으로 2명이상 조건 적용
-- 이후 GROUP BY 직원번호와 성명을 묶어서 조회
;    


SELECT * FROM EMP_C;
SELECT * FROM FAM_C;
SELECT * FROM ORG_C;
SELECT * FROM FAM_REL_C;


--3-4) 현재 기준 조직의 부서별 직원이 몇 명인지 구하는 sql을 작성하세요. (조직코드, 조직명, 직원수)
--힌트 group by org_cd, org_nm
-- 현재 기준 : ?
-- 부
-- 부서별 직원 수 : COUNT(*)서별 직원 : GROUP BY ?(ORG..?)
SELECT
    org_nm   조직분류,
    COUNT(*) 직원수
FROM
         emp_c e
    JOIN org_c o USING ( org_cd )
GROUP BY
    org_nm;    
;

--BETWEEN
-- 반대로 급여를 3500000미만 6000000초과를 받는 사원의 사번, 이름, 급여, 부서코드, 직급코드 조회

SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
--WHERE SALARY BETWEEN 3500000 AND 6000000;
WHERE SALARY NOT BETWEEN 3500000 AND 6000000;
-- 1.EMPLOYEE 테이블에 고용일이 90/01/01 ~ 01/01/01인 사원의 전체 내용을 조회 (BETWEEN 사용)
SELECT *
FROM EMPLOYEE
--WHERE HIRE_DATE >= '90/01/01' AND HIRE_DATE <= '01/01/01';
WHERE HIRE_DATE BETWEEN '90/01/01' AND '01/01/01';

-- LIKE
--EMPLOYEE 테이블에서 이메일 중 _의 앞 글자가 3자리인 이메일 주소를 가진 사원의 사번의 사번, 이름, 이메일 주소 조회
SELECT EMP_ID, EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '___^_%' ESCAPE '^';

-- NULL
-- EMPLOYEE 테이블에서 부서 배치를 받지 않았지만 보너스를 지급받는 직원의 이름,  보너스, 부서코드 조회
SELECT EMP_NAME, BONUS, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE IS NULL AND BONUS IS NOT NULL;

-- IN
-- 직급코드가 J1, J2, J3, J4인 사람들의 이름, 직급코드, 급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE JOB_CODE = 'J1' OR JOB_CODE = 'J2' OR JOB_CODE = 'J3' OR JOB_CODE = 'J4';


-----------실습문제-----------
--1.EMPLOYEE 테이블에서 이름 끝이 '연'으로 끝나는 사원 이름 조회
--2.EMPLOYEE 테이블에서 전화번호 처음 세자리가 010이 아닌 사원의 이름, 전화번호 조회
--3.EMPLOYEE 테이블에서 메일주소 _의 앞이 4자이면서 DEPT_CODE가 D9 또는 D6이고
--  고용일이 90/01/01 ~ 00/12/01이고, 급여가 2700000만 이상인 사원의 전체 정보 조회

SELECT EMP_NAME
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%연';

SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE NOT LIKE '010%';

SELECT  *
FROM EMPLOYEE
WHERE EMAIL LIKE '____!_%' ESCAPE '!' 
        AND (DEPT_CODE 'D9' OR DEPT_CODE 'D6')
        AND HIRE_DATE '90/01/01' AND '00/12/01'
        AND SALARY >= 2700000;

논리연산자

자바 : && ||

오라클 : AND OR

문자 그대로 AND, OR로 사용

※ 날짜를 기준으로 조건식을 쓴다면 ' '로 감싸줘야한다

기준일보다 작은게 빠른 것. 오늘보다 내일이 더 큰 숫자

-- EMPLOYEE 테이블에서 부서코드가 D6이고 급여를 3000000보다 많이 받는 직원의 이름, 부서코드, 급여 조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6' AND SALARY > 3000000;

-- EMPLOYEE 테이블에서 부섴드가 D6이거나 급여를 3000000보다 많이 받는 직원의 이름,부서코드, 급여조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6' OR SALARY > 3000000;

OR AND 연산 우선순위

자바나 SQL이나 OR AND 연산순서는 AND가 우선순위로 먼저 연산

OR를 먼저 연산하고 싶다면 소괄호를 쳐줄 것

BETWEEN

~이상 ~이하

-- EMPLOYEE 테이블에서 급여를 3500000이상 6000000이하를 받는 사원의 사번, 이름, 급여, 부서코드, 직급코드 조회
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE SALARY BETWEEN 3500000 AND 6000000;

초과 미만은?

not between

-- 반대로 급여를 3500000미만 6000000초과를 받는 사원의 사번, 이름, 급여, 부서코드, 직급코드 조회
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
-- WHERE SALARY < 3500000 OR SALARY > 6000000;
WHERE SALARY NOT BETWEEN 3500000 AND 6000000;
WHERE NOT SALARY BETWEEN 3500000 AND 6000000;
-- NOT 위치 둘 다 가능

LIKE

비교하려는 값이 특정 패턴을 만족시키는지 조회

ex) 이멜에 i가 들어가는 것을 조회, 이름이 김씨가 들어간 사람들 조회

--EMPLOYEE 테이블에서 이름이 '하'가 포함된 직원의 이름, 주민번호, 부서코드 조회
SELECT EMP_NAME, EMP_NO, DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%하%';

와일드카드 2종류 :  _  %

_ : 1글자 % : 0글자 이상

ex) '' (한글자) '__' (두글자) ***'__***' (세글자)

• '글자%' : 글자로 시작하는 값

글자 뒤로 뭐가와도 ok

ex) 글자최고, 글자(공백), 글자왕, 글자

• '%글자' : 글자로 끝나는 값

글자 앞에 뭐가와도 ok

ex) 먹글자, 한글은 글자, (공백)글자

• '글%자' : 글로 시작해서 자로 끝나는 값

글과 자 사이 뭐가와도 ok

ex)글씨를 잘쓰자, 글자, 글과자, 글(공백)자

• '%글자%' : 글자가 포함되어있는 값

ex) 한글자값, 먹글자, 글자, 한두글자

-- 논리 연산자 AND OR
-- EMPLOYEE 테이블에서 부서코드가 D6이고 급여를 3000000보다 많이 받는 직원의 이름, 부서코드, 급여 조회
SELECT EMP_NAME, DEPT_CODE,SALARY  
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6' AND SALARY > 3000000;


-- EMPLOYEE 테이블에서 부섴드가 D6이거나 급여를 3000000보다 많이 받는 직원의 이름,부서코드, 급여조회
SELECT  EMP_NAME, DEPT_CODE,SALARY 
FROM EMPLOYEE
WHERE   DEPT_CODE = 'D6' OR SALARY > 3000000;




-- 1.EMPLOYEE 테이블에서 월급이 4000000이상이고 JOB_CODE가 J2인 사원의 전체 내용 조회
-- 2.EMPLOYEE 테이블에 DEPT_CODE가 D9이거나 D5인 사원 중에 고용일이 02년 1월 1일보다 빠른 사원의
--   이름, 부서코드, 고용일 조회
SELECT *  
FROM EMPLOYEE
WHERE SALARY >= 4000000 AND JOB_CODE ='J2';

SELECT EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9' OR HIRE_DATE < '02/01/01';



-- 이 코드는 틀린 코드
-- 기준일보다 작은게 빠른 것. 오늘보다 내일이 더 큰 숫자
-- 날짜를 기준으로 조건식을 쓴다면 ''로 감싸줘야한다
-- 자바나 SQL이나 OR AND는 AND가 우선순위로 먼저 연산
-- OR를 먼저 연산하고 싶다면 소괄호를 쳐줄 것





------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------


-- BETWEEN AND
-- ~이상 ~이하

-- EMPLOYEE 테이블에서 급여를 3500000이상 6000000이하를 받는 사원의 사번, 이름, 급여, 부서코드, 직급코드 조회
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE  
WHERE SALARY >= 3500000 AND SALARY <= 6000000;

SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE 
WHERE SALARY BETWEEN 3500000 AND 6000000;

-- 반대로 급여를 3500000미만 6000000초과를 받는 사원의 사번, 이름, 급여, 부서코드, 직급코드 조회
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
--WHERE SALARY < 3500000 OR SALARY > 6000000; 
--WHERE SALARY NOT BETWEEN 3500000 AND 6000000;
WHERE NOT SALARY BETWEEN 3500000 AND 6000000;

-------실습문제-------
-- 1.EMPLOYEE 테이블에 고용일이 90/01/01 ~ 01/01/01인 사원의 전체 내용을 조회
-- 시간의축을 가로선상에 놓고 보면 시간과 연산자 사용계산이 쉬워짐

SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE >= '90/01/01' AND HIRE_DATE <= '01/01/01';

SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN '90/01/01' AND  '01/01/01';

-- WHERE HIRE_DATE >= '90/01/01' AND HIRE_DATE <= '01/01/01';




------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------

/*
-- LIKE
비교하려는 값이 특정 패턴을 만족시키는지 조회
ex) 이멜에 i가 들어가는 것을 조회, 이름이 김씨가 들어간 사람들 조회

와일드카드 2종류
 _ : 1글자
 % : 0글자 이상

ex)
'_' (한글자)
'__' (두글자)
'___' (세글자)

 '글자%' : 글자로 시작하는 값
    글자 뒤로 뭐가와도 ok
    ex) 글자최고, 글자(공백), 글자왕, 글자
 '%글자' : 글자로 끝나는 값
    글자 앞에 뭐가와도 ok
    ex) 먹글자, 한글은 글자, (공백)글자
 '글%자' : 글로 시작해서 자로 끝나는 값
    글과 자 사이 뭐가와도 ok
    ex)글씨를 잘쓰자, 글자, 글과자, 글(공백)자
 '%글자%' : 글자가 포함되어있는 값
        ex) 한글자값, 먹글자, 글자, 한두글자

*/

--EMPLOYEE 테이블에서 성이 전씨인 사원의 사번, 이름, 고용일 조회
SELECT EMP_ID, EMP_NAME, HIRE_DATE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '전%';
-- WHERE EMP_NAME LIKE '전__';
-- 도 가능하나 이름이 세글자인 사람들만 가능해서 이름이 두글자나 네글자이상이면 정확도가 떨어짐



--EMPLOYEE 테이블에서 이름이 '하'가 포함된 직원의 이름, 주민번호, 부서코드 조회
SELECT EMP_NAME, EMP_NO, DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%하%';


--EMPLOYEE 테이블에 전화번호 4번째 자리가 9로 시작하는 사원의 사번, 이름, 전화번호 조회
-- 전번 양식 : 01012345678
-- 01X 세자리 + 9로 시작하는 네번지째 자릿수
SELECT EMP_ID, EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE LIKE '___9%';

--EMPLOYEE 테이블에서 이메일 중 _의 앞 글자가 3자리인 이메일 주소를 가진 사원의 사번의 사번, 이름, 이메일 주소 조회
SELECT EMP_ID, EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '___ _%' ESCAPE ' ';
D
--WHERE EMAIL LIKE '___%'; -- 언더바_가 들어간 이메일 출력. 앞글자 2개도 4개 전부 출력



-- ESCAPE의 기준이 되는 코드는 ' '안에서 설정 가능? YES
-- ESCAPE의 ' '안에 어떤 코드이던 들어가서 설정 가능?
/*
LIKE를 쓸 때 패턴!
와일드카드 : _ %
각각 한글자,와 0글자 이상을 나타내는 와일드 카드

세글자 자릿수 언더바 3개 + 검색하고자 하는 데이터로의 언더바 1개 = 총4개의 언더바
검색하고자 하는 패턴의 문자와 와일드 카드가 일치할 경우에는 패턴과 와일드 카드를 구분하지 못하기 때문에
ESCAPE OPTION을 통해 구분해준다
 - 패턴(데이터)으로 쓸 것 앞에 구분기호 사용

*/