Oracle day4

728x90

# Oracle 오답 Mission3 리뷰(9/30)

# 문제 8번

• 문제 요구사항에 조인 조건이 아닌 일반 조건문에 있어서 where 절에 사용해야함.

-- 8. CUSTS, ORDERS 테이블을 이용하여 다음 조건에 만족하는 행을 검색하세요.
-- 검색: CUST_ID, LNAME, GENDER, ORDER_DATE, ORDER_STATUS, ORDER_TOTAL 
-- 조인: CUST_ID가 같은 행 검색 
-- 조건: ORDER_MODE가 direct
select * from orders;
select c.CUST_ID, c.LNAME, c.GENDER, o.ORDER_DATE, o.ORDER_STATUS, o.ORDER_TOTAL
  from CUSTS c
  join ORDERS o
    on c.CUST_ID = o.CUST_ID
WHERE O.ORDER_MODE = 'direct';

# 문제 9번

• 조건절 + ORDER BY절 컬럼명 앞에 테이블의 별칭을 적어줘야 한다.(성능관련)

-- 9. 차주정보(TID), 대출계좌(TACCT) 테이블을 이용하여 다음 조건에 만족하는 행을 검색하세요.
-- 컬럼: lnid, bthday, score, lnact, lnact_seq, ln_dt, ln_amt, repay_typ, rate
-- 조인: 차주번호(LNID)가 같은 행끼리 연결
-- 조건1: 개인 차주 검색 (ID_TYP:'1')
-- 조건2: 현재 대출 상태인 계좌 검색 (REPAY:NULL)
-- 조건3: 대출 계좌 검색 (LMT_TYP:NULL)
-- 정렬: 대출금액(LN_AMT) 내림차순, 동일 금액내에서 차주번호(LNID) 오름차순

select t.lnid, t.bthday, t.score, ta.lnact, ta.lnact_seq, ta.ln_dt, ta.ln_amt, ta.repay_typ, ta.rate
  from TID t
  join TACCT ta
    on t.LNID =ta.LNID
 where t.ID_TYP = '1' 
   and ta.REPAY is null and ta.LMT_TYP is null
order by t.ln_amt DESC, t.lnid;

1. Subquery 서브쿼리 ★★★

• Main query 보다 먼저 실행될 수 있고, 실행 결과는 Subquery 위치로 리턴된다.

• GROUP BY절을 제외한 모든 절에 Subquery 사용이 가능하다.

# 서브쿼리 기본 구조

-- 조회 대상 집합으로 쓸 수 있음
SELECT (SELECT ...)
  FROM ...

-- 검색 대상 집합으로 쓸 수 있음.
SELECT * 
  FROM (SELECT ...)

-- 조건 대상 집합으로 쓸 수 있음.
SELECT ...
  FROM ...
 GROUP BY ...
HAVING (SELECT ...) 

-- 정렬할 때 쓸 수 있음.
SELECT ...
  FROM ...
 ORDER BY (SELECT ...)
 
-- 보통 서브쿼리 
-- 2. main 쿼리 실행.
select * from emp where sal >
-- 1. 서브쿼리 먼저 실행, 결과 조회 후 메인쿼리로 이동
(select sal from emp where empno = 7566);

# 조건절에 사용되는 Subquery (WHERE, HAVING)

• 리턴하는 행, 컬럼 개수에 따라 사용 가능한 비교 연산자의 종류 제한된다.

• ORDER BY 사용 불가능.

SELECT * -- error : missing right parenthesis
  FROM emp 
 WHERE sal IN (SELECT MIN(sal) 
                 FROM emp 
                GROUP BY deptno
                ORDER BY 1) ; -- 사용하면 안된다!
                
-- 올바른 사용 예
SELECT * 
  FROM emp 
 WHERE sal IN (SELECT MIN(sal) 
                 FROM emp 
                GROUP BY deptno) ;

# Single row subquery
• 하나의 행, 하나의 컬럼 즉, 단일 값을 리턴하는 서브쿼리

• 단일행 비교 연산자 사용 (=,<>, >,>=,<, <= )

-- 단일행 예제
SELECT * 
  FROM EMP 
 WHERE SAL > (SELECT SAL
                -- 조회할 컬럼
                FROM EMP 
               WHERE EMPNO = 7566);
                -- 조회할 사원 번호

# Multiple row subqurey

• 둘 이상의 행을 리턴하는 서브쿼리, 다중 행 비교 연산자 사용 (IN, ANY, ALL)

-- 다중행 예제
SELECT * 
  FROM EMP 
 WHERE SAL IN (SELECT MIN(sal)
                FROM emp
               GROUP BY deptno);
-- 서브 쿼리의 결과와 같은 행 Multiple row subqurey 조회 IN 문을 사용하면
-- 둘 이상의 행을 조회 할 수 있다.

