내용

글번호 918
작성자 허진경
작성일 2018-10-11 18:05:58
제목 차례대로 입력해보기
내용 --카테고리 테이블에 새 카테고리를 추가하세요. --아이디: 10, 분류1 : 한국소프트웨어산업협회, 분류2: 과제게시판 --카테고리이름 : 2018 빅데이터 --설명 : 2018 빅데이터
INSERT INTO BOARD_CATEGORY
(category_id, category_class1, category_class2, category_name, category_description)
VALUES (10, '한국소프트웨어산업협회', '과제게시판', '2018 빅데이터', '2018 빅데이터');
SELECT * FROM board_category;
--게시글을 입력하세요. --게시글 아이디 : 1, 카테고리 아이디 : 10, 작성자 : 홍길동 --이메일 : hong@hong.com, 제목 : 반갑습니다. 내용 : 내용 없음, 작성일 : 2018-10-11 --비밀번호 : 1234, 메인글아이디 : 1, 댓글순서 : 0, 댓글 단계 : 0, 조회수 : 0 -- 2, 1, 이순신, lee@lee.com, 나도, 내용없음, 1234, 2018-10-11, 2, 0, 0 -- 3, 1, 홍길동, hong@hong.com, 오랜만이야, 그렇지, 1234, 28-10-12, 2, 4, 1 -- 4, 1, 무명씨, noname@name.com, 할루, 재미없음, 1234, 2018-10-11, 4, 0, 0 -- 5, 1, 홍길서, seo@hong.com, 나도, 나도나도, 1234, 2018-10-11, 2, 1, 1 -- 6, 1, 조심씨, josin@josim.com, 조심해, 안전하게, 1234, 2018-10-11, 2, 2, 2 -- 7, 1, 안전씨, an@anjeon.com, 자나깨나, 불조심, 1234, 2018-10-11, 4, 1, 1 -- 8, 1, 소심씨, so@somcom, 잘삐져, 조심씨, 1234, 2018-10-11, 2, 3, 3 --1번글 : 메인글
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
(1, 10, '홍길동', 'hong@hong.com', '반갑습니다', '내용 없음', to_date('2018-10-11', 'YYYY-MM-DD'),
'1234', 1, 0, 0, 0);
--1 10 홍길동 hong@hong.com 반갑습니다 내용 없음 18/10/11 00:00:00.000000000 1234 1 0 0 0 --2번글 : 메인글
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
(2, 10, '이순신', 'lee@lee.com', '나도', '내용없음', to_date('2018-10-11', 'YYYY-MM-DD'),
'1234', 2, 0, 0, 0);
--1 10 홍길동 hong@hong.com 반갑습니다 내용 없음 18/10/11 00:00:00.000000000 1234 1 0 0 0 --2 10 이순신 lee@lee.com 나도 내용없음 18/10/11 00:00:00.000000000 1234 2 0 0 0 --3번글 : 2번글의 댓글
UPDATE board
SET    reply_number = reply_number + 1
WHERE  master_id = (SELECT master_id FROM board WHERE board_id=2)
  AND  reply_number > (SELECT reply_number FROM board WHERE board_id=2)
;
--0개 행 이(가) 업데이트되었습니다.
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
( (SELECT MAX(board_id)+1 FROM board),
   10, '홍길동', 'hong@hong.com', '오랜만이야', '그렇지', to_date('2018-10-12', 'YYYY-MM-DD'),
   '1234',
  (SELECT master_id FROM board WHERE board_id=2),
  (SELECT reply_number+1 FROM board WHERE board_id=2),
  (SELECT reply_step+1 FROM board WHERE board_id=2),
  0
);
--1 10 홍길동 hong@hong.com 반갑습니다 내용 없음 18/10/11 00:00:00.000000000 1234 1 0 0 0 --2 10 이순신 lee@lee.com 나도 내용없음 18/10/11 00:00:00.000000000 1234 2 0 0 0 --3 10 홍길동 hong@hong.com 오랜만이야 그렇지 18/10/12 00:00:00.000000000 1234 2 1 1 0 --4번글 메인글
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
(4, 10, '무명씨', 'noname@name.com',  '할루',     	'재미없음', to_date('2018-10-13', 'YYYY-MM-DD'), '1234', 4, 0, 0, 0);
--5번글 2번글의 두 번째 댓글
UPDATE board
SET    reply_number = reply_number + 1
WHERE  master_id = (SELECT master_id FROM board WHERE board_id=2)
  AND  reply_number > (SELECT reply_number FROM board WHERE board_id=2)
