Day 39. NULL 개념, 문자 함수 및 문자 조작 함수

데이터베이스

-- NULL 개념 : 0이 아니고 빈문자열과 동일(순수 데이터 공간이 NULL)
-- NULL 값과 다른값과의 연산 결과는 NULL
-- '' O ' ' X
-- 자바에서는 ""가 널값이 아니였지만 DB에서는 NULL값

 

-- 스칼라 함수는 단일 값 하나만 출력

 

-- 함수 안에서 NULL
-- AVG 구할때 1000 NULL NULL NULL 2000 이면 1500로 출력

 

-- 주의사항 1000 + NULL은 NULL

 

---

문자 함수

INITCAP : 문자열의 첫 번째 문자만 대문자로 변환

INITCAP( EXPR | COLUMN )

 

LOWER : 문자열 전체를 소문자로 변환

LOWER ( EXPR | COLUMN )

 

UPPER : 문자열 전체를 대문자로 변환

UPPER ( EXPR | COLUMN )

 

문자열 길이 반환 함수

LENGTH : 문자열의 길이를 반환

LENGTHB : 문자의 바이트 수를 반환

LENGTH ( EXPR | COLUMN )

LENGTHB ( EXPR | COLUMN )

 

문자조작 함수

CONCAT : 두 문자열을 결합, '||' 와 동일

SUBSTR : 특정 문자 또는 문자열 일부를 추출

INSTR : 특정 문자가 출현하는 첫 번째 위치를 반환

CONCAT('SQL', 'PLUS') > SQLPLUS

SUBSTR('SQL*PLUS', 5, 4) > PLUS

INSTR('SQL*PLUS', '*') > 4

 

LPAD : 오른쪽 정렬 후 왼쪽으로 지정 문자를 삽입

RPAD : 왼쪽 정렬후 오른쪽으로 지정 문자를 삽입

LPAD('SQL', 5, *) > **SQL

RPAD('SQL', 5, *) > SQL**

 

LTRIM : 왼쪽 지정 문자를 삭제

RTRIM : 오른쪽 지정 문자를 삭제

LTRIM('*SQL', *) > SQL

RTRIM('SQL*', *) > SQL

 

INSTR 함수

-- N번째 위치부터 M번째 CHAR의 위치를 찾음

-- 만약 N이 음수이면 CHAR의 뒤부터 찾음

SELECT 1, '12345678901234567890' FROM DUAL
UNION
SELECT 2, 'CORPERATE FLOOR' FROM DUAL
UNION
SELECT 3, TO_CHAR(INSTR('CORPERATE FLOOR', 'OR')) FROM DUAL
UNION
SELECT 4, TO_CHAR(INSTR('CORPERATE FLOOR', 'OR', 3)) FROM DUAL
UNION
SELECT 5, TO_CHAR(INSTR('CORPERATE FLOOR', 'OR', 3, 2)) FROM DUAL
UNION
SELECT 6, TO_CHAR(INSTR('CORPERATE FLOOR', 'OR', -3)) FROM DUAL
UNION
SELECT 7, TO_CHAR(INSTR('CORPERATE FLOOR', 'OR', -3, 2)) FROM DUAL;

-- NULL 개념 : 0이 아니고 빈문자열과 동일(순수 데이터 공간이 NULL)
-- NULL 값과 다른값과의 연산 결과는 NULL
-- '' O ' ' X
-- 자바에서는 ""가 널값이 아니였지만 DB에서는 NULL값
SELECT * FROM STUDENT;

SELECT * FROM professor WHERE profno = 9903;

-- 스칼라 함수는 단일 값 하나만 출력
SELECT
    SYSDATE,
    TO_CHAR(SYSDATE, 'MM/DD') MD,
    TO_CHAR(NULL, 'MM/DD') NV,
    TO_CHAR(SYSDATE, NULL) NV2
FROM DUAL;

-- 함수 안에서 NULL
-- AVG 구할때 1000 NULL NULL NULL 2000 이면 1500로 출력
SELECT AVG(MYVAL) 
FROM (
    SELECT 1000 AS MYVAL FROM DUAL
    UNION ALL
    SELECT NULL FROM DUAL
    UNION ALL
    SELECT NULL FROM DUAL
    UNION ALL
    SELECT NULL FROM DUAL
    UNION ALL
    SELECT 2000 FROM DUAL
);

-- 주의사항 1000 + NULL은 NULL
SELECT (1000 + NULL + NULL + NULL + 2000) FROM DUAL;

SELECT 2 / 0 FROM DUAL;

-- 교수 테이블에서 이름, 직급, 보직수당을 출력하여라
SELECT * FROM professor;
SELECT NAME, POSITION, COMM
FROM PROFESSOR;