• IN 연산자는 목록안에 행이 하나라도 사용할 수 있다.

-- 하지만 서브쿼리가 하나의 행만 갖고 있다면 = 연산자를 사용하는게 성능상 좋은 문장이다.
SELECT * 
  FROM EMP 
 WHERE SAL IN (SELECT MIN(sal) FROM emp);

# IN연산자(복습)

• in 연산자는 명시된 속성이 명시된 객체에 존재하면 true를 반환

SELECT * 
  FROM EMP 
 WHERE SAL IN (800,950,1300);

-- IN 연산자와 같은 결과
SELECT * 
  FROM EMP 
 WHERE SAL = 800
    OR SAL = 950
    OR SAL = 1300;

# ANY 연산자 : OR와 같은 결과를 리턴하는 키워드

-- 문장을 구성하다보면 분명 조건문에 비교 연산자를 사용해야 하는 경우가 있다.
SELECT * 
  FROM EMP 
 WHERE DEPTNO = 10 
    OR DEPTNO = 20 ;

SELECT * 
  FROM EMP 
 WHERE DEPTNO IN (10,20) ;
 
SELECT * 
  FROM EMP 
 WHERE DEPTNO = ANY (10,20) ;
      -- 그런 경우 ANY or ALL 연산자 사용한다.
 
 SELECT * 
  FROM EMP 
 WHERE DEPTNO > ANY (10,20);
                 -- 서브쿼리 사용가능

-- IN 연산자를 사용했을때와 같은 결과.
SELECT * 
  FROM EMP 
 WHERE SAL > ANY (SELECT MIN(sal)
                   FROM emp 
                  GROUP BY deptno);
                  -- 1566, 2916, 2175 3개의 조건중 하나만 만족해도 된다.
                  -- 1566보다 큰 급여를 갖고있는 사원을 검색한다.

# ALL 연산자 : AND와 같은 결과를 리턴하는 키워드

SELECT * 
  FROM EMP 
 WHERE SAL > ALL (SELECT AVG(SAL)
                FROM EMP 
               GROUP BY DEPTNO);
               -- 2916보다 큰 급여를 갖고 있는 사원을 조회한다.

@ UPDATE 문 사용법, ROLLBACK 사용법.

update emp
 -- 수정할 테이블
   set sal = 950
 -- 수정할 컬럼
 where empno = 7788;
 -- 수정해야하는 컬럼의 조건
 
-- 문서 편집 후 저장 안하는 것과 똑같다.      
ROLLBACK;

# Q1. 각 부서의 최저 급여를 받는 사원을 검색하라.

SELECT *
  FROM EMP 
 WHERE SAL IN (SELECT MIN(SAL) FROM EMPGROUP BY DEPTNO);
   -- SAL의 값이 IN (부서별 MIN(SAL))
                          -- 최소값
                          
 -- 틀린문장 왜? 실행결과를 보면 알 수 있음.

WHERE SAL IN (SELECT MIN(SAL) FROM EMPGROUP BY DEPTNO);
-- 800, 950, 1300
-- EMPNO = 7788 사원도 950 값을 갖고있음 그래서 출력됨.
-- 우리의 목적은 요구사항의 조건을 출력하는 것
-- 그래서 7788이 출력되지 않게 해야함.

-- 서브쿼리가 몇개의 행을 리턴하는가?
-- 싱글, 멀티, 컬럼 3가지의 구분으로 나눠짐.
SELECT *
  FROM EMP 
 WHERE (DEPTNO,SAL) IN (SELECT DEPTNO, MIN(SAL)
 -- Multiple 서브쿼리 오라클에서만 지원, 이런경우 무조건 IN 연산자만 사용가능.
 -- 두개의 컬럼을 비교할 수 있도록 ()로 묶어주어야함.
                FROM EMP
               GROUP BY DEPTNO);

# Q2. 소속 부서의 평균 급여보다 많은 급여를 받는 사원을 검색하라.

SELECT *
  FROM EMP 
 WHERE SAL ???? (SELECT AVG(SAL)
                FROM EMP
               GROUP BY DEPTNO);
-- single-row subquery returns more than one row
-- 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

-- FROM절에 쓰는 서브쿼리에서 해결가능.

# FROM 절의 서브쿼리(Inline View)

• ORDER BY 절 사용 가능 (TOP-N 질의 가능), Correlated Subquery 사용 불가능

@Correlated Subquery 사용 불가능? 메인 쿼리가 먼저 실행, 서브쿼리가 나중 실행.

@TOP-N 질의 가능?

SELECT * 
  FROM (SELECT *
          FROM EMP        
         ORDER BY SAL DESC)
          -- TOP-N 질의 : ORDER BY 절을 통해서 정렬
          -- 제일 빨리 입사한 사원
          -- 제일 늦게 입사한 사원
 WHERE ROWNUM <= 3;

