014 dev log

INSERT (데이터 추가 구문)

한 행으로 추가, 추가할 값을 기술한다(값 순서 지켜야함)

INSERT INTO 테이블명 VALUES(첫번째컬럼값,두번째컬럼값,세번째...)

--컬럼이 ID, PW, 이름, 생년월일 일때
INSERT INTO MEMBER VALUES('user01','pass01','김나영','1988-10-10');
INSERT INTO MEMBER VALUES('user02','pass02','양다일',SYSDATE);
INSERT INTO MEMBER VALUES('user03','pass03','선우정아','82/05/15');

INSERT INTO MEMBER VALUES(NULL,NULL,NULL,SYSDATE); 
--ID,PW,NAME에 NULL값이 존재해선 안됨
INSERT INTO MEMBER VALUES('user03','pass11','카더가든',SYSDATE); 
--ID는 고유값으로 중복되어서는 안됨

--위의 NULL값이나 중복된 ID값은 유효하지 않은 값이다
--유효한 데이터값을 유지하기 위해서 제약조건을 걸어줘야함
--데이터 무결성 보장이 가능해야함

CREATE TABLE

테이블 : 행(ROW),열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체종류 중 하나
            모든 데이터는 테이블을 통하여 저장되므로 데이터를 조작하기 위해 테이블을 만들어야한다

CREATE TABLE 테이블명(
컬럼명 자료형,
컬럼명 자료형,
컬럼명 자료형,
...
)

--회원들의 데이터(아이디,비밀번호,이름,생년월일)를 담기 위한 테이블 MEMBER 생성
CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20),
    MEMBER_BDATE DATE
); 

--테이블 확인
SELECT *
FROM MEMBER;

오라클에서 컬럼명의 대소문자 구분을 하지 않기 때문에 낙타봉표기법의 의미가 없고 _(언더 하이픈)으로 구분한다

데이터 딕셔너리 (DATA DICTIONARY)

다양한 객체들의 정보를 저장하고 있는 시스템 테이블

테이블 확인 및 컬럼을 데이터 딕셔너리로도 확인해보자

USER_TABLES : 현재 이 사용자 계정이 가지고 있는 테이블들의 전반적인 구조를 조회
USER_TAB_COLUMNS : 현재 이 사용자 계정이 가지고 있는 테이블들의 모든 컬럼이 정보를 조회

--테이블 확인
SELECT *
FROM USER_TABLES;

--컬럼 확인법
SELECT *
FROM USER_TAB_COLUMNS;

자료형

컬럼에 주석 달기

컬럼에 대한 설명을 추가할수 있다

COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원 아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원 비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원 이름';
COMMENT ON COLUMN MEMBER.MEMBER_BDATE IS '생년월일';

DDL(DATA DEFINITION LANGUAGE) : 데이터 정의 언어

오라클에서 제공하는 객체(OBJECT)를 새롭게 만들고(CREATE), 구조를 변경하고(ALTER), 삭제(DROP)를 실행하는 명령문이다.

구조자체를 정의하는 언어로 DB관리자와 설계자가 사용하는 언어이다.

CF. 오라클 객체 종류 : 테이블(TABLE), 사용자(USER), 함수(FUNCTION), 뷰(VIEW) 등

DDL 구성

CREATE, ALTER, DROP

RANK

정렬기준 : ORDER BY(정렬기준컬럼이름, 오름차순/내림차순) → SELECT절에서만 사용 가능

RANK() OVER(정렬 기준)

DENSE_RANK() OVER(정렬 기준)

만약 공동 1위가 3명일 경우

- RANK() OVER : 그 다음 순위 4위

- DENSE_RANK() OVER : 그 다음 순위 2위

--사원들의 급여가 높은 순서대로 매겨서 사원명,급여,순위 조회 : RANK() OVER()
SELECT EMP_NAME,SALARY,RANK()OVER(ORDER BY SALARY DESC) 순위
FROM EMPLOYEE; --공동순위 인원만큼 다음 순위 밀림

--사원들의 급여가 높은 순서대로 매겨서 사원명,급여,순위 조회 : DENSE_RANK() OVER()
SELECT EMP_NAME,SALARY, DENSE_RANK()OVER(ORDER BY SALARY DESC) 순위
FROM EMPLOYEE; --공동순위 있어도 다음순위는 +1위

