Oracle Day2

# Oracle 오답 Mission1 리뷰(9/28)

# 문제 14번

-- 14. 계좌정보(TACCT) 테이블을 이용하여 다음의 데이터를 검색하세요.
--컬럼: lnact, lnact_seq, lnid, ln_dt, exp_dt, ln_amt/1000000
--조건1: 한도계좌 제외 (lmt_typ:NULL)
select lnact, lnact_seq, lnid, ln_dt, exp_dt, ln_amt/1000000
from TACCT
where lmt_typ is null
--조건2: 2021년에 개설된 대출 계좌(ln_dt) 틀렸음, 문제 좀 제대로 읽자!!!!!!!
--AND ln_dt >= '2021/01/01';
AND ln_dt BETWEEN '2021/01/01' AND '2021/12/31' ;

# 문제 18번

-- 18. 사원 테이블(EMP)에서, 입사일자(HIREDATE)가 '1980/12/17'인 사원 정보를 검색하세요.
select EMPNO, ENAME, HIREDATE, DEPTNO
from emp
-- 모든 비교연산자의 제일 먼저 떠올려야 하는 연산자 BETWEEN
WHERE hiredate BETWEEN 
TO_DATE('1980/12/17','YYYY/MM/DD')
AND
TO_DATE('1980/12/18','YYYY/MM/DD') - 1/86400;
-- 1/86400 1초를 빼라.

-- LIKE문 활용예제의 정답은 이거임
-- LIKE는 데이터 타입이 문자일때만 사용하라.
-- 날짜타입에 사용하면 성능 저하, 인덱스 사용안함, 올바른 문장이 아님.
-- HIREDATE LIKE '1980/12/17%';

-- 1980/12/18 자정까지도 검색됨. 그래서 정답문장이 아님.
-- where hiredate between '1980/12/17' and '1980/12/18'; 

-- ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; -- 날짜형식 시분초 추가.

-- 데이터 양식이 이렇게 검색했을때 검색자체가 안됨
-- HIREDATE LIKE '1980/12/17';

-- 인덱스를 안씀, 좋은 문장이 아님.
-- where TO_CHAR(HIREDATE,'YYYYMMDD') = '19801217';

• Oracle DB는 날짜를 저장할 때 시분초까지 저장한다.

• 정확한 데이터 출력을 위해 다음날 - 1초 해주면 정확한 범위의 날짜 데이터를 조회할 수있다.

• 날짜 연산을 해야할땐 무조건 BETWEEN부터 떠올릴 것!

•  LIKE 문장은 문자 타입일때만 사용할것 !!

 

# 함수 REGEXP_LIKE 사용예제.

• LIKE 여러문장으로 사용해야 할 때 사용하자!

SELECT prod_id, prod_name, list_price  
  FROM prods 
 WHERE REGEXP_LIKE(prod_name,'^(RAM|Monitor)') ;
