Oracle day3

# Oracle 오답 Mission2 리뷰(9/29)

# 문제 1번

• 문제 점검 확실히 하자..

-- 1. EMPLOYEES 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 컬럼: employee_id, first_name, last_name, email, hire_date, salary, salary * commission_pct, department_id (별칭 지정)
-- 이름은 하나의 컬럼으로 표시, 이메일은 도메인 표시

-- 1. 별칭(이름) 빼먹음, Lower(email) 소문자 변환안함.
select employee_id 사번
    , first_name || ' ' || last_name 이름
    , lower(email) || '@company.com' 이메일
    , hire_date 입사일
    , salary 급여
    , department_id 부서번호
    , salary * commission_pct 수당 
from EMPLOYEES;

 

# 문제 2번

• 문제 점검 확실히 하자.

-- 2. EMPLOYEES 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 컬럼: employee_id, first_name, last_name, email, hire_date, salary, salary * commission_pct, department_id (별칭 지정)
-- 조건: 2005년도에 입사한 사원 검색

-- 2. 별칭(이름) 빼먹음, Lower(email) 소문자 변환안함.
select employee_id 사번
    , first_name || ' ' || last_name 이름
    , email || '@company.com' 이메일
    , hire_date 입사일
    , salary 급여
    , salary * commission_pct 수당 
    , department_id 부서번호
from EMPLOYEES
where hire_date
between 
to_date('2005/01/01', 'YYYY/MM/DD') 
and 
to_date('2006/1/1', 'YYYY/MM/DD') - 1/86400;
-- 제일 정확한 문장
-- WHERE hire_date BETWEEN TO_DATE('20050101000000','YYYYMMDDhh24miss')
--                     AND TO_DATE('20051231235959','YYYYMMDDhh24miss') - 1/86400 ;

 

# 문제 3번

• 문제 점검 확실히 하자.

-- 3. EMPLOYEES 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 컬럼: employee_id, first_name, last_name, email, hire_date, salary, salary * commission_pct, department_id (별칭 지정)
-- 조건: 9월에 입사한 사원 검색

-- 3. 문제 점검 제대로 안했음. 2번 쿼리문이랑 똑같음. 정신 차리자.
 SELECT employee_id                  AS 사번
      ,first_name||' '||last_name   AS 이름 
      ,LOWER(email)||'@company.com' AS 이메일
      ,hire_date                    AS 입사일
      ,salary                       AS 급여 
      ,salary * commission_pct      AS 수당      
      ,department_id                AS 부서번호
  FROM employees 
 WHERE TO_CHAR(hire_date,'MM') = '09';

 

# 문제 6번

-- 6. 차주정보(TID) 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 컬럼: lnid, bthday, 나이, 생일 
-- 나이: SYSDATE 이용 연산 (별칭 지정)
-- 조건: 개인 차주 (ID_TYP:'1')
-- 정렬: LNID (차주번호)

-- 6. TRUNC 함수 이중으로 사용함.
SELECT lnid, bthday
       ,TRUNC((SYSDATE - bthday)/365)  AS 나이
     --,TRUNC(TRUNC(SYSDATE - bthday, 0)/365,0) 불편..
       ,TO_CHAR(bthday,'MM/DD')        AS 생일
  FROM tid
 WHERE id_typ = '1'
ORDER BY lnid ;

 

# 문제 7번 (보완)

-- 7. 계좌정보(TACCT) 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 컬럼: lnact, lnact_seq, lnid, ln_dt, exp_dt, ln_amt/1000000
-- 조건1: 대출 계좌 검색 (lmt_typ IS NULL)
-- 조건2: 2021년에 개설된 대출 계좌(ln_dt)
-- 정렬: 대출금 기준 내림차순

-- 7. 컬럼의 별칭은 정렬작업을 진행하는 곳에서 사용할 수 있음.
select lnact
    , lnact_seq
    , lnid, ln_dt
    , exp_dt
    , ln_amt/1000000 "대출금(백만)"
