[문제] Day67-1 - oracle 예제 문제

※ 순차적으로 실습해보기!

 

# 8

Q11. vtbl_1 테이블에 no4 가상 컬럼을 추가하는데 no4 컬럼의 값은 no1 * 12 + no2로 하고,
       그리고 vtbl_1 테이블 전체 데이터 조회하기

A.

ALTER TABLE vtbl_1 ADD (no4 GENERATED ALWAYS AS ((no1*12)+no2) VIRTUAL); 
SELECT * FROM vtbl_1;

Q12. sqlplus에서 출력되는 행을 100으로 하기(100character씩 끊어서)
A.

SET LINE 100  # SET LINEsize 100의 약어 
SELECT * FROM jobs; 
SET LINE 50 
SELECT * FROM jobs;

Q13. 컬럼 column_name 10 character 길이로 세팅하기 

A.

COL column_name FOR a10  # COLumn column_name FORmat a10 줄임말 @ 컬럼 no1을 5자리 숫자 길이로 세팅

col no1 for 99999 
SELECT * FROM jobs;

COL job_id for a20 
SELECT * FROM jobs; 

COL job_id for a10 
SELECT * FROM jobs; 

COL min_salary for 9999999999 
SELECT * FROM jobs; 

COL min_salary for 9,999,999,999  
SELECT * FROM jobs; 

COL min_salary for 9,999,999,999.99 
SELECT * FROM jobs;

Q14. 컬럼 data_type 10 character 길이와 data_default 20 character 길이로 세팅하기 

A.

COL data_type FOR a10 
COL data_default FOR a20

 
Q15. user_tab_columns 테이블에서 

       table_name이 ‘VTBL_1’인 것들의

       column_name, data_type, data_default를 column_id로 오름차순 정렬해서 출력하기

A.

SELECT column_name, data_type, data_default FROM user_tab_columns WHERE table_name='VTBL_1' ORDER BY column_id;

// Tip : 테이블이름, 컬럼이름은 ''안에 들어갈 경우 대문자로 표기해야 한다.

Q16. employees5 테이블을 만드는데, 

       employees 테이블의 department_id가 100 초과인 것들의 department_id, first_name을 가지고 만들기

       그리고 employees5 테이블의 모든 데이터 조회하기

A.

CREATE TABLE employees5 AS SELECT department_id, first_name from employees WHERE department_id > 100; 
SELECT * FROM employees5;

Q17. employees5 테이블에 LOC라는 이름의 컬럼을 가변형 문자 10글자로 추가하기
       그리고 employees5 테이블의 모든 데이터를 출력하기 
A.

ALTER TABLE employees5 ADD(LOC VARCHAR2(10)); 
SELECT * FROM employees5;

Q18. employees5 테이블에 컬럼 LOC2를 추가하는데 가변형문자 10글자로 기본값은 ‘서울’로 하고, 

       employees5 테이블의 전체 데이터를 출력한다.
A.

ALTER TABLE employees5 ADD (LOC2 varchar2(10) DEFAULT '서울'); 
SELECT * FROM employees5;

Q19. employees5 테이블의 LOC2 컬럼을 AREA로 이름을 변경하기 
       employees5 테이블의 구조 보기 
A.

ALTER TABLE employees5 RENAME COLUMN LOC2 TO AREA; 
DESC employees5;

Q20. employees5 테이블을 employees6로 테이블 이름을 변경하기 
A.

RENAME employees5 to employees6; 
SELECT table_name FROM tabs;

// Tip : 실습을 다시 해볼 때

DROP TABLE employees6;

 

#9

Q21. employees6 테이블의 area 컬럼의 가변형 문자길이 10을 가변형 문자길이 20으로 변경하기

       그리고 employees6의 테이블 구조보기 
A.

ALTER TABLE employees6 MODIFY (area VARCHAR2(20)); 
DESC employees6;

Q22. employees6 테이블의 loc 컬럼 삭제하기

       그리고 employees6의 테이블 구조보기
A.

ALTER TABLE employees6 DROP COLUMN loc; 
DESC employees6;

//Tip: 만약 참조키로 설정되어 있는 부모 테이블의 컬럼을 삭제하려 할 경우 에러가 발생하는데 

        아래와 같은 방법으로 지우면 된다.

ALTER TABLE employees6 DROP COLUMN loc CASCADE CONSTRAINTS;

Q23. t_read라는 테이블을 컬럼 이름 no, 자료형 숫자, 컬럼 이름 name 가변형 문자 10자 길이로 만들기
A.

CREATE TABLE t_read ( no NUMBER, name VARCHAR2(10));

Q24. t_read라는 테이블에 1, ‘AAA’라는 값을 삽입하고,

      삽입된 값 확인하기 
A.

