[Day +40]서브쿼리, DDL

210818 수

 

5.상[호연]관 서브쿼리
: 일반적으로 서브쿼리가 만든 결과 값을 메인쿼리가 비교 연산하는 구조
-상관커리는 메인쿼리가 사용하는 테이블 값을 서브쿼리가 이용해서 결과를 만듦
-메인쿼리의 테이블 값이 변경되면 서브쿼리의 결과 값도 바뀌게 되는 구조

5-1) 관리자 사번이 employee 테이블에 존재하는 직원의 사번, 이름, 부서명, 관리자 사번 조회

SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, MANAGER_ID
FROM EMPLOYEE E
--EXISTS : 서브쿼리에 해당하는 행이 적어도 한 개 이상 존재할 경우가 충족되는 경우
--SELECT 실행
WHERE EXISTS (SELECT
                    EMP_ID
               FROM EMPLOYEE M
              WHERE E.MANAGER_ID = M.EMP_ID);

(1) 메인쿼리에서 관리자 사번(E.MANAGER_ID)을 읽어서 서브쿼리로 전달
     (관리자 사번이 존재하지 않으므로 NULL 전달)
(2) 서브쿼리는 메인쿼리에서 받은 관리자 사번에 해당하는 사번(EMP_ID) SELECT
     (NULL이 전달되었다면 해당하는 행이 0개)
(3) 다시 메인쿼리는 서브쿼리에서 SELECT된 행이 적어도 한 개 이상 존재한다면 SELECT 실행(EXISTS)

     (NULL이 전달되었다면 SELECT가 동작하지 않아서 최종 결과에 불포함)

 

 

5-2) 직급별 급여 평균보다 급여를 많이 받는 직원의 이름, 직급, 급여 조회

단, 급여와 급여 평균은 십만원 단위로 계산 TRUNC(컬럼명, -5)

SELECT
EMP_NAME
,JOB_CODE
, SALARY
FROM EMPLOYEE E
WHERE SALARY > (SELECT TRUNC(AVG(SALARY), -5)
FROM EMPLOYEE M
WHERE E.JOB_CODE = M.JOB_CODE);

(1) 메인 쿼리에서 직급코드(E.JOB_CODE)를 읽어서 서브쿼리로 전달
(2) 서브쿼리는 메인쿼리에서 받은 직급코드로 평균 급여 계산
(3) 다시 메인쿼리는 서브쿼리의 평균 급여보다 급여가 큰 직원 출력


6. 스칼라 서브쿼리
: SELECT 문에서 사용되는 서브쿼리 결과로 1행만 반환
-SQL에서 단일값을 가리켜 '스칼라'라고 함

SELECT
E.EMP_ID
, E.EMP_NAME
, E.MANAGER_ID
, NVL((SELECT M.EMP_NAME FROM EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID), '없음') 관리자명
FROM EMPLOYEE E
ORDER BY 1;

7. 인라인뷰(INLINE-VIEW)
: FROM절에 서브쿼리를 사용
-서브쿼리가 만든 결과 집합(RESULT SET)을 테이블 대신 사용함

7-1) 인라인 뷰를 활용한 TOP-N 분석
-전 직원 중 급여가 높은 상위 5명의 순위, 이름, 급여 조회

ROWNUM
: 조회된 순서대로 1부터 번호를 매김

(1) 방법 1 (잘못 된 방법)

SELECT
ROWNUM
, EMP_NAME
, SALARY
FROM EMPLOYEE
WHERE ROWNUM <= 5 -- 상위 5명
ORDER BY SALARY DESC;

-의도는 메인쿼리 결과를 급여 내림차순으로 정렬하고 그 중 상위 5개를 출력하려고 했으나 ROWNUM은 FROM절을 수행하면서 붙여지기 때문에EMPLOYEE 테이블의 행 순서대로 ROWNUM이 붙어서 조건이 의미 없어지게 됨

 

