BITTEAM.sql

CREATE TABLE TEAMZ(
    TEAM_ID VARCHAR2(20) PRIMARY KEY,
    TEAM_NAME VARCHAR2(20)
);

CREATE TABLE MEMBERS(
    MEMBER_ID VARCHAR2(20) PRIMARY KEY,
    TEAM_ID VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20),
    MEMBER_AGE DECIMAL,
    ROLL VARCHAR2(20) -- 팀장, 팀원
    --회원담당, 아이템담당, 게시판담당, 관리자담당
);

ALTER TABLE MEMBERS ADD CONSTRAINT MEMBERS_FK_TEAM_ID FOREIGN KEY (TEAM_ID)
REFERENCES TEAMZ (TEAM_ID);

ALTER TABLE MEMBERS ADD (ROLL VARCHAR2(20));

INSERT INTO TEAMZ VALUES('AT','저스티스');
INSERT INTO TEAMZ VALUES('HT','엘카로');
INSERT INTO TEAMZ VALUES('CT','가오갤');
INSERT INTO TEAMZ VALUES('ST','어벤저스');

INSERT INTO MEMBERS VALUES('01','AT','형준',34);
INSERT INTO MEMBERS VALUES('02','AT','세인',35);
INSERT INTO MEMBERS VALUES('03','AT','희태',21);
INSERT INTO MEMBERS VALUES('04','AT','상훈',29);
INSERT INTO MEMBERS VALUES('05','AT','태형',25);
INSERT INTO MEMBERS VALUES('06','HT','혜리',26);
INSERT INTO MEMBERS VALUES('07','HT','지은',26);
INSERT INTO MEMBERS VALUES('08','HT','준',27);
INSERT INTO MEMBERS VALUES('09','HT','재경',30);
INSERT INTO MEMBERS VALUES('10','HT','단아',26);
INSERT INTO MEMBERS VALUES('11','CT','최정훈',32);
INSERT INTO MEMBERS VALUES('12','CT','윤호',31);
INSERT INTO MEMBERS VALUES('13','CT','가은',29);
INSERT INTO MEMBERS VALUES('14','CT','정훈',23);
INSERT INTO MEMBERS VALUES('15','CT','승태',30);
INSERT INTO MEMBERS VALUES('16','ST','승호',27);
INSERT INTO MEMBERS VALUES('17','ST','소진',26);
INSERT INTO MEMBERS VALUES('18','ST','이슬',29);
INSERT INTO MEMBERS VALUES('19','ST','진태',26);
INSERT INTO MEMBERS VALUES('20','ST','누리',30);

SELECT * FROM TAB;

--DROP TABLE TEAMZ;
--DROP TABLE MEMBERS;

--팀원수
SELECT
    TEAM_ID 팀아이디,
    COUNT(MEMBER_ID) 팀원수
FROM
    MEMBERS
GROUP BY
    TEAM_ID
;

--팀별 나이합
SELECT
    TEAM_ID 팀아이디,
    SUM(MEMBER_AGE) 나이합
FROM
    MEMBERS
GROUP BY
    TEAM_ID
;

--팀별 최대나이
SELECT
    TEAM_ID 팀아이디,
    MAX(MEMBER_AGE) 최대나이
FROM
    MEMBERS
GROUP BY
    TEAM_ID
;

--팀별 최소나이
SELECT
    TEAM_ID 팀아이디,
    MIN(MEMBER_AGE) 최소나이
FROM
    MEMBERS
GROUP BY
    TEAM_ID
;

--팀별 평균나이
SELECT
    TEAM_ID 팀아이디,
    AVG(MEMBER_AGE) 평균나이
FROM
    MEMBERS
GROUP BY
    TEAM_ID
;
--팀별 5가지 집계
SELECT
    M.TEAM_ID 팀아이디,
    (SELECT TEAM_NAME
     FROM TEAMZ
     WHERE
        TEAM_ID LIKE M.TEAM_ID
    ) 팀명,
    COUNT(MEMBER_ID) 명수,
    SUM(MEMBER_AGE) 나이합,
    MAX(MEMBER_AGE) 최대나이,
    MIN(MEMBER_AGE) 최소나이,
    AVG(MEMBER_AGE) 평균나이
FROM
    MEMBERS M
GROUP BY
    M.TEAM_ID
HAVING
    AVG(MEMBER_AGE) >= 28
ORDER BY
    M.TEAM_ID
;

