[42][23.11.21][구디아카데미 후기/국비지원 IT개발자취업/김승수 선생님]

SELECT
     empno,sal
FROM(SELECT 
       empno,ename
       FROM emp
      WHERE deptno = 10
     );

• 속도향상의 원리: index,인라인뷰 원소의 수 줄임
• 인덱스 테이블 정보를 꺼내오기 때문에 테이블을 acess하지 않고 인덱스 정보만 읽은 것으로 조회가 된다
• pk는 인덱스 제공을 받게 된다
• 테이블 access 없이도 검색이 가능
• fk는 해당없음(중복허락)

---

• PK는 제약조건을 가지고 있고
• FK는 외래키 - 중복 허락, 인덱스는 해당사항 없음
• 인덱스 별도 집합
• PK-FK 참조무결성 제약조건
• PK-부모집합, FK-자손집합이니까 두개가 조인 대상이 된다는 것
• 조인을 위해서 필요한 정보들

SELECT empno,ename
  FROM emp;

조회하는 4단계 과정
1. 파싱(parsing)-문법체크하는 과정 : 한 번이라도 요청되면 메모리 기억이 되기 때문에 다음에는 3단계 진행됨-> 속도 빨라짐
2. 실행계획-RDBMS
3. 옵티마이저에게 실행계획 전달
4. (집합을)open - cursor - fetch(메모리에 올리는 과정) - 메모리에 상주하게 되니까 그 정보가 grid에 보이는 것 - 화면에 반환하고 나면 close

꼭 SELECT문에만 해당되는 것은 아님

ex. insert into 집합() values(select문)

사원집합에서 사원이름 가져오기

SELECT ename,sal FROM emp;

---

슈퍼계정 쿼리

• 메모리에 실행된 SQL문장을 확인하는 쿼리문 (DBA가 권한 가짐)
• 오라클 관리자의 v$sqlarea view를 보면 Oracle Memory에 저장되어있는 SQL 구문을 확인할 수 있다

SELECT
       sql_text,sharable_mem,executions
  FROM v$sqlarea
 WHERE sql_text LIKE 'SELECT ename%';

조건절 WHERE

SELECT
       emp.empno,emp.ENAME,dept.dname
  FROM emp,dept
 WHERE dept.deptno = emp.deptno;

• 내 안에 dept.dname가 없기 때문에 natural join
• 사원집합 안에 부서명도 넣어달라고 하면 괜찮을까?
  : 이걸 반정규화라고 하는데 일반적으로 권장하지 않는다(기본적으로 3정규화까지 가서 해결하는 걸로, 왜? 예외가 자꾸 생기니까)

다국적 기업 사원수 50만명, 부서종류 100가지일 경우, 해당 쿼리가 합리적인지?

• 실행계획을 통해서 테이블이 2개 이상일 때 먼저 읽어들이는 테이블의 순서에 따라 속도차이 발생함
• 실행계획을 보는 방법: crtl+E

그룹함수

SELECT
       max(sal)
  FROM emp;

SELECT
       max(sal),max(ename),min(ename) 
  FROM emp;
-- 이렇게 쓰는 건 X. 이 max가 누구인지 알 필요 없으니

SELECT
       max(sal),max(ename),min(ename) 
  FROM emp;

• max(ename),min(ename): 이렇게 쓰면 max(sal)의 사원 이름이 오는 것이 아니고 알파벳 순으로 정렬됨

SELECT ename
  FROM emp
 WHERE sal = (SELECT max(sal)FROM emp);

• 컬럼 자리에 함수를 래핑할 수 있는데, 이때 유효한 정보를 얻기 위해서는 서브쿼리가 필요하다

---

6. TEMP의 자료 중 EMP_ID와 EMP_NAME을 각각 ‘사번’,’성명’으로 표시되어 DISPLAY되도록 COLUMN ALLIAS를 부여하여 SELECT 하시오.