from TACCT
where lmt_typ is null
and ln_dt between
TO_DATE('2021/01/01','YYYY/MM/DD')
AND
TO_DATE('2022/01/01','YYYY/MM/DD') - 1/86400
order by "대출금(백만)" desc;
      -- 이렇게 사용 가능하다!

 

# 문제 8번(CASE문 활용 보완)

-- 8. 차주정보(TID) 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 컬럼: lnid, bthday, grade
-- 조건1: 법인 차주 검색 (ID_TYP:'2')
-- 조건2: 신용 등급(GRADE) : AA+, AA, AA- 
-- 정렬: 신용등급 순서 (AA+ => AA => AA-), 같은 등급내에서는 차주번호(LNID) 오름차순

-- 8. CASE 문을 사용하여 조건처리.
SELECT lnid, bthday, grade
FROM tid 
WHERE id_typ = '2' 
  AND grade IN ('AA+','AA','AA-')
ORDER BY CASE grade WHEN 'AA+' THEN 1
                    WHEN 'AA'  THEN 2 
                    WHEN 'AA-' THEN 3
         END
        ,lnid ;

 

# 문제 13번

-- 13. 차주정보(TID) 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 검색: 성별(GENDER)별 인원 수(COUNT) 검색 
-- 조건: 개인 차주 검색 (ID_TYP:'1')
-- 그룹: 성별(GENDER)
-- 정렬: 성별(GENDER) 기준 오름차순

-- 13. 별칭 자꾸 빼먹음.
select gender, count(*) cnt
from TID
where id_typ = '1'
group by gender
order by gender asc;

 

# 문제 15번

-- 15. 대출 계좌정보(TACCT) 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 검색: 지점(BRANCH)별 연체 계좌 수와 전체 연체 계좌 수 검색 
-- 조건1: 한도 계좌 제외 (LMT_TYP:NULL)
-- 조건2: 연체 중인 계좌 (DLQ_CNT > 0)
-- 그룹1: 지점(BRANCH)별 연체 계좌 수
-- 그룹2: 전체 연체 계좌 수 
-- 정렬: 지점(BRANCH) 오름차순

SELECT  decode(grouping(branch),1,'total',branch) , count(*) 
FROM TACCT                                                    
WHERE lmt_typ is null and dlq_cnt>0
GROUP BY rollup(branch)
ORDER BY branch;

 

# 문제 16번(보완)

-- 16. 차주 정보(TID) 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 검색: 성별, 출생연도 기준 인원수 검색 
-- 조건: 개인 차주 (ID_TYP:'1')
-- 그룹: 성별(GENDER), 출생연도(BTHDAY)
-- 정렬: 성별(GENDER), 출생연도(BTHDAY)

SELECT gender, TO_CHAR(bthday,'YYYY') AS YYYY, COUNT(*) 
  FROM tid 
 WHERE id_typ = '1' 
 GROUP BY gender, TO_CHAR(bthday,'YYYY')
ORDER BY 1, 2 ;

 

# 문제 17번

-- 17. 차주 정보(TID) 테이블을 이용하여 다음의 데이터를 검색하세요.
-- 검색: 출생연도, 성별을 기준으로 인원수 검색
-- 조건: 개인 차주 (ID_TYP:'1')
-- 그룹: 성별(GENDER), 출생연도(BTHDAY)
-- 정렬: 성별(GENDER), 출생연도(BTHDAY)

SELECT TO_CHAR(bthday,'YYYY')                 AS 출생연도
      ,COUNT(CASE gender WHEN '1' THEN 1 END) AS 남성
      ,COUNT(CASE gender WHEN '2' THEN 1 END) AS 여성
  FROM tid 
 WHERE id_typ = '1' 
 GROUP BY TO_CHAR(bthday,'YYYY')
ORDER BY 1 ;

 

1. Join 이란? ★★★

•  JOIN : 동시에 둘 이상의 테이블(집합) 결과를 검색한다.

# 오라클에는 2가지 종류의 JOIN이 있다.

•  ORACLE JOIN : 집합 구분 쉼표(,) 사용, 조인 조건: WHERE 절에 정의

select *
from emp e, dept d
where e.deptno = d.deptno;