• FROM 절은 테이블의 이름을 정의하는 곳이다? 반만 맞음

select *
  from emp e 
 where e.sal > 2000;

SELECT * -- ERROR : "EMP"."SAL": invalid identifier(부적합한 식별자.)
  FROM emp e -- 1. FROM절의 의해 EMP 테이블이 올라왔는데 이름을 e로 바꿈.
 WHERE emp.sal > 2000; -- 그래서 테이블 EMP의 이름은 존재하지 않는다.

• FROM 절은 내가 필요한 집합을 정의하는 곳이다.

SELECT *
  FROM EMP E 
      ,(SELECT DEPTNO, AVG(SAL) AS AVG_SAL
          FROM EMP 
         GROUP BY DEPTNO) A
     -- 결과에 필요한 A라는 집합을 생성함.
 WHERE E.DEPTNO = A.DEPTNO;

# Q2. 소속 부서의 평균 급여보다 많은 급여를 받는 사원을 검색하라. (아까 조건절에서 풀지 못한 문제 해결)

SELECT *
  FROM EMP E 
      ,(SELECT DEPTNO, AVG(SAL) AS AVG_SAL
          FROM EMP 
         GROUP BY DEPTNO) A 
 WHERE E.DEPTNO = A.DEPTNO 
   AND E.SAL    > A.AVG_SAL ;
--   모든컬럼 > 부서별 평균급여

• SELECT 절이나 FROM절에 사용된 서브쿼리는 결과화면에 출력할 수 있다.

# 조회 결과를 담아 테이블을 생성하는 명령어 CREATE!

CREATE TABLE EMP_AVG
-- 2. EMP_AVG 테이블을 생성하여 조회된 내용을 담아준다.
AS 
SELECT DEPTNO, AVG(SAL) AS AVG_SAL
-- 1. 부서별 평균 급여를 조회하여 저장한다.
  FROM EMP 
 GROUP BY DEPTNO;

-- EMP_AVG 테이블이 있다면

-- 사원정보의 EMP 테이블의 컬럼을 붙혀 출력.
SELECT *
  FROM EMP      E 
      ,EMP_AVG  A 
 WHERE E.DEPTNO = A.DEPTNO ;

-- 부서의 평균 급여보다 많은 급여를 받는 사원을 검색
SELECT *
  FROM EMP      E 
      ,EMP_AVG  A 
 WHERE E.DEPTNO = A.DEPTNO 
   AND E.SAL    > A.AVG_SAL;

• 근데 EMP_AVG 테이블이 없어도 FROM 절에 서브쿼리를 사용하여 조회 가능하다.

SELECT *
  FROM EMP      E
      ,(SELECT DEPTNO, AVG(SAL) AS AVG_SAL
          FROM EMP 
         GROUP BY DEPTNO) A
         -- FROM 절에 서브쿼리를 통해 EMP_AVG와 같은 테이블 A를 조회할 수 있다.
 WHERE E.DEPTNO = A.DEPTNO 
   AND E.SAL    > A.AVG_SAL;
   
-- ANSI
SELECT *
  FROM EMP      E
  JOIN (SELECT DEPTNO, AVG(SAL) AS AVG_SAL
          FROM EMP 
         GROUP BY DEPTNO) A
         -- FROM 절에 서브쿼리를 통해 EMP_AVG와 같은 테이블 A를 조회할 수 있다.
    ON E.DEPTNO = A.DEPTNO 
   AND E.SAL    > A.AVG_SAL;

# Correlated Subquery : Mainquery 컬럼을 참조하는 Subquery

• Mainquery가 Subquery보다 먼저 실행, 후보행이 결정되면 후보값을 Subquery에 입력하여 결과를 생성

SELECT *
  FROM emp e
  -- 1. 메인 쿼리의 문장이 먼저 실행된다.
 WHERE sal > (SELECT AVG(SAL)
                FROM emp 
               WHERE deptno = e.deptno);
               -- 2. 서브쿼리에서 메인 쿼리의 별칭을 참조하고 있다면

• 배치 업무에 최적화 되어 있음.

SELECT E.*
  FROM EMP      E 
  JOIN (SELECT DEPTNO, AVG(SAL) AS AVG_SAL
          FROM EMP 
         GROUP BY DEPTNO)  A 
    ON E.DEPTNO = A.DEPTNO 
   AND E.SAL    > A.AVG_SAL;

• 온라인성 업무에 최적화 되어 있음.

SELECT E.*
  FROM emp e
 WHERE sal > (SELECT AVG(SAL)
                FROM emp 
               WHERE deptno = e.deptno);

# SELECT, ORDER BY 절 서브쿼리 (Inline View)

• Scalar Subquery만 가능 (단일 값 리턴하는 서브쿼리)