<정답> (as 생략 가능)
SELECT 
     emp_id as "사번", emp_name as "성명"
FROM temp;

SELECT
        사번,성명
  FROM (
        SELECT 
               emp_id as "사번", emp_name as "성명"
          FROM temp
       );

• SELECT문은 commit과 rollback의 대상이 아니다. (테이블 구조에 있는 데이터를 오직 읽기만 가능한 구문)
• FROM절 아래서 사용된 컬럼명은 SELECT문 뒤 컬럼이 오는 자리에 사용 가능함
• 인라인 뷰도 FROM절 아래서 사용된 집합이니, 여기서 사용된 별칭은 주쿼리에서 사용가능함
• 별칭을 줄 때 as를 붙이든, 붙이지 않든 모두 가능함

---

7. TEMP의 자료를 직급 명(LEV)에 ASCENDING하면서 결과내에서 다시 사번 순으로 DESCENDING하게 하는 ORDER BY하는 문장을 만들어 보시오.

SELECT * FROM temp;

SELECT emp_id,emp_name,lev FROM temp;

SELECT emp_id,emp_name,lev FROM temp
ORDER BY lev asc;

<정답>
SELECT emp_id,emp_name,lev FROM temp
WHERE lev ='과장'
ORDER BY lev asc, emp_id desc;

• 자바에서 연역적,귀납적 사고를 강조했다면, 데이터베이스에서는 집합적 사고하기

그룹함수

부서별 급여 평균을 구하시오

• 그룹함수는 전체범위 처리를 한다(모두 다 따져야 하니까 속도가 느리다)

SELECT sal
  FROM emp;
 
-- 그룹함수와 일반컬럼은 같이 사용할 수 없다
-- 전체를 다 비교해야 한다: 전체범위 처리(↔부분범위처리)
SELECT sal,avg(sal)
  FROM emp;
  
-- 부서가 3개이기 때문에 하나만 나오는 건 맞지 않다
SELECT avg(sal)
  FROM emp;
  
-- distinct: 중복제거
SELECT distinct(deptno)FROM emp;

-- sal은 GROUP BY의 대상이 아니다(내용이 없으니까) 
SELECT
       sal
  FROM emp
 GROUP BY deptno;
 
<정답>
SELECT
      deptno,avg(sal)
 FROM emp
GROUP BY deptno;

• GROUP BY에 대한 검색 조건을 줄 때는 WHERE절이 아닌 HAVING절에 준다

SELECT
       deptno,avg(sal)
  FROM emp
 GROUP BY deptno
HAVING avg(sal) > 2000;

---

연습문제 (sql/4장/t_letitbe)

1) 영어가사만 나오게 하기

• 홀수만 출력하기: MOD(seq_vc,2) - 나머지값 구하는 함수

SELECT * FROM t_letitbe;

SELECT MOD(seq_vc,2) FROM t_letitbe;

SELECT MOD(seq_vc,2) FROM t_letitbe
 WHERE MOD(seq_vc,2) = 1;
 
SELECT MOD(seq_vc,2) FROM t_letitbe
 WHERE MOD(seq_vc,2) = 0;
 
<정답>
SELECT MOD(seq_vc,2),words_vc FROM t_letitbe
 WHERE MOD(seq_vc,2) = 1;

<DECODE 및 함수 사용>
SELECT 
       DECODE(MOD(seq_vc,2),1, words_vc)eng_words
  FROM t_letitbe;
  
-- 존재하는 컬럼만 보여줘야하기 때문에 이 코드는 불가능함
SELECT 
       DECODE(MOD(seq_vc,2),1, words_vc)eng_words
  FROM t_letitbe
 WHERE eng_words = 1;
 
-- 해결방법은? 
-- 별칭을 조건절에서 사용하고 싶다면 인라인 뷰(from절 밑에 select문) 사용
SELECT 
       seq_vc,eng_words
  FROM (
        SELECT MOD(seq_vc,2)seq_vc
              ,DECODE(MOD(seq_vc,2),1, words_vc)eng_words
        FROM t_letitbe
        )
 WHERE seq_vc = 1;
 