-- 한줄에 LIKE 여러문장 비교하고 싶을때.
-- REGEXP_LIKE(prod_name,'^(RAM|Monitor)' 사용

1.  ORDER BY 절 사용법

# 한줄 요약.

• 문제의 요구사항을 정확하게 파악하는게 제일 중요하다!!!!!!!!!!!!!!!! 문제좀 제대로 읽자!!!!!!!

1.  ORDER BY 절 사용법

select *
   from emp
ORDER BY comm

• ORDER BY 절을 사용하여 검색된 행을 정렬합니다.

• SELECT 명령문의 가장 마지막 절로 사용합니다.

• 하나 이상의 컬럼 이름, 별칭 순서를 이용하여 정렬합니다.

• ASC : 오름차순, 기본값(안적으면 오름차순)

• DESC :  내림차순

-- asc 오름차순(기본값)
select *
from emp
order by comm;

-- desc 내림차순
select *
from emp
order by comm desc;

• DESC :  내림차순

SELECT * 
  FROM EMP 
 ORDER BY DEPTNO, SAL;
 -- DEPTNO 기준으로 1차 오름차순 정렬 후
 -- SAL 기준으로 2차 오름차순 정렬.
 
 -- 컬럼 이름을 쓰는게 귀찮다면 위치로도 가능.
 
 SELECT * 
  FROM EMP 
 ORDER BY 8 , 6 ;
-- 8번째 컬럼을 기준으로 1차 오름차순 정렬 후
-- 6번째 컬럼을 기준으로 2차 오름차순 정렬.

• 어떤 컬럼을 기준으로 먼저 Sorting 할 것인지 정해줄 수 있음.

SELECT * 
  FROM EMP 
ORDER BY COMM DESC NULLS FIRST;
-- NULL 값을 제일 먼저 출력해줘.

SELECT * 
  FROM EMP 
ORDER BY COMM ASC NULLS LAST;
-- COMM 컬럼의 NULL 값을 제일 나중에 출력해줘.

• 컬럼의 Null값 또한 지정하여 정렬가능.

• 조건 LMT_TYP가 = '1' 인것과 BRANCH 기준으로 내림차순 정렬 후, LN_DT 기준으로 오름차순으로 하라.

SELECT * 
  FROM TACCT
 WHERE LMT_TYP = '1' 
-- LMT_TYP 컬럼이 '1'(문자형 데이터)가 1인것중에
ORDER BY BRANCH DESC, LN_DT ASC ;
-- 1차적으로 BRANCH 컬럼의 데이터를 내림차순으로 정렬하고
-- 2차적으로 LN_DT 컬럼의 데이터를 오름차순으로 정렬하라.

 

2.  단일 행 함수 사용법.

# UPPER(컬럼명) : 대문자로 변경

# LOWER(컬럼명) : 소문자로 변경

SELECT last_name, UPPER(last_name), LOWER(last_name)
-- UPPER(last_name) : 컬럼의 데이터를 대문자로 변경
-- LOWER(last_name) : 컬럼의 데이터를 소문자로 변경
  FROM employees ;

 

# INITCAP(컬럼명) : 첫글자 대문자, 나머지 소문자 변환

-- INITCAP: 첫글자 대문자, 나머지 소문자로 변환 
SELECT email, INITCAP(email), job_id, INITCAP(job_id) 
  FROM employees ;

 

# LENGTH : 문자열의 길이 조회

-- LENGTH: 문자열의 길이 
SELECT job_id, LENGTH(job_id)
  FROM employees;

 

 

 

# SUBSTR: 문자열에서 지정 위치(position)부터 원하는 길이의 문자를 추출함 

  (substring_length 생략 시 마지막 문자까지 추출)

SELECT job_id, SUBSTR(job_id, 4,3)
            -- 인덱스 4번째 자리부터 3개 출력.
             , SUBSTR(job_id, 4)
            -- 인덱스 4번째 자리부터 마지막 문자까지 추출
             , SUBSTR(job_id, -2, 2)
            -- 뒤에서부터 2개
FROM employees;

 

# ROUND : 특정 데이터 반올림.

SELECT 45.926, ROUND(45.926, 2), ROUND(45.926, 0), ROUND(45.926, -1)
             -- 2번째에서 반올림   0번째에서 반올림.  절삭, 첫번째 자리에서 반올림.
  FROM dual ;
-- dual 테이블 : 간단한 1개의 컬럼의 데이터를 출력할때 사용!!

ROUND 함수 결과.

 

# 날짜 데이터의 조건값을 구하기 위해서 형식을 변경할 때 사용하는 명령어

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; -- 날짜형식 시분초 추가.
ALTER SESSION SET nls_date_format = 'YYYY/MM/DD' ; -- 시간 부분은 숨겨짐.

 

# 날짜 연산 예제.

--날짜 연산: 날짜 ± 숫자 => 날짜 (DATE : YYYY/MM/DD HH:MI:SS)
SELECT SYSDATE, SYSDATE + 1, SYSDATE + 1/24, SYSDATE + 1/1440, SYSDATE + 1/86400
                   -- + 1일          1시간          1분을             1초
  FROM dual ;
  
 SELECT SYSDATE, SYSDATE - 1, SYSDATE - 1/24, SYSDATE - 1/1440, SYSDATE - 1/86400
  FROM dual ;
  
  --날짜 연산: 날짜 - 날짜 => 숫자 (일 수) 

SELECT last_name, hire_date, SYSDATE - hire_date 
                        -- 현재 날짜 - hire_date = 일수.시간
  FROM employees ;

날짜 - 날짜 =  날짜일수

 

# 날짜 연산 활용.

SELECT last_name, hire_date, trunc(SYSDATE - hire_date)
                            -- 입사 이후로 오늘까지 얼마나 지났는지.
  FROM employees ;

입사일 계산

 

# 날짜 가산은 할 수 없음(주의)

-- 날짜와 날짜의 가산은 할 수 없습니다
SELECT last_name, hire_date, SYSDATE + hire_date
  FROM employees ;

가산은 불가능!

# MONTHS_BETWEEN : 두 날짜 사이의 개월 수 계산.

--MONTHS_BETWEEN: 두 날짜 사이의 개월 수 
SELECT SYSDATE, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date)
  FROM employees ;

