Posted Updated  minkuen SQL / oracle12 minutes read (About 1854 words)

Oracle_practice6

집계함수

sql developer

  • sql developer에서 새로운 sql 워크시트를 생성한다.

    도구 → sql워크시트 : ch05_0427

  • 오라클 SQL과 PL/SQL을 다루는 기술

COUNT

  • COUNT (expr)
  • COUNT는 쿼리 결과 건수, 즉 전체 로우 수를 반환하는 집계 함수다.
  • 테이블 전체 로우는 물론 WHERE 조건으로 걸러진 로우 수를 반환한다.
  • 대부분은 COUNT() 형태로 사용하는데, ‘’ 대신 컬럼명을 넣기도 한다.
1
2
3
4
5
-- 152p
-- 기본 집계 함수
-- Count(expr) 쿼리 결과 건수, 전체 로우 수 반환 (행 갯수)
SELECT count(*) FROM employees;
SELECT count(employee_id) FROM employees; -- 107

Untitled

DISTINCT

  • DISTINCT를 붙이면 뒤따라 나오는 컬럼에 있는 유일한 값만 조회된다.
1
2
3
4
5
6
-- DISTINCT
SELECT count(DISTINCT department_id) FROM employees;

SELECT DISTINCT department_id
FROM employees
ORDER BY 1;

Untitled

SUM

  • SUM(expr)
  • UM은 expr의 전체 합계를 반환하는 함수로 매개변수 expr에는 숫자형만 올 수 있다.
1
2
3
4
5
6
7
-- SUM(expr)
-- 합계 구하기
SELECT SUM(salary) FROM employees;
SELECT
    SUM(salary)
    , SUM(DISTINCT salary)
FROM employees;

Untitled

AVG

  • AVG(expr)
  • AVG는 매개변수 형태나 쓰임새는 COUNT, SUM과 동일하며 평균값을 반환한다.
1
2
3
4
5
-- AVG(expr)
SELECT
    AVG(salary)
    , AVG(DISTINCT salary)
FROM employees;

Untitled

MIN, MAX

  • MIN(expr)
    • MIN은 최솟값을  반환한다.
  • MAX(expr)
    • MAX는 최댓값을 반환한다.
1
2
3
4
5
-- MIN, MAX
SELECT 
    MIN(salary)
    , MAX(salary)
FROM employees;

Untitled

VARIANCE, STDDEV

  • VARIANCE(expr)
    • VARIANCE는 분산을 구해 반환한다.
  • STDDEV(expr)
    • STDDEV는 표준편차를 구해 반환한다.
1
2
3
4
5
6
-- VARIANCE, STDDEV
-- 분산, 표준편차
SELECT
    VARIANCE(salary)
    , STDDEV(salary)
FROM employees;

Untitled

GROUP BY / HAVING

  • GROUP BY
    • 전체가 아닌 특정 그룹으로 묶어 데이터를 집계할 때 사용되는 구문이다.
    • 그룹으로 묶을 컬럼명이나 표현식을 GROUP BY 절에 명시해서 사용한다.
    • GROUP BY 구문은 WHERE와 ORDER BY절 사이에 위치한다.
1
2
3
4
5
6
SELECT 
    department_id
    , SUM(salary)
FROM employees
GROUP BY department_id
ORDER BY department_id;

Untitled

  • HAVING
    • GROUP BY절 다음에 위치해 GROUP BY한 결과를 대상으로 다시 필터를 거는 역할을 수행.
    • HAVING 필터 조건 형태로 사용한다.
1
2
3
4
5
6
7
8
9
SELECT 
    period
    , region
    , SUM(loan_jan_amt) totl_jan
FROM kor_loan_status
WHERE period = '201311'
GROUP BY period, region
HAVING SUM(loan_jan_amt) > 100000
ORDER BY region;

Untitled

ROLLUP

  • ROLLUP(expr1, expr2, …)
  • ROLLUP은 expr로 명시한 표현식을 기준으로 집계한 결과, 즉 추가적인 집계 정보를 보여 준다.
  • ROLLUP 절에 명시할 수 있는 표현식에는 그룹핑 대상, 즉 SELECT 리스트에서 집계 함수를 제외한 컬럼 등의 표현식이 올 수 있다.
  • 명시한 표현식 수와 순서(오른쪽에서 왼쪽 순으로)에 따라 레벨 별로 집계한 결과가 반환된다.
  • 표현식 개수가 n개이면 n+1 레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계된다.
1
2
3
4
5
6
7
8
9
-- 2013년 1개월 총 잔액만 구한다.
-- ROLLUP : 소그룹간의 합계를 계산함
SELECT 
    period
    , gubun
    , SUM(loan_jan_amt) totl_jan
FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY ROLLUP(period, gubun);

Untitled

CUBE

  • CUBE(expr1, expr2, …)
  • ROLLUP과 CUBE는 GROUP BY절에서 사용되어 그룹별 소계를 추가로 보여 주는 역할을 한다.
  • ROLLUP이 레벨별로 순차적 집계를 했다면, CUBE는 명시한 표현식 개수에 따라 가능한 모든 조합별로 집계한 결과를 반환한다.
  • CUBE는 2의(expr 수)제곱 만큼 종류별로 집계 된다.
    • 예를 들어, expr 수가 3이면, 집계 결과의 유형은 총 2^3 = 8개가 된다.
1
2
3
4
5
6
7
8
-- CUBE
SELECT 
    period
    , gubun
    , SUM(loan_jan_amt) totl_jan
FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY CUBE(period, gubun);

Untitled

GROUPING SETS

  • GROUPING SETS (expr1, expr2, expr3)
  • ROLLUP이나 CUBE처럼 GROUP BY 절에 명시해서 그룹 쿼리에 사용되는 절이다.
  • GROUP BY 절에 명시했을 때, 괄호 안에 있는 세 표현식별로 각각 집계가 이루어진다.
    • 그러므로 쿼리 형태는 다음과 같다.
    • ((GROUP BY expr1) UNION ALL (GROUP BY expr2) UNION ALL (GROUP BY expr3))
1
2
3
4
5
6
7
8
9
-- GROUPing SETS
-- 특정항목에 대한 소계를 계산하는 함수
SELECT
    period
    , gubun
    , SUM(loan_jan_amt) totall
FROM kor_loan_status
WHERE period LIKE'2013%'
GROUP BY GROUPING SETS(period, gubun);

Untitled

집합 연산자

  • 집합(Set) 연산자는 데이터 집합을 대상으로 연산을 수행하는 연산자아더,
  • UNION, UNION ALL, INTERSECT, MINUS 등이 있다.
  • 다음 데이터로 집합 연산자를 사용해본다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE exp_goods_asia (
       country VARCHAR2(10),
       seq     NUMBER,
       goods   VARCHAR2(80));
       
INSERT INTO exp_goods_asia VALUES ('한국', 1, '원유제외 석유류');
INSERT INTO exp_goods_asia VALUES ('한국', 2, '자동차');
INSERT INTO exp_goods_asia VALUES ('한국', 3, '전자집적회로');
INSERT INTO exp_goods_asia VALUES ('한국', 4, '선박');
INSERT INTO exp_goods_asia VALUES ('한국', 5,  'LCD');
INSERT INTO exp_goods_asia VALUES ('한국', 6,  '자동차부품');
INSERT INTO exp_goods_asia VALUES ('한국', 7,  '휴대전화');
INSERT INTO exp_goods_asia VALUES ('한국', 8,  '환식탄화수소');
INSERT INTO exp_goods_asia VALUES ('한국', 9,  '무선송신기 디스플레이 부속품');
INSERT INTO exp_goods_asia VALUES ('한국', 10,  '철 또는 비합금강');