-- seq_vc를 num으로 바꿔도 가능

2) 한글가사만 나오게 하기

SELECT MOD(seq_vc,2),words_vc FROM t_letitbe
 WHERE MOD(seq_vc,2) = 0;

<DECODE 및 함수 사용>
SELECT 
       DECODE(MOD(seq_vc,2),0, words_vc)han_words
  FROM t_letitbe
 WHERE MOD(seq_vc,2) = 0;

3) 영문가사와 한글 가사 모두 나오게 하기

• 조건: 합집합 이용 / 정렬 / 영문,한글 가사 교대로 출력
  (SELECT * FROM t_letitbe는 답 아님)

<1단계>
-- 홀수
SELECT
       seq_vc
      ,decode(MOD(seq_vc,2),1, words_vc)A
  FROM t_letitbe;
-- 짝수 
SELECT
       seq_vc
      ,decode(MOD(seq_vc,2),0, words_vc)A
  FROM t_letitbe;

<2단계>
SELECT
       *
  FROM(
        SELECT
               seq_vc
              ,decode(MOD(seq_vc,2),1, words_vc)A
          FROM t_letitbe
         UNION ALL
        SELECT
               seq_vc
              ,decode(MOD(seq_vc,2),0, words_vc)A
          FROM t_letitbe
  );
  
<정답>
SELECT
       seq_vc
      ,max(A) all_word
  FROM(
        SELECT
               seq_vc
              ,decode(MOD(seq_vc,2),1, words_vc)A
          FROM t_letitbe
         UNION ALL
        SELECT
               seq_vc
              ,decode(MOD(seq_vc,2),0, words_vc)A
          FROM t_letitbe
  )
GROUP BY seq_vc
ORDER BY TO_NUMBER(seq_vc) asc;

<max(A)에 대한 이해-왜 max를 붙여야할까?>
-- 이건 안 됨
SELECT
       empno,sum(sal)
  FROM emp;

-- 가능: GROUP BY에 있는 컬럼명은 select,from에 쓸 수 있으니까 
SELECT
       deptno,sum(sal)
  FROM emp
 GROUP BY deptno;
 ----------------------------------
 -- 이렇게는 가능하지만
 SELECT
       deptno,sum(sal),max(sal)
  FROM emp
 GROUP BY deptno;
 
 -- 이렇게는 불가능
SELECT
       deptno,sal
  FROM emp
 GROUP BY deptno;
 
 -- sal을  GROUP BY절에 추가
 -- 문법문제는 해결했지만, GROUP BY 효과가 없다
SELECT
       deptno,sal
  FROM emp
 GROUP BY deptno,sal;
 
--
SELECT
       deptno,sal
FROM emp;

• max나 min을 주거나 결과에 영향이 없는 건, 결국 A컬럼에 대해서 문법적인 문제를 해결하는 용도로 사용되었기 때문이다

-- 고정된 2차원 테이블을 오른쪽으로 늘릴 때
SELECT 1,2,3 FROM dual;

-- UNION ALL
SELECT 1 FROM dual
UNION ALL
SELECT 2 FROM dual
UNION ALL
SELECT 3 FROM dual;

---

연습문제

문제 1. 영화 티켓을 받을 수 있는 사람의 명단과 현재 가지고 있는 포인트, 영화 티켓의 포인트, 그리고 그 티켓을 사용한 후 남은 예상 포인트를 출력하시오.

SELECT * FROM t_giftpoint;
SELECT * FROM t_giftmem;

주어진 정보: 영화티켓 - 15000점
회원보유포인트 - 영화티켓포인트 = 잔여포인트
SELECT
       point_nu
  FROM t_giftpoint
 WHERE name_vc='영화티켓';
