서브쿼리 구문을 사용한 테이블 생성

create table dept80
as
select employee_id, last_name, salary*12 ANNSAL, hire_date
from employees
where department_id=80;

desc dept80;

create table copy_dept
as
select * from departments;

desc copy_dept;

테이블 수정(alter table)

• alter table 테이블명 add ---;
  컬럼 추가, 제약조건 추가
• alter table 테이블명 modify ---;
  컬럼 수정, 제약조건 추가
• alter table 테이블명 drop ---;
  컬럼 삭제, 제약조건 삭제
• alter table 테이블명 rename ---;
  컬럼명 수정, 제약조건명 수정

➀ 테이블에 컬럼 추가

alter table dept80
add (job_id varchar2(10));

desc dept80;

➁ 테이블의 기존 컬럼 수정 – 데이터타입, 컬럼 사이즈, Default값 등...

alter table dept80
modify (job_id varchar2(20));

alter table dept80
modify (last_name varchar2(20));

➂ 테이블의 기존 컬럼 삭제

alter table dept80
drop column job_id;

desc dept80;

➃ 테이블의 기존 컬럼에 제약조건 추가

[문법1] table level 문법

=> pk, fk, uk, ck
alter table 테이블명
add constraint 제약조건명 제약조건유형(컬럼명);

[문법2] column level 문법

=> pk, fk, uk, ck, nn
alter table 테이블명
modify 컬럼명 constraint 제약조건명 제약조건유형

create table emp2
as
select * from employees;

• employees 제약조건 확인하기

• emp2 제약조건 확인하기

not null 제약조건만 옮겨짐
제약조건명은 오라클에서 자동으로 부여되는 이름

• emp2 테이블에 제약조건 추가하기

alter table emp2
add constraint e2_eid_pk primary key(employee_id);

alter table emp2
add constraint e2_sal_ck check(salary>0);

alter table emp2
modify department_id constraint e2_did_fk references departments(department_id);

alter table emp2
modify manager_id constraint e2_mid_fk references emp2(employee_id);

➄ 테이블의 제약조건 삭제

alter table emp2
drop constraint e2_sal_ck;

삭제됨

alter table emp2
drop primary key cascade;

cascade 자식도 같이 삭제

이름 수정

• 컬럼명 수정

alter table emp2
rename column hire_date to start_date;

• 제약 조건명 수정

alter table emp2
rename constraint sys_c007452 to e2_jid_nn;

[참고] 테이블명 수정

rename 테이블명 to 바꿀 테이블명

rename emp2 to emp5;

테이블 삭제(drop table)

➀ 테이블 삭제 명령어

drop table 테이블명 [purge] ;

➁ Flashback Table 기능

• 실수로 테이블을 삭제한 경우 휴지통(recyclebin)으로부터 되살리는 작업

• 휴지통 조회
  select original_name, operation, droptime from recyclebin;
  (==) show recyclebin

• 휴지통으로부터 테이블 되돌리기
  flashback table 테이블명 to before drop;

select original_name, operation, droptime from recyclebin;

show recyclebin;

• emp5 삭제

drop table emp5;

• 휴지통 조회

select original_name, operation, droptime from recyclebin;

• 복구하기

flashback table emp5 to before drop;

➂ 휴지통을 거치지 않고 테이블 삭제하기

purge 옵션 붙이기

테이블 절단(truncate table)

• 테이블 구조는 남겨두고 테이블의 모든 행 제거함
• DDL구문이므로 rollback 안됨

➀ 테이블 절단 명령어

truncate table 테이블명;
(==) delete from 테이블명;

➁ TRUNCATE와 DELETE 비교

	DELETE	TRUNCATE
개념	테이블의 특정 행 삭제 (단, WHERE절 생략 시 모든 행 삭제됨)	테이블의 모든 행 절단(삭제)
문법	SQL> delete from 테이블명;	SQL> truncate table 테이블명;
차이점	DML(데이터조작어) 저장공간 반납 X ROLLBACK O	DDL(데이터정의어) 저장공간 반납 O ROLLBACK X

join 연습문제

1번

select location_id, street_address, city, state_province, country_name
from locations natural join countries;

2번

select e.last_name, e.job_id, d.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id
join locations l
on l.location_id = d.location_id
where l.city = 'Toronto';

3번

select e1.last_name "Employee", e1.employee_id "EMP#", e2. last_name "Manager",
e1.manager_id "Mgr#" 
from employees e1 left join employees e2
on e1.manager_id = e2.employee_id
order by EMP#;

4번

select e1.last_name, e1.hire_date, e2.last_name, e2.hire_date
from employees e1 join employees e2
on e1.manager_id = e2.employee_id
where e1.hire_date<e2.hire_date;

5번

select d.department_id, d.department_name, d.location_id, count(e.employee_id)
from employees e right join departments d
on e.department_id = d.department_id
group by d.department_id, d.department_name, d.location_id;