개월수(소수점은 시간인듯)

# ADD_MONTHS : 개월 수를 가감 연산.

SELECT ADD_MONTHS(SYSDATE,3), ADD_MONTHS(SYSDATE,-3)
    -- 현재 날짜 + 3개월, 현재날짜 - 3개월
  FROM dual; 

SELECT ADD_MONTHS(SYSDATE+1,3), ADD_MONTHS(SYSDATE+1,-3)
    -- 현재 날짜 + 1일, 3개월 이후, 현재 날짜 + 1일, 3개일 이전
  FROM dual ;

 

# LAST_DAY: 지정 일자가 속한 월의 마지막 날짜

SELECT LAST_DAY(SYSDATE)
  FROM dual ;

 

3. 데이터 타입에 따른 연산결과.

문자	VARCHAR2, CHAR	to_char
숫자	NUMBER	to_number
날짜	DATE	to_date

 

## TO_CHAR

# NUMBER 타입 1과, CHAR 타입의 차이점.

# DUMP 함수는 숫자1과 문자'1'이 내부적으로 움직이는 코드값을 조회한다.

-- 눈에보이는 숫자 1과 '1'의 내부적 구조 차이점
select DUMP(1), DUMP('1')
    from dual;

코드값 비교

# 자동 형변환, 연산자

• 같은 타입의 연산을 수행할 땐 자동으로 변환하여 계산해줌.

-- 자동형변환, 연산자
select '1' + '2'
from dual;

 

# 자동 형변환, 문자와 숫자는 연산안됨.

• 우리 이름을 숫자로 바꿀 수 없자나.

select 'A' +  1
from dual;

퍽

# 날짜 부분 형식별 문자 변환.

참고

SELECT SYSDATE
      ,to_char (SYSDATE,'YYYY') -- 오늘 날짜 'YYYY'을 (2022)를 문자로 변환한다.
      ,to_char(SYSDATE,'MM') -- 오늘 날짜의 'MM'을 (10)을 문자로 변환한다.
      ,to_char(SYSDATE,'DD') -- 오늘 날짜의 'DD'을 (2)을 문자로 변환한다.
  FROM DUAL ;

년 월 일 부분 문자 변환 출력

• YEAR 대문자,  Year 앞글자 대문자, 소문자 출력

SELECT SYSDATE
      ,to_char(SYSDATE,'YEAR') -- 전체 대문자.
      ,to_char(SYSDATE,'Year') -- 앞자리만 대문자
  FROM DUAL ;

변환 출력(2)

• Q, DAY, DY, W model 변환 출력.

SELECT SYSDATE 
      ,TO_CHAR(SYSDATE,'Q')
                   -- 분기값 형식 model
      ,TO_CHAR(SYSDATE,'DAY')
                   -- 요일값 형식 model
      ,TO_CHAR(SYSDATE,'DY')
                   -- 요일.
      ,TO_CHAR(SYSDATE,'W')
                   -- n주차값 형식 model
  FROM DUAL ;

 

변환 출력(3)

 

• 천 단위 구분자 출력.소수점자리 출력.

SELECT LN_AMT
      ,TO_CHAR(LN_AMT, '999,999,999,999.00')
      ,TO_CHAR(LN_AMT, '000,000,000,000.00')
  FROM TACCT;

 

• $, L(원화) 통화 기호 표시법.

SELECT LN_AMT
    ,TO_CHAR(LN_AMT, '$999,999,999.00')
    ,TO_CHAR(LN_AMT, 'L999,999,999.00')
  FROM TACCT;

<추가>

통화 기호 설정 보는곳 / 환경설정

 