----------------------------------------------
조건식: 보유포인트 >= 영화티켓포인트
WHERE mem.point_nu >= poi.point_nu
----------------------------------------------
두 집합이 필요한데, 이건 카타시안곱 데이터를 복제
SELECT
       *
  FROM t_giftpoint,t_giftmem;

----------------------------------------------
우리는 모든 집합을 원하는 게 아니라
상품집합에 대해 영화티켓만 원한다.
SELECT
       *
  FROM t_giftmem mem,
       (
        SELECT
               point_nu
          FROM t_giftpoint
         WHERE name_vc='영화티켓'
        )poi;
----------------------------------------------
SELECT
       mem.point_nu-poi.point_nu as "잔여포인트"
  FROM t_giftmem mem,
       (
        SELECT
               point_nu
          FROM t_giftpoint
         WHERE name_vc='영화티켓'
        )poi;
        
----------------------------------------------
SELECT
       mem.point_nu-poi.point_nu as "잔여포인트"
  FROM t_giftmem mem,
       (
        SELECT
               point_nu
          FROM t_giftpoint
         WHERE name_vc='영화티켓'
        )poi
  WHERE mem.point_nu >= poi.point_nu;

<정답>
  SELECT mem.name_vc as "이름"
        ,mem.point_nu as "보유포인트"
        ,poi.point_nu as "적용포인트"
        ,mem.point_nu-poi.point_nu as "잔여포인트"
  FROM t_giftmem mem,
       (
        SELECT
               point_nu
          FROM t_giftpoint
         WHERE name_vc='영화티켓'
        )poi
  WHERE mem.point_nu >= poi.point_nu;

<인라인 뷰를 사용하지 않을 때>

SELECT mem.name_vc as "이름"
      ,mem.point_nu as "보유포인트"
      ,poi.point_nu as "적용포인트"
      ,mem.point_nu-poi.point_nu as "잔여포인트"
  FROM t_giftmem mem,t_giftpoint poi
 WHERE mem.point_nu >= poi.point_nu
   AND poi.name_vc='영화티켓';

---

문제2. 김유신씨가 보유하고 있는 마일리지 포인트로 얻을 수 있는 상품 중 가장 포인트가 높은 것은 무엇인가?

SELECT point_nu FROM t_giftmem
 WHERE name_vc = '김유신';
-----------------------------------
SELECT name_vc
  FROM t_giftpoint
 WHERE point_nu <= 50012;
-----------------------------------
<정답>
SELECT name_vc
  FROM t_giftpoint
 WHERE point_nu = (
                   SELECT max(point_nu)
                     FROM t_giftpoint
                    WHERE point_nu <= 50012
                  );

DECODE

• DECODE는 일반적인 프로그래밍 언어의 if문을 SQL문장 또는 PL/SQL안으로 끌어들여 사용하기 위하여 만들어진 오라클 함수이다
• 크다/작다는 비교할 수 없고, 같은 것만 비교할 수 있다
• CASE..WHEN과 DECODE는 표준 (IF문은 표준 아님)

IF A = B THEN
   return 'T';
END IF;

DECODE(1,1,'T')

SELECT DECODE(1,1,'T')
      ,DECODE(1,2,'T','F')
FROM dual;

• dual: 오라클에서만 제공되는 가상 테이블(로우1개,컬럼1개) - 함수테스트 시에 사용하기 위해 제공됨
• sign( ): 인수가 양수면 '1' 반환 / 인수가 음수인면 '-1' 반환 / 인수가 0인 경우 '0' 반환

SELECT sign(1+100),sign(-5000),sign(100-100)
  FROM dual;

-- 뺄셈을 했을 때 마이너스가 나오기 때문에 '음수' 출력
SELECT DECODE(SIGN(100-200),1,'양수',-1,'음수',0,'0') FROM dual;

DECODE는 FROM절만 빼고는 어디서나 사용할 수 있다

