localhost_STUDENT_220308.sql

INSERT INTO DEPARTMENT(DEPTNO, DNAME)
VALUES(900, '생명공학부');

INSERT INTO DEPARTMENT
VALUES (301, '환경보건학부', '', NULL);

SELECT * FROM DEPARTMENT
WHERE DEPTNO = 900;

SELECT * FROM DEPARTMENT
WHERE DEPTNO = 301;

INSERT INTO PROFESSOR (PROFNO, NAME, POSITION, HIREDATE, DEPTNO)
VALUES(9920, '최윤식', '조교수', TO_DATE('2006/01/01', 'YYYY/MM/DD'), 102);

SELECT * FROM PROFESSOR;

INSERT INTO PROFESSOR
VALUES (9910, '백미선', 'white', '전임강사', 200, SYSDATE, 10, 101);

DROP TABLE TBL_BOARD;

CREATE TABLE TBL_BOARD (
    BNO NUMBER,
    TITLE VARCHAR2(1000),
    CONTENT CLOB,
    HITCOUNT NUMBER DEFAULT 0,
    REGDATE DATE DEFAULT SYSDATE,
    WRITER VARCHAR2(200)
);

CREATE SEQUENCE SEQ_BOARD;
DROP SEQUENCE SEQ_BOARD;
TRUNCATE TABLE TBL_BOARD;

SELECT * FROM TBL_BOARD;