•  ANSI JOIN : 집합 구분 키워드(JOIN), 조인 조건 : ON 절에 정의

SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DEPTNO, D.DNAME
  FROM EMP E JOIN DEPT D 
          -- 키워드
    ON E.DEPTNO = D.DEPTNO
 -- 조인조건

SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DEPTNO, D.DNAME
  FROM EMP E JOIN DEPT D 
    ON E.DEPTNO = D.DEPTNO
-- join 조건을 정의하는게 목적
 WHERE E.EMPNO = 7788 ;
-- 일반조건을 주는 자리.

 

# 조인을 사용할 때는 조건문에 접두어를 붙여주는게 좋다.

select *
from emp e, dept d
where e.deptno = d.deptno
-- 앞에 접두가 붙지 않으면 empno는 deptno 테이블에도 있지 않을까? 찾으러감
  and empno = 7788;
-- 테이블의 별칭을 주어 접두어로 사용할 수 있다.

select *
from emp e, dept d
where e.deptno = d.deptno
  and e.empno = 7788;
-- 이렇게 쓰는게 성능에 더 좋음.

 

# Non-Equi Join: 비교 연산자가 '=' 이외의 다른 연산자 사용.

SELECT *
  FROM EMP E, SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL ;
 
 -- ENSI JOIN
SELECT *
  FROM EMP E JOIN SALGRADE S 
    ON E.SAL BETWEEN S.LOSAL AND S.HISAL ;

 

Q2. ORDER_ITEMS, PRODS 테이블을 이용하여 조인 결과를 검색

select *
  from ORDER_ITEMS a join PRODS b
    on a.prod_id = b.prod_id;

Q2

 

# ERD를 통해 두 테이블의 관계를 파악할 수 있다.

• Join 할때는 두 테이블의 관계를 파악해야 하는데 ERD를 참고하면 쉽게 알 수 있다.

• 화살표를 클릭하면 연관된 컬럼을 조회할 수 있다.

EMP, DEPT ERD

• EMP와 DEPT 사이에는 DEPTNO라는 컬럼이 연관되어 있는데 컬럼을 사용하여 부서번호로 부서명을 조회할 수 있다.

• 이런 관계가 있다면 그들을 연결시켜서 조인작업을 할 수 있다.

JOIN할 때 사용해야 하는 컬럼.

# Q. ORDER_ITEMS, PRODS 테이블을 이용하여 조인 결과를 검색하시오.

Q2

select p.prod_id,
       p.prod_name,
       p.list_price,
       p.min_price,
       oi.order_id,
       oi.unit_price,
       oi.quantity
from ORDER_ITEMS oi join PRODS p
  on oi.PROD_ID = p.PROD_ID
where oi.UNIT_PRICE < p.MIN_PRICE;
       -- 적은금액          -- 원가

결과화면

# 주의사항, 비교는 조건연산자로만 하는게 좋음.

SELECT P.prod_id, P.prod_name, P.list_price, P.min_price, 
       O.order_id, O.unit_price, O.quantity
 FROM ORDER_ITEMS O, PRODS P
WHERE o.prod_id = p.prod_id 
  AND P.MIN_PRICE - O.UNIT_PRICE>0
  -- 인덱스 사용안하는 조건식
    ORDER BY UNIT_PRICE ASC;

 

# Q. EMP, DEPT, SALGRADE 테이블을 조인.

SELECT *
  FROM EMP       E 
      ,DEPT      D 
      ,SALGRADE  S 
 WHERE E.DEPTNO = D.DEPTNO 
   AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
   
-- ANSI JOIN
SELECT *
  FROM EMP       E 
  JOIN DEPT      D 
   ON E.DEPTNO = D.DEPTNO 
  JOIN SALGRADE  S 
   ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
--JOIN XXXXX X
-- ON XXXX BETWEEN..

 

# INNER JOIN : 조인 조건에 만족하는 행만 검색

SELECT *
  FROM EMP E INNER JOIN DEPT  D 
    ON E.DEPTNO = D.DEPTNO ;