• ORDER BY절
  문제. 강의시간과 학점이 같으면 '일반과목'을 리턴받은 후 정렬도 하고 싶다면?

-- 같을 때만 값을 주었으니, 다를 때는 무조건 null
SELECT
       decode(lec_time,lec_point,'일반과목',null)
  FROM lecture;

<정답>
-- ASC(오름차순)는 생략할 수 있기 때문에 일반과목이 먼저 나왔다
-- desc를 붙이면 null이 먼저 나온다
SELECT
       decode(lec_time,lec_point,'일반과목')
  FROM lecture
 ORDER BY decode(lec_time,lec_point,'일반과목');

Q. null이 있는 경우, 정렬은 어떻게 되나?

<내림차순>
SELECT comm
  FROM emp
 ORDER BY comm desc;

<오름차순>
SELECT comm
  FROM emp
 ORDER BY comm asc;

• null은 '모른다' 혹은 '결정되지 않았다, 정렬을 할 수 없다'이므로 맨 뒤에 붙였다(오름차순)
• 그래서 null인 컬럼을 내림차순으로 정렬하면 null이 맨 앞에 온다

---

문제1. 주당 강의시간과 학점이 같으면 '일반과목'을 돌려받고자 한다. 쿼리문을 작성하시오.

<정답>
SELECT decode(lec_time,lec_point,'일반과목')
  FROM lecture;

SELECT decode(lec_time,lec_point,'일반과목',null)
  FROM lecture;

• 조건절을 만족하지 않는 경우에는 null을 반환한다는 것을 알 수 있다

---

문제2. 주당강의시간과 학점이 같은 강의의 숫자를 알고 싶다.쿼리문을 작성하시오

SELECT
       count(lec_id)
  FROM lecture
 WHERE lec_time = lec_point;

<decode>
SELECT
       decode(lec_time,lec_point,1)
  FROM lecture;

<정답>
SELECT
       count(decode(lec_time,lec_point,1))
  FROM lecture;

• 왜 2가 나오는지?
  : null일 때 옵티마이저가 계산할 수 없다

ex.
SELECT count(empno) FROM emp;
SELECT count(comm) FROM emp;

형전환 함수

to_char / to_number / to_date

SELECT to_char(sysdate,'YYYY-MM--DD') FROM dual;

SELECT sysdate-1,sysdate+2 FROM dual;

• UI(user interface or View 계층) 받아오는 값인 경우가 대부분이다(받아온 값이 문자열이다)

<input type="text" name="start_day">

-- 이렇게는 안 됨
SELECT '2023-11-23'+1 FROM dual;
-- 정답
SELECT to_date('2023-11-23')+1 FROM dual;

연습문제
월요일엔 해당일자에 01을 붙여서 4자리 암호를 만들고, 화요일엔 11, 수요일엔 21, 목요일엔, 31, 금요일엔 41, 토요일엔 51, 일요일엔 61을 붙여서 4자리 암호를 만든다고 할 때 암호를 SELECT하는 SQL을 만들어 보시오.

<1단계>
SELECT to_char(sysdate,'day') FROM dual;

<2단계>
SELECT DECODE(to_char(sysdate,'day'),'화요일','11','나머지')
  FROM dual;
  
<3단계>
SELECT to_char(sysdate,'dd')||'11' FROM dual;

<4단계>
SELECT (to_char(sysdate,'day'),'월요일','01'
                              ,'화요일','11'
                              ,'수요일','21'
                              ,'목요일','31'
                              ,'금요일','41'
                              ,'토요일','51'
                              ,'일요일','61')
 FROM dual;
 
 <정답>
SELECT to_char(sysdate,'dd')||
       DECODE(to_char(sysdate,'day'),'월요일','01'
                                    ,'화요일','11'
                                    ,'수요일','21'
                                    ,'목요일','31'
                                    ,'금요일','41'
                                    ,'토요일','51'
                                    ,'일요일','61') sec_key
  FROM dual;
 