--5위까지만 조회
SELECT EMP_NAME,SALARY,RANK()OVER(ORDER BY SALARY DESC) 순위
FROM EMPLOYEE --공동순위 인원만큼 다음 순위 밀림
WHERE RANK()OVER(ORDER BY SALARY DESC) <= 5; --오류발생!! RANK함수는 WHERE에서 사용불가 => 인라인뷰 사용

--인라인뷰를 사용하여 5위까지 조회
SELECT *
FROM (SELECT EMP_NAME,SALARY,RANK()OVER(ORDER BY SALARY DESC) 순위
      FROM EMPLOYEE)--공동순위 인원만큼 다음 순위 밀림
WHERE 순위<=5;

인라인뷰 (INLINE VIEW)

FROM절에 서브쿼리를 제시한다

서브쿼리 수행한 결과(RESULT SET)를 테이블 대신 사용한다

TOP-N 분석 시 주로 활용(데이터베이스 상에 있는 자료 중 최상위 N개의 자 료를 보기 위해 사용하는 기능)

일반문과 인라인뷰 비교

--보너스 포함 연봉이 3000만원 이상인 사원들의 사번,이름,연봉(상여포함),부서코드 조회
SELECT EMP_ID, EMP_NAME, ((SALARY*12)+(SALARY*NVL(BONUS,0))) 연봉, DEPT_CODE
FROM EMPLOYEE
WHERE ((SALARY*12)+(SALARY*BONUS)) >= 30000000;

--인라인 뷰를 사용 : 사원명만 골라내기(보너스 포함 연봉이 3000만원 이상인 사원들의 이름만)
SELECT EMP_NAME
FROM (SELECT EMP_ID, EMP_NAME, ((SALARY*12)+(SALARY*NVL(BONUS,0))) 연봉, DEPT_CODE
      FROM EMPLOYEE
      WHERE ((SALARY*12)+(SALARY*BONUS)) >= 30000000);

TOP-N분석 (ROWNUM)

ROWNUM : 오라클에서 제공해주는 컬럼으로 조회된 순서대로 1부터 순서를 부여해주는 컬럼

--전 직원중 급여가 가장 높은 상위5명(순위,사원명,급여)
SELECT ROWNUM 순위, EMP_NAME 사원명, SALARY 급여
FROM EMPLOYEE
WHERE ROWNUM <= 5
ORDER BY SALARY DESC; --급여순으로 순위 안됨

--해결(급여순으로 정렬된 테이블로 뽑자, FROM이 실행순서가 빠르므로)
SELECT ROWNUM 순위, EMP_NAME 사원명, SALARY 급여
FROM (SELECT *
      FROM EMPLOYEE
      ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;

(단일행)다중열 서브쿼리

서브쿼리의 조회결과가 한 개의 행에 컬럼이 여러 개인 경우

서브쿼리 조회 결과가 값은 한 행이지만, 나열된 컬럼의 개수가 여러개일 경우

--하이유 사원과 같은 부서코드, 같은 직급코드에 해당하는 사원들 조회
--(사원명, 부서코드, 직급코드, 입사일)
--1)하이유 사원의 부서코드, 직급코드 조회하기
SELECT DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NAME='하이유'; --부서코드 D5, 직급코드 J5
--2)부서코드 D5, 직급코드 J5인 사원들 조회해보기(사원명,부서코드,직급코드,입사일)
SELECT EMP_NAME,DEPT_CODE,JOB_CODE,HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE='D5' AND JOB_CODE='J5';
--하나로 합치기
SELECT EMP_NAME,DEPT_CODE,JOB_CODE,HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE,JOB_CODE) = (SELECT DEPT_CODE,JOB_CODE
                              FROM EMPLOYEE
                              WHERE EMP_NAME='하이유');

--박나라 사원과 같은 직급코드,같은 사수사번을 가진 사원들의 사번,이름,직급코드,사수사번 조회
--다중열 서브쿼리로 작성
SELECT EMP_ID, EMP_NAME, JOB_CODE, MANAGER_ID
FROM EMPLOYEE
WHERE (JOB_CODE, MANAGER_ID) = (SELECT JOB_CODE, MANAGER_ID
                                FROM EMPLOYEE
                                WHERE EMP_NAME='박나라');

다중행 다중열 서브쿼리

결과 값이 여러 행 여러 열일 때

서브쿼리의 조회결과가 여러 개의 컬럼과 여러 개의 행인 경우​