## TO_NUMBER

• 숫자, 날짜 -> 문자 : TO_CHAR
• TO_NUMBER, 문자 (0~9) -> 숫자

 

# 이 문장은 실행 될 수 없음. 

왜? 암시적인 형변환을 수행할 수 없음. $와 , 

SELECT '$1,500' + '$2,000'  -- ERROR 
  FROM DUAL ;

퍽

• TO_NUMBER을 사용해서 변환해서 사용해야함.

SELECT TO_NUMBER('$1,500','$999,999') + TO_NUMBER('$2,000','$999,999')
  FROM DUAL;

good

 

# ''묶여있는 것은 모두 CHAR 문자형이다.

-- DATE까요 CHAR일까요?
-- 바로바로바로바로 CHAR 입니다.
select '2022/09/29'
from dual;

SELECT '2022/09/29' 
        -- 나만 날짜라고 생각하는거임
      ,TO_DATE('2022/09/29','YYYY/MM/DD')
        -- 날짜가 되려면 TO_DATE 사용.
  FROM DUAL ;

 

# 하지만 비교 대상에 컬럼이 있는 경우는 암시적으로 형변환을 통해 DATE 형을 자동 형변환 된다.

SELECT * 
  FROM EMP 
 WHERE HIREDATE = '1980/12/17' ;

 

# 사칙 연산을 수행할 때 가장 먼저 형변환 실행되는  NUMBER!!,

• '2022/09/29' 에서 /가 숫자가 아니라서 오류, 날짜 연산에는 무조건 TO_DATE 사용을 습관화 하라!

SELECT '2022/09/29' + 1
  FROM DUAL ;
  
  -- 날짜 타입 데이터에 + 1일 하는법
SELECT TO_DATE('2022/09/29','YYYY/MM/DD') + 1 
  FROM DUAL ;

 

# NULL 상태의 값을 치환하고 싶은 값 설정해주는 법.

select empno, ename, sal, comm, nvl(comm, 0), -- null 상태의 값을 치환하고 싶은 값 0
    sal + nvl(comm, 0)
from emp;

NULL 값 치환.

 

# 불필요한 함수를 사용하면 성능이 떨어진다.

•  왜? 단일행 함수에선 1개만 실행하기 때문에 상관없지만 1억개의 데이터에서 사용하면 어어어어어엄청 경과시간 낭비.

select *
from emp
order by nvl(comm, -1) desc;

 

4. CASE 식.

• IF-THEN-ELSE 문 작업을 수행하여 조건부 조회를 편리하게 수행하도록 한다.

 

# 조건 1  : EMP 테이블에서 모든 사원 검색, SAL * 1.1 하라.

# 조건 2  : EMP 테이블에서 직급별로 영업부는 

--case 문은 다양한 조건 평가를 할 수 있음.
select empno, ename, job, sal
    ,CASE job WHEN 'SALESMAN' THEN sal * 1.1
    -- WHEN 찾고자하는 절, THEN 처리하고자 하는 수식
              WHEN 'MANAGER'  THEN sal * 1.2
                         -- 모두 아니라면 ELSE
                              ELSE sal * 1.3
      -- CASE 문 끝맺음, END
    END AS NEW_SAL
      -- 이 만큼을 NEW_SAL이라고 별칭을 줌.
from emp;

CASE 결과(1)

# 조건 1  : EMP 테이블의 급여가 3000 이상은 상으로, 2000 이상은 중, 이것도 저것도 아니면 하 출력하시오.

select empno, ename, sal,
        case when sal >= 3000 then '상'
            -- WHEN 절에는 조건
             when sal >= 2000 then '중'
                              else '하'
        end as 등급
from emp;

CASE 결과(2)

# 조건 1 : 고정 금리는 1번, 변동 금리는 2번

SELECT lnact, lnact_seq, ln_amt, rate, rate_typ,
CASE WHEN rate_typ = '1' 
THEN TRUNC((ln_amt * rate)/12) -- 고정금리
WHEN rate_typ = '2' 
THEN TRUNC((ln_amt * (rate+0.01))/12) -- 변동금리
ELSE 0 
END AS 이자
FROM tacct
WHERE lmt_typ IS NULL ;

CASE 결과(3)

 

