[Oracle] days 15- 설문조사 쿼리 데이터 넣어보기

-- SCOTT 계정 접속 --  
-- 10:05  수업 시작~~
SELECT *
FROM tabs
WHERE table_name LIKE 'T\_%' ESCAPE '\';
--
DESC T_SAMPLE;
--
SELECT * FROM t_sample;
------------------------------------------------------------------------------------
T_MEMBER 회원
T_POLL   설문
T_POLLSUB 설문항목
T_VOTER 투표
------------------------------------------------------------------------------------
회원
1) 회원 가입/탈퇴/수정
CREATE SEQUENCE seq_member
INCREMENT BY 1
START WITH 1
MAXVALUE   9999
NOCACHE;

INSERT INTO T_MEMBER ( MemberSeq, MemberID, MemberPasswd, MemberName,MemberPhone, MemberAddress  )
VALUES ( seq_member.nextval,'admin', '1234', '관리자','010-1111-1111', '서울 강남구'  );
INSERT INTO T_MEMBER ( MemberSeq, MemberID, MemberPasswd, MemberName,MemberPhone, MemberAddress  )
VALUES ( seq_member.nextval,'hong', '1234', '홍길동','010-2222-2222', '경기도 남양주'  );
INSERT INTO T_MEMBER ( MemberSeq, MemberID, MemberPasswd, MemberName,MemberPhone, MemberAddress  )
VALUES ( seq_member.nextval,'kim', '1234', '김기수','010-3333-3333', '서울 양천구'  );
COMMIT;

SELECT *
FROM t_member;


-- 11:04 수업 시작
2) 설문 등록(작성) / 수정 / 탈퇴

CREATE SEQUENCE seq_poll;
-- Sequence SEQ_POLL이(가) 생성되었습니다.
INSERT INTO T_POLL (PollSeq,Question,SDate, EDAte , ItemCount,PollTotal, RegDate, MemberSEQ )
VALUES             ( seq_poll.nextval,'좋아하는 여배우?'
                          , TO_DATE( '2022-03-01 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , TO_DATE( '2022-03-15 18:00:00'   ,'YYYY-MM-DD HH24:MI:SS') 
                          , 5
                          , 0
                          , TO_DATE( '2022-02-01 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , 1
                    );
--
INSERT INTO T_POLL (PollSeq,Question,SDate, EDAte , ItemCount,PollTotal, RegDate, MemberSEQ )
VALUES             ( seq_poll.nextval,'좋아하는 과목?'
                          , TO_DATE( '2022-04-20 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , TO_DATE( '2022-05-01 18:00:00'   ,'YYYY-MM-DD HH24:MI:SS') 
                          , 4
                          , 0
                          , TO_DATE( '2022-04-15 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , 1
                    );
--
INSERT INTO T_POLL (PollSeq,Question,SDate, EDAte , ItemCount,PollTotal, RegDate, MemberSEQ )
VALUES             ( seq_poll.nextval,'5월 5일 휴강 찬반?'
                          , TO_DATE( '2022-05-01 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , TO_DATE( '2022-05-04 18:00:00'   ,'YYYY-MM-DD HH24:MI:SS') 
                          , 2
                          , 0
                          , SYSDATE
                          , 1
                    );
COMMIT;

SELECT * FROM t_poll;

-- 세부 항목 추가 쿼리 작성  
CREATE SEQUENCE seq_pollsub;
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'배슬기', 0, 1 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'김옥빈', 0, 1 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'아이유', 0, 1 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'김선아', 0, 1 );

INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'수학', 0, 2 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'국어', 0, 2 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'영어', 0, 2 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'사회', 0, 2 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'과학', 0, 2 );


INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'예', 0, 3 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'아니요', 0, 3 );

COMMIT;


SELECT * FROM t_pollsub;
-- 설문 수정/삭제 

3) 설문 목록 페이지 = 쿼리
-- 번호/질문/작성자/시작일/종료일/항목수/참여수/상태
SELECT pollseq, question, membername , sDate, edate
       , itemcount, polltotal
       , CASE 
            WHEN SYSDATE > edate THEN  '종료'
            WHEN SYSDATE BETWEEN sdate AND edate THEN  '진행중'
            ELSE   '시작전'
         END  state
FROM t_poll  p JOIN t_member m ON p.memberseq = m.memberseq ; 

4) [설문 투표] / 수정 / 삭제
CREATE SEQUENCE seq_vecter;

-- [설문 투표]   1) + 2) + 3)    PL/SQL 
-- 1)
INSERT INTO t_voter (VectorSeq         , UserName,RegDate, PollSeq,PollSubSeq, MemberSeq) 
VALUES             ( seq_vecter.nextval, '홍길동', SYSDATE,  1,     3             , 2 );
-- 2)
UPDATE t_poll
SET polltotal = polltotal +1
WHERE pollseq = 1;
--3)
UPDATE t_pollsub
SET acount = acount + 1
WHERE pollsubseq = 3;


-- [설문 투표]   1) + 2) + 3)    PL/SQL 
-- 1)
INSERT INTO t_voter (VectorSeq         , UserName,RegDate, PollSeq,PollSubSeq, MemberSeq) 
VALUES             ( seq_vecter.nextval, '김기수', SYSDATE,  1,     2             , 3 );
-- 2)
UPDATE t_poll
SET polltotal = polltotal +1
WHERE pollseq = 1;
--3)
UPDATE t_pollsub
SET acount = acount + 1
WHERE pollsubseq = 3;

-- [설문 투표]   1) + 2) + 3)    PL/SQL 
-- 1)
INSERT INTO t_voter (VectorSeq         , UserName,RegDate, PollSeq,PollSubSeq, MemberSeq) 
VALUES             ( seq_vecter.nextval, '관리자', SYSDATE,  1,     2             , 1 );
-- 2)
UPDATE t_poll
SET polltotal = polltotal +1
WHERE pollseq = 1;
--3)
UPDATE t_pollsub
SET acount = acount + 1
WHERE pollsubseq = 2;

COMMIT;
ROLLBACK;
SELECT * FROM t_voter;
SELECT * FROM t_poll;
SELECT * FROM t_pollsub; 

5) 1번 설문에 대한  투표 결과 보기
-- 1) 
SELECT COUNT(*)
FROM t_voter
WHERE pollseq = 1;
-- 1-2)  polltotal 참여자수
SELECT polltotal
FROM t_poll
WHERE pollseq = 1;

-- 
SELECT question , answer
    , RPAD(' ', v+1 ,'#'  ) || acount || '(' ||  v || '%)'
FROM ( 
    SELECT question , answer,  acount , polltotal
          ,  ROUND( acount/ polltotal * 100 ) v
    FROM t_pollsub s  JOIN t_poll p ON s.pollseq = p.pollseq
    WHERE p.pollseq = 1
) t;

좋아하는 여배우?   배슬기    0(0%)
좋아하는 여배우?   김옥빈    #################################1(33%)
좋아하는 여배우?   아이유    ###################################################################2(67%)
좋아하는 여배우?   김선아    0(0%)