;
--1 행 이(가) 업데이트되었습니다.
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
( (SELECT MAX(board_id)+1 FROM board),
   10, '홍길서', 'seo@hong.com', '나도', '나도나도', to_date('2018-10-13', 'YYYY-MM-DD'),
   '1234',
  (SELECT master_id FROM board WHERE board_id=2),
  (SELECT reply_number+1 FROM board WHERE board_id=2),
  (SELECT reply_step+1 FROM board WHERE board_id=2),
  0
);
--1 10 홍길동 hong@hong.com 반갑습니다 내용 없음 18/10/11 00:00:00.000000000 1234 1 0 0 0 --2 10 이순신 lee@lee.com 나도 내용없음 18/10/11 00:00:00.000000000 1234 2 0 0 0 --3 10 홍길동 hong@hong.com 오랜만이야 그렇지 18/10/12 00:00:00.000000000 1234 2 2 1 0 --4 10 무명씨 noname@name.com 할루 재미없음 18/10/13 00:00:00.000000000 1234 4 0 0 0 --5 10 홍길서 seo@hong.com 나도 나도나도 18/10/13 00:00:00.000000000 1234 2 1 1 0 --6번글 : 5번글(2번글의 두 번째 댓글)의 댓글
UPDATE board
SET    reply_number = reply_number + 1
WHERE  master_id = (SELECT master_id FROM board WHERE board_id=5)
  AND  reply_number > (SELECT reply_number FROM board WHERE board_id=5)
;
--1 행 이(가) 업데이트되었습니다.
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
( (SELECT MAX(board_id)+1 FROM board),
   10, '조심씨', 'josin@josim.com', '조심해', '안전하게', to_date('2018-10-15', 'YYYY-MM-DD'),
   '1234',
  (SELECT master_id FROM board WHERE board_id=5),
  (SELECT reply_number+1 FROM board WHERE board_id=5),
  (SELECT reply_step+1 FROM board WHERE board_id=5),
  0
);
--1 10 홍길동 hong@hong.com 반갑습니다 내용 없음 18/10/11 00:00:00.000000000 1234 1 0 0 0 --2 10 이순신 lee@lee.com 나도 내용없음 18/10/11 00:00:00.000000000 1234 2 0 0 0 --3 10 홍길동 hong@hong.com 오랜만이야 그렇지 18/10/12 00:00:00.000000000 1234 2 3 1 0 --4 10 무명씨 noname@name.com 할루 재미없음 18/10/13 00:00:00.000000000 1234 4 0 0 0 --5 10 홍길서 seo@hong.com 나도 나도나도 18/10/13 00:00:00.000000000 1234 2 1 1 0 --6 10 조심씨 josin@josim.com 조심해 안전하게 18/10/15 00:00:00.000000000 1234 2 2 2 0 --7번글 : 4번글의 댓글
UPDATE board
SET    reply_number = reply_number + 1
WHERE  master_id = (SELECT master_id FROM board WHERE board_id=4)
  AND  reply_number > (SELECT reply_number FROM board WHERE board_id=4)
;
--0개 행 이(가) 업데이트되었습니다.
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
( (SELECT MAX(board_id)+1 FROM board),
   10, '안전씨', 'an@josim.com', '자나깨나', '불조심', to_date('2018-10-16', 'YYYY-MM-DD'),
   '1234',
  (SELECT master_id FROM board WHERE board_id=4),
  (SELECT reply_number+1 FROM board WHERE board_id=4),
  (SELECT reply_step+1 FROM board WHERE board_id=4),
  0
);
--1 10 홍길동 hong@hong.com 반갑습니다 내용 없음 18/10/11 00:00:00.000000000 1234 1 0 0 0 --2 10 이순신 lee@lee.com 나도 내용없음 18/10/11 00:00:00.000000000 1234 2 0 0 0 --3 10 홍길동 hong@hong.com 오랜만이야 그렇지 18/10/12 00:00:00.000000000 1234 2 3 1 0 --4 10 무명씨 noname@name.com 할루 재미없음 18/10/13 00:00:00.000000000 1234 4 0 0 0 --5 10 홍길서 seo@hong.com 나도 나도나도 18/10/13 00:00:00.000000000 1234 2 1 1 0 --6 10 조심씨 josin@josim.com 조심해 안전하게 18/10/15 00:00:00.000000000 1234 2 2 2 0 --7 10 안전씨 an@josim.com 자나깨나 불조심 18/10/16 00:00:00.000000000 1234 4 1 1 0 --8번글 : 6번글의 댓글(6번글은 5번글의 댓글, 5번글은 2번글의 댓글)
UPDATE board
SET    reply_number = reply_number + 1
WHERE  master_id = (SELECT master_id FROM board WHERE board_id=6)
  AND  reply_number > (SELECT reply_number FROM board WHERE board_id=6)