5. GROUP BY 절을 이용한 그룹 생성

• GROUP BY절은 기본적으로 NULL 값을 제외하고 출력, 연산한다.

 

# 그룹 함수 유형

• SUM = 더하기

• AVG = 평균

• MAX = 큰값

• MIN = 작은 값

• COUNT = 카운트

SELECT SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL), COUNT(SAL)
  FROM EMP ;

작은값

# 잘못 사용된 예.

SELECT SUM(ENAME)   -- ERROR 이름을 더할 수 있어?
  FROM EMP ; 
SELECT AVG(HIREDATE) -- ERROR 날짜로 평균을 구할 수 있어?
  FROM EMP ;

# 이건 가능

SELECT MAX(ENAME), MAX(HIREDATE)
   -- 마지막 사원, 가장 최근 날짜
  FROM EMP ;

 

# COUNT 함수는 *로 컬럼 지정이 가능함.

SELECT COUNT(*), COUNT(EMPNO)
  FROM EMP ;
  
SELECT COUNT(EMPNO), COUNT(COMM)
                   -- null 값 제외
  FROM EMP ;

 

# COMM 컬럼의 데이터의 합 가능, NULL 값은 포함안됨.

SELECT SUM(COMM)
  FROM EMP ;

 

# 현재 요구 사항이 어떤가에 따라서 결과 값이 달라짐.

SELECT AVG(NVL(COMM,0)), SUM(COMM)/14
   -- null이 계산에 참여해야 하면 바꿔줘야 함.
  FROM EMP ; 

SELECT AVG(COMM), SUM(COMM)/4
  FROM EMP ;

 

# 컬럼 중복값 제거 후 조회하기(DISTINCT 사용)

SELECT COUNT(DISTINCT DEPTNO)
  FROM EMP ; 

SELECT SUM(DISTINCT DEPTNO)
  FROM EMP ;

 

# GROUP BY 절 예제1

SELECT DEPTNO, SUM(SAL) 
  FROM EMP 
 GROUP BY DEPTNO ;

예제1

 

# GROUP BY 절 예제2

select deptno, job, sum(sal) 
from emp
group by deptno, job;

예제2

 

# GROUP BY 절 예제3

select  branch, sum(ln_amt)
from tacct
where lmt_typ is null
group by branch;

예제3

 

# GROUP BY 절 예제4

SELECT BRANCH, PROD_CD, SUM(LN_AMT)
  FROM TACCT 
 WHERE LMT_TYP IS NULL 
 GROUP BY BRANCH, PROD_CD ;

예제4

 

# 그룹 함수 정리

SELECT BRANCH, PROD_CD, SUM(LN_AMT)
                     -- 나머지 행은 그룹 함수 함께 써야한다.
  FROM TACCT 
 WHERE LMT_TYP IS NULL 
 GROUP BY BRANCH, PROD_CD ;
         -- 같아야함

 

# HAVING 절 사용법

SELECT DEPTNO, SUM(SAL)  
  FROM EMP 
GROUP BY DEPTNO 
HAVING SUM(SAL) > 9000;
     -- SUM(SAL) 9000 보다 큰거 검색.
     -- 그룹 함수를 이용하는 조건식을 사용할 때 쓴다.

 

6. SQL 문장해석 순서(완성)

-- 5 SELECT   : 검색 대상의 표현식 (컬럼 이름, 계산식)
-- 1 FROM     : 검색 대상 집합 정의 (테이블, 서브쿼리, 뷰) 
-- 2 WHERE    : 행 제한을 위한 조건식 
-- 3 GROUP BY : 그룹 생성을 위한 표현식 
-- 4 HAVING   : 그룹 제한을 위한 조건식 
-- 6 ORDER BY : 정렬

SELECT DEPTNO, SUM(SAL)  
  FROM EMP 
     -- 어디서? EMP 테이블에서
 WHERE DEPTNO IN (10,20)
     -- 무엇을? DEPTNO 컬럼의 10번과 20번을
GROUP BY DEPTNO 
     -- SUM(SAL)을 나머지 행을 사용하기 위해 DEPTNO 그룹핑
HAVING SUM(SAL) > 9000;     
     -- 그룹한 DEPTNO 컬럼의 SUM(SAL) 합이 9000보다 큰거.