INSERT INTO exp_goods_asia VALUES ('일본', 1, '자동차');
INSERT INTO exp_goods_asia VALUES ('일본', 2, '자동차부품');
INSERT INTO exp_goods_asia VALUES ('일본', 3, '전자집적회로');
INSERT INTO exp_goods_asia VALUES ('일본', 4, '선박');
INSERT INTO exp_goods_asia VALUES ('일본', 5, '반도체웨이퍼');
INSERT INTO exp_goods_asia VALUES ('일본', 6, '화물차');
INSERT INTO exp_goods_asia VALUES ('일본', 7, '원유제외 석유류');
INSERT INTO exp_goods_asia VALUES ('일본', 8, '건설기계');
INSERT INTO exp_goods_asia VALUES ('일본', 9, '다이오드, 트랜지스터');
INSERT INTO exp_goods_asia VALUES ('일본', 10, '기계류');

COMMIT;

UNION

  • UNON은 합집합을 의미한다.
  • 예를 들어, 두 데이터 집합이 있으면 각 집합 원소(SELECT 결과)를 모두 포함한 결과가 반환된다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- UNION
-- 합집합을 의미한다.
-- 두 개의 데이터 집합에서 출발
SELECT goods
FROM exp_goods_asia
WHERE country = '한국'
ORDER BY seq;

SELECT goods
FROM exp_goods_asia
WHERE country = '일본'
ORDER BY seq;

SELECT goods
FROM exp_goods_asia
WHERE country = '한국'
UNION -- 합집합 개념 적용
SELECT goods
FROM exp_goods_asia
WHERE country = '일본';
  • 중복된 항목 5개를 제외한 15개가 출력되었다.

Untitled

UNION ALL

  • UNION ALL은 UNION과 비슷한데 한 가지 다른 것은 중복된 항목도 모두 조회된다는 점이다.
1
2
3
4
5
6
7
8
9
-- UNION ALL

SELECT goods
FROM exp_goods_asia
WHERE country = '한국'
UNION ALL-- 합집합 개념 적용
SELECT goods
FROM exp_goods_asia
WHERE country = '일본';
  • 중복까지 포함하여 20개의 행의 출력되었다.

Untitled

INTERSECT

  • INTERSECT는 합집합이 아닌 교집합을 의미한다.
  • 즉 데이터 집합에서 공통된 항목만 추출해 낸다.
1
2
3
4
5
6
7
8
9
-- INTERSECT
-- 합집합이 아니라 교집합을 의미한다.
SELECT goods
FROM exp_goods_asia
WHERE country = '한국'
INTERSECT -- 교집합 개념 적용
SELECT goods
FROM exp_goods_asia
WHERE country = '일본';

Untitled

MINUS

  • MINUS는 차집합을 의미한다.
  • 즉 한 데이터 집합을 기준으로 다른 데이터 집합과 공통된 항목을 제외한 결과만 추출해 낸다.
1
2
3
4
5
6
7
8
9
-- MINUS
-- 차집합을 의미한다.
SELECT goods
FROM exp_goods_asia
WHERE country = '한국'
MINUS -- 차집합 개념 적용
SELECT goods
FROM exp_goods_asia
WHERE country = '일본';

Untitled

  • Reference : 오라클 SQL과 PL/SQL을 다루는 기술
Posted Updated  minkuen SQL / oracle18 minutes read (About 2651 words)

Oracle_practice5

SQL 함수

sql developer

  • sql developer에서 새로운 sql 워크시트를 생성한다.

    도구 → sql워크시트 : ch04_0427

  • 오라클 SQL과 PL/SQL을 다루는 기술

ABS 함수

  • 교재를 참고하여 코드를 익혀보자
  • ABS 함수는 매개변수로 숫자를 받아 그 절대값을 반환하는 함수다.
  • ABS는 다음과 같이 사용한다.
1
2
3
4
5
6
7
8
9
-- SQL 함수 살펴 보기
-- ABS

SELECT 
    ABS(10)
    , ABS(-10) 
    , ABS(10.123)
    , ABS(-10.123)
FROM DUAL;

Untitled

CEIL & FLOOR 함수

  • CEIL 함수는 매개변수 n과 같거나 가장 큰 정수를 반환한다.
  • FLOOR 함수는 CEIL 함수와는 반대로 매개변수 n보다 작거나 가장 큰 정수를 반환한다.
1
2
3
4
5
6
7
-- CELL & FLOOR
SELECT
    CEIL(10.123)
    , CEIL(-10.123)
    , FLOOR(10.123)
    , FLOOR(-10.213)
FROM DUAL;

Untitled

ROUND 함수

  • ROUND(n, i)
  • ROUND 함수는 매개변수 n을 소수점 기준 (i+1)번 째에서 반올림한 결과를 반환한다.
  • i는 생략할 수 있고 디폴트 값은 0이다.
    • 즉 소수점 첫 번째 자리에서 반올림이 일어나 정수 부분의 일의 자리에 결과가 반영된다.
1
2
3
4
5
6
SELECT
    ROUND(10.154, 1)
    , ROUND(10.151, 2)
    , ROUND(-10.154, 1)
    , ROUND(-10.151, 2)
FROM DUAL;

Untitled

TRUNC 함수

  • TRUNC(n1, n2)
  • TRUNC 함수는 반올림을 하지 않고 n1을 소수점 기준 n2자리에서 잘라낸 결과를 반환한다.
  • n2 역시 생략할 수 있으며 디폴트 값은 0이다.
  • 양수일 때는 소수점 기준으로 오른쪽, 음수일 때는 소수점 기준 왼쪽 자리에서 잘라낸다.
1
2
3
4
5
6
7
8
-- TRUNC
-- 반올림 안함. 소수점 절삭
SELECT
    TRUNC(115.155)
    , TRUNC(115.155, 1)
    , TRUNC(115.155, 2)
    , TRUNC(115.155, -2)
FROM DUAL;

Untitled

POWER & SQRT

  • POWER(n2, n1)
    • POWER 함수는 n2를 n1 제곱한 결과를 반환한다.
    • n1은 정수와 실수 모두 올 수 있는데, n2가 음수일 때 n1은 정수만 올 수 있다.
  • SQRT(n)
    • SQRT 함수는 n의 제곱근을 반환한다.
1
2
3
4
5
6
7
8
9
-- 128p
-- POWER(n2, n1) SQRT(n)
-- 제곱 & 제곱근
SELECT
    POWER(3, 2)
    , POWER(3, 3)
    , SQRT(9)
    , SQRT(8)
FROM DUAL;

Untitled

MOD & REMAINDER

  • MOD(n2, n1)
    • MOD 함수는 n2를 n1으로 나눈 나머지 값을 반환한다.
  • REMAINDER(n2, n1)
    • REMAINDER 함수 역시 n2를 n1으로 나눈 나머지 값을 반환한다.
    • 나머지를 구하는 내부적 연산 방법이 MOD 함수와는 약간 다르다.
1
2
3
4
5
6
-- MOD(n2, n1)와 REMAINDER(n2, n1)
-- MOD 함수는 n2를 n1으로 나눈 나머지 값 반환
SELECT
    MOD(19,4)
    , REMAINDER(19,4)
FROM DUAL;

Untitled

EXP, LN, LOG

  • EXP(n)
    • EXP는 지수 함수로 e(e=2.71828183…)의 n제곱 값을 반환한다.
  • LN(n)
    • LN 함수는 자연 로그 함수로 밑수가 e인 로그 함수다.
  • LOG(n2, n1)
    • LOG는 n2를 밑수로 하는 n1의 로그 값을 반환한다.
1
2
3
-- EXP, LN, LOG
SELECT EXP(2), LN(2.713), LOG(10,100)
FROM DUAL;

Untitled

문자 함수

CONCAT

  • CONCAT(char1, char2)
  • CONCAT함수는 ‘||’ 연산자처럼 매개변수로 들어오는 두 문자를 붙여 반환한다.
1
2
3
4
5
6
-- 문자 함수
-- CONCAT :  '||' 연산자처럼 두 문자를 붙여 반환
SELECT
    CONCAT('I Have', ' A Dream'),
    'I Have' || ' A Dream'
FROM DUAL;

Untitled

SUBSTR

  • 주로 쓰이는 중요한 함수이다.
  • SUBSTR(char, pos, len)
  • 잘라올 대사 문자열인 char의 pos번째 문자부터 len길이만큼 잘라낸 결과를 반환하는 함수다.
  • pos 값으로 0이 오면 디폴트 값인 1, 즉 첫 번째 문자를 가리키며, 음수가 오면 char 문자열 맨 끝에서 시작한 상대적 위치를 의미한다.
  • 또한 len 값이 생략되면 pos번째 문자부터 나머지 모든 문자를 반환한다.