--각 직급별 최소 급여를 받는 사원들 조회(사번,이름,직급코드,급여)
--1)각 직급별 최소 급여를 조회
SELECT JOB_CODE, MIN(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE;

--2)위에서 구한 목록들에 해당하는 사원 조회
--SELECT EMP_ID,EMP_NAME,JOB_CODE,SALARY
--FROM EMPLOYEE
--WHERE (JOB_CODE,SALARY) = ('J2',3700000)
--OR    (JOB_CODE,SALARY) = ('J7',1380000)
--OR    (JOB_CODE,SALARY) = ('J3',3400000)
--OR    (JOB_CODE,SALARY) = ('J6',2000000)
--OR    (JOB_CODE,SALARY) = ('J5',2200000)
--OR    (JOB_CODE,SALARY) = ('J1',8000000)
--OR    (JOB_CODE,SALARY) = ('J4',1550000);

--위에 단계들을 하나의 쿼리문으로 합치기
SELECT EMP_ID,EMP_NAME,JOB_CODE,SALARY
FROM EMPLOYEE
WHERE (JOB_CODE,SALARY) IN (SELECT JOB_CODE, MIN(SALARY)
                            FROM EMPLOYEE
                            GROUP BY JOB_CODE);

단일행(단일열) 서브쿼리

서브쿼리의 조회결과 값이 한 개인 경우

일반 연산자 사용 가능(=, !=, >, <, >=, <=, ...)

--전 직원의 평균 급여보다 더 적게 받는 사원들의 사원명 직급코드 급여를 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY<(SELECT FLOOR(AVG(SALARY)) 
              FROM EMPLOYEE);

--부서별 급여 합이 가장 큰 부서 하나만을 조회하여 부서코드, 부서명, 급여합 출력
--ORACLE
SELECT DEPT_CODE, DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_CODE=D.DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING SUM(SALARY)=(SELECT MAX(SUM(SALARY)) FROM EMPLOYEE GROUP BY DEPT_CODE);
--ANSI
SELECT DEPT_CODE, DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID)
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING SUM(SALARY)=(SELECT MAX(SUM(SALARY)) FROM EMPLOYEE GROUP BY DEPT_CODE);

다중행(단일열) 서브쿼리

서브쿼리의 조회결과 하나의 컬럼에 행의 개수가 여러 개인 경우

- 조건

   IN (10,20,30) : 여러 개의 결과 값 중에서 하나라도 일치하는 것이 있다면

   NOT IN(10,20,30) : 여러 개의 결과 값 중에서 하나라도 일치하는 것이 없다면

   > ANY (10,20,30) : 여러개의 결과값중에서 하나라도 클 경우

   < ANY (10,20,30) : 여러개의 결과값중에서 하나라도 작을 경우

   > ALL : 여러개의 결과값의 모든 값보다 클 경우(결과값 중 가장 큰 값보다 클 경우)

   < ALL : 여러개의 결과값의 모든 값보다 작을 경우(결과값 중 가장 작은 값보다 작을 경우)

--각 부서 별 최고 급여를 받는 사원의 이름, 직급코드, 급여 조회
--1)각 부서별 최고급여 조회(여러행,단일열)
SELECT DEPT_CODE, MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE; --2890000,3660000,8000000,3760000,3900000,2490000,2550000
--2)위에서 조회한 급여들을 받는 사원들 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN(2890000,3660000,8000000,3760000,3900000,2490000,2550000);
--3)합치기
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN(SELECT MAX(SALARY)
                FROM EMPLOYEE
                GROUP BY DEPT_CODE);

--이오리 또는 하동운 사원과 같은 직급인 사원 조회(사원명,직급코드,부서코드,급여)
SELECT EMP_NAME,JOB_CODE,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE JOB_CODE IN (SELECT JOB_CODE
                    FROM EMPLOYEE
                    WHERE EMP_NAME IN('이오리','하동운'));

--대리 직급인데 과장보다 급여 많이 받는 사원들 조회 (사번,이름,직급명,급여)
SELECT EMP_ID,EMP_NAME,JOB_NAME,SALARY
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE=J.JOB_CODE
AND SALARY > ANY(SELECT SALARY
             FROM EMPLOYEE E, JOB J
             WHERE E.JOB_CODE=J.JOB_CODE
             AND J.JOB_NAME='과장')
AND JOB_NAME='대리';