내용

글번호 917
작성자 허진경
작성일 2018-10-11 16:50:08
제목 게시판 글 입력/목록조회/댓글달기
내용 --카테고리 테이블에 새 카테고리를 추가하세요. --아이디: 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, 1, 1 -- 4, 1, 무명씨, noname@name.com, 할루, 재미없음, 1234, 2018-10-11, 4, 0, 0 -- 5, 1, 홍길서, seo@hong.com, 나도, 나도나도, 1234, 2018-10-11, 2, 2, 1 -- 6, 1, 조심씨, josin@josim.com, 조심해, 안전하게, 1234, 2018-10-11, 2, 1, 2 -- 7, 1, 안전씨, an@anjeon.com, 자나깨나, 불조심, 1234, 2018-10-11, 4, 1, 1 -- 8, 1, 소심씨, so@somcom, 잘삐져, 조심씨, 1234, 2018-10-11, 2, 1, 3
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);
SELECT * FROM BOARD;
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);
SELECT * FROM BOARD;
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
(3, 10, '홍길동', 'hong@hong.com',    '오랜만이야', 	'그렇지', to_date('2018-10-12', 'YYYY-MM-DD'), '1234', 2, 1, 1, 0);
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);
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
(5, 10, '홍길서', 'seo@hong.com',     '나도',     	'나도나도', to_date('2018-10-14', 'YYYY-MM-DD'), '1234', 2, 2, 1, 0);
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
(6, 10, '조심씨', 'josin@josim.com',  '조심해',    	'안전하게', to_date('2018-10-15', 'YYYY-MM-DD'), '1234', 2, 1, 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
(7, 10, '안전씨', 'an@anjeon.com',    '자나깨나',  	'불조심', to_date('2018-10-16', 'YYYY-MM-DD'), '1234', 4, 1, 1, 0);
INSERT INTO board
(board_id, category_id, writer, email, title, content, write_date, password,
master_id, reply_number, reply_step, read_count)
VALUES
(8, 10, '소심씨', 'so@somcom',        '잘삐져',    	'조심씨', to_date('2018-10-17', 'YYYY-MM-DD'), '1234', 2, 1, 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
);