(2) 방법 2 (올바른 방법)
=> 급여 내림차순으로 정렬되어 있는 결과에 ROWNUM을 사용해야 하므로 인라인 뷰를 활용한다

SELECT
EMP_NAME
, SALARY
FROM(SELECT 
*
FROM EMPLOYEE
ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;

-서브쿼리에서 전 직원 급여 내림차순으로 정렬해두고 메인 커리에서 서브 쿼리의 결과 중 상위 5개만 조회함
-FROM절에서 이미 정렬된 서브쿼리(인라인뷰)적용 시 ROWNUM으로 TOP-N 분석 가능

 

7-2) 급여 평균 3위 안에 드는 부서의 부서코드와 부서명 평균 급여 조회
-INLINE-VIEW안에 있는 컬럼만 메인에서 SELECT할 수 있으며 Function을 사용한 컬럼의 경우 별칭을 붙여서 사용해야 함

SELECT
DEPT_CODE
, DEPT_TITLE
, 평균급여 --FLOOR(AVG(SALARY))는 안됨, 새로운 함수로 인식하기 때문
FROM (SELECT
DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY)) 평균급여
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_CODE, DEPT_TITLE
ORDER BY 3 DESC)
WHERE ROWNUM <= 3;

SELECT
DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_CODE, DEPT_TITLE
ORDER BY 3 DESC;

8. WITH
-서브쿼리에 이름을 붙여주고 사용할 수 있음
-인라인 뷰로 사용될 서브쿼리에 주로 이용

WITH TOPN_SAL AS(SELECT EMP_ID, EMP_NAME, SALARY 
FROM EMPLOYEE
ORDER BY SALARY DESC)
SELECT EMP_NAME, SALARY
FROM TOPN_SAL;

9. RANK() OVER / DENSE_RANK() OVER
1) RANK() OVER : 동일한 순위 이후의 등수를 인원 수만큼 건너 뛰고 순위 계산
EX) 공동 1위가 2명이면 다음 순위는 3위

SELECT
EMP_NAME
, SALARY
, RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE;

2) ENSE_RANK() OVER : 동일한 순위 이후의 등수를 건너 뛰지 않고 순위 계산
EX) 공동 1위가 2명이더라도 다음 순위는 2위

SELECT
EMP_NAME
, SALARY
, DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE;

 

 

<실습문제>

1. 부서별 급여 합계가 전체 급여 총 합의 20%보다 많은 부서의 부서명, 부서별 급여 합계 조회

SELECT
DEPT_TITLE, SUM(SALARY)
FROM DEPARTMENT
JOIN EMPLOYEE ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) > (SELECT SUM(SALARY) * 0.2 FROM EMPLOYEE)
ORDER BY 2 DESC;

2. 직원 테이블에서 보너스 포함한 연봉이 높은 5명의 사원의 사번, 이름, 부서명, 직급명, 입사일, 순위 조회
2-1) ROWNUM
(1) 내 답

WITH AA AS(SELECT
E.EMP_ID 사번, E.EMP_NAME 이름, D.DEPT_TITLE 부서, E.HIRE_DATE 입사일
, (E.SALARY * 12 + NVL((E.SALARY * 12 * E.BONUS) ,0)) 연봉
,RANK() OVER(ORDER BY SALARY * 12 + NVL((SALARY * 12 * BONUS) ,0) DESC) AS 순위
FROM EMPLOYEE E
JOIN department D ON(E.DEPT_CODE = D.DEPT_ID))
SELECT 
사번, 이름, 부서, 입사일, 연봉, 순위
FROM AA
WHERE ROWNUM <= 5;

(2) 강사님 답

SELECT
EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, HIRE_DATE, ROWNUM 순위
FROM (SELECT
EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, HIRE_DATE, SALARY, 
(SALARY + (SALARY * NVL(BONUS,0))) * 12 연봉
FROM EMPLOYEE 
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
ORDER BY 7 DESC)
WHERE ROWNUM <= 5;

