-- 7-1 employees 테이블의 last_name이 'Da_haan'인 직원과 salary가 동일한 직원, 단일 행 서브쿼리
select * from employees where salary = (select salary from employees where last_name like 'De Haan');
select * from employees where salary = (select salary from employees where last_name like 'De Haan');
-- 7-2 다중 행 서브쿼리 employees 테이블에서 department_id 별로 가장 낮은 salary에 해당하는 직원, 다중 행 서브쿼리
select * from employees e
where e.salary in (select min(salary) from employees group by department_id)
order by e.salary desc;
-- 7-3 다중 열 서브쿼리, employees 테이블에서 job_id 별로 가장 낮은 salary가 얼마인지 찾아서
-- 찾아낸 job_id 별 salary에 해당하는 직원 찾기, where 조건 ()괄호로 묶기
select * from employees e
where (e.job_id, e.salary)
in (select job_id, min(salary) from employees group by job_id);
-- 7-4 인라인 뷰 (from절에 서브쿼리)로 직원중에서 department_name 이 it인 직원 가져오기
-- 외래키를 select 절에서 서브쿼리로 가져와서 비교하는 것 같다
select * from employees e, (select department_id from departments where department_name = 'IT') d
where e.department_id = d.department_id;
select * from employees;
-- 테이블 생성, 그냥 열 하나씩 입력하는 방법, create table 테이블명 as 다음 조건문으로 테이블 복사, 가져오기
-- alter table 테이블명 add (컬럼명 속성)
-- alter table 테이블명 modify (컬럼명 속성)
-- alter table 테이블명 drop column 컬럼명
-- 테이블의 모든 데이터 삭제하기 truncate table 테이블명;
-- rename tableA to tableB; <- 테이블명 변경
-- insert into emp select * from employees; <- 서브쿼리를 이용해서 데이터 복사
-- update sam02 set (sal, hiredate) = (select sal, hiredate from sam02 where ename = 'KING');
-- update 서브쿼리로 이름이 king인 사람의 데이터 로 sam02를 일괄 갱신, set에도 서브쿼리 사용이 가능하다
-- 제약조건 5개 nn, u, pk - nn+unique
-- fk : 참조되는 테이블의 칼럼의 값이 존재하면 허용한다, 참조하는 값은 부모 테이블의 기본키거나 유니크 제약조건을 만족해야 한다
-- check - 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.
-- 유니크 제약조건을 설정했어도 null은 값이 아니기에 중복해서 들어갈 수 있다
--제약 조건명을 명시한 제약조건, 테이블명_칼럼명_제약조건유형
-- 테이블 레벨 제약조건
-- 복합키가 기본키거나, ALTER 테이블로 밑에처럼 제약조건을 추가할때는 테이블 레벨로 제약조건을 추가한다
-- 기본키로 설정할 속성이 여러개일때 속성을 선언하고 아래처럼 테이블 레벨로 제약조건을 추갛나다
-- CONSTRAINT MEMBER01_COMBO_PK PRIMARY KEY(NAME, HPHONE)
-- 다른 제약조건들도 테이블 레벨로 추가하기
-- CONSTRAINT EMP03_EMPNO_PK PRIMARY KEY(EMPNO),
-- CONSTRAINT EMP03_JOB_UK UNIQUE(JOB),
-- CONSTRAINT EMP03_DEPTNO_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
-- 이미 생성된 테이블에 제약조건 추가는 ADD, 널 제약조건만 MODIFY
-- ALTER TABLE EMP01 ADD CONSTRAINT EMP01_EMPNO_PK PRIMARY KEY(EMPNO);
-- ALTER TABLE EMP01 ADD CONSTRAINT EMP01_DEPTNO_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);
-- ALTER TABLE EMP04 MODIFY ENAME2 CONSTRAINT EMP02_ENAME_NN NOT NULL;
-- 제약 조건 제거하기 DROP CONSTRAINT 제약명 형태
-- ALTER TABLE EMP05 DROP CONSTRAINT EMP05_EMPNO_PK;
-- 제약 조건을 막 삭제하면 데이터 무결성이 지켜지지 않아서 DISABLE/ENABLE로 제약조건을 활성/비활성 한 후에 데이터 변경이 가능하다
-- ALTER TABLE EMP01 DISABLE CONSTRAINT EMP01_DEPTNO_FK;
--하지만 위의 DISABLE을 사용해서 제약조건을 비활성화하면 연결된 테이블에서 문제가 생길 수 있다
-- CASCADE 옵션을 붙여서 연속적으로 자식 테이블의 제약조건을 비활성화, 삭제할 수 있다
-- ALTER TABLE DEPT01 DISABLE PRIMARY KEY CASCADE;
-- ALTER TABLE DEPT01 DROP PRIMARY KEY CASCADE;
desc departments;
CREATE TABLE EMP04(
EMPID NUMBER(4) CONSTRAINT EMP04_EMPID_PK PRIMARY KEY ,
EMPNO NUMBER(4) CONSTRAINT EMP04_EMPNO_UK UNIQUE,
ENAME VARCHAR2(10) CONSTRAINT EMP04_ENAME_NN NOT NULL,
SAL NUMBER(7, 2) CONSTRAINT EMP04_SAL_CK CHECK(SAL BETWEEN 500 AND 5000),
GENDER VARCHAR2(1) CONSTRAINT EMP07_GENDER_CK CHECK (GENDER IN('M', 'F')),
LOC VARCHAR2(13) DEFAULT 'SEOUL',
DEPARTMENT_ID NUMBER(4) CONSTRAINT EMP04_DEPARTMENT_ID_FF REFERENCES DEPARTMENTS(DEPARTMENT_ID) --외래 키 제약조건
);
DESC EMP04;
ALTER TABLE EMP04 DROP COLUMN DEPARTMENT_ID;
ALTER TABLE EMP04 ADD (DEPARTMENT_ID NUMBER(4));
ALTER TABLE EMP04 MODIFY (DEPARTMENT_ID NUMBER(4) CONSTRAINT EMP04_DEPARTMENT_ID_FF REFERENCES DEPARTMENTS(DEPARTMENT_ID));
ALTER TABLE EMP04 DROP COLUMN DEPARTMENT_ID;
ALTER TABLE EMP04 ADD CONSTRAINT EMP04_TEST_UK UNIQUE(TEST);
DESC EMP04;
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('EMP04');
DROP TABLE EMP04;
SELECT TABLE_NAME, CONSTRAINT_TYPE,
CONSTRAINT_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('EMP04');