1
2
3
4
5
6
-- SUBSTR (**** 중요함 ****)
-- 문자 개수 단위로 문자열 자름
SELECT 
    SUBSTR('ABCDEFG', 1, 4)
    , SUBSTR('ABCDEFG', -3, 4)
FROM DUAL;

Untitled

SUBSTRB

  • SUBSTRB(char, pos, len)
  • 문자열의 바이트 수만큼 출력한다.
  • SUBSTRB는 문자 개수가 아닌 문자열의 바이트(BYTE) 수만큼 잘라낸 결과를 반환한다
  • 나머지 처리 로직은 SUBSTR과 같다.
1
2
3
4
5
6
-- SUBSTRB
-- 문자열의 바이트 수만큼
SELECT 
    SUBSTRB('ABCDEFG', 1, 4)
    , SUBSTRB('가나다라마바사', 1, 4)
FROM DUAL;

Untitled

LTRIM, RTRIM

  • LTRIM(char, set),
    • LTRIM 함수는 매개변수로 들어온 char 문자열에서 set으로 지정된 문자열을 왼쪽 끝에서 제거한 후 나머지 문자열을 반환한다.
    • 두 번째 매개변수인 set은 생략할 수 있으며, 디폴트로 공백 문자 한 글자가 사용된다.
  • RTRIM(char, set)
    • RTRIM 함수는 LTRIM 함수와 반대로 오른쪽 끝에서 제거한 뒤 나머지 문자열을 반환한다.
1
2
3
4
5
6
7
-- LTRIM(char, set), RTRIM(char, set)
SELECT
    LTRIM('ABCDEFGABC', 'ABC')
    , LTRIM('가나다라', '가')
    , RTRIM('ABCDEFGABC', 'ABC')
    , RTRIM('가나다라', '라')
FROM DUAL;

Untitled

LPAD, RPAD

  • LPAD(expr1, n, expr2)
    • LPAD 함수는 매개변수로 들어온 expr2 문자열(생략할 때 디폴트는 공백 한 문자)을 n자리만큼 왼쪽부터 채워 expr1을 반환하는 함수다.
    • 매개변수 n은 expr2와 expr1이 합쳐져 반환되는 총 자릿수를 의미한다.
    • 예를 들어, 서울의 지역 전화번호는 ‘02’인데 전화번호 컬럼에 지역번호가 없으면 LPAD 함수로 번호 02를 자동으로 채워 넣을 수 있다.
  • RPAD(expr1, n, expr2)
    • RPAD는 LPAD와는 반대로 오른쪽에 해당 문자열을 채워 반환한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- LPAD, RPAD
-- 무언가를 입력해준다.
CREATE TABLE ex4_1 (
    phone_num VARCHAR2(30)
);

INSERT INTO ex4_1 VALUES('111-1111');
INSERT INTO ex4_1 VALUES('222-2222');
INSERT INTO ex4_1 VALUES('333-3333');

SELECT * FROM ex4_1;

-- (02)를 덧붙인다.
SELECT LPAD(phone_num, 12, '(02)') FROM ex4_1;
SELECT RPAD(phone_num, 12, '(02)') FROM ex4_1;

Untitled

날짜 함수

  • 날짜 함수는 DATE 함수나 TIMESTAMP 함수와 같은 날짜형을 대상으로 연산을 수행해 결과를 반환하는 함수다.

SYSDATE, SYSTIMESTAMP

  • SYSDATE와 SYSTIMESTAMP는 현재 일자와 시간을 각각 DATE, TIMESTAMP 형으로 반환한다.
1
2
3
4
SELECT 
    SYSDATE
    , SYSTIMESTAMP
FROM DUAL;

Untitled

ADD_MONTHS

  • ADD_MONTHS (date, integer)
  • ADD_MONTHS 함수는 매개변수로 들어온 날짜에 interger 만큼의 월을 더한 날짜를 반환한다.
1
2
3
4
5
-- ADD_MONTHS
SELECT
    ADD_MONTHS(SYSDATE, 1)
    , ADD_MONTHS(SYSDATE, -1)
FROM DUAL;

Untitled

MONTHS_BETWEEN

  • MONTHS_BETWEEN(date1, date2)
  • MONTHS_BETWEEN 함수는 두 날짜 사이의 개월 수를 반환한다.
    • date2가 date1보다 빠른 날짜가 온다.
1
2
3
4
5
-- MONTHS_BETWEEN
SELECT
    MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(SYSDATE, 1)) mon1
    , MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, 1), SYSDATE) mon2
FROM DUAL;

Untitled

LAST_DAY

  • LAST_DAY(date)
  • LAST_DAY는 date 날짜를 기준으로 해당 월의 마지막 일자를 반환한다.
1
2
3
4
5
6
-- LAST_DAY
-- 해당 달의 마지막 날
SELECT 
    LAST_DAY(SYSDATE)
    , LAST_DAY(ADD_MONTHS(SYSDATE, 1))
FROM DUAL;

Untitled

ROUND & TRUNC

  • ROUND와 TRUNC는 숫자 함수이면서 날짜 함수로도 쓰인다.
  • ROUND(date, format)
    • ROUND는 format에 따라 반올림한 날짜를 반환한다.
  • TRUNC(date, format)
    • TRUNC는 잘라낸 날짜를 반환한다.
1
2
3
4
5
6
7
-- ROUND(date, format)
-- TRUNC(date, format)
SELECT 
    SYSDATE
    , ROUND(SYSDATE, 'month')
    , TRUNC(SYSDATE, 'month')
FROM DUAL;

Untitled

NEXT_DAY

  • NEXT_DAY (date, char)
  • NEXT_DAY는 date를 char에 명시한 날짜로 다음 주 주중 일자를 반환한다.
1
2
3
-- NEXT_DAY (date, char)
SELECT NEXT_DAY(SYSDATE, '금요일')
FROM DUAL;

Untitled

TO_CHAR

  • TO_CHAR (숫자 혹은 날짜, format)
  • 숫자나 날짜를 문자로 변환해 주는 함수가 바로 TO_CHAR로, 매개변수로는 숫자나 날짜가 올 수 있고 반환 결과를 특정 형식에 맞게 출력할 수 있다.
1
2
3
4
5
6
7
-- 140p
-- 형변환 
SELECT TO_CHAR(123456789, '999,999,999')
FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL;
  • 123456789를 문자로 변환

Untitled

  • 날짜 함수를 이용해 당일 날짜를 출력

Untitled

  • 매개변수로 오는 숫자나 날짜에 따라 자주 사용하는 포맷을 정리하면 다음과 같다.

Untitled

Untitled

TO_NUMBER

  • TO_NUMBER(expr, format)
  • 문자나 다른 유형의 숫자를 NUMBER 형으로 변환하는 함수다.
1
2
3
4
-- TO_NUMBER(expr, format)
-- 문자나 다른 유형의 숫자를 NUMBER 형으로 변환하는 함수
SELECT TO_NUMBER('123456')
FROM DUAL;

Untitled

TO_DATE, TO_TIMESTAMP

  • TO_DATE(char, format)
    • 문자를 날짜형으로 변환하는 함수다.
    • TO_DATE는 DATE 형으로 변환해 값을 반환한다.

Untitled

  • TO_TIMESTAMP(char, format)
    • 문자를 날짜형으로 변환하는 함수다.
    • TO_TIMESTAMP는 TIMESTAMP 형으로 변환해 값을 반환한다.