SELECT
EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, HIRE_DATE, SALARY, 
(SALARY + (SALARY * NVL(BONUS,0))) * 12 연봉
FROM EMPLOYEE 
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
ORDER BY 7 DESC;

2-2) RANKOVER
-강사님 답

SELECT
EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, HIRE_DATE, 순위
 FROM (SELECT
EMP_ID, 
EMP_NAME, 
DEPT_TITLE, 
JOB_NAME, 
HIRE_DATE, 
SALARY,
(SALARY + (SALARY * NVL(BONUS,0))) * 12,
RANK() OVER (ORDER BY (SALARY + (SALARY * NVL(BONUS,0))) * 12 DESC) 순위
FROM EMPLOYEE 
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE))
WHERE ROWNUM <= 5;

---

*SQL(Structured Query Language)

: 관계형 데이터베이스에서 데이터를 조회하거나 조작하기 위해 사용하는 표준 검색 언어 원하는 데이터를 찾는 방법이나 절차를 기술하는 것이 아닌 조건을 기술하여 작성

 

*DDL(Data Definition Language)

: 데이터 정의 언어로 객체(OBJECT)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말함

(1) 오라클 객체 종류

: 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 패키지(PACKAGE), 프로시저(PROCEDUAL), 함수(FUNCTION), 트리거(TRIGGER), 동의어(SYNONYM), 사용자(USER)

(2) 오라클의 데이터형

 

1. CREATE

: 테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문

 

 

: 테이블이나 인덱스, 뷰 등 다양한 데이터베이스 객체를 생성하는 구문
-생성된 객체는 DROP 구문으로 제거할 수 있음

 

1) 테이블 만들기

테이블이란?
: 행(ROW)과 열(COLUMN)로 구성되는 가장 기본적은 데이터 베이스 객체
-데이터 베이스 내의 모든 데이터는 테이블을 통해 저장된다

 

1-1) 테이블 만들기

표현식 CREATE TABLE 테이블명 (컬럼명 자료형(크기), 컬럼명 자료형(크기), ...);

CREATE TABLE MEMBER(
MEMBER_ID VARCHAR2(20),
MEMBER_PWD VARCHAR2(20),
MEMBER_NAME VARCHAR2(20),
MEMBER_DATE DATE DEFAULT SYSDATE);

*DEFAULT
: 입력되는 값이 없거나 DEFUALT로 입력될 경우의 컬럼 값 지정

 

1-2) 만든 테이블 확인

SELECT * FROM MEMBER;

2) 컬럼에 주석 달기
2-1) 컬럼에 주석 달기

표현식 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_DATE IS '회원가입일';

데이터 딕셔너리란?
자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블
데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의
작업을할 때 데이터 베이스 서버에 의해 자동으로 갱신되는 테이블
DDL명령이 실행될때마다 데이터 딕셔너리에 접근함여 정보가 반영됨

 

2-2) 테이블 주석 확인

SELECT *
FROM USER_COL_COMMENTS
WHERE TABLE_NAME = 'MEMBER';

2-3) 사용자가 작성한 테이블 확인

SELECT *
FROM USER_TABLES;

2-4) 사용자의 컬럼 확인

SELECT *
FROM USER_TAB_COLUMNS;

3) MEMBER 테이블에 행 추가(INSERT)

3-1)  MEMBER 테이블에 행 추가

INSERT INTO MEMBER 
VALUES ('mem1', '123abc', '우별림', '2020-11-20');

INSERT INTO MEMBER
VALUES ('mem2', 'QWERabc', '김영희', SYSDATE);

INSERT INTO MEMBER
VALUES ('mem3', 'QWER123', '홍길동', DEFAULT);

INSERT INTO MEMBER(MEMBER_ID, MEMBER_PWD, MEMBER_NAME)
VALUES ('mem4', 'QWER123', '금성무');