INSERT INTO TBL_BOARD (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목1', '내용1', 'javamam');
INSERT INTO TBL_BOARD (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목2', '내용2', 'babamba');
INSERT INTO TBL_BOARD (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목3', '내용3', 'amanna');
INSERT INTO TBL_BOARD (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목4', '내용4', 'odoung');
INSERT INTO TBL_BOARD (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목5', '내용5', 'kyoungbow');


INSERT INTO TBL_BOARD
SELECT * FROM TBL_BOARD;

INSERT INTO TBL_BOARD(BNO, TITLE, CONTENT, WRITER)
SELECT SEQ_BOARD.NEXTVAL, TITLE, CONTENT, WRITER FROM TBL_BOARD;

SELECT * FROM TBL_BOARD ORDER BY 1 DESC;

-- 한 페이지에 10개씩

SELECT * 
FROM (
    SELECT A.*, ROWNUM RN 
    FROM (
        SELECT * FROM TBL_BOARD ORDER BY 1 DESC
    ) A
WHERE ROWNUM <= 20
)
WHERE RN > 10;


-- 다중행입력을위™height_info, weight_info 예제테이블생성
CREATE TABLE HEIGHT_INFO (
    STUDNO NUMBER,
    NAME VARCHAR(10),
    HEIGHT NUMBER
);

CREATE TABLE WEIGHT_INFO (
    STUDNO NUMBER,
    NAME VARCHAR(10),
    WEIGHT NUMBER
);

-- 학생테이블에서2학년이상의학생을검색하여height_info 테이블에는
-- 학번, 이름, 키, weight_info 테이블에는학번, 이름, 몸무게를각각입력하여라.

INSERT ALL 
INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
SELECT * FROM STUDENT WHERE GRADE = '2';

TRUNCATE TABLE WEIGHT_INFO;
TRUNCATE TABLE HEIGHT_INFO;

SELECT * FROM HEIGHT_INFO;
SELECT * FROM WEIGHT_INFO;


--학생테이블에서 2학년 이상의 학생을 검색하여height_info 테이블에는키가170보다큰학생의
--학번, 이름, 키를입력하고weight_info 테이블에는몸무게가70보다큰학생의학번, 이름, 몸무게를각각입력하여라.

INSERT ALL -- 동일한 사람이 HEIGHT, WEIGHT 둘다 들어갈 수 있음
WHEN HEIGHT > 170 THEN
INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
WHEN WEIGHT > 70 THEN
INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
SELECT * FROM STUDENT WHERE GRADE >= '2';

INSERT FIRST -- 중복 X, HEIGHT에 들어가면 WEIGHT까지 물어보지를 않음
WHEN HEIGHT > 170 THEN
INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
WHEN WEIGHT > 70 THEN
INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
SELECT * FROM STUDENT WHERE GRADE >= '2';

SELECT * FROM HEIGHT_INFO;
SELECT * FROM WEIGHT_INFO;

-- PIVOTING INSERT 를실습하기위™예제테이블
DROP TABLE SALES;
CREATE TABLE SALES(
    SALES_NO NUMBER,
    WEEK_NO NUMBER,
    SALES_MON NUMBER,
    SALES_TUE NUMBER,
    SALES_WED NUMBER,
    SALES_THU NUMBER,
    SALES_FRI NUMBER
);

INSERT INTO SALES VALUES (1101, 4, 100, 150, 80, 60, 120);
INSERT INTO SALES VALUES (1102, 5, 300, 300, 230, 120, 150);

CREATE TABLE SALES_DATA (
    SALES_NO NUMBER,
    WEEK_NO NUMBER,
    DAY_NO NUMBER,
    SALES NUMBER
);

SELECT * FROM SALES ORDER BY 1;

-- 1101 4 월 : 1, SALES : 100
-- 1101 4 화 : 2, SALES : 150
ROLLBACK;
INSERT ALL
INTO SALES_DATA VALUES (SALES_NO, WEEK_NO, 1, SALES_MON)
INTO SALES_DATA VALUES (SALES_NO, WEEK_NO, 2, SALES_TUE)
INTO SALES_DATA VALUES (SALES_NO, WEEK_NO, 3, SALES_WED)
INTO SALES_DATA VALUES (SALES_NO, WEEK_NO, 4, SALES_THU)
INTO SALES_DATA VALUES (SALES_NO, WEEK_NO, 5, SALES_FRI)
SELECT * FROM SALES ORDER BY 1;

-- SALES_DATE를 기반으로 주차별 평균 매출, 매출합을 구하시오.
SELECT WEEK_NO, AVG(SALES), SUM(SALES)
FROM SALES_DATA
GROUP BY WEEK_NO;



SELECT SALES_NO , WEEK_NO, DAY_NO, SALES FROM SALES_DATA ORDER BY 2, 3; -- >> 10행 컬럼 늘리기
SELECT 
    SALES_NO , WEEK_NO,
    MAX(CASE WHEN DAY_NO = 1 THEN SALES END) SALES_MON,
    MAX(CASE WHEN DAY_NO = 2 THEN SALES END) SALES_THE,
    MAX(CASE WHEN DAY_NO = 3 THEN SALES END) SALES_WED,
    MAX(CASE WHEN DAY_NO = 4 THEN SALES END) SALES_THU,
    MAX(CASE WHEN DAY_NO = 5 THEN SALES END) SALES_FRI
FROM SALES_DATA
GROUP BY SALES_NO, WEEK_NO;


SELECT SALES_NO, WEEK_NO, 1 DAY_NO, SALES_MON SALES FROM SALES UNION
SELECT SALES_NO, WEEK_NO, 2 DAY_NO, SALES_TUE SALES FROM SALES UNION
SELECT SALES_NO, WEEK_NO, 3 DAY_NO, SALES_WED SALES FROM SALES UNION
SELECT SALES_NO, WEEK_NO, 4 DAY_NO, SALES_THU SALES FROM SALES UNION
SELECT SALES_NO, WEEK_NO, 5 DAY_NO, SALES_FRI SALES FROM SALES;

SELECT 
--    TRUNC((RN - 1) / 7),
    MAX(CASE WHEN MOD(RN ,7) = 1 THEN RN END) 일,
    MAX(CASE WHEN MOD(RN ,7) = 2 THEN RN END) 월,
    MAX(CASE WHEN MOD(RN ,7) = 3 THEN RN END) 화,
    MAX(CASE WHEN MOD(RN ,7) = 4 THEN RN END) 수,
    MAX(CASE WHEN MOD(RN ,7) = 5 THEN RN END) 목,
    MAX(CASE WHEN MOD(RN ,7) = 6 THEN RN END) 금,
    MAX(CASE WHEN MOD(RN ,7) = 0 THEN RN END) 토
FROM (
    SELECT ROWNUM RN FROM DICT WHERE ROWNUM <= 31
)
GROUP BY TRUNC((RN - 1) / 7)
ORDER BY TRUNC((RN - 1) / 7);

-- 교수번호가9903인교수의n재직급을?부교수?로수정하여라
UPDATE PROFESSOR SET
POSITION = '부교수'
WHERE PROFNO = 9903;

SELECT * FROM PROFESSOR;

-- 서브쿼리를 이용하여 학번이 10201인 학생의 학년과 학과번호를 
-- 10103학번 학생의 학년과 학과번호와 동일하게 수정하여라.

SELECT STUDNO, GRADE, DEPTNO FROM STUDENT
WHERE STUDNO IN(10201, 10103);

UPDATE STUDENT SET
(GRADE, DEPTNO) = (SELECT GRADE, DEPTNO FROM STUDENT WHERE STUDNO = 10103)
WHERE STUDNO = 10201;

SELECT * FROM STUDENT;

-- 학생테이블에서학번이20103인학생의데이터를삭제하여라.
DELETE FROM STUDENT
WHERE STUDNO = 20103;

-- 학생테이블에서컴퓨터공학과에소속된학생을모두삭제하여라
DELETE STUDENT WHERE DEPTNO = (
SELECT DEPTNO FROM DEPARTMENT WHERE DNAME = '컴퓨터공학과');

SELECT * FROM STUDENT;

-- professor 테이블과professor_temp 테이블을비교하여professor 테이블에있는
-- 기昞�謙姑professor_temp 테이블의데이터에의해수정하고, professor 테이블에없는데이터는š薩韜括韜쩜™다.
CREATE TABLE PROFESSOR_TEMP AS
SELECT * 
FROM PROFESSOR
WHERE POSITION = '교수';

SELECT * FROM PROFESSOR_TEMP;

UPDATE PROFESSOR_TEMP SET
POSITION = '명예교수';

INSERT INTO PROFESSOR_TEMP
VALUES (9999, '김도경', 'ARON21', '전임강사', 200, SYSDATE, 10, 101);

MERGE INTO PROFESSOR P
USING PROFESSOR_TEMP P2
ON (P.PROFNO = P2.PROFNO)
WHEN MATCHED THEN
    UPDATE SET POSITION = P2.POSITION
WHEN NOT MATCHED THEN
    INSERT VALUES (P2.PROFNO, P2.NAME, P2.USERID, P2.POSITION, P2.SAL, P2.HIREDATE, P2.COMM, P2.DEPTNO);
    
SELECT * FROM PROFESSOR;

-- 시작값을 3으로 하고 2씩 증가하는 최대값 100의 SEQUENCE를 생성, 단 이름은 SEQ_TEST로 하여라
CREATE SEQUENCE SEQ_TEST
START WITH 3
INCREMENT BY 2
MAXVALUE 100;

SELECT SEQ_TEST.CURRVAL FROM DUAL;
SELECT SEQ_TEST.NEXTVAL, SEQ_TEST.NEXTVAL, SEQ_TEST.NEXTVAL, SEQ_TEST.CURRVAL FROM DUAL;

SELECT * FROM TBL_TEST;

-- 연락처정보를저장하기위™주소록(address)테이블을생성하여라.
CREATE TABLE ADDRESS (
    ID NUMBER(3),
    NAME VARCHAR(50),
    ADDR VARCHAR(100),
    PHONE VARCHAR(30),
    EMAIL VARCHAR(100)
);
DESC ADDRESS;

DROP TABLE ADDRESS;
CREATE TABLE ADDRESS (
    ID NUMBER(3) PRIMARY KEY,
    NAME VARCHAR(50),
    ADDR VARCHAR(100),
    PHONE VARCHAR(30),
    EMAIL VARCHAR(100)
);

INSERT INTO ADDRESS (ID, NAME, ADDR)
VALUES(101, '고길동', '영등포');

INSERT INTO ADDRESS (ID, NAME, ADDR)
VALUES(NULL, '고길동', '영등포');

SELECT * FROM TAB;

INSERT INTO ADDRESS
VALUES (1, 'HGONG', 'SEOUL', '123-4567', 'GDHONG@CWUNET.AC.KR');

SELECT * FROM ADDRESS;

-- 서브쿼리‡을이용하여주소록테이블의구조와데이터를복사하여addr_second 테이블을생성하여라.
CREATE TABLE ADDRESS_SECOND(ID, NAME, ADDR, PHONE, E_MAIL) AS
SELECT * FROM ADDRESS;


DROP TABLE ADDRESS_SECOND;
SELECT * FROM ADDRESS_SECOND;

DROP TABLE ADDRESS_THIRD;
CREATE TABLE ADDRESS_THIRD AS
SELECT ID COL1, NAME COL2, ADDR COL3, PHONE COL4, EMAIL COL5 FROM ADDRESS WHERE 1 = 0;
-- 조소가 가지고 오시싶으면 거짓으로 만들기 1 = 0
SELECT * FROM ADDRESS_THIRD;

SELECT * FROM PROFESSOR;

--PROFNO, NAME, POSITION, SAL, COMM, USERID, HIREDATE, DEPTNO순서가 되도록 테이블의 컬럼 순서 변경
CREATE TABLE PROF2 AS
SELECT 
    PROFNO, NAME, POSITION, SAL, COMM, USERID, HIREDATE, DEPTNO
FROM 
    PROFESSOR;
    
SELECT * FROM PROF2;

RENAME PROFESSOR TO PROF;
RENAME PROF2 TO PROFESSOR;