DROP TABLE STUDENT_T;
DROP TABLE PROFESSOR_T;
DROP TABLE DEPARTMENT_T;
CREATE TABLE DEPARTMENT_T (
DEPT_NO NUMBER(4) NOT NULL
, DEPT_NAME VARCHAR2(30 BYTE) NULL
, DEPT_LOC VARCHAR2(10 BYTE) NULL
, CONSTRAINT PK_DEPT PRIMARY KEY(DEPT_NO)
);
CREATE TABLE PROFESSOR_T (
PROF_NO NUMBER(4) NOT NULL
, PROF_NAME VARCHAR2(10 BYTE) NULL
, PROF_POS VARCHAR2(20 BYTE) NULL
, PROF_SAL NUMBER(10) NULL
, PROF_HIRED DATE NULL
, PROF_COMM NUMBER(2) NULL
, DEPT_NO NUMBER(4) NULL
, CONSTRAINT PK_PROF PRIMARY KEY(PROF_NO)
, CONSTRAINT FK_DEPT_PROF FOREIGN KEY(DEPT_NO) REFERENCES DEPARTMENT_T(DEPT_NO)
);
CREATE TABLE STUDENT_T (
STUD_NO NUMBER(5) NOT NULL
, STUD_NAME VARCHAR2(20 BYTE) NULL
, STUD_GRADE VARCHAR2(1 BYTE) NULL
, STUD_BORN DATE NULL
, STUD_TEL VARCHAR2(13 BYTE) NULL
, STUD_HEIGHT NUMBER(5,2) NULL
, STUD_WEIGHT NUMBER(5,2) NULL
, DEPT_NO NUMBER(4) NULL
, CONSTRAINT PK_STUD PRIMARY KEY(STUD_NO)
, CONSTRAINT FK_DEPT_STUD FOREIGN KEY(DEPT_NO) REFERENCES DEPARTMENT_T(DEPT_NO)
);
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, DEPT_LOC) VALUES(100, '컴퓨터공학과', '1호관');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, DEPT_LOC) VALUES(200, '멀티미디어학과', '2호관');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, DEPT_LOC) VALUES(300, '전자공학과', '3호관');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, DEPT_LOC) VALUES(400, '기계공학과', '4호관');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, DEPT_LOC) VALUES(500, '정보미디어학과', '5호관');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, DEPT_LOC) VALUES(600, '메카트로닉스학과', '6호관');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, DEPT_LOC) VALUES(700, '빅데이터학과', '7호관');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, DEPT_LOC) VALUES(800, '응용소프트웨어학과', '8호관');
COMMIT;
INSERT INTO PROFESSOR_T(PROF_NO, PROF_NAME, PROF_POS, PROF_SAL, PROF_HIRED, PROF_COMM, DEPT_NO) VALUES(9901, '김도훈', '교수', 5000, TO_DATE('24-01-1982','DD-MM-YYYY'), 20, 100);
INSERT INTO PROFESSOR_T(PROF_NO, PROF_NAME, PROF_POS, PROF_SAL, PROF_HIRED, PROF_COMM, DEPT_NO) VALUES(9902, '이재우', '조교수', 4200, TO_DATE('12-04-1995','DD-MM-YYYY'), NULL, 200);
INSERT INTO PROFESSOR_T(PROF_NO, PROF_NAME, PROF_POS, PROF_SAL, PROF_HIRED, PROF_COMM, DEPT_NO) VALUES(9903, '성연희', '조교수', 4600, TO_DATE('17-05-1993','DD-MM-YYYY'), 15, 300);
INSERT INTO PROFESSOR_T(PROF_NO, PROF_NAME, PROF_POS, PROF_SAL, PROF_HIRED, PROF_COMM, DEPT_NO) VALUES(9904, '염일웅', '전임강사', 5400, TO_DATE('02-12-1998','DD-MM-YYYY'), NULL, 400);
INSERT INTO PROFESSOR_T(PROF_NO, PROF_NAME, PROF_POS, PROF_SAL, PROF_HIRED, PROF_COMM, DEPT_NO) VALUES(9905, '권혁일', '교수', 6500, TO_DATE('08-01-1986','DD-MM-YYYY'), 25, 500);
INSERT INTO PROFESSOR_T(PROF_NO, PROF_NAME, PROF_POS, PROF_SAL, PROF_HIRED, PROF_COMM, DEPT_NO) VALUES(9906, '이만식', '부교수', 4200, TO_DATE('13-09-1988','DD-MM-YYYY'), NULL, 600);
INSERT INTO PROFESSOR_T(PROF_NO, PROF_NAME, PROF_POS, PROF_SAL, PROF_HIRED, PROF_COMM, DEPT_NO) VALUES(9907, '전은지', '전임강사', 6100, TO_DATE('01-06-2001','DD-MM-YYYY'), NULL, 700);
INSERT INTO PROFESSOR_T(PROF_NO, PROF_NAME, PROF_POS, PROF_SAL, PROF_HIRED, PROF_COMM, DEPT_NO) VALUES(9908, '남은혁', '부교수', 4200, TO_DATE('18-11-1990','DD-MM-YYYY'), 17, 800);
COMMIT;
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10101, '전인하', '4', TO_DATE('02-01-1979','DD-MM-YYYY'), '051)781-2158', 176, 72, 100);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(20101, '이동훈', '1', TO_DATE('10-12-1983','DD-MM-YYYY'), '055)426-1752', 172, 64, 300);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10102, '박미경', '1', TO_DATE('16-05-1984','DD-MM-YYYY'), '055)261-8947', 168, 52, 100);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10103, '김영균', '3', TO_DATE('11-01-1981','DD-MM-YYYY'), '051)824-9637', 170, 88 ,100);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(20102, '박동진', '1', TO_DATE('24-11-1985','DD-MM-YYYY'), '051)742-6384', 182, 70, 300);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10201, '김진영', '2', TO_DATE('06-06-1982','DD-MM-YYYY'), '055)419-6328', 164, 48, 200);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10104, '지은경', '2', TO_DATE('12-04-1980','DD-MM-YYYY'), '055)418-9627', 161, 42, 100);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10202, '오유석', '4', TO_DATE('12-10-1977','DD-MM-YYYY'), '051)724-9618', 177, 92, 200);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10203, '하나리', '1', TO_DATE('18-12-1984','DD-MM-YYYY'), '055)296-3784', 160, 68, 200);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10105, '임유진', '2', TO_DATE('21-01-1983','DD-MM-YYYY'), '02)312-9838', 171, 54, 100);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10106, '서재진', '1', TO_DATE('29-11-1985','DD-MM-YYYY'), '051)239-4861', 186, 72, 100);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10204, '윤진욱', '3', TO_DATE('02-04-1979','DD-MM-YYYY'), '053)487-2698', 171, 70, 200);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10107, '이광훈', '4', TO_DATE('13-10-1981','DD-MM-YYYY'), '055)736-4981', 175, 92, 100);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(20103, '김진경', '2', TO_DATE('28-02-1983','DD-MM-YYYY'), '052)175-3941', 166, 51, 300);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(20104, '조명훈', '1', TO_DATE('16-09-1984','DD-MM-YYYY'), '02)785-6984', 184, 62, 300);
INSERT INTO STUDENT_T(STUD_NO, STUD_NAME, STUD_GRADE, STUD_BORN, STUD_TEL, STUD_HEIGHT, STUD_WEIGHT, DEPT_NO) VALUES(10108, '류민정', '2', TO_DATE('19-08-1981','DD-MM-YYYY'), '055)248-3679', 162, 72, 100);
COMMIT;
SET SERVEROUTPUT ON;
-- 1. 다음 사용자를 만드는 쿼리문을 모두 작성하시오.(5점)
-- 1) 사용자명 : SUPERVISOR
-- 2) 비밀번호 : SUPERVISOR
-- 3) 사용자명이 "C##SUPERVISOR"가 되지 않도록 주의하시오.
-- 2. SUPERVISOR 계정에 DBA 권한을 부여하는 쿼리문을 작성하시오.(5점)
-- 3. SUPERVISOR 계정의 DBA 권한을 회수하는 쿼리문을 작성하시오.(5점)
-- 4. SUPERVISOR 계정을 삭제하는 쿼리문을 작성하시오.(5점)
-- 5. 학생(STUDENT_T) 테이블에서 학생 이름과 생일을 조회하시오. 생일(STUD_BORN)은 'YYYY-MM-DD' 형식으로 조회하시오.(5점)
--학생명 생일
--전인하 1979-01-02
--이동훈 1983-12-10
--박미경 1984-05-16
--김영균 1981-01-11
--박동진 1985-11-24
--김진영 1982-06-06
--지은경 1980-04-12
--오유석 1977-10-12
--하나리 1984-12-18
--임유진 1983-01-21
--서재진 1985-11-29
--윤진욱 1979-04-02
--이광훈 1981-10-13
--김진경 1983-02-28
--조명훈 1984-09-16
--류민정 1981-08-19
-- 6. 교수(PROFESSOR_T) 테이블에서 교수 이름과 '2020-12-31'까지 근무한 근속일을 조회하시오. 근속일은 정수로 내림 또는 절사하여 조회하시오.(5점)
--교수명 근속일
--김도훈 14221
--이재우 9395
--성연희 10090
--염일웅 8065
--권혁일 12776
--이만식 11797
--전은지 7153
--남은혁 11001
-- 7. 학생(STUDENT_T) 테이블에서 가장 키가 큰 학생과 가장 키가 작은 학생을 조회하시오.(10점)
--학번 학생명 키
--10203 하나리 160
--10106 서재진 186