3-2) 추가된 행 내용 확인

SELECT * FROM MEMBER;

행 삽입 여러번 실행했더니 8개나 추가됨ㅠㅠ

2. 제약조건(CONSTRAINTS)
: 사용자가 원하는 조건의 데이터만 유지하기 위해서 특정 컬럼에 설정하는 제약
-테이블 작성시 각 컬럼에 대해 값 기록에 대한 제약조건 설정 가능
-데이터 무결성 보장을 목적으로 함
-입력 데이터에 문제가 없는지 자동으로 검사
-데이터 수정/삭제 가능 여부를 자동으로 검사

1) 제약조건 확인 구문(제약 조건별로)

SELECT * FROM USER_CONSTRAINTS;

2) 제약조건 확인 구문(컬럼별로)

SELECT *
FROM USER_CONS_COLUMNS;

NOT NULL
-해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용
-삽입/수정 시 NULL 값을 허용하지 않도록 "컬럼 레벨"에서 제한

 

3) 제약 조건 없이 생성

CREATE TABLE USER_NOCONS(
USER_NO NUMBER,
USER_ID VARCHAR2(20),
USER_PWD VARCHAR2(20),
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);

INSERT INTO USER_NOCONS
VALUES (1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678'
, 'hong123@kh.or.kr');

INSERT INTO USER_NOCONS
VALUES (2, NULL, NULL, '부승관', '남', '010-1234-5678'
, 'hong123@kh.or.kr');
--> 컬럼값에 NULL이 있더라도 삽입 성공

SELECT * FROM USER_NOCONS;

4) NOT NULL 제약조건을 추가하여 테이블 생성

4-1) 테이블 생성

CREATE TABLE USER_NOTNULL(
USER_NO NUMBER NOT NULL,
USER_ID VARCHAR2(20) NOT NULL,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);

INSERT INTO USER_NOTNULL
VALUES (1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_NOTNULL
VALUES (2, 'user02', 'pass02', '부승관', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_NOTNULL
VALUES (3, NULL, NULL, '부승관', '남', '010-1234-5678', 'hong123@kh.or.kr');
--해당 테이블의 NOT NULL 제약 조건에 위배되어 오류 발생
--cannot insert NULL into ("KH"."USER_NOTNULL"."USER_ID")

SELECT * FROM USER_NOCONS;

4-2) 작성한 제약 조건 조회

SELECT * FROM USER_CONSTRAINTS C1
JOIN USER_CONS_COLUMNS C2 USING(CONSTRAINT_NAME)
WHERE C1.TABLE_NAME = 'USER_NOTNULL';

 

3. UNIQUE
-컬럼의 입력 값에 대해서 중복을 제한하는 제약 조건
-"컬럼 레벨"과 "테이블 레벨" 모두 설정 가능

1) 조회

SELECT * FROM USER_NOCONS;

 

2) 중복 데이터 삽입 가능

INSERT INTO USER_NOCONS
VALUES (2, 'user02', 'pass02', '부승관', '남', '010-1234-5678', 'hong123@kh.or.kr');

3) UNIQUE 제약 조건을 건 테이블 생성

3-1) 삽입

CREATE TABLE USER_UNIQUE(
USER_NO NUMBER UNIQUE,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);

INSERT INTO USER_UNIQUE
VALUES (1, 'user01', 'pass02', '부승관', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_UNIQUE
VALUES (2, 'user01', 'pass02', '부승관', '남', '010-1234-5678', 'hong123@kh.or.kr');
--동일한 USER_ID로 INSERT하면 같은 아이디 데이터가 
--이미 테이블에 있으므로 UNIQUE제약 조건 위배
--unique constraint (KH.SYS_C007131) violated 오류 발생

3-2) 조회

-오류보고에 나타나는 SYS_C007131 제약조건명으로 해당 제약조건이 설정된
-테이블명, 컬럼, 제약조건 타입 조회

