/*
오라클 학습용 계정
*/
ALTER USER HR ACCOUNT UNLOCK;
ALTER USER HR IDENTIFIED BY 1234;
-- HR계정이 가지고 있는 테이블 조회
SELECT * FROM DBA_TABLES WHERE OWNER = 'HR';
SELECT * FROM HR.REGIONS;
/*
<SYNONYM>
데이터베이스 객체에 대한 별칭, 동의어
CREATE OR REPLACE [PUBLIC] SYNONYM
[스키마명.]시노님명 FOR[스키마명.]객체명;
기본값은 PRIVATE으로 생성됨
*/
-- 시노님 생성
CREATE OR REPLACE SYNONYM REGIONS FOR HR.REGIONS;
CREATE OR REPLACE PUBLIC SYNONYM P_REGIONS FOR HR.REGIONS;
-- 시노님 조회
SELECT * FROM ALL_SYNONYMS WHERE TABLE_OWNER = 'HR';
-- 시노님 제거
DROP PUBLIC SYNONYM P_REGIONS;
DROP SYNONYM REGIONS;
SELECT * FROM REGIONS;
GRANT SELECT ON REGIONS TO PUBLIC;
REVOKE SELECT ON REGIONS FROM JUNGANG;
-- 권한 부여
GRANT SELECT ON P_REGIONS TO PUBLIC ;
-- 권한 제거
REVOKE SELECT ON P_REGIONS FROM PUBLIC;
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'JUNGANG';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'JUNGANG';
-- HR계정의 JOBS테이블의 조회 권한을 JUNGANG에 부여
GRANT SELECT ON HR.JOBS TO JUNGANG;
-- HR계정의 테이블 권한을 조회
SELECT * FROM DBA_TAB_PRIVS WHERE OWNER = 'HR';
-- 시노님(동의어)을 생성 할 수 있는 권한 부여
GRANT CREATE SYNONYM TO JUNGANG;
-- 계정의 권한을 조회
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'JUNGANG';
-- 조회권한 회수
REVOKE SELECT ON HR.JOBS FROM JUNGANG;
-- 생성권한 회수
REVOKE CREATE SYNONYM FROM JUNGANG;
/*
SQL_DDL
*/
-- 1.계열 정보를 저장할 카테고리 테이블을 만들려고 한다. 다음과 같은 테이블을 작성하시오.
CREATE TABLE tb_category (
NAME VARCHAR(10),
USE_YN CHAR(1) DEFAULT 'Y' CHECK(USE_YN IN ('Y','N'))
);
-- 2. 과목 구분을 저장할 테이블을 만들려고 한다. 다음과 같은 테이블을 작성하시오.
CREATE TABLE TB_CLASS_TYPE (
NO VARCHAR2(5) PRIMARY KEY,
NAME VARCHAR2(10)
);
-- 3. TB_CATEGORY 테이블의 NAME 컬럼에 PRIMARY KEY를 생성하시오.
ALTER TABLE tb_category ADD CONSTRAINT tb_category_name_pk PRIMARY KEY(name);
-- 4.TB_CLASS_TYPE 테이블의 NAME 컬럼에 NULL 값이 들어가지 않도록 속성을 변경하시오.
ALTER TABLE tb_class_type MODIFY name CONSTRAINT tb_class_type_name_nn NOT NULL;
-- 5.두 테이블에서 컬럼 명이 NO인 것은 기존 타입을 유지하면서 크기는 10으로
-- , 컬럼명이 NAME인 것은 마찬가지로 기존 타입을 유지하면서 크기 20으로
-- 변경하시오.
ALTER TABLE tb_class_type MODIFY no VARCHAR2(10);
ALTER TABLE tb_class_type MODIFY name VARCHAR2(20);
ALTER TABLE tb_category MODIFY name VARCHAR2(20);
-- 6번
-- 두 테이블의 NO 컬럼과 NAME 컬럼의 이름을 각 테이블 이름이 앞에 붙은 형태로 변경한다.
-- EX. CATEGORY_NAME
ALTER TABLE tb_category RENAME COLUMN name TO category_name;
ALTER TABLE tb_class_type RENAME COLUMN name TO class_tpye_name;
ALTER TABLE tb_class_type RENAME COLUMN no TO class_type_no;
DESC tb_category;
-- 7.
SELECT CATEGORY
FROM tb_department
GROUP BY category;
INSERT INTO tb_category (
SELECT CATEGORY, 'Y'
FROM tb_department
GROUP BY category
);
commit;
-- 8번
-- TB_DEPARTMENT의 CATEGORY 컬럼이
-- TB_CATEGORY 테이블의 CATEGORY_NAME 컬럼을 부모값으로 참조하도록 FOREIGN KEY를 지정하시오.
ALTER TABLE tb_department ADD CONSTRAINT fk_department_category FOREIGN KEY(category) REFERENCES tb_category(category_name);
-- 9번
-- 학생들의 정보만이 포함되어 있는 학생일반정보 VIEW를 만들고자 한다.
-- 아래 내용을 참고하여 적절한 SQL문을 작성하시오.
-- 학번, 학생이름, 주소
CREATE VIEW vw_학생일반정보(학번, 학생이름, 주소)
AS SELECT student_no, student_name, student_address
FROM tb_student;
SELECT * FROM vw_학생일반정보 WHERE 학번 = 'A317222';
-- 10번
-- 1년에 두 번씩 학과별로 지도교수가 지도 면담을 진행한다.
-- 이를 위해 사용할 학생이름, 학과이름, 담당교수이름으로 구성되어 있는 VIEW를 만드시오.
-- 이때 지도 교사가 없는 학생이 있을 수 있음을 고려하시오.
-- (학과별로 정렬되어 화면에 보여지게 만드시오.)
CREATE VIEW vw_지도면담(학생이름, 학과이름, 지도교수이름)
AS
SELECT student_name, department_name, NVL(professor_name, '지도교수없음')
FROM tb_student
JOIN tb_department USING(department_no)
LEFT JOIN tb_professor ON(COACH_PROFESSOR_NO=PROFESSOR_NO)
ORDER BY department_name;
SELECT * FROM vw_지도면담;
-- 11번
-- 모든 학과의 학과별 학생 수를 확인할 수 있도록 적절한 VIEW를 작성해보자.
CREATE VIEW vw_학과별학생수(학과이름, 학생수)
AS
SELECT department_name, COUNT(student_no)
FROM tb_student
JOIN tb_department USING(department_no)
GROUP BY department_no, department_name
ORDER BY COUNT(student_no);
SELECT * FROM vw_학과별학생수;
-- 12번
-- 위에서 생성한 학생일반정보 VIEW를 통해서 학번이 A213046인 학생의 이름을 본인 이름으로 변경 해봅시다
UPDATE vw_학생일반정보
SET 학생이름 = '나예쁨'
WHERE 학번 = 'A213046';
SELECT * FROM vw_학생일반정보 WHERE 학번 = 'A213046';
SELECT * FROM tb_student WHERE student_no = 'A213046';
-- 13. 12번에서와 같이 VIEW를 통해서 데이터가 변경될 수 있는 상황을 막으려면 VIEW를 어떻게 생성해야 하는지 작성하시오
-- WITH READ ONLY 기재 시 SELECT만 가능
CREATE OR REPLACE VIEW vw_학생일반정보(학번, 학생이름, 주소)
AS SELECT student_no, student_name, student_address
FROM tb_student
WITH READ ONLY;
-----------------------------------------------------------------------------
-- 14. 사용자가 SYNONYM 생성, 사용
-- 관리자로부터 HR계정의 JOBS테이블의 조회권한을 받아야 조회가 가능
SELECT * FROM HR.JOBS;
-- 시노님 생성
-- 관리자로부터 시노님의 생성 권한을 받아야 생성이 가능
CREATE SYNONYM JOBS FOR HR.JOBS;
-- 시노님 조회
SELECT * FROM JOBS;
-- 시노님 삭제
DROP SYNONYM JOBS;
-----------------------------------------------------------------------------
-- 15. 시퀀스
-----------------------------------------------------------------------------
SELECT * FROM TB_CLASS_TYPE;
SELECT CLASS_TYPE FROM TB_CLASS GROUP BY CLASS_TYPE;
CREATE SEQUENCE SEQ_01;
-- NEXTVAL 이후 CURRVAL 사용 가능
SELECT SEQ_01.NEXTVAL FROM DUAL; -- 값을 증가시킨후 반환
SELECT SEQ_01.CURRVAL FROM DUAL;
-- 10씩 증가하는 시퀀스를 생성
CREATE SEQUENCE seq_tb_class_type_no START WITH 10 INCREMENT BY 10;
SELECT seq_tb_class_type_no.CURRVAL FROM DUAL;
-- 시퀀스 삭제
DROP SEQUENCE seq_tb_class_type_no;
-- class_type 중복을 제거후 삽입 (DISTINCT)
-- 시퀀스를 이용해서 일괄적으로 삽입
/*
INSERT INTO 테이블명
서브쿼리
서브쿼리의 조회결과집합을 테이블에 삽입
*/
INSERT INTO tb_class_type (
SELECT seq_tb_class_type_no.NEXTVAL, class_type
FROM ( SELECT class_type FROM TB_CLASS
GROUP BY CLASS_TYPE)
);
댓글