--로우값수정
--UPDATE MEMBERS SET MEMBER_NAME = '태형'
--WHERE MEMBER_NAME LIKE '태영';
ALTER TABLE MEMBERS ADD (ROLL VARCHAR2(20));

UPDATE MEMBERS SET ROLL = '팀장'
WHERE MEMBER_NAME IN ('형준','최정훈','혜리','승호');

UPDATE MEMBERS SET ROLL = '팀원'
WHERE ROLL IS NULL;

UPDATE MEMBERS SET ROLL = NULL
WHERE ROLL LIKE '팀장';

UPDATE MEMBERS SET ROLL =
    CASE
        WHEN M.MEMBER_NAME IN ('형준','최정훈','혜리','승호') THEN '팀장'
        ELSE '팀원'
    END
;

SELECT MEMBER_NAME, ROLL FROM MEMBERS;

SELECT
    (SELECT TEAM_NAME
     FROM TEAMZ
     WHERE
        TEAM_ID LIKE M.TEAM_ID)팀아이디,
    M.MEMBER_NAME 이름,
    CASE
        WHEN M.MEMBER_NAME IN ('형준','최정훈','혜리','승호') THEN '팀장'
        WHEN M.MEMBER_NAME IS NOT NULL THEN'팀원'
    END 직책
FROM
    MEMBERS M
ORDER BY TEAM_ID, 직책 DESC
;

SELECT
    (SELECT TEAM_NAME
     FROM TEAMZ
     WHERE
        TEAM_ID LIKE M.TEAM_ID) 팀명,
    COUNT(M.MEMBER_ID) 인원수,
    SUM(M.MEMBER_ID) 나이합,
    MAX(M.MEMBER_AGE) 최대나이,
    MIN(M.MEMBER_AGE) 최소나이,
    AVG(M.MEMBER_AGE) 평균나이,
    (SELECT MEMBER_NAME
     FROM MEMBERS
     WHERE
        ROLL LIKE '팀장'
        AND TEAM_ID LIKE M.TEAM_ID) 팀장
FROM
    MEMBERS M
GROUP BY
    M.TEAM_ID
ORDER BY M.TEAM_ID
;

ALTER TABLE TEAMZ
RENAME TO PROJECT_TEAM;
ALTER TABLE MEMBERS
RENAME TO MEMBER;

SELECT * FROM MEMBER;
--컬럼명 수정 ALTER TABLE 테이블명 RENAME COLUMN 원래컬럼명 TO 바꿀컬럼명
ALTER TABLE MEMBER
RENAME COLUMN MEMBER_PW TO PASSWORD;
ALTER TABLE MEMBER
RENAME COLUMN MEMBER_AGE TO AGE;


------DB에서 화면까지--------
CREATE SEQUENCE EXAM_SEQ;
CREATE SEQUENCE RECORD_SEQ;
CREATE SEQUENCE SUBJECT_SEQ;

CREATE TABLE EXAM(
    EXAM_SEQ DECIMAL PRIMARY KEY,
    MEMBER_ID VARCHAR2(20),
    SUBJECT_SEQ DECIMAL,
    MONTH VARCHAR2(20),
    SCORE VARCHAR2(20),
    RECORD_SEQ DECIMAL
);

CREATE TABLE RECORD(
    RECORD_SEQ DECIMAL PRIMARY KEY,
    AVERAGE VARCHAR2(20),
    GRADE VARCHAR2(20)
);

CREATE TABLE SUBJECT(
    SUBJECT_SEQ DECIMAL PRIMARY KEY,
    SUBJECT_NAME VARCHAR2(20)
);

ALTER TABLE EXAM ADD CONSTRAINT MEMBER_FK_MEMBER_ID FOREIGN KEY (MEMBER_ID)
REFERENCES MEMBER(MEMBER_ID);
ALTER TABLE EXAM ADD CONSTRAINT SUBJECT_FK_SUBJECT_SEQ FOREIGN KEY (SUBJECT_SEQ)
REFERENCES SUBJECT(SUBJECT_SEQ);
ALTER TABLE EXAM ADD CONSTRAINT RECORD_FK_RECORD_SEQ FOREIGN KEY (RECORD_SEQ)
REFERENCES RECORD(RECORD_SEQ);

INSERT INTO SUBJECT VALUES (SUBJECT_SEQ.NEXTVAL, 'Java');
INSERT INTO SUBJECT VALUES (SUBJECT_SEQ.NEXTVAL, 'SQL');
INSERT INTO SUBJECT VALUES (SUBJECT_SEQ.NEXTVAL, 'HTML5');
INSERT INTO SUBJECT VALUES (SUBJECT_SEQ.NEXTVAL, 'R');
INSERT INTO SUBJECT VALUES (SUBJECT_SEQ.NEXTVAL, 'Python');