SELECT UCC.TABLE_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UCC.CONSTRAINT_NAME = 'SYS_C007131';

4) 테이블 레벨에서 제약조건 설정
CREATE TABLE 테이블명(컬럼명 자료형,..., [CONSTRAINT 제약조건명] 제약조건 타입(컬럼명));
4-1) 테이블 레벨에서 제약조건 작성

CREATE TABLE USER_UNIQUE2(
USER_NO NUMBER UNIQUE,
USER_ID VARCHAR2(20),
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
UNIQUE(USER_ID)
);

4-2) 두 개의 컬럼을 묶어서 하나의 UNIQUE 제약 조건 설정 가능

CREATE TABLE USER_UNIQUE3(
USER_NO NUMBER,
USER_ID VARCHAR2(20),
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
UNIQUE(USER_NO, USER_ID)
);

INSERT INTO USER_UNIQUE3
VALUES (1, 'user01', 'pass01', '부승관', '남', '010-1234-5678'
, 'hong123@kh.or.kr');

INSERT INTO USER_UNIQUE3
VALUES (2, 'user01', 'pass01', '부승관', '남', '010-1234-5678'
, 'hong123@kh.or.kr');

INSERT INTO USER_UNIQUE3
VALUES (1, 'user02', 'pass01', '부승관', '남', '010-1234-5678'
, 'hong123@kh.or.kr');

INSERT INTO USER_UNIQUE3
VALUES (1, 'user01', 'pass01', '부승관', '남', '010-1234-5678'
, 'hong123@kh.or.kr');
--여러 개의 컬럼을 묶어서 UNIQUE 제약 조건을 설정하면
--해당 컬럼들이 모두 중복되는 값일 경우에만 오류가 발생함
--unique constraint (KH.SYS_C007138) violated 오류 발생

 

5) 오류보고에 나타나는 SYS_C007138 제약조건명으로 해당 제약조건이 설정됨
5-1) 테이블명, 컬럼, 제약조건 타입 조회

SELECT UCC.TABLE_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE, UC.CONSTRAINT_NAME
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UCC.CONSTRAINT_NAME = 'SYS_C007138';
--> 두 개의 컬럼을 대상으로 하나의 제약 조건 명으로 되어 있는 것을 확인

테이블 삭제 방식
DROP TABLE USER____;

 

5-2) 제약조건에 이름 설정하기

CREATE TABLE CONS_NAME(
TEST_DATA1 VARCHAR2(20) CONSTRAINT NN_TEST_DATA1 NOT NULL,
TEST_DATA2 VARCHAR2(20) CONSTRAINT UK_TEST_DATA2 UNIQUE,
TEST_DATA3 VARCHAR2(30),
CONSTRAINT UK_TEST_DATA3 UNIQUE(TEST_DATA3)
);

SELECT * FROM USER_CONSTRAINTS;

6) PRIMARY KEY(기본키)
: 테이블에서 한 행의 정보를 찾기 위해 사용할 컬럼을 의미
-테이블에 대한 식별자(IDENTIFIER) 역할
-NOT NULL + UNIQUE의 의미로 한 테이블 당 한 개만 설정 가능
-한 개 컬럼을 대상으로 할 수도 있고 여러 개의 컬럼을 묶어서 설정할 수도 있음

(1) 유일성 : 주식별자에 읳 엔티티 내의 모든 인스턴스들을 유일하게 구분해야 함
-> UNIQUE 해야 한다는 뜻
(2) 존재성 : 주식별자가 지정되면 반드시 데이터 값이 존재해야 함
(3) 최소성 : 최소한의 속성으로 식별자 구성
(4) 불변성 : 식별자가 한 번 특정 엔티티에서 지정되면 그 식별자는 변하지 않아야 함

Q. 주 식별자?
A. 식별자란 하나의 엔티티에 구성되어 있는 여러 개의 속성 중에서 엔티티를 대표할 수 있는 속성을 의미

 