• Correlated Subquery 실제로 많이 사용.

SELECT * 
  FROM tid a
  -- tid를 조회 후 ORDER BY 절 실행 
 ORDER BY (SELECT code 
             FROM tcode 
            WHERE grade = a.grade);
            -- ERROR : a.grade가 누군지 몰라서!!
            -- tcode의 grade를 참조하여 emp a.grade와 매칭되는 값을 연결 후 code 기준으로 정렬한다.

# Q1. EMP 테이블을 검색하여 다음과 같은 결과가 검색될 수 있도록 명령문을 작성하세요.

-- 검색: EMPNO, ENAME, SAL, "Jones's Salary"
-- 조건: JONES 보다 많은 급여를 받는 사원 검색 (JONES 급여도 함께)
-- 정렬: EMPNO

-- 나의 풀이.
select empno, ename, sal, sal "Jones's Salar"
  from emp
 where sal >= (select sal
                from emp
               where ename = 'JONES');
               
-- 강사님 풀이.
SELECT A.EMPNO, A.ENAME, A.SAL, B.SAL AS "Jones's Salary"
FROM EMP A, (SELECT *
             FROM EMP
             WHERE ENAME = 'JONES') B
    -- JOIN도 서브쿼리도 사용된거임, 서브쿼리는 문장안에 포함된 또다른 select문.
WHERE A.SAL > B.SAL
ORDER BY A.EMPNO;

-- SELF JOIN
SELECT A.EMPNO, A.ENAME, A.SAL, B.SAL AS "Jones's Salary"
FROM EMP A
    ,EMP B
WHERE A.SAL > B.SAL
  AND B.ENAME = 'JONES'
ORDER BY A.EMPNO ;

# Q2. 다음 요구 조건에 만족하는 행을 검색하세요.

-- TACCT(대출계좌), TCREDIT(신용평가정보), TCODE(신용등급 코드) 테이블을 사용
-- 한도 계좌 제외 검색 (TACCT.LMT_TYP:NULL)
-- '04'(TCREDIT.ACODE) 평가사에서 '신용상태 우수'(TCODE.GRADE_DESC) 등급의 신용 평가를 받은 차주 검색 
-- 신용평가 만료일(TCREDIT.END_DT)이 오늘 이후인 평가만 사용
-- 검색 결과는 계좌번호, 계좌일련번호를 기준으로 오름차순 정렬

SELECT DISTINCT TA.LNACT, TA.LNACT_SEQ, TA.ACCT_TYP, TA.LNID, TA.LN_DT, TA.LN_TERM, TA.EXP_DT, TA.LN_AMT, TD.GRADE_DESC
  -- 절대 *쓰지 말것 !!!!! 테이블의 컬럼이 누구껀지 분명히 확인하기!!!!

  FROM TACCT    TA
  -- 검색 대상의 집합을 정의하는곳이 FROM절이다!!
      ,TCREDIT  TC
      ,TCODE    TD 
 WHERE TA.LNID = TC.LNID 
   AND TC.CODE = TD.CODE 
 --  AND TC.ACODE = '04' 
   AND TD.GRADE_DESC = '신용상태 우수' 
   AND TA.LMT_TYP IS NULL
   AND TC.END_DT > SYSDATE 
ORDER BY 1, 2;

-- 캡쳐되어있는 화면은 없을때, 검증하는 방법
--  + 내가 작성한 문장 여기에 신뢰도를 높이는 방법
-- 요구사항에 해당되는 조건이 다 들어가있는가? 
-- 모든 문장 해석의시작은 FROM 절이다.

'Study > OracleDB' 카테고리의 다른 글

Oracle day6 (1)	2022.10.06
Oracle day5 (2)	2022.10.05
Oracle day3 (1)	2022.09.30
Oracle Day2 (0)	2022.09.29
Oracle Day1 (2)	2022.09.28

슬기로운 개발자

Oracle day4

# Oracle 오답 Mission3 리뷰(9/30)

1. Subquery 서브쿼리 ★★★

# 서브쿼리 기본 구조

# 조건절에 사용되는 Subquery (WHERE, HAVING)

# FROM 절의 서브쿼리(Inline View)

# SELECT, ORDER BY 절 서브쿼리 (Inline View)

'Study > OracleDB' 카테고리의 다른 글

티스토리툴바

Oracle day4

# Oracle 오답 Mission3 리뷰(9/30)

1. Subquery 서브쿼리 ★★★

# 서브쿼리 기본 구조

# 조건절에 사용되는 Subquery (WHERE, HAVING)

# FROM 절의 서브쿼리(Inline View)

# SELECT, ORDER BY 절 서브쿼리 (Inline View)

'Study > OracleDB' 카테고리의 다른 글

'Study/OracleDB' Related Articles

티스토리툴바