Day13_SCOTT schema, LIKE(와일드카드), DISTINCT, 연산자

2023.08.30

 

Day13 세 줄 요약

- SCOTT 스키마를 활용한 예제연습

- LIKE = 문자열 필터

- DISTINCT를 통해 조회한 COLUMN의 중복 DATA를 제거 

 

---

SCOTT schema

 

SOCTT 스키마

Oracle DB에 기본적으로 저장되어 있는 데이터 세트 (schema)

DB example로 오랫동안 활용됨

출처 : https://www.orafaq.com/wiki/SCOTT

" Scott은 EMP, DEPT, BONUS 및 SALGRADE 테이블을 포함하는

데이터베이스 USER입니다. 이 계정은 Bruce Scott (오라클 v1~v3

의 공동 저자이자 공동 설계자)의 이름을 따서 명명되었으며,

암호 'TIGER' 는 딸의 고양이 이름에서 따왔습니다. "

 

 

TABLES

-- example로 활용한 dataset은 약간 변형된 version

-- 1. DEPT(부서 : DEPARTMENT) TABLE
CREATE TABLE DEPT(
    -- 중복값 예방을 위해 CONSTRAINT문 추가
    DEPTNO  NUMBER(2)   CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME   NVARCHAR2(14),
    LOC     NVARCHAR2(13)
);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEWYORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');


-- 2. EMP(사원 : EMPLOYEE) 테이블
CREATE TABLE EMP(
    EMPNO   NUMBER(4)   CONSTRAINT PK_EMP PRIMARY KEY,  -- 사원번호
    ENAME   NVARCHAR2(10),  -- 사원이름
    JOB     NVARCHAR2(9),   -- 직급, 직무
    MGR     NUMBER(4),      -- 상사의 사원번호
    HIREDATE    DATE,       -- 입사일
    SAL     NUMBER(7,2),    -- 급여
    COMM    NUMBER(7,2),    -- 수당
    DEPTNO  NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT  -- 부서번호
);


INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-2020','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-2021','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-2021','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-2021','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-2021','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-2021','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-2021','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-07-2016','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-2021','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-2021','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-07-2017','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-2021','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-2021','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-2022','dd-mm-yyyy'),1300,NULL,10);


-- 3. BONUS(수당) TABLE
CREATE TABLE BONUS(
    ENAME   NVARCHAR2(10),  -- 직원이름
    JOB     NVARCHAR2(9),   -- 직무
    SAL     NUMBER,         -- 급여
    COMM    NUMBER          -- 수당
);


-- 4. SALGRADE(급여 수준) 테이블
CREATE TABLE SALGRADE(
    GRADE   NUMBER,     -- 급여등급
    LOSAL   NUMBER,     -- 최저급여
    HISAL   NUMBER      -- 최고급여
);

INSERT INTO SALGRADE VALUES(1, 700, 1200);
INSERT INTO SALGRADE VALUES(2, 1201, 1400);
INSERT INTO SALGRADE VALUES(3, 1401, 2000);
INSERT INTO SALGRADE VALUES(4, 2001, 3000);
INSERT INTO SALGRADE VALUES(5, 3001, 9999);

 

---

 

LIKE (와일드카드)

 

LIKE : 문자열 필터

% (percent) : 길이에 상관없이 (문자가 없는 경우도 포함) '모든 문자 데이터'를 의미

_ (underbar) : 어떤 값이든 상관없이 '한 개의 문자 데이터'를 의미

-- 이름이 'S'로 끝나는 사원 조회
SELECT * FROM EMP WHERE ENAME LIKE '%S';
-- 입사월이 9월인 사원 조회
SELECT * FROM EMP WHERE HIREDATE LIKE '__/09%';

이름이 'S'로 끝나는 사원

입사월이 '9월'인 사원

 

---

 

 

DISTINCT

 

DISTINCT

조회한 DATA의 중복 값을 제외하기 위한 함수

-- JOB 종류 출력 (중복 제외)
SELECT JOB FROM EMP ORDER BY JOB;
SELECT DISTINCT JOB FROM EMP;

모든 JOB 출력

DISTINCT로 중복 제외

---

 

산술 / 논리 / BETWEEN / NULL 연산자

 

산술 연산자

같다, 같지 않다, 크다, 크거나 같다, 작다, 작거나 같다

=, !=, >, >=, <. <=

-- 급여가 '2000'보다 높은 사원 조회
SELECT * FROM EMP WHERE SAL > 2000;

 

 

논리 연산자

AND : 조건이 모두 TRUE 일 때 TRUE

OR : 조건이 하나만 TRUE 여도 TRUE

IN : 여러 개의 OR 조건을 하나로 묶어 쓸 때 사용

NOT : TRUE -> FALSE, FALSE -> TRUE

-- 20,30번 부서에서 근무하고 급여가 '2000'보다 높은 사원 조회
SELECT * FROM EMP WHERE DEPTNO IN (20, 30) AND SAL > 2000;

 

 

BETWEEN 연산자

특정 구간의 DATA를 조회할 때 사용 (a AND b = a 이상 b 이하)

앞에 'NOT'을 붙이면 미만, 초과 (NOT a AND b = a 미만 b 초과)

-- 급여가 2000이상 3000이하인 사원의 (번호, 이름, 급여)를 조회
SELECT EMPNO AS 사원번호, ENAME AS 이름, SAL AS 급여 FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

 

 

NULL 연산자

문자열 type의 default 값인 'NULL'이 들어간 data 조회할 때 사용

-- 20년 이후 입사자 중 보너스(COMM, COMMITION)가 없는 사원 조회
SELECT * FROM EMP WHERE HIREDATE >= '20/01/01' AND COMM IS NULL;