SELECT * FROM MEMBER;

UPDATE MEMBER SET MEMBER_PW = MEMBER_ID;

SELECT ROWNUM, A.*
FROM( SELECT * FROM MEMBER) A;


ALTER TABLE MEMBER ADD (MEMBER_PW VARCHAR2(20));
INSERT INTO MEMBER(MEMBER_PW) VALUES ('123');

commit;

SELECT
    MEMBER_ID USERID,
    TEAM_ID TEAMID,
    NAME,
    SSN,
    ROLL,
    MEMBER_PW PW
FROM
    MEMBER
WHERE
    MEMBER_ID LIKE 'SJ25'
    AND MEMBER_PW LIKE 'SJ25'
;

ALTER TABLE MEMBER ADD (SUBJECT VARCHAR2(20));
ALTER TABLE MEMBER RENAME COLUMN TEMP TO SSN;
ALTER TABLE MEMBER MODIFY (SSN VARCHAR2(20));
ALTER TABLE MEMBER DROP COLUMN SSN;

UPDATE MEMBER SET SSN = SSN || '1'
WHERE NAME NOT IN('혜리', '지은', '가은', '누리', '단아', '소진', '이슬');

UPDATE MEMBER SET AGE = 2018-(1900+SUBSTR(SSN,1,2))+1
WHERE AGE IS NULL;

UPDATE MEMBER SET GENDER = 
    CASE
        WHEN SUBSTR(SSN,8,1) LIKE '1' THEN '남'
        ELSE '여'
    END;

SELECT
    SUBSTR(SSN,3,2) 년도
FROM MEMBER;

DELETE FROM MEMBER WHERE MEMBER_ID LIKE 'TEST%';

SELECT
    MEMBER_ID USERID
FROM
    MEMBER
WHERE
   TEAM_ID IS NULL
;

UPDATE MEMBER SET TEAM_ID = ''
WHERE ROLL IS NULL;
SELECT * FROM MEMBER;
SELECT * FROM PROJECT_TEAM;
SELECT * FROM TAB;

UPDATE MEMBER SET TEAM_ID = 
    CASE
        WHEN MEMBER_ID LIKE '%N%' THEN 'NOLJA'
        WHEN MEMBER_ID LIKE '%J%' THEN 'JIEUN_HOUSE'
        WHEN MEMBER_ID LIKE '%K%' THEN 'TURTLE_KING'
        WHEN MEMBER_ID LIKE '%D%' THEN 'CODDING_ZZANG'
    END
WHERE TEAM_ID IS NULL;

--PAGE를 만들기위한 쿼리--

SELECT COUNT(*)
FROM MEMBER;

--1. PAGE 수를 구하는 쿼리

SELECT
    ROUND(((ROWNUM+2)/5),0) PAGE,
    MEMBER_ID
FROM MEMBER;

--2. ROWNUM을 구한 뒤

SELECT ROWNUM AS "NUM", A.MEMBER_ID, ROUND(((ROWNUM+2)/5),0) PAGE
FROM (SELECT ROWNUM RO, MEMBER_ID FROM MEMBER ORDER BY ROWNUM DESC)A ;

SELECT ROWNUM AS "NUM", A.MEMBER_ID
FROM (SELECT ROWNUM RO, MEMBER_ID FROM MEMBER ORDER BY ROWNUM DESC)A ;

--3. PAGE 당 회원의 ID 만 보여주는 쿼리

SELECT B.*
FROM (
        SELECT ROWNUM AS "NUM", A.*, ROUND(((ROWNUM+2)/5),0) PAGE
        FROM (SELECT ROWNUM RO, M.* FROM MEMBER M ORDER BY ROWNUM DESC)A
     )B
WHERE B.PAGE LIKE '1';

SELECT B.*
FROM (
        SELECT ROWNUM AS "NUM", A.*
        FROM (SELECT * FROM MEMBER M
        WHERE TEAM_ID LIKE '%A%'
        ORDER BY ROWNUM DESC)A
     )B
WHERE B.NUM BETWEEN '1' AND '5'
;

INSERT INTO MEMBER (member_Id , team_Id , name , ssn , roll , password , age , gender , subject)
  VALUES  ( 'TEST2', 'JIEUN_HOUSE', '테스트2', '890430-1', 'back', 'TEST2', '30', '남', 'java' ) ;