담달의 숨은 마을

desc user_constraints
desc user_cons_columns

SELECT * FROM user_constraints
ORDER BY constraint_type

SELECT * FROM user_cons_columns

SELECT table_name ,constraint_name , constraint_type , column_name , r_constraint_name
FROM user_constraints
JOIN user_cons_columns
USING (table_name , constraint_name)
WHERE table_name = 'EMPLOYEES'

SELECT table_name , constraint_name ,constraint_type , column_name , search_condition ,r_constraint_name
FROM user_constraints
JOIN user_cons_columns
USING (table_name , constraint_name)
WHERE table_name = 'DEPARTMENTS'

부서 테이블에 신규 데이터를 추가하자

SELECT * FROM departments

INSERT INTO departments
VALUES (1001 , 'Entertainment', null, null);

SEQUENCE

시작값을 290으로 증가값을 10으로 하는 SEQUNCE 를 생성하자.

CREATE SEQUENCE department_seq START WITH 290 INCREMENT BY 10

INSERT INTO departments
VALUES (department_seq.NEXTVAL , 'Entertainment2', null, null);

commit
rollback

DESC user_sequences
SELECT * FROM user_sequences

-- 보너스 테이블 생성
CREATE TABLE bonuses(
employee_id number(4),
bonuses_salary number(8,2)
)

SELECT * FROM bonuses

-- 보너스  테이블에 값 넣어주기
INSERT INTO bonuses
   SELECT employee_id, salary*1.1
   FROM employees
   WHERE commission_pct > 0.25; 

-- 테이블 생성하면서 데이터 추가
CREATE TABLE emp(empno , sal)
AS
SELECT employee_id, salary*1.1
FROM employees
WHERE commission_pct > 0.25;
  
SELECT * FROM emp

DESC emp

--원격 테이블 생성

--remote 생성
CREATE DATABASE LINK remote
CONNECT TO oe IDENTIFIED BY oe
USING 'YOU'

--확인
SELECT * FROM orders@remote;