문제3. 강의시간과 학점이 같거나 강의시간이 학점보다 작으면 '일반과목'을 돌려받고, 강의시간이 학점보다 큰 경우만 '실험과목'이라고 돌려받고 싶다. 쿼리문을 작성하시오.

<1단계>
SELECT
       lec_id,lec_time,lec_point
  FROM lecture;
  
<2단계>
-- 같거나
SELECT decode(lec_time-lec_point,0,'일반과목') FROM lecture;
-- 작으면
SELECT decode(SIGN(lec_time-lec_point),-1,'일반과목') FROM lecture;
-- 크면
SELECT decode(SIGN(lec_time-lec_point),1,'실험과목') FROM lecture;

<정답1>
SELECT decode(SIGN(lec_time-lec_point),1,'실험과목','일반과목') 
  FROM lecture;

<정답2>
SELECT decode(SIGN(lec_time-lec_point),1,'실험과목'
                                      ,0,'일반과목'
                                      ,-1,'일반과목')
  FROM lecture;

문제3-1. lec_time이 크면 '실험과목',lec_point가 크면 '기타과목', 둘이 같으면 '일반과목'으로 값을 돌려받고자 한다. 쿼리문을 작성하시오.

<정답>
SELECT lec_time,lec_point,
       DECODE(SIGN(lec_time-lec_point),1,'실험과목',0,'일반과목',-1,'기타과목')
  FROM lecture;

사전학습문제(6장 06-002)

컬럼레벨에 있는 학과별 정원수를 로우레벨로 내려서 출력하시오

• 답안예시
  

코드를 입력하세요

각 행에 1학년부터 4학년까지를 분리해서 한 행에 한 학년만 나오도록 하고자 한다. 쿼리문을 작성하시오

코드를 입력하세요

DECODE 실전문제 1

emp 테이블의 사원이름을 한 행에 사번, 성명을 3명씩 보여주는 쿼리문을 작성하시오
(목표: 나는 로우에 있는 이름을 컬럼레벨에 출력할 수 있다)

---

실전문제 2

사원테이블에서 job이 clerk인 사람의 급여 합, salesman인 사람의
급여의 합을 구하고 나머지 job에 대해서는 기타 합으로 구하시오.
(모든 테이블은 한 번만 읽고서 처리할 것)

SELECT 
       decode(job,'CLERK',sal)
  FROM emp;
-------------------------------------  
SELECT 
       sum(decode(job,'CLERK',sal)),max(sal),count(empno)
  FROM emp;
-------------------------------------  
SELECT count(empno) FROM emp;
-------------------------------------  
SELECT 
       decode(job,'CLERK',sal)
      ,decode(job,'SALESMAN',sal)
  FROM emp;

sum(decode 패턴) - 자주 나옴

SELECT 
       count(decode(job,'CLERK',sal,null))
      ,sum(decode(job,'CLERK',sal,null))
      ,count(decode(job,'SALESMAN',sal,null))
      ,sum(decode(job,'SALESMAN',sal,null))      
  FROM emp;
-------------------------------------    
-- empno(사원번호)는 다른 값들과 아무런 의존 관계가 없다
-- empno에 max함수를 씌우는 건 문법적인 문제를 해결하기 위해서일 뿐이다
SELECT max(empno)
      ,count(decode(job,'CLERK',sal,null))
      ,sum(decode(job,'CLERK',sal,null))
      ,count(decode(job,'SALESMAN',sal,null))
      ,sum(decode(job,'SALESMAN',sal,null))      
  FROM emp;
-------------------------------------      
SELECT 
      sum(decode(job,'CLERK',sal,null))
      ,sum(decode(job,'SALESMAN',sal,null))
      ,sum(decode(job,'CLERK',null,'SALESMAN',null,sal))etc_sal
      ,sum(sal)      
  FROM emp;