• INNER 조인은 조인조건에 만족하는 행만 검색하여 만족하지 않는 행은 조회하지 못한다.

 

# OUTER JOIN : INNER JOIN 결과 + 어느 한쪽, 또는 양쪽에 따로 있는 데이터

• DEPARTMENTS 테이블에는 없는 데이터 조회.

SELECT *
  FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D 
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

결과화면

• EMPLOYEES 테이블에는 없는 데이터 조회.

SELECT *
  FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D 
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

결과화면(2)

• 부서 배치가 안된 사원을 검색할 때, 양쪽 모두 데이터를 검색하기 위해서 FULL OUTER JOIN을 사용한다.

SELECT *
  FROM EMPLOYEES E FULL JOIN DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
 
 -- oracle join에서는 
 SELECT *
  FROM EMPLOYEES   E 
      ,DEPARTMENTS D 
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID (+);
                    -- 만족하는게 없는쪽에 (+)를 붙힌다.

 

# Q. EMP, DEPT 테이블을 이용하여 다음 조건에 만족하는 행을 검색하세요.
- 검색: empno, ename, sal, deptno, dname, loc
- 조인: DEPTNO가 같은 행 (근무하는 사원이 없는 부서도 함께 검색)
- 조건: 2000 보다 많은 급여를 받는 사원 

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D 
 WHERE E.DEPTNO (+) = D.DEPTNO 
 -- 없는쪽에 (+)
   AND (E.SAL      > 2000 
    OR E.SAL IS NULL) ;
    
SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D 
 WHERE E.DEPTNO (+) = D.DEPTNO 
   AND E.SAL    (+) > 3000 ;
    -- 여기에 (+) 붙여주면 같은 결과를 출력할 수 있음.
    -- (+) 없는데이터 검색이면 sal에 비어있는 데이터도 출력할 수 있다.

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
  FROM EMP E RIGHT OUTER JOIN DEPT D 
                     -- 데이터를 갖고있는 쪽을 가르킴.
    ON E.DEPTNO = D.DEPTNO 
 WHERE E.SAL    > 2000
    OR E.SAL IS NULL ;

왜 안나오는거야 40번!

•  비어있는 부서의 결과가 나오지 않았던 이유는 일반 조건에 해당하는 행은 null값은 연산 결과에 만족할 수 있는 성질이 아니기 때문이다.

• 그래서 SAL 컬럼의 NULL 값인 데이터 조회하기 위해서 OR 조건에 IS NULL 값을 추가하여 출력한다.

 

# 문장의 실행 순서 문제 차이점 이해하기(1)

• 먼저 조인 문장을 실행하여 만들고, 나중에 WHERE절 조건을 실행.

-- oracle
SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D 
 WHERE E.DEPTNO (+) = D.DEPTNO 
   AND (E.SAL      > 3000 
    OR E.SAL IS NULL);

예제(1)

• 전체 목록중에 해당되는 항목은 2가지 이다.

전체 결과(1)

•  출력 결과는 다음과 같다.

결과(1)

 

# 문장의 실행 순서 문제 차이점 이해하기(2)

• EMP라는 테이블에서 E.SAL > 3000 만족하는 하나의 행만을 JOIN에 참여시킨다.

-- oracle
SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D 
 WHERE E.DEPTNO (+) = D.DEPTNO 
   AND E.SAL    (+) > 3000 ;

예제(2)

• dept 나머지 4개의 행은 떨궈 놓는다.

이해도

• 출력 결과는 다음과 같다.

• ON절에 조건이 추가한다면, AND 조건에 만족하는 녀석만 OUTER JOIN에 참여하겠다.

• 일반 조건을 어디에 추가하느냐에 따라 결과가 달라진다!!(주의)

떨궈진 DEPT 행

# 중요한건 내가 문장을 만들때 내가 정확하게 어떤 데이터를 검색해야하는지 잘 모를경우엔 요청사항을 던진 그 사람에게 물어봐야 한다. 조인 작업을 진행하고 조건문을 작성해도 되는것인지, 조건에 만족하는 행을 조인에 참여시켜야하는것인지.