-- 교수 테이블에서 보직수당을 받는 교수의 이름, 직급, 보직수당을 출력하여라
SELECT NAME, POSITION, COMM
FROM PROFESSOR
WHERE COMM IS NOT NULL; -- 보직수당이 없는 교수 IS NULL

-- 교수 테이블에서 급여에 보직수당을 더한 값은 sal_com 이라는 별명으로 출력하여라.
SELECT * FROM PROFESSOR;
SELECT (SAL + NVL(COMM, 0)) AS sal_com
FROM PROFESSOR;

-- 102번 학과의 학생 중에서 1학년 또는 4학년 학생의 이름, 학년, 학과번호를 출력하여라
SELECT * FROM STUDENT;
SELECT NAME, GRADE, DEPTNO
FROM STUDENT
WHERE DEPTNO = 102 AND (GRADE = 1 OR GRADE = 4);

-- 102번 학과의 학생중에서 4학년 학생이거나 소속학과에 상관없이 1학년 학생의 이름, 학년, 학과 번호를 출력하여라
SELECT * FROM STUDENT;
SELECT NAME, GRADE, DEPTNO
FROM STUDENT
WHERE DEPTNO = 102 AND GRADE = 4 OR GRADE = 1;

-- 집합 연산자 UNION UNIONALL, MINUS, INTERSECT(교집합)
-- 1학년 이면서 몸무게가 70kg 이상인 학생의 집합(stud_heavy)과
-- 1학년 이면서 101번 학과에 소속된 학생(stud_101)으로 구성된
-- 두 개의 테이블 생성

CREATE TABLE stud_heavy AS
SELECT *
FROM STUDENT
WHERE GRADE = 1 AND WEIGHT >= 70;

CREATE TABLE STUD_101 AS
SELECT *
FROM STUDENT
WHERE GRADE = 1 AND DEPTNO = 101;

SELECT STUDNO, NAME, DEPTNO, WEIGHT
FROM STUD_HEAVY
UNION
SELECT STUDNO, NAME, DEPTNO, WEIGHT
FROM STUD_101;

SELECT 1, 2, 3 FROM DUAL
UNION
SELECT 4, 5, 6 FROM DUAL
UNION
SELECT 7, 8, 9 FROM DUAL;

-- 다른 방법

SELECT ROWNUM * 3 - 2 AS "1", ROWNUM * 3 - 1 AS "2", ROWNUM * 3 AS "3"
FROM STUDENT
WHERE ROWNUM <= 3;


-- 학생 테이블에서 이름을 가나다순으로 정렧하여 이름, 학년, 전화번호를 출력하여라
SELECT NAME, GRADE, TEL
FROM STUDENT
ORDER BY NAME;

-- 학생 테이블에서 학년을 내림차순으로 정렬하여 이름, 학년, 전화번호를 출력하여라
SELECT NAME, GRADE, TEL
FROM STUDENT
ORDER BY 2 DESC;

-- 학생 테이블에서 학년 내림차순, 이름을 내림차순으로 정렬하여 이름, 학년, 전화번호를 출력하여라
SELECT NAME, GRADE, TEL
FROM STUDENT
ORDER BY 2 DESC, 1 DESC;

-- 학생 테이블에서 학번, 이름, 탄생년도를 조회
SELECT * FROM STUDENT;
SELECT STUDNO, NAME, TO_CHAR(BIRTHDATE, 'YYYY') AS BIRTHYEAR FROM STUDENT;

-- 학생 테이블에서 평균키, 평균 몸무게를 조회
SELECT * FROM STUDENT;
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM STUDENT;

-- 학생 테이블에서 학번이 "김영균"인 학생의 사용자 아이디를 첫 문자 소문
-- 자와 나머지 대문자로 변환하여 출력하여라
SELECT * FROM STUDENT;
SELECT INITCAP(USERID)
FROM STUDENT
WHERE NAME = '김영균';

-- 부서 테이블에서 부서 이름의 길이를 묷자 수와 바이트 수로 각각 춗력하여라
SELECT * FROM department;
SELECT LENGTH(DNAME), LENGTHB(DNAME)
FROM DEPARTMENT;

-- 학생 테이블에서 1학년 학생의 주민등록번호에서 
-- 생년월일과 태어난 달을 추출하여 이름, 주민번호, 생년월일, 태어난 달을 출력하여라
SELECT * FROM STUDENT;
SELECT NAME, IDNUM, SUBSTR(IDNUM, 1, 6), SUBSTR(IDNUM, 3, 2)
FROM STUDENT
WHERE GRADE = 1;