INSERT INTO t_read  VALUES(1, 'AAA'); 
SELECT * FROM t_read; 

Q25. t_read라는 테이블을 읽기 전용 테이블로 만들기
A.

ALTER TABLE t_read read only;

Q26. t_read에 tel 컬럼을 숫자 자료형으로 기본값은 111으로 해서 추가하기

      그리고 에러가 나는 것도 확인하기 
A.

ALTER TABLE t_read ADD(tel number default 111);

Q27. t_read 테이블을 읽기, 쓰기가 가능하도록 바꾸기
A.

ALTER TABLE t_read read write; 

//Tip : 읽기 전용 테이블도 삭제는 가능하다.

drop table t_read;

Q28. t_read 테이블이 읽기 전용인지 조회하기
A.

SELECT table_name, read_only FROM user_tables WHERE table_name ='T_READ';

Q29. t_read의 데이터만 지우고 쓰고 있던 디스크 상의 공간은 그대로 유지하기
A.

delete from t_read;

Q30. t_read를 최초에 테이블이 만들어졌던 상태

       즉, 데이터가 1건도 없는 상태로 모든 데이터를 삭제하고 컬럼값만 남겨 놓기 
A.

truncate table t_read;

 

#10

Q31. t_read를 데이터와 테이블 모두 지우기

A.

drop table t_read;

Q32. employees6 테이블에 아래와 같은 내용으로 새로운 부서 정보 입력하기
* department_id : 9000
* first_name : James
* area : seoul

A.

INSERT INTO employees6(department_id, first_name, area) values(9000, 'James', 'seoul');

 

// Tip : 위 명령어에서 모든 컬럼에 데이터를 넣을 경우 아래와 같이 테이블 이름 뒤에 컬럼 이름 생략 가능하다.

INSERT INTO employees6 VALUES(9001, 'John', 'busan');

Q33. employees6 테이블에 department_id, first_name 컬럼에만 아래의 정보 입력하기
* department_id : 9002
* first_name : susan
A.

INSERT INTO employees6(department_id, first_name) VALUES(9002, 'susan');

34. employees7 테이블을 만드는데 employees 테이블에서 department_id, first_name, hire_date 구조 가져오기
A.

CREATE TABLE employees7  
AS SELECT department_id, first_name, hire_date  
FROM employees WHERE 1 = 2;

 
Q35. employees7 테이블에 아래 내용 입력하기
* department_id : 5001
* first_name : 박동주
* hire_date : 2014년 12월01일
A.

INSERT INTO employees7 (department_id, first_name, hire_date)  
VALUES (5001, '박동주', '2014-12-01'); 

// Tip :  만약 위의 방식이 에러가 난다면, 날짜 형식을 바꾼다.

① 방법

INSERT INTO employees7 (department_id, first_name, hire_date) VAULES(5001, '박동주', TO_DATE('2014-12-01'));

② 방법

ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD:HH24:MI:SS’; 
INSERT INTO employees7 (department_id, first_name, hire_date) VALUES (5001, '박동주', '2014-12-01');

// Tip : 현재 시간을 입력 받는 방법

INSERT INTO employees7 values(5002, '홍길동', SYSDATE); 
SELECT * FROM employees7;

Q36. employees 테이블에서 department_id가 100 이상인 부서의

       department_id, first_name, hire_date 값을 가져와서 employees7 테이블에 입력하기
A.

INSERT INTO employees7 SELECT department_id, first_name, hire_date FROM employees WHERE department_id >= 100; 

Q37. employees7 테이블에서 department_id가 110인 사원들의 hire_date를 SYSDATE로 변경하기
A.

UPDATE employees7 SET hire_date = SYSDATE WHERE department_id = 110;

Q38. employees7 테이블에서 department_id가 110인 사원들을 삭제하기
A.

DELETE FROM employees7 WHERE department_id = 110;

Q39. pt_01테이블을 다음 컬럼들로 만들기
판매번호 VARCHAR2(8)
제품번호 NUMBER
수량 NUMBER
금액 NUMBER
A.

CREATE TABLE pt_01 (판매번호 VARCHAR2(8), 제품번호 NUMBER, 수량 NUMBER, 금액 NUMBER);

Q40. pt_02 테이블을 다음 컬럼들로 만들기
판매번호 VARCHAR2(8)
제품번호 NUMBER
수량 NUMBER
금액 NUMBER
A.

CREATE TABLE pt_02 (판매번호 VARCHAR2(8), 제품번호 NUMBER, 수량 NUMBER, 금액 NUMBER);

 

# 11

Q41. p_total 테이블을 다음 컬럼들로 만들기
판매번호 VARCHAR2(8)
제품번호 NUMBER
수량 NUMBER
금액 NUMBER
A.