6-1) 삽입

CREATE TABLE USER_PRIMARYKEY(
USER_NO NUMBER CONSTRAINT PK_USER_NO PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);

INSERT INTO USER_PRIMARYKEY
VALUES (1, 'user01', 'pass01', '부승관', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_PRIMARYKEY
VALUES (1, 'user02', 'pass02', '부승관', '남', '010-1234-5678', 'hong123@kh.or.kr');
--> 기본키 중복으로 오류
--unique constraint (KH.PK_USER_NO) violated 오류 발생

INSERT INTO USER_PRIMARYKEY
VALUES (NULL, 'user03', 'pass03', '부승관', '남', '010-1234-5678', 'hong123@kh.or.kr');
--> 기본키가 NULL이므로 오류
--cannot insert NULL into ("KH"."USER_PRIMARYKEY"."USER_NO") 오류 발생

 

7) FORIEGN KEY(외부키/외래키)
: 참조(REFERENCES)된 다른 테이블이 제공하는 값만 사용할 수 있음
-FOREIGN KEY 제약 조건에 의해서 테이블간의 관계(RELATIONSHIP)이 형성됨

CREATE TABLE USER_GRADE(
GRADE_CODE NUMBER PRIMARY KEY,
GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE VALUES(10, '일반회원');
INSERT INTO USER_GRADE VALUES(20, '우수회원');
INSERT INTO USER_GRADE VALUES(30, '특별회원');

SELECT * FROM USER_GRADE;

 

 

8) 컬럼레벨
컬럼형 자료형(크기) [CONSTRAINT 제약조건명] REFERENCES 참조테이블명 [(참조할컬럼)] [삭제룰]

9) 테이블 레벨
[CONSTRAINT 제약조건명] FOREIGN KEY(적용컬럼명) REFERENCES 참조테이블명 [(참조할컬럼)] [삭제룰]

10) 참조될 수 있는 컬럼은 PRIMARY KEY 컬럼과 UNIQUE 지정된 컬럼만 참조 가능
-참조할 테이블의 참조할 컬럼며이 생략되면 PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 됨

CREATE TABLE USER_FOREIGNKEY(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER,
CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE)
REFERENCES USER_GRADE(GRADE_CODE)
);

INSERT INTO USER_FOREIGNKEY
VALUES (1, 'user01', 'pass01', '부승관', '남', '010-1234-5678', 'hong123@kh.or.kr', 10);

INSERT INTO USER_FOREIGNKEY
VALUES (2, 'user02', 'pass02', '금성무', '남', '010-2234-3678', 'hong123@kh.or.kr', 10);

INSERT INTO USER_FOREIGNKEY
VALUES (3, 'user03', 'pass03', '닝닝', '여', '010-1114-3378', 'hong123@kh.or.kr', 30);

INSERT INTO USER_FOREIGNKEY
VALUES (4, 'user04', 'pass04', '전지현', '여', '010-1224-3338', 'hong123@kh.or.kr', NULL);
--GRADE에 NULL값도 가능

SELECT * FROM USER_FOREIGNKEY;

INSERT INTO USER_FOREIGNKEY
VALUES (5, 'user05', 'pass05', '수지', '여', '010-3324-4438', 'hong123@kh.or.kr', 50);
--integrity constraint (KH.FK_GRADE_CODE) violated - parent key not found
--오류 발생
--50이라는 값은 USER_GRADE 테이블의 GRADE_CODE 컬럼에서 제공하는 값이 아니므로
--외래키 제약 조건에 위배되어 오류 발생

integrity
: 진실성

10-1) 회원 아이디, 이름, 성별, 연락처, 회원 등급명 조회

SELECT USER_ID, USER_NAME, GENDER, PHONE, GRADE_NAME
FROM USER_FOREIGNKEY
LEFT JOIN USER_GRADE USING(GRADE_CODE);