1
2
SELECT TO_DATE('20220427 14:07:20', 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL;

Untitled

NULL

  • NULL을 비교할 때는 IS NULL이나 IS NOT NULL 구문을 사용하였다
  • 오라클에서는 NULL을 연산 대상으로 처리하는 SQL 함수를 제공하고 있다
1
2
3
4
5
-- NULL 관련 함수
SELECT 
    NVL(manager_id, employee_id)
FROM employees
WHERE manager_id IS NULL;

Untitled

NLV

  • NVL(expr1, expr2)
  • NVL함수는 expr1이 NULL일 때 expr2를 반환한다.

NLV2

  • NVL2((expr1, expr2, expr3)

  • NVL2는 NVL을 확장한 함수로 expr1이 NULL이 아니면 expr2를,

    NULL이면 expr3를 반환하는 함수다.

1
2
3
4
5
6
7
8
9
10
-- NVL2(expr1, expr2, expr3)
-- expr1이 NULL이 아니면 expr2 실행하라
-- expr1이 NULL이면 expr3을 실행하라
DESC employees;
SELECT
    employee_id
    , NVL2(commission_pct
        , salary + (salary + commission_pct)
        , salary) as final_salary
FROM employees;

Untitled

COALESCE

  • COALESCE (expr1, expr2, …)
  • COALESCE는 매개변수로 들어오는 표현식에서 NULL이 아닌 첫 번째 표현식을 반환하는 함수다.
1
2
3
4
5
6
7
8
-- COALESCE(expr1, expr2, ...)
-- 매개변수로 들어오는 표현식에서 NULL이 아닌 첫번째 표현식 반환
SELECT
    employee_id
    , salary
    , commission_pct
    , COALESCE(salary * commission_pct, salary) AS salary2
FROM employees;

Untitled

LNNVL

  • LNNVL(조건식)
  • LNNVL은 매개변수로 들어오는 조건식의 결과가 FALSE나 UNKNOWN
    이면 TRUE를, TRUE이면 FALSE를 반환한다.
1
2
3
4
5
6
SELECT 
    -- count(*) -- 행 갯수 반환
    employee_id
    , commission_pct
FROM employees
WHERE LNNVL(commission_pct >= 0.2);

Untitled

NULLIF

  • NULLIF (expr1, expr2)
  • NULLIF 함수는 expr1과 expr2을 비교해 같으면 NULL을, 같지 않으면 expr1을 반환한다.
1
2
3
4
5
6
7
-- NULLIF
SELECT
    employee_id
    , TO_CHAR(start_date, 'YYYY') start_year
    , TO_CHAR(end_date, 'YYYY') end_year
    , NULLIF(TO_CHAR(end_date, 'YYYY'), TO_CHAR(start_date, 'YYYY'))
FROM job_history;

Untitled

DECODE

  • DECODE (expr, search1, result1, search2, result2, …, default)
  • DECODE는 expr과 search1을 비교해 두 값이 같으면 result1을, 같지 않으면 다시 search2와 비교해 값이 같으면 result2를 반환하고, 이런 식으로 계속 비교한 뒤 최종적으로 같은 값이 없으면 default 값을 반환한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
-- DECODE (IF-ELIF-ELIF-ELIF-ELSE)
SELECT UNIQUE(channel_id) FROM sales;

SELECT 
    prod_id
    , channel_id
    , DECODE(channel_id, 3, 'Direct', 
                         9, 'Direct', 
                         5, 'Indirect', 
                         4, 'Indirect', 
                            'Others') decodes
FROM sales
WHERE prod_id = 125;

Untitled

  • Reference : 오라클 SQL과 PL/SQL을 다루는 기술
Posted Updated  minkuen SQL / oracle8 minutes read (About 1264 words)

Oracle_practice4

조건식

sql developer

  • sql developer에서 새로운 sql 워크시트를 생성한다.

    도구 → sql워크시트 :

  • 오라클 SQL과 PL/SQL을 다루는 기술 92p

연산자

  • 교재를 참고하여 코드를 익혀보자
  • 연산자는 다음과 같이 사용한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 정리
-- 테이블 생성 : CREATE
-- 테이블 변경 (컬럼명, 데이터타입 등) : ALTER
-- 테이블 삭제 : DROP

-- 쿼리
-- SELECT (조회) FROM WHERE ORDER BY (...)
-- MERGE
-- 테이블 데이터 변경 : UPDATE SET
-- 테이블 특정 데이터 삭제 : DELETE

-- 연산자
DESC employees;

SELECT employee_id || '-' || emp_name AS employee_info
FROM employees;

Untitled

표현식

  • 표현식이란 한 개 이상의 값과 연산자, SQL 함수 등이 결합된 식이다.
  • 조건문은 다음과 같이 사용한다.
  • CASE WHEN 조건 THEN ELSE END
1
2
3
4
5
6
7
8
9
10
-- 표현식 (조건문)
-- CASE WHEN 조건 THEN ELSE END
SELECT 
    employee_id
    , salary
    , CASE  WHEN salary <= 5000 THEN 'C등급'
            WHEN salary > 5000 AND salary <= 15000 THEN 'B등급'
            ELSE 'A등급'
      END AS salary_grade
FROM employees;

Untitled

조건식

  • 조간 혹은 조건식은 한 개 이상의 표현식과 논리 연산자가 결합된 식으로 TRUE, FALSE, UNKNOWN 세 가지 타입을 반환한다.
  • 지금까지 SQL문을 학습하면서 WHERE절에서 사용했던 모든 조건이 바로 조건식에 포함된다.

비교 조건식

  • 비교 조건식 : ANY, SOME, ALL 키워드로 비교하는 조건식

ANY

  • ANY - OR 조건식을 사용해본다.
1
2
3
4
5
6
7
8
-- p.114
-- 비교 조건식 : ANY, SOME, ALL 키워드로 비교하는 조건식
-- ANY - OR
SELECT
    employee_id
    , salary
FROM employees
WHERE salary = 2000 or salary = 3000 or salary = 4000
  • ANY가 ‘아무것’이나 ‘하나’라는 뜻이 있으므로 위 코드는 salary(급여)가 2000이나 3000이나 4000 중 하나라도 일치하는 모든 사원을 추출한 것이다.
  • 하나라도 일치하면 추출하는 것이므로 ANY는 OR로 표현이 가능하다.

Untitled

ALL

  • ALL 조건문을 사용해본다.
  • ALL이므로 모든 조건을 만족하는 것만 추출한다.
  • ALL 조건식은 AND 조건으로 변환할 수 있다.
1
2
3
4
5
6
7
8
-- ALL
-- 모든 조건을 동시에 만족해야 함
SELECT
    employee_id
    , salary
FROM employees
WHERE salary = ALL(2000, 3000, 4000)
ORDER BY employee_id;

Untitled

SOME

  • SOME 조건문을 사용한다.
  • SOME은 ANY와 동일하게 사용되며 동작한다.
1
2
3
4
5
6
7
-- SOME 
SELECT
    employee_id
    , salary
FROM employees
WHERE salary = SOME(2000, 3000, 4000)
ORDER BY employee_id;

Untitled

논리 조건식

  • 논리 조건식 = 조건절에서 AND, OR, NOT을 사용하는 조건식

  • AND는 모든 조건을 만족해야 하고 OR는 여러 조건 중 하나만 만족해도 TRUE를 반환된다.

  • NOT은 조건식 평가 결과가 **거짓(FALSE)**일 때 원하는 결과, 즉 TRUE를 반환한다.

  • NOT을 사용해본다.

1
2
3
4
5
6
7
-- 논리 조건식
SELECT 
    employee_id
    , salary
FROM employees
WHERE NOT (salary >= 2500)
ORDER BY employee_id;

Untitled

BETWEEN AND 조건식

  • BETWEEN은 범위에 해당되는 값을 찾을 때 사용하는데 크거나 같고 작거나 같은 값을 찾는다.
    • 따라서 ‘>=’와 ‘<=’ 논리 연산자로 변환이 가능하다.
1
2
3
4
5
6
7
8
9
-- BETWEEN AND 조건식
-- 범위 지정
-- 급여가 2000~2500 사이에 해당하는 사원을 조회하라
SELECT
    employee_id
    , salary
FROM employees
WHERE salary BETWEEN 2000 AND 2500
ORDER BY employee_id;

Untitled

IN 조건식

  • OR, ANY와 같은 기능을 수행 가능하다.
  • IN 조건식은 조건절에 명시한 값이 포함된 건을 반환하는데 앞에서 배웠던 ANY와 비슷하다.
1
2
3
4
5
6
7
8
-- IN 조건식
-- OR - ANY 조건식과 비슷하다
SELECT
    employee_id
    , salary
FROM employees
WHERE salary IN (2000, 3000, 4000)
ORDER BY employee_id;

Untitled

NOT IN 조건식

  • IN 조건식과 반대의 결과를 출력한다.
1
2
3
4
5
6
7
-- NOT IN
SELECT
    employee_id
    , salary
FROM employees
WHERE salary NOT IN (2000, 3000, 4000)
ORDER BY employee_id;

Untitled

EXISTS 조건식

  • XISTS 조건식 역시 IN과 비슷하지만 후행 조건절로 값의 리스트가 아닌 서브 쿼리만 올 수 있다.
  • 또한 서브 쿼리 내에서 조인 조건(a.department_id = b.department_id)이 있어야 한다.
1
2
3
4
5
6
7
8
9
10
-- EXISTS 조건식
-- IN과 비슷함, 단 서브쿼리 절에만 사용 가능
SELECT department_id
       , department_name
FROM departments a
WHERE EXISTS (  SELECT *
                FROM employees b
                WHERE a.department_id = b.department_id
                AND b.salary > 3000)
ORDER BY a.department_name;
  • 서브쿼리 부분이 메인이다.

Untitled

LIKE 조건식

  • LIKE 조건식은 문자열의 패턴을 검색할 때 사용하는 조건식이다.
  • 예를 들어, 사원 테이블에서 사원이름이 ‘A’로 시작되는 사원을 조회하는 쿼리를 작성한다면 다음과 같이 LIKE 조건식을 사용.
1
2
3
4
5
6
7
-- LIKE 조건식
-- 문자열의 패턴을 검색할 때 사용하는 조건식
-- 사원 테이블 사원 이름이 'A'로 시작되는 사원 조회
SELECT emp_name
FROM employees
WHERE emp_name LIKE 'A%'
ORDER BY emp_name;

Untitled

  • Reference : 오라클 SQL과 PL/SQL을 다루는 기술
Posted Updated  minkuen SQL / oracle9 minutes read (About 1423 words)

Oracle_practice3

sql developer

  • sql developer에서 새로운 sql 워크시트를 생성한다.

    도구 → sql워크시트 :

  • 오라클 SQL과 PL/SQL을 다루는 기술 92p

SELECT 사용법

  • 교재를 참고하여 코드를 익혀보자
  • SELECT문은 다음과 같이 사용한다.
1
2
3
4
5
6
7
8
9
-- SELECT
SELECT * 혹은 컬럼
FROM 테이블 또는 VIEW
WHERE 조건
ORDER BY 컬럼;

어디서 가져올 것인가? FROM
무얼을 가져올 것인가? SELECT
어떻게 가져올 것인가? WHERE

SELECT 문

  • SELECT문을 실제로 사용해본다.
1
2
3
4
5
6
7
8
9
10
-- SELECT
-- 질문 : 사원 테이블에서 급여가 5000이 넘는 사원번호와 사원명을 조회한다.
DESC employees;

SELECT 
    employee_id
    , emp_name
    , salary
FROM employees
WHERE salary > 5000;
  • 다음과 같이 출력된다.

Untitled

  • select문을 좀 더 다양하게 작성해본다.
1
2
3
4
5
6
7
8
9
10
-- 급여가 5000 이상이고 job_id가 IT_PROG 사원 조회
-- 94p
SELECT 
    employee_id
    , emp_name
    , salary
FROM employees
WHERE salary > 5000
    AND job_id = 'IT_PROG'
ORDER BY employee_id;

Untitled

INSERT문, SELECT문

  • INSERT문으로 행을 삽입하고 SELECT로 출력해보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- p.94
-- 급여가 5000 이상이고 job_id가 IT_PROG 사원 조회
SELECT 
    employee_id
    , emp_name
    , salary
FROM employees
WHERE salary > 5000
    AND job_id = 'it_prog' -- 영어는 대소문자 구분
ORDER BY employee_id;

-- 급여가 5000 이상이거나 job_id가 'IT_PROG'인 사원 (OR)
-- 합집합
SELECT 
    employee_id
    , emp_name
    , salary
    , job_id
FROM employees
WHERE salary > 5000
    OR job_id = 'it_prog' -- 영어는 대소문자 구분
ORDER BY employee_id;

SELECT * FROM employees;

-- INSERT 
CREATE TABLE ex3_1 (
    col1 VARCHAR2(10), 
    col2 NUMBER, 
    col3 DATE
);

INSERT INTO ex3_1(col1, col2, col3) 
VALUES ('ABC', 10, SYSDATE);

-- 컬럼 순서를 바꿔도 큰 문제가 안됨
INSERT INTO ex3_1(col3, col1, col2)
VALUES (SYSDATE, 'DEF', 20);

SELECT * FROM ex3_1;

-- 타입 맞추지 않음 오류 발생
INSERT INTO ex3_1(col1, col2, col3)
VALUES ('ABC', 10, 30);

-- p.97
-- 컬럼명 생략
INSERT INTO ex3_1
VALUES ('GHI', 10, SYSDATE);

INSERT INTO ex3_1 (col1, col2)
VALUES('GHI', 20);

SELECT * FROM ex3_1;

Untitled

  • 자주 사용되는 SELECT문 활용법이다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- p.98
CREATE TABLE ex3_2(
    emp_id      NUMBER
    , emp_name  VARCHAR2(100)
);

-- 실무에서 많이 쓰임
INSERT INTO ex3_2(emp_id, emp_name) 
SELECT
    employee_id
    , emp_name
FROM employees
WHERE salary > 5000;

SELECT * FROM ex3_2;

Untitled

UPDATE 문 사용법

  • UPDATE : 기존 데이터를 수정
  • ARTER : 기존 테이블의 컬럼명 수정, 제약조건
1
2
3
UPDATE 테이블명
SET 컬럼1 = 변경값1, 컬럼2 = 변경값2
WHERE 조건;

UPDATE 문

  • UPDATE 문을 실제로 사용해보자.
1
2
3
4
5
6
7
SELECT * FROM ex3_1;

-- col2 모두 50으로 변경한다.
UPDATE ex3_1
SET col2 = 50;

SELECT * FROM ex3_1;
  • col2 값이 모두 50으로 수정되었다.

Untitled

  • SELECT문으로 사람 이름을 수정해보자.
1
2
3
4
5
UPDATE ex3_2
SET EMP_NAME = 10 -- Michael Hartstein
WHERE EMP_ID = 201;

SELECT * FROM ex3_2;
  • Michael이란 이름이 10으로 변경되었다.

Untitled

MERGE

  • 조건 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 INSERT 있으면 UPDATE하는 방식이다.
1
2
3
4
5
6
7
8
9
10
MERGE INTO [스키마.]테이블명
        USING (update나 insert될 데이터 원천)
             ON (update될 조건)
    WHEN MATCHED THEN
           SET 컬럼1 = 값1, 컬럼2 = 값2, ...
    WHERE update 조건
           DELETE WHERE update_delete 조건
    WHEN NOT MATCHED THEN
           INSERT (컬럼1, 컬럼2, ...) VALUES (값1, 값2,...)
           WHERE insert 조건;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- MERGE
-- 조건 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 INSERT
-- 있으면 UPDATE

CREATE TABLE ex3_3(
    employee_id     NUMBER
    , bonum_amt     NUMBER DEFAULT 0
);

SELECT * FROM SALES;
DESC SALES;

-- ex3_3 신규테이블 생성
INSERT INTO ex3_3(employee_id)
SELECT 
    e.employee_id
FROM employees e, sales s 
WHERE e.employee_id = s.employee_id
    AND s.SALES_MONTH BETWEEN '200010' AND '200012'
GROUP BY e.employee_id;
-- group by를 통해 중복 제거

SELECT * FROM ex3_3 ORDER BY employee_id;

Untitled

서브쿼리

  • 쿼리 안에 또 다른 쿼리가 있는 형태이다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- (1) 관리자 사번(manager_id)이 146번인 사원을 찾는다.
-- (2) ex3_3 테이블에 있는 사원의 사번과 일치하면
--     보너스 금액에 자신의 급여의 1%를 보너스로 갱신
-- (3) ex3_3 테이블에 있는 사원의 사번과 일치하지 않으면
--      (1)의 결과의 사원을 신규로 입력 (이 때 보너스 금액은 급여의 0.1%
-- (4) 이 때 급여가 8000미만인 사원만 처리해보자.

-- 서브쿼리
SELECT
    employee_id
    , manager_id
    , salary
    , salary * 0.01 -- 10%
FROM employees
WHERE employee_id IN (SELECT employee_id FROM ex3_3);

Untitled

  • 서브쿼리의 또 다른 예시이다.
1
2
3
4
5
6
7
8
9
10
11
-- 관리자 사번이 146번인 사원은 161번 사원 한 명이므로
-- ex3_3 테이블에서 161인 건의 보너스 금액은 7,000 * 0.01, 즉 70으로 갱신

SELECT
    employee_id
    , manager_id
    , salary
    , salary * 0.01 -- 10%
FROM employees
WHERE employee_id NOT IN (SELECT employee_id FROM ex3_3)
    AND manager_id = 146;

Untitled

MERGE 문

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MERGE INTO ex3_3 d 
    USING (SELECT
                employee_id
                , salary 
                , manager_id
           FROM employees
           WHERE manager_id = 146) b 
        ON (d.employee_id = b.employee_id)
WHEN MATCHED THEN 
    UPDATE SET d.bonus_amt = d.bonus_amt + b.salary * 0.01 
WHEN NOT MATCHED THEN 
    INSERT (d.employee_id, d.bonus_amt) VALUES (b.employee_id, b.salary * 0.001)
    WHERE (b.salary < 8000);
    
SELECT * FROM ex3_3 ORDER BY employee_id;
  • 오타 처리하다 3번 실행한 결과이다.

Untitled

DELETE 문

  • UPDATE에 비해 간단하다.
  • 삭제하기만 하면 되기 때문.
1
2
3
4
-- DELETE 문
-- 106p
DELETE ex3_3;
SELECT * FROM ex3_3;

COMMIT, ROLLBACK

  • commit과 rollback을 사용해보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Commit Rollback Truncate
-- Commit : 변경한 데이터를 DB에 마지막으로 반영
-- ROLLBACK : 반대로 변경한 데이터를 변경하기 이전 상태로 되돌림
CREATE TABLE ex3_4 (
    employee_id NUMBER 
);
    
INSERT INTO ex3_4 VALUES (100);

SELECT * FROM ex3_4;

commit;
rollback;

Untitled

  • commit 후에는, sqlplus에서도 테이블을 출력 할 수 있게 된다.

Untitled

TRUNCATE문

  • 한번 실행 시, 데이터 바로 삭제한다.
  • ROLLBACK 적용 안됨
1
2
3
4
5
6
-- TRUNCATE 문
-- 한번 실행시, 데이터 바로 삭제, ROLLBACK 적용 안됨
-- DELETE문은 데이터 삭제 후, COMMIT 필요 / ROLLBACK 데이터가 삭제되기 전의 복구 불가

SELECT * FROM ex3_4;
TRUNCATE TABLE ex3_4;

Untitled

  • Reference : 오라클 SQL과 PL/SQL을 다루는 기술
Posted Updated  minkuen SQL / oracle10 minutes read (About 1529 words)

Oracle_practice2

sql developer

  • sql developer에서 새로운 sql 워크시트를 생성한다.
    • 도구 → sql 워크시트 : ch02_0426
  • 오라클 SQL과 PL/SQL을 다루는 기술 60p

실습

  • 교재를 참고하여 코드를 익혀보자
  • 제약조건을 작성해본다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 60P
-- not null
-- 제약 조건
CREATE TABLE ex2_6(
    COL_NULL        VARCHAR2(10)
    , COL_NOT_NULL  VARCHAR2(10) NOT NULL
);

INSERT INTO ex2_6 VALUES ('AA', '');
INSERT INTO ex2_6 VALUES ('AA', 'BB');

SELECT * FROM ex2_6;

-- USER CONSTRAINTS 제약 조건 확인
SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_6';

  • SELECT 문을 사용하여 제약 조건을 출력.

Untitled

  • 이어서 실습한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

-- UNIQUE
-- 중복값 허용 안 함
CREATE TABLE ex2_7 (
    COL_UNIQUE_NULL VARCHAR2(10) UNIQUE
    , COL_UNIQUE_NULL VARCHAR2(10) UNIQUE NOT NULL
    , COL_UNIQUE VARCHAR(10)
    , CONSTRAINTS unique_nm1 UNIQUE (COL_UNIQUE)
);

SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_7';
 
 SELECT * FROM ex2_7;
 
 INSERT INTO ex2_7 VALUEs('AA', 'AA', 'AA');
 SELECT * FROM ex2_7;
 
 INSERT INTO ex2_7 VALUES('AA', 'AA', 'AA');
 SELECT * FROM ex2_7;
 
 INSERT INTO ex2_7 VALUES('', 'BB', 'BB');
 INSERT INTO ex2_7 VALUES('', 'CC', 'CC');
 SELECT * FROM ex2_7;

-- 기본키 ( 63P)
CREATE TABLE ex2_8(
    COL VARCHAR2(10) PRIMARY KEY
    , COL2 VARCHAR2(10)
);

SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_8';
 
INSERT INTO ex2_8 VALUES('','AA');
-- 오류 보고 ORA-01400: NULL을 ("ORA_USER"."EX2_8"."COL") 안에 삽입할 수 없습니다
-- NULL 값을 삽입하여 생기는 오류이다. NULL값 대신 다른 값을 입력하자.
  • 여기까지 입력하고 실행하면 오류가 발생한다.

— 오류 보고 ORA-01400: NULL을 (“ORA_USER”.”EX2_8”.”COL”) 안에 삽입할 수 없습니다

Untitled

  • NULL 값을 삽입하여 생기는 오류이다. NULL값 대신 다른 값을 입력하자.
1
2
3
4
-- 오류 보고 ORA-01400: NULL을 ("ORA_USER"."EX2_8"."COL") 안에 삽입할 수 없습니다
-- NULL 값을 삽입하여 생기는 오류이다. NULL값 대신 다른 값을 입력하자.
INSERT INTO ex2_8 VALUES('AA','AA');
SELECT * FROM ex2_8;
  • 이번에는 오류가 출력되지 않았다.

Untitled

  • 같은 값을 다시 입력해보자.
1
2
3
4
INSERT INTO ex2_8 VALUES('AA','AA');
-- ORA-00001: 무결성 제약 조건(ORA_USER.SYS_C007484)에 위배됩니다
-- 값이 같은 데이터를 입력하여 생기는 오류이다. 다른 데이터를 입력해야 한다.

  • 오류가 발생했다.

– ORA-00001: 무결성 제약 조건(ORA_USER.SYS_C007484)에 위배됩니다

  • 값이 같은 데이터를 입력하여 생기는 오류이다. 다른 데이터를 입력하면 해결된다.

Untitled

테이블 생성과 제약조건

  • 90p의 테이블을 생성해보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- 90p 테이블 1, 2, 3번 생성
CREATE TABLE ORDERS (
    ORDER_ID              NUMBER(12, 0)
    , ORDER_DATE          DATE
    , ORDER_MODE          VARCHAR2(8 BYTE)
    , CUSTOMER_ID         NUMBER(6, 0)
    , ORDER_STATUS        NUMBER(2, 0)
    , ORDER_TOTAL         NUMBER(8, 2) DEFAULT 0
    , SALES_REP_ID        NUMBER(6, 0)
    , PROMOTION_ID        NUMBER(6, 0)
    , CONSTRAINT PK_ORDER PRIMARY KEY (ORDER_ID)
    , CONSTRAINT CK_ORDER_MODE CHECK (ORDER_MODE in ('direct', 'online'))
);

CREATE TABLE ORDER_ITEMS (
           ORDER_ID	    NUMBER(12,0),
           LINE_ITEM_ID NUMBER(3,0) ,
           ORDER_MODE	  VARCHAR2(8 BYTE),
           PRODUCT_ID   NUMBER(3,0), 
           UNIT_PRICE   NUMBER(8,2) DEFAULT 0, 
           QUANTITY     NUMBER(8,0) DEFAULT 0,
           CONSTRAINT PK_ORDER_ITEMS PRIMARY KEY (ORDER_ID, LINE_ITEM_ID)
); 

CREATE TABLE PROMOTIONS (
           PROMO_ID	    NUMBER(12,0),
           PROMO_NAME	  VARCHAR2(8 BYTE),
           CONSTRAINT PK_PROMOTIONS PRIMARY KEY (PROMO_ID)
); 

-- CHECK
CREATE TABLE ex2_9 (
    num1     NUMBER 
        CONSTRAINTS check1 CHECK ( num1 BETWEEN 1 AND 9),
    gender   VARCHAR2(10) 
        CONSTRAINTS check2 CHECK ( gender IN ('MALE', 'FEMALE'))        
); 

SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_9';
 
INSERT INTO ex2_9 VALUES (10, 'MAN');
INSERT INTO ex2_9 VALUES (5, 'FEMALE');

테이블 삭제

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- DEFAULT 
-- PL/SQL 사용하면 편하게 해결 가능
DROP TABLE ex2_10;

CREATE TABLE ex2_10 (
   Col1        VARCHAR2(10) NOT NULL, 
   Col2        VARCHAR2(10) NULL, 
   Create_date DATE DEFAULT SYSDATE
);
   
-- Col1 Col2 사용자가 입력
-- Create_Date DB에서 자동으로 입력
INSERT INTO ex2_10 (col1, col2) VALUES ('AA', 'BB');
SELECT * FROM ex2_10;
  • 여기까지 작성하고 실행하면 다음과 같이 출력된다.

Untitled

테이블 변경

  • 이번엔 테이블을 여러 명령어를 통해 다뤄보자
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 테이블 변경
-- 69p (1) 컬럼명 변경

ALTER TABLE ex2_10 RENAME COLUMN Col1 TO Col11;
SELECT * FROM ex2_10;

DESC ex2_10;

-- (2) 컬럼 타입 변경
-- 컬럼 타입 변경 (VARCHAR2(10) ~ VARCHAR2(30))으로 변경
ALTER TABLE ex2_10 MODIFY Col2 VARCHAR2(30);
DESC ex2_10;
  • 테이블이 변경된다.

Untitled

  • 이번엔 생성하거나 삭제해본다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- (3) col3 NUMBER 타입으로 신규 생성
ALTER TABLE ex2_10 ADD Col3 NUMBER;
DESC ex2_10;

-- (4) 컬럼삭제
ALTER TABLE ex2_10 DROP COLUMN Col3;
DESC ex2_10;

-- 제약조건 추가
ALTER TABLE ex2_10 ADD CONSTRAINTS pk_ex2_10 PRIMARY KEY (col11);

-- USER CONSTRAINTS 제약 조건 확인
SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_10';
  • 제약조건이 추가되었다.

Untitled

  • 제약조건을 삭제해보자.
1
2
3
4
5
6
7
-- 제약조건 삭제
 ALTER TABLE ex2_10 DROP CONSTRAINTS pk_ex2_10;
 
 -- USER CONSTRAINTS 제약 조건 확인
SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_10';
  • 이전에 추가한 제약조건이 삭제되었다.

Untitled

뷰(view)

  • emp_dept_v1 뷰를 생성한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT 
    a.employee_id
    , a.emp_name
    , a.department_id -- 부서명 컬럼
    , b.department_name
FROM 
    employees a
    , departments b
WHERE a.department_id = b.department_id;

CREATE OR REPLACE VIEW emp_dept_v1 AS 
SELECT 
    a.employee_id
    , a.emp_name
    , a.department_id
    , b.department_name
FROM 
    employees a
    , departments b
WHERE a.department_id = b.department_id;
  • 새로운 view가 생성된다.

Untitled

인덱스(index)

  • 인덱스를 생성한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 인덱스 생성
-- 75p
-- 추후 공부해야 할 내용 : 인텍스 내부 구조에 따른 분류
---- (초중급 레벨) B-Tree 인덱스, 비트맵 인덱스, 함수 기반 인덱스
---- DB 성능
-- 인덱스 생성
-- col11 값에 중복 값을 허용하지 않는다.
-- 인덱스 생성 시, user_indexes 시스템 뷰에서 내역 확인
CREATE UNIQUE INDEX ex2_10_ix011 
ON ex2_10(col11);

SELECT index_name, index_type, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EX2_10';
  • 실제로 인덱스가 생성되었다는 안내문이 출력된다.

Untitled

  • Reference : 오라클 SQL과 PL/SQL을 다루는 기술
Posted Updated  minkuen SQL / oracle3 minutes read (About 473 words)

Oracle_practice1

사전준비

실습

  • SQL Developer 에서 실행
  • 테이블을 생성한다.
    • 다음 코드를 작성해보자
1
2
3
4
5
6
CREATE TABLE ex2_1 (
COLUMN1     CHAR(10)
, COLUMN2     VARCHAR2(10)
, COLUMN3     NVARCHAR2(10)
, COLUMN4     NUMBER
);
  • 저장(ctrl + s) 후 실행(ctrl + enter)
    • 다음과 같이 테이블과 컬럼이 생성된다.

Untitled

  • 오라클 SQL과 PL/SQL을 다루는 기술 52p
  • 교재를 따라 다음과 같이 작성하고 실행해 본다.
    • 스크립트 실행 = 전체 실행 = F5
    • 명령문 실행 = 일부 실행 = ctrl + enter
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

CREATE TABLE ex2_1 (
COLUMN1 CHAR(10)
, COLUMN2 VARCHAR2(10)
, COLUMN3 NVARCHAR2(10)
, COLUMN4 NUMBER
);

-- 데이터 추가
INSERT INTO ex2_1 (COLUMN1, COLUMN2) VALUES('abc', 'abc')

-- 데이터 확인
SELECT
COLUMN1
, LENGTH(COLUMN1) as len1
, COLUMN2
, LENGTH(COLUMN2) as len2
FROM ex2_1;

-- 새로운 테이블 생성
CREATE TABLE ex2_2(
COLUMN1 VARCHAR2(3)
, COLUMN2 VARCHAR2(3 byte)
, COLUMN3 VARCHAR2(3 char)
);

-- 데이터 추가
INSERT INTO ex2_2 VALUES('abc', 'abc', 'abc');

-- 조회
SELECT
COLUMN1
, LENGTH(COLUMN1) As len1
, COLUMN2
, LENGTH(COLUMN2) As len2
, COLUMN3
, LENGTH(COLUMN3) As len3
FROM ex2_2;

-- 한글 데이터 삽입
-- 에러 발생
INSERT INTO ex2_2 VALUES('홍길동', '홍길동', '홍길동');

- 데이터 조회
SELECT
COLUMN3
, LENGTH(COLUMN3) As len3
, LENGTHB(COLUMN3) As bytelen
FROM ex2_2;

- 숫자 데이터 삽입
CREATE TABLE ex2_3(
COL_INT INTEGER
, COL_DEC DECIMAL
, COL_NUM NUMBER
);

SELECT
column_id
, column_name
, data_type
, data_length
FROM user_tab_cols
WHERE table_name = 'EX2_3'
ORDER BY column_id;

-- p57
CREATE TABLE ex2_4 (
COL_FLOT1 FLOAT(32),
COL_FLOT2 FLOAT
);

INSERT INTO ex2_4 (col_flot1, col_flot2) VALUES (1234567891234, 1234567891234);

-- 조회
SELECT * FROM ex2_4;

-- p.58
CREATE TABLE ex2_5(
COL_DATE DATE
, COL_TIMESTAMP TIMESTAMP
);
INSERT INTO ex2_5 VALUES (SYSDATE, SYSTIMESTAMP);
SELECT * FROM ex2_5;

-- LOB 데이터 타입
-- Large object의 약자로 대용량 데이터 저장할 수 있는 데이터 타입
-- 비정형 데이터는 그 크기가 매우 큰데, 이런 데이터를 저장한다.
Posted Updated  minkuen SQL / postgreSQL4 minutes read (About 636 words)

PSQL practice 02

  • 실무 예제로 배우는 데이터 공학 72p

  • 파일 생성, 가상 환경 진입

관리자 권한으로 실행 : Ubuntu

→ cd ..→ cd ..→ cd mnt/c

→ mkdir sql

→ cd sql

→ virtualenv venv

→ source venv/bin/activate

  • 라이브러리 설치

→ pip3 install psycopg2-binary pandas faker

→ pip3 install pandas

→ pip3 install numpy

  • 실무 예제로 배우는 데이터 공학 72p 실습 진행

→ mkdir chapter04

→ cd chapter04/

→ 파일 생성 : vi createrecord.py

→ 내용 작성

1
2
3
4
5
6
7
import numpy as np
import pandas as pd
import psycopg2

print(np.__version__)
print(pd.__version__)
print(psycopg2.__version__)

→ 저장 후 코드 실행 : 버전 확인

→ python3 createrecord.py

→ 버전이 출력되면 성공

  • 서비스 활성화

→sudo service postgresql status

→sudo service postgresql stop

→sudo service postgresql start

→ createrecord.py에 다음 내용을 추가한다.

1
2
3
4
5
6
import psycopg2 as db
conn_string="dbname='dataengineering' host='localhost' user='postgres' password='postgres'"
# 집 pc에서는 201610974
conn=db.connect(conn_string)
cur=conn.cursor()
print("Connected:", cur)

→ 저장 후 실행

→ python3 createrecord.py

→ Connected : cursor…. 가 출력되면 성공.

  • pgAdmin에서 실습 진행

→ 관리자 권한으로 실행 : pgAdmin

→ 로그인 비밀번호 : 201610974

test 서버 비밀번호 : postgres

→ dataengineering → Schema → public → Tables → users우클릭

→ querytool

Untitled

→ 내용 작성 : SELECT * FROM public.users;

→ F5 키로 실행한다.

→ 다음과 같은 결과가 나온다.

Untitled

  • 실무 예제로 배우는 데이터 공학 77p
  • 데이터 추출
  • Ubuntu에서 진행

→ createrecord.py에 다음 내용을 추가한다.

1
2
3
4
5
6
# 데이터 추출 예제
print("step 2: ----- select -----")
query = "select * from users"
cur.execute(query)
for record in cur:
    print(record)

→ 저장 후 실행

→ python3 createrecord.py

→ createrecord.py에 다음 내용을 추가한다.

#print("step is done!") print(cur.fetchall()) print("--------------") print(cur.fetchmany(3)) print("--------------") print(cur.fetchone()) print("----") print(cur.rowcount)

→ 저장 후 실행

→ python3 createrecord.py

→ createrecord.py에 다음 내용을 추가한다.

1
2
3
4
5
6
# 78페이지 8번.
conn = db.connect(conn_string)
cur = conn.cursor()
f = open('fromdb.csv', 'w')
cur.copy_to(f, 'users', sep=',')
f.close()

→ 저장 후 실행

→ python3 createrecord.py

→ ls

→ fromdb.csv 파일이 생성되면 성공

→ createrecord.py에 다음 내용을 추가한다.

#78p 11번

f = open('fromdb.csv', 'r') f.read() print("reading data is done!")

→ 저장 후 실행

→ python3 createrecord.py

  • 새 폴더 생성

→ 폴더 생성 : vi querydf.py

→ 내용 작성

import psycopg2 as db import pandas as pd conn_string="dbname='dataengineering' host='localhost' user='postgres' password='postgres'" conn=db.connect(conn_string)

df = pd.read_sql("select * from users", conn) print(df.head())

→ 저장 후 실행

→ python3 querydf.py

  • Reference : 실무 예제로 배우는 데이터 공학
Posted Updated  minkuen SQL / postgreSQL4 minutes read (About 543 words)

PSQL practice 01

  • pgAdmin은 GUI 툴 (있으나 없으나 상관이 없음)

  • sudo service postgresql start

    DB(형광등)—> 쿼리

             —> Select, insert, ...

    —> 형광등 켜야 불이 들어오듯 필수적이다.

실습

  • 실무 예제로 배우는 데이터 공학 72p부터 따라한다.

VSCord 에서 Ubuntu Terminal 열기

→ 폴더 생성 : chapter04

→ 파일 생성 : step01_createdf.py

→ 내용 작성

import psycopg2 as db

# 호스트, 데이터베이스 이름, 사용자 이름, 패스워드

conn_string = "dbname='dataengineering' host = 'localhost' user='postgres' password='postgres'"

#집pc의 경우에는 password='201610974‘

conn = db.connect(conn_string)

cur = conn.cursor()

print("db connecting....")

print(cur)

( 아이디/ 비밀번호 모두 postgres인 듯하다)

→ 저장

→ cd .. → cd .. → cd mnt/c → cd airflow-test → cd chapter04

→ 실행 : python3 step01_createdf.py

→ 다음 내용이 출력되면 성공.

db connecting….
<cursor object at 0x7fa097ba86d0; closed: 0>

→ step01_createdf.py 파일에 다음 내용 추가하고 저장

1
2
3
4
5
6
7
query = "insert into users (id,name,street,city,zip) values({},'{}','{}','{}','{}')".format(1,'Big Bird','Sesame Street','Fakeville','12345')
print(cur.mogrify(query))
query2 = "insert into users (id,name,street,city,zip) values(%s,%s,%s,%s,%s)"
data=(1,'Big Bird','Sesame Street','Fakeville','12345')
print(cur.mogrify(query2,data))
cur.execute(query2,data)
conn.commit()

→ 실행

  • pgAdmin에서 실습 진행

→ 관리자 권한으로 실행 : pgAdmin

→ 비밀번호 : postgres

→ dataengineering 우클릭 → querytool

→ 내용 작성 : SELECT * FROM public.users;

→ F5 키로 실행한다.

→ 다음과 같은 결과가 나온다.

Untitled

  • VSCord 에서 새로 파일을 작성한다.

→ 파일 생성 : step02_insertmany.py

→ 내용 작성 ( 실무 예제로 배우는 데이터 공학 75p )

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import psycopg2 as db
from faker import Faker
fake=Faker()
data=[]
i=2
for r in range(1000):
    data.append((i,fake.name(),fake.street_address(), fake.city(),fake.zipcode()))
    i+=1
data_for_db=tuple(data)
print(data_for_db)
conn_string="dbname='dataengineering' host='localhost' user='postgres' password='postgres'"
# 집pc의 경우에는 password='201610974'

conn=db.connect(conn_string)
cur=conn.cursor()
query = "insert into users (id,name,street,city,zip) values(%s,%s,%s,%s,%s)"
print(cur.mogrify(query,data_for_db[1]))
cur.executemany(query,data_for_db)
conn.commit()
query2 = "select * from users"

cur.execute(query2)
print(cur.fetchall())

→ 저장 후 실행

→ 실행 완료

  • pdAdmin 으로 이동

→ F5 키로 다시 실행

→ 다음과 같이 1000개의 데이터가 추가된다.

Untitled

  • Referencd : 실무 예제로 배우는 데이터 공학
Follow

Links

Categories

Recents

Archives

Tags

HTML6
JPA11
JPA 기초11
Java9
JavaScript1
NiFi1
Notion4
PL/SQL4
R2
R Markdown1
VScord1
Vue.js7
Vue.js 기초7
airflow4
anaconda1
apache1
chatbot5
crawling4
elasticsearch1
git1
github blog1
heroku4
javascript10
kakao_chatbot5
kibana1
machine learning22
oracle15
pipeline1
postgreSQL5
python30
setting14
spark7
wsl21
생활코딩1

Subscribe for updates

You need to set client_id and slot_id to show this AD unit. Please set it in _config.yml.

follow.it

×