;
--1 행 이(가) 업데이트되었습니다.
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
( (SELECT MAX(board_id)+1 FROM board),
   10, '소심씨', 'so@sosim.com', '잘삐져', '조심씨', to_date('2018-10-17', 'YYYY-MM-DD'),
   '1234',
  (SELECT master_id FROM board WHERE board_id=6),
  (SELECT reply_number+1 FROM board WHERE board_id=6),
  (SELECT reply_step+1 FROM board WHERE board_id=6),
  0
);
--1 10 홍길동 hong@hong.com 반갑습니다 내용 없음 18/10/11 00:00:00.000000000 1234 1 0 0 0 --2 10 이순신 lee@lee.com 나도 내용없음 18/10/11 00:00:00.000000000 1234 2 0 0 0 --3 10 홍길동 hong@hong.com 오랜만이야 그렇지 18/10/12 00:00:00.000000000 1234 2 4 1 0 --4 10 무명씨 noname@name.com 할루 재미없음 18/10/13 00:00:00.000000000 1234 4 0 0 0 --5 10 홍길서 seo@hong.com 나도 나도나도 18/10/13 00:00:00.000000000 1234 2 1 1 0 --6 10 조심씨 josin@josim.com 조심해 안전하게 18/10/15 00:00:00.000000000 1234 2 2 2 0 --7 10 안전씨 an@josim.com 자나깨나 불조심 18/10/16 00:00:00.000000000 1234 4 1 1 0 --8 10 소심씨 so@sosim.com 잘삐져 조심씨 18/10/17 00:00:00.000000000 1234 2 3 3 0
select * from board;
-- 게시판 전제 목록을 조회하세요.
SELECT board_id, category_id, writer, title, master_id, reply_number, reply_step
FROM board
ORDER BY master_id DESC, reply_number, reply_step ASC;
-- 게시판 목록을 출력하는 페이지는 한 페이지에 4개의 목록을 출력합니다. 두 번째 페이지를 출력하세요.
SELECT *
FROM (
    SELECT rownum AS rnum, board_id, category_id, writer, title, master_id, reply_number, reply_step 
    FROM (
        SELECT board_id, category_id, writer, title, master_id, reply_number, reply_step
        FROM board
        ORDER BY master_id DESC, reply_number, reply_step ASC
    )
)
--WHERE rnum between 5 and 8
;
SELECT * FROM board;
--변경 사항을 저장하세요.
COMMIT;
-- 2번 게시글의 정보 --(board_id, category_id, writer, email, password, titie, write_date, -- master_id, reply_number, reply_step)를 조회하세요. -- 2번 글에 답글을 다는 쿼리문을 작성하세요. --주어진 값은 category_id(10), writer(허진경), email(hjk790), title(제목), 2018-10-20 입니다. --board_id, master_id, reply_number, reply_step은 조회후 값을 이용해야 합니다.
SELECT board_id, category_id, writer, email, password, title, write_date, 
       master_id, reply_number, reply_step
FROM board
WHERE board_id=2;
--2 10 이순신 lee@lee.com 1234 나도 18/10/11 00:00:00.000000000 2 0 0
UPDATE board
SET    reply_number = reply_number + 1
WHERE  master_id = (SELECT master_id FROM board WHERE board_id=2)
  AND  reply_number > (SELECT reply_number FROM board WHERE board_id=2)
;
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
( (SELECT MAX(board_id)+1 FROM board),
   10, '허진경', 'hjk7902', '제목', null, to_date('2018-10-20', 'YYYY-MM-DD'),
   '1234',
  (SELECT master_id FROM board WHERE board_id=2),
  (SELECT reply_number+1 FROM board WHERE board_id=2),
  (SELECT reply_step+1 FROM board WHERE board_id=2),
  0
);
select board_id, writer, title, write_date, master_id, reply_number, reply_step from board;
--1 홍길동 반갑습니다 1 0 0 --2 이순신 나도 2 0 0 --3 홍길동 오랜만이야 2 4 1 --4 무명씨 할루 4 0 0 --5 홍길서 나도 2 1 1 --6 조심씨 조심해 2 2 2 --7 안전씨 자나깨나 4 1 1 --8 소심씨 잘삐져 2 3 3
SELECT *
FROM (
    SELECT rownum AS rnum, board_id, category_id, writer, title, master_id, reply_number, reply_step 
    FROM (
        SELECT board_id, category_id, writer, title, master_id, reply_number, reply_step
        FROM board
        ORDER BY master_id DESC, reply_number, reply_step ASC
    )
)
;
--1 4 10 무명씨 할루 4 0 0 --2 7 10 안전씨 자나깨나 4 1 1 --3 2 10 이순신 나도 2 0 0 --4 5 10 홍길서 나도 2 1 1 --5 6 10 조심씨 조심해 2 2 2 --6 8 10 소심씨 잘삐져 2 3 3 --7 3 10 홍길동 오랜만이야 2 4 1 --8 1 10 홍길동 반갑습니다 1 0 0