CREATE TABLE p_total(판매번호 VARCHAR2(8), 제품번호 NUMBER, 수량 NUMBER, 금액 NUMBER);

Q42. 다음 데이터를 pt_01에 컬럼 순으로 입력하기
'14120101', '1000', 1, 500
'14120102', '1001', 1, 400
'14120103', '1003', 1, 300
A.

INSERT INTO pt_01 VALUES('14120101', '1000', 1, 500); 
INSERT INTO pt_01 VALUES('14120102', '1001', 1, 400); 
INSERT INTO pt_01 VALUES('14120103', '1003', 1, 300); 

Q43. 다음 데이터를 pt_02에 컬럼 순으로 입력하기
'14120201', '1004', 1, 600
'14120202', '1000', 1, 500
'14120203', '1005', 1, 700
A.

INSERT INTO pt_02 VALUES('14120201', '1004', 1, 600); 
INSERT INTO pt_02 VALUES('14120202', '1000', 1, 500); 
INSERT INTO pt_02 VALUES('14120203', '1005', 1, 700); 

 

Q44. pt_01의 데이터 전부 출력하기 
A.

SELECT * FROM pt_01;

Q45. pt_02의 데이터 전부 출력하기 
A.

SELECT * FROM pt_02;

 

// Tip : 테이블에 데이터가 이미 존재하면 업데이트 하고,

          존재하지 않으면 입력을 해야 하는 경우가 종종 있다. 

          오라클에서 이런 작업을 한 번에 할 수 있는 쿼리가 MERGE INTO문 이다.

Q46. p_total 테이블에 p_total의 판매번호가 pt_01의 판매번호와 같으면

       p_total의 제품번호를 pt_01의 제품번호로 수정하고

       그렇지 않으면, p_total테이블에 pt_01의 판매번호, 제품번호, 수량, 금액을 삽입하기
A.

MERGE INTO p_total total USING pt_01 p01 ON(total.판매번호 = p01.판매번호) 
	WHEN MATCHED THEN UPDATE SET total.제품번호=p01.제품번호 
    WHEN NOT MATCHED THEN INSERT VALUES(p01.판매번호, p01.제품번호, p01.수량, p01.금액);

Q47. p_total 테이블에 p_total의 판매번호가 pt_02의 판매번호와 같으면

       p_total의 제품번호를 pt_02의 제품번호로 수정하고

       그렇지 않으면, p_total테이블에 pt_02의 판매번호, 제품번호, 수량, 금액을 삽입하기
A.

MERGE INTO p_total total USING pt_02 p02 ON(total.판매번호=p02.판매번호) 
	WHEN MATCHED THEN UPDATE SET total.제품번호=p02.제품번호 
    WHEN NOT MATCHED THEN INSERT VALUES(p02.판매번호, p02.제품번호, p02.수량, p02.금액); 

 

Q48. p_total의 모든 데이터 조회하기

A. 

select * from p_total;

 

Q50. employees9 테이블을 만드는데  
컬럼 이름 no 4자리 숫자 PRIMARY KEY 
컬럼 이름 name 가변형 10자리 NULL을 허용하지 않기 
컬럼 이름 jumin 가변형 13자리 NULL을 허용하지 않고, 유일값 
컬럼 이름 area 1자리 숫자이고 값은 5미만의 숫자 
컬럼 이름 departno 가변형 6자리 departments 테이블의 department_id를 참조하기

A. 

CREATE TABLE employees9 
( no NUMBER(4) 
    CONSTRAINT emp9_no_pk primary key, 
    name varchar2(10) 
    CONSTRAINT emp9_name_nn not null, 
    jumin varchar2(13) 
    CONSTRAINT emp9_jumin_uk unique, 
    area number(1) 
    constraint emp9_area_ck check(area < 5), 
   deptno number(4) 
   CONSTRAINT emp_deptno_fk references departments(department_id) 
);

 
// Tip : 제약조건 조회

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'EMPLOYEES9'; 
SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'EMPLOYEES9';

 

# 제약조건의 종류와 특징 

① NOT NULL : 이 조건이 설정된 컬럼에는 NULL 값이 입력되지 못한다.
② UNIQUE : 이 조건이 설정된 컬럼에는 중복된 값이 입력되지 못한다.
③ PRIMARY KEY: 이 조건은 NOT NULL + UNIQUE의 의미를 가지며 

                       테이블 내에서 데이터들끼리의 유일성을 보장하는 컬럼에 설정할 수 있으며

                       테이블 당 1개만 설정할 수 있다.
④ FOREIGH KEY: 이 조건은 다른 테이블의 컬럼을 참조해서 무결성 검사를 하게 된다.
⑤ CHECK : 이 조건으로 설정된 범위 값만 입력을 허용하고 나머지는 거부된다.