50, 51일차 oracle 연결, 테이블, 그룹, 페이지 네이션

50일차

오전

오전일과

오라클 설치
oracle 접속 +
사진 참고

 

멤버 테이블 만듬
성별 제약조건

기본키 아이디로 설정하기
제약조건명을 PK_MEMBER_ID PRIMARY KEY(ID)

사진참고

테이블 삭제

테이블명 변경

테이블 구조변경(컬럼명 변경) 아이디, 비밀번호 변경해봄


컬럼 추가해보기

컬럼 데이터 길이 변경해보기(컬럼 타입 수정)

컬럼 삭제 해보기


실습 게시판 테이블 생성

아이템 테이블 만들기

오더 테이블만들기(외래키 받아오기)


오라클 문제
나중에 시험
실습해봄

 

오후일과
추가
삭제
수정
조회

시퀀스
글 작성
글번호 골라서 하나 수정
조회수 증가

일괄 수정 중요

 

 

 

 

 

 

 

 

51일차

 

오전
라이브러리 추가

스프링에서 mybatis sql 연동시켜보기

APPLICATION.PROPERTIES 추가

JPA는 저장소라고 불렀었고(repository)
MYBATIS는 맵퍼라고 부름 (DTO, VO라고 함)

vo
// MEMBERTBL과 연관이 없음, 그냥 변수만 만들어 넣을 뿐임
// java에서 service controller Mapper 연동하는 전달하는 타입(전송용)

service>MyBatisMemberService.java
짜는 중

mapper> MemberMapper.java
@Mapper // <= mybatis
// @Repository <= jpa
여기가 가장 중요
value에 sql문을 적음

//주의 사항1) 마지막에 ;빼기
//주의 사항2) autocommit으로 설정되어 있음.

membercontroller에 
    @Autowired
    MyBatisMemberService mmService;

    @GetMapping(value = "/memberlist.do")
    public String memberLsitGET(Model model){
        List<MemberVO> list = mmService.selectMemberList();
        model.addAttribute("list", list);
        return "memberlist";
    }

app에
@MapperScan(basePackages = {"com.example.mapper"})
추가

지금 몽고디비에 연결된게 많아서 몽고디비 그냥 연결 시켜놓고 작업중

mybatis>member>memberlist.html 작성(테이블)



ORACLE> HOON203
중요중요중요중요중요중요중요중요중요중요

SAVEPOINT ROLLBACK 가능여부 개념 알아보기

클래스, 학생 테이블 만듬
-클래스 할때 디폴트로 날짜 넣어줌 INSERT할때 날짜 불필요
- 고정길이 CHAR를 배움 CHAR(3)은 A만 입력하면 A**으로 만들어짐
나중에 비교할때도 A**으로 비교해야함.

여러가지 문제 +
조회로 SUM, MAX, MIN, AVG

A반의 국어점수 합계, 평균 찾아봄
SELECT 
    SUM(S.STDSCOREKOR) TOT,
    AVG(S.STDSCOREKOR) AVG
FROM 
    STUDENTTBL S
WHERE
    STDCLASS='A';

날짜부분을 바꿔볼 것임
날짜가 DATE타입인데 STRING으로 바꿔서 원하는 일, 시, 분까지만 출력 써볼 것임.
TO_CHAR

오후
ROUND(숫자, 자리수)

그룹 중요중요중요중요중요중요중요중요중요중요중요중요중요
GROUP, HAVING 배움

두개합치기 못함 이거 따로 해보기

날짜별 조회수 문제 어려움 

갑자기 vscode에서 apptest중

학생 이름 vs에서 받아와서 oracle로 받아오는 것 중요중요중요중요
param 사용
@Param("text") String text

페이지 
1 -> 1~5 (page-1)*5+1, (page)*5
2 -> 6~10
3 -> 11~15


마지막에 문제내주신게 가장 중요한듯

 

전체 sql코드

-- DDL (데이터정의어)
--CRATE : 테이블 생성
--DROP : 테이블 삭제
--ALTER : 테이블 구조 변경
--RENAME : 테이블 이름 변경
--TRUNCATE : 모든 데이터 삭제

-- 회원 테이블, 아이디(문자), 암호(문자), 나이(숫자), 연락처(문자), 성별(문자), 등록일(날짜)
-- 제약조건 :성별은 MF만 가능, ID는 기본키(고유값, NOT NULL)
CREATE TABLE MEMBER(
    ID VARCHAR2(30),
    PASSWORD VARCHAR2(200),
    AGE NUMBER,
    PHONE VARCHAR2(15),
    GENDER VARCHAR2(1) CONSTRAINT MEMBER_GENDER_CK CHECK(GENDER IN('M', 'F')),
    REGDATE TIMESTAMP,
    CONSTRAINT PK_MEMBER_ID PRIMARY KEY(ID)
);

-- 테이블 삭제
DROP TABLE MEMBER CASCADE CONSTRAINTS;
DROP TABLE TEST CASCADE CONSTRAINTS;
DROP TABLE BOARD CASCADE CONSTRAINTS;

-- 테이블명 변경 MEMBER -> MEMBERTBL
RENAME MEMBER TO MEMBERTBL;

-- 테이블 구조 변경(컬럼명변경) 아이디
ALTER TABLE MEMBERTBL RENAME COLUMN ID TO USERID;

-- 테이블 구조 변경(컬럼명변경) 비밀번호
ALTER TABLE MEMBERTBL RENAME COLUMN PASSWORD TO USERPW;

-- 컴럼추가하기(ADDRESS추가)
ALTER TABLE MEMBERTBL ADD ADDRESS VARCHAR2(100);

-- 컬럼 타입 수정하기
ALTER TABLE MEMBERTBL MODIFY ADDRESS VARCHAR(150);

-- 컬럼 삭제
ALTER TABLE MEMBERTBL DROP COLUMN ADDRESS;


-- 게시판 테이블 생성 BOARD,
--NO숫자 기본기, TITLE문자, WRITER문자, HIT숫자, REGDATE날짜
CREATE TABLE BOARD(
    NO NUMBER PRIMARY KEY,
--    NO NUMBER ,
    TITLE VARCHAR2(200),
    WRITER VARCHAR2(30),
    HIT NUMBER,
    REGDATE TIMESTAMP
--    CONSTRAINT PK_BOARD_NO PRIMARY KEY(NO) 기본키
);

-- 문제2
RENAME BOARD TO BOARDTBL;

-- 문제3
ALTER TABLE BOARDTBL ADD CONTENT VARCHAR2(500);

-- 문제4
ALTER TABLE BOARDTBL MODIFY CONTENT VARCHAR(1000);


-- 물품테이블 ITEMTBL
-- 물품번호 : NO
-- 물품명 : NAME
-- 물품내용 : CONTENT CLOB
-- 가격 : NUMBER,
-- 수량 : NUMBER,
-- 등록일 : REGDATE

CREATE TABLE ITEMTBL(
    NO NUMBER,
    NAME VARCHAR2(100),
    CONTENT CLOB,
    price NUMBER,
    quantity NUMBER,
    REGATE TIMESTAMP
);

-- 기본키 제약조건
ALTER TABLE ITEMTBL ADD PRIMARY KEY(NO); --제약조건 명이 SYS_????로 생성됨
ALTER TABLE ITEMTBL ADD CONSTRAINT PK_ITEM_NO PRIMARY KEY(NO); -- 제약조건명 PK_ITEM_NO

-- 물품명 컬럼을 not null로 설정하고싶다
ALTER TABLE ITEMTBL MODIFY NAME NOT NULL;--편집 확인해보면 널이 아님이라고 뜸.




--주문테이블
-- NO 주문번호
-- CNT 주문수량
-- ITEMNO 물품번호
-- USERID 주문자
-- REGDATE 등록일
CREATE TABLE ODERTBL(
--    NO NUMBER PRIMARY KEY,
    NO NUMBER,
    CNT NUMBER,
    ITEMNO NUMBER, --ITEMTBL의 기본키
    USERID VARCHAR(30), --MEMBERTBL의 기본키
    REGATE TIMESTAMP,
    CONSTRAINT PK_ORDER_NO PRIMARY KEY(NO),
    CONSTRAINT FK_ITEM_NO FOREIGN KEY (ITEMNO) REFERENCES ITEMTBL(NO),
    CONSTRAINT FK_MEMBER_ID FOREIGN KEY (USERID) REFERENCES MEMBERTBL(USERID)
);

--문제1
-- 부서테이블(dept)
-- 부서번호 : deptno 숫자
-- 부서이름 : name 문자(7)
-- 등록일 : regdate 날짜
CREATE TABLE DEPT(
    DEPTNO NUMBER,
    NAME VARCHAR2(7),
    REGDATE TIMESTAMP
);

-- 문제2) 부서 테이블에 지역(area)을 가변기일(varchar2) 20자로 컬러추가
ALTER TABLE dept ADD area VARCHAR2(20);

-- 문제3) 지역의 서울, 부산, 대구만 가능하게 check 제약조건 설정
--GENDER VARCHAR2(1) CONSTRAINT MEMBER_GENDER_CK CHECK(GENDER IN('M', 'F')),
ALTER TABLE DEPT ADD CONSTRAINT DEPT_AREA_CK CHECK (AREA IN ('서울','부산','대구'));
-- ALTER TABLE DEPT DROP CONSTRAINT SYS_asdf..  CASCADE; -- 제약조건 지우기
DROP TABLE DEPTTBL CASCADE CONSTRAINTS;
COMMIT;
-- 문제4) 기본키 설정(기본키명 넣어주세요 PK_DEPT_NO)
ALTER TABLE dept ADD CONSTRAINT PK_DETP_NO PRIMARY KEY(DEPTNO);

-- 문제5) 부서이름을 문자 30자로 변경
ALTER TABLE dept MODIFY name VARCHAR2(30);

-- 문제6) 부서 이름의 NO NULL로 변경
ALTER TABLE DEPT MODIFY name NOT NULL;

-- 문제7) 부서이름의 UNIQUE 제약조건 추가 UK_DEPT_NAME
ALTER TABLE DEPT ADD CONSTRAINT UK_DEPT_NAME UNIQUE(name);

-- 문제8) 부서의 UNIQUE제약조건 삭제하기
--ALTER TABLE DEPTTBL DROP CONSTRAINT UK_DEPT_NAME;
ALTER TABLE DEPT DROP CONSTRAINT UK_DEPT_NAME CASCADE;

-- 문제9)
-- 직원 테이블(employee)
-- empno : 직원코드 숫자, 기본키
-- name : 직원이름 문자(20)
-- deptno : 부서번호 not null
-- pay : 급여 숫자
-- regdate 등록일
-- position 직급 cgeck제약조건('사원','대리','과장')
CREATE TAble EMPTBL(
    EMPNO NUMBER PRIMARY KEY,
    NAME VARCHAR2(20),
    DEPTNO NUMBER NOT NULL,
    PAY NUMBER,
    REGATE TIMESTAMP,
    POSITION VARCHAR2(10) CHECK(POSITON IN('사원', '대리', '과장'))
--    CONSTRAINT PK_EMP_NO PRIMARY KEY(EMPNO)
--    CONSTRAINT FK_DEPT_NO FOREIGN KEY (DEPTNO) REFERENCES DEPTTBL(DEPTNO)
);

-- 삭제
DROP TABLE EMPTBL CASCADE CONSTRAINTS;

--문제10) deptno를 외래키 제약조건 설정 FK_DEPT_DEPTNO
ALTER TABLE EMPTBL ADD CONSTRAINT FK_EMP_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPTTBL(DEPTNO);


-- 오후일과
-- DML (데이터 조작어)

-- 추가 : INSERT
-- 삭제 : DELETE
-- 수정 : UPDATE
-- 조회 : SELECT

SELECT * FROM MEMBERtbl;

-- INSERT INTO 테이블명(컬럼엳으.생략가능) VALUES(추가할 값)
INSERT INTO MEMBERTBL VALUES('a', 'b', 12, '010-0000-0000', 'M', CURRENT_DATE);
COMMIT; -- 적용
ROLLBACK; -- 되돌리기

--
INSERT INTO MEMBERTBL VALUES('b', 'b', 12, '010-0000-0000', 'F', CURRENT_DATE);

--ENTITY 뒤죽박죽 넣을 수 있음
INSERT INTO MEMBERTBL(USERPW, AGE, PHONE, GENDER, REGDATE, USERID) VALUES('b', '12', '010-0000-0000', 'F', CURRENT_DATE, 'c');

--일괄 추가
INSERT ALL
    INTO MEMBERTBL VALUES('e', 'b', 12, '010-0000-0000', 'F', CURRENT_DATE)
    INTO MEMBERTBL VALUES('h', 'b', 12, '010-0000-0000', 'F', CURRENT_DATE)
    INTO MEMBERTBL VALUES('g', 'b', 12, '010-0000-0000', 'F', CURRENT_DATE)
SELECT * FROM DUAL;  

--시퀀스 생성
CREATE SEQUENCE SEQ_BOARDTBL_NO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;

SELECT * FROM BOARDTBL;
-- 글 작성하기
INSERT INTO BOARDTBL(NO, TITLE, WRITER, HIT, REGDATE, CONTENT) VALUES(SEQ_BOARDTBL_NO.NEXTVAL ,'제목1','aaa',1, CURRENT_DATE, '내용들@@@');

COMMIT;
ROLLBACK;

-- 업데이트
UPDATE BOARDTBL SET TITLE='변경제목', WRITER='변경작성자' WHERE NO=2;
SELECT * FROM BOARDTBL;
ROLLBACK;
SELECT * FROM BOARDTBL;

-- 문제) 글번호 6번인것 제목, 내용, 작성자 변경
UPDATE BOARDTBL SET TITLE='변경제목', CONTENT='변경내용', WRITER='변경작성자' WHERE NO=6;
SELECT * FROM BOARDTBL;
ROLLBACK;
SELECT * FROM BOARDTBL;

-- 조회수 증가
UPDATE BOARDTBL SET HIT=HIT+1 WHERE NO=3;


-- 글번호가 3,4,5인것의 제목과 작성자를 일괄 수정하기
UPDATE BOARDTBL SET
    TITLE = (CASE
        WHEN NO= 3 THEN '333'
        WHEN NO= 4 THEN '444'
        WHEN NO= 5 THEN '555'
    END),
    WRITER = (CASE
        WHEN NO= 3 THEN '작성자3'
        WHEN NO= 4 THEN '작성자4'
        WHEN NO= 5 THEN '작성자5'
    END),
    CONTENT = (CASE
        WHEN NO = 3 THEN '일괄변경1'
        WHEN NO = 4 THEN '일괄변경2'
        WHEN NO = 5 THEN '일괄변경3'
    END)
WHERE NO IN(3,4,5); -- WHERE NO=3 OR NO=4 OR NO=5

COMMIT;
ROLLBACK;

SELECT * FROM BOARDTBL;

-- 삭제
-- DELETE FROM 테이블명 WHERE 조건;
DELETE FROM BOARDTBL WHERE NO=5;

--조회수가 2보다 큰것만 삭제해라
DELETE FROM BOARDTBL WHERE HIT>=2;


-- 문제)시퀀스 SEQ_ITEM_NO 시작값은 1001
CREATE SEQUENCE SEQ_ITEMTBL_NO START WITH 1001 INCREMENT BY 1 NOMAXVALUE NOCACHE;

-- 문제2) 시퀀스를 이용해서 물품 5개 추가
--아래거 안됨
--INSERT ALL
--    INTO ITEMTBL VALUES(SEQ_ITEMTBL_NO.NEXTVAL, 'a', '내용12', 1, 80, CURRENT_DATE)
--    INTO ITEMTBL VALUES(SEQ_ITEMTBL_NO.NEXTVAL, 'b', '내용23', 2, 90, CURRENT_DATE)
--    INTO ITEMTBL VALUES(SEQ_ITEMTBL_NO.NEXTVAL, 'c', '내용34', 3, 101, CURRENT_DATE)
--    INTO ITEMTBL VALUES(SEQ_ITEMTBL_NO.NEXTVAL, 'c', '내용45', 3, 110, CURRENT_DATE)
--    INTO ITEMTBL VALUES(SEQ_ITEMTBL_NO.NEXTVAL, 'c', '내용56', 3, 120, CURRENT_DATE)
--SELECT * FROM DUAL;  

INSERT INTO ITEMTBL(NO, NAME, CONTENT, PRICE, QUANTITY, REGDATE)
    VALUES(SEQ_ITEMTBL_NO.NEXTVAL, 'a','a',111,222, CURRENT_DATE);
    
ROLLBACK;
COMMIT;
SELECT * FROM ITEMTBL;

-- 문제3) 물품번호가 1002번인 것의 수량을 10000, 가격을 9999로 변경
UPDATE ITEMTBL SET QUANTITY='10000', PRICE='9999' WHERE NO=1002;
--UPDATE ITEMTBL SET QUANTITY='80', PRICE='9999' WHERE NO=5;

-- 문제4) 수량이 100 미만인것만 삭제
DELETE FROM ITEMTBL WHERE QUANTITY<100;

--조회 중요중요중요중요중요중요중요중요중요중요중요중요중요중요중요중요중요중요중요중요
SELECT * FROM BOARDTBL;
SELECT NO, TITLE, WRITER FROM BOARDTBL;
SELECT NO, TITLE FROM BOARDTBL ORDER BY NO DESC; -- ASC는 오름차순 --DESC는 내림차순
SELECT NO, TITLE FROM BOARDTBL WHERE NO > 5 ORDER BY NO DESC;
-- 검색을 넣고싶다 제목같은데 포함되면 1이포함되면
SELECT * FROM BOARDTBL WHERE TITLE LIKE '%' || '1' || '%' ORDER BY NO DESC;

-- 전체 게시물 수
SELECT COUNT(*) FROM BOARDTBL; -- 그냥 들고오기
SELECT COUNT(*) cnt FROM BOARDTBL; -- 가져온거 CNT로 바꿔서 출력
SELECT NO, TITLE 제목, WRITER 작성자 FROM BOARDTBL; -- 가져온거 제목, 작성자로 바꿔서 들고옴
SELECT COUNT(*) cnt FROM BOARDTBL WHERE HIT>1;

--문제1) 물품의 물품번호, 이름, 가격, 수량만 조회
SELECT NO, NAME, PRICE, QUANTITY FROM ITEMTBL;

--문제2) 수량이 큰 순서대로 조회
SELECT NO, NAME, PRICE, QUANTITY FROM ITEMTBL ORDER BY QUANTITY DESC;

--문제3) 가격이 100~199인 것만 조회 WHERE 조건 AND 조건
SELECT NO, NAME, PRICE, QUANTITY FROM ITEMTBL WHERE 100<=PRICE AND PRICE < 200;

--문제4) 재고수량이 100이상 OR 가격이 200이상인 것을 날짜를 내림차순으로 조회
SELECT NO, NAME, PRICE, QUANTITY FROM ITEMTBL WHERE 100<=QUANTITY OR PRICE >= 200;




-- 보드로 페이지네이션(가상의 테이블)
SELECT B.*, ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN FROM BOARDTBL B;

-- 가상의 테이블 <= BOARDTBL + ROWN
SELECT * FROM (
    SELECT B.*, ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN FROM BOARDTBL B
) WHERE ROWN BETWEEN 11 AND 20;

SELECT * FROM (
    SELECT B.*, ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN FROM BOARDTBL B
) WHERE ROWN BETWEEN 1 AND 5;
SELECT * FROM (SELECT S.*, ROW_NUMBER() OVER (ORDER BY STDNO DESC) ROWN FROM STUDENTTBL S) WHERE ROWN BETWEEN 1 AND 5;

-- 문제1) 시퀀스 2개 생성 SEQ_DEPT_DEPTNO (1), SEQ_EMPLOYEE_EMPNO (1)
CREATE SEQUENCE SEQ_DEPT_DEPTNO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;
CREATE SEQUENCE SEQ_EMPLOYEE_EMPNO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;

-- 문제2) 부서 시퀀스를 이용하여 자료를 3개 추가
INSERT INTO DEPT(DEPTNO, NAME, REGDATE, AREA)
    VALUES(SEQ_DEPT_DEPTNO.NEXTVAL, 'a', CURRENT_DATE,'대구');
commit;
SELECT * FROM DEPT;

-- 문제3) 사원테이블에 시퀀스를 이용하여 5명 추가
INSERT INTO EMPTBL(EMPNO, POSITON, NAME, DEPTNO, REGDATE, PAY)
    VALUES(SEQ_EMPLOYEE_EMPNO.NEXTVAL,'사원' ,'현현현', SEQ_DEPT_DEPTNO.NEXTVAL, CURRENT_DATE, 80);
SELECT * FROM EMPTBL;

-- 문제4) 사원테이블의 사원 번호가 2인 사람의 이름을 가나다로 변경
UPDATE EMPTBL SET NAME='가나다' WHERE EMPNO=2;

-- 문제5) 사원테이블의 사원번호가 4인 사람 삭제
DELETE FROM EMPTBL WHERE EMPNO=4;
ROLLBACK;

-- 문제6) positon이 대리인사람만 급여가 높은 순으로 조회
SELECT EMPNO, NAME, DEPTNO, PAY, REGDATE, POSITION FROM EMPTBL WHERE POSITION = '대리' ORDER BY PAY DESC;

-- 문제7) 급여가 100이상인 사람만 조회
SELECT EMPNO, NAME, DEPTNO, PAY, REGDATE, POSITION FROM EMPTBL WHERE PAY >= 100;


SELECT * FROM MEMBERTBL M;


-- 22_09_07(2일차)

-- DCL(데이터 제의어) => 사용자의 권한을 생성하고 부여, 삭제(최상위 권위자 같은 느낌인듯)
    --system, 암호.., DBA
    --DS200 ~DS225(DBA)
-- 수행X(아이디 aaa, 암호 bbb) 계정 생성
CREATE USER aaa IDENTIFIED BY "bbb";
-- 수행X (생성된 사람에게 모든권한(DBA) 부여) -- 세밀하게 설정 가능 셀렉만, 인서트만 이렇게도 가능
GRANT CONNECT, RESOURCE, DBA TO aaa;
-- 수행X (생성된 사용자 삭제)
DROP USER aaa CASCADE;


-- DDL(데이터 정의어) => 테이블, 함수, 시퀀스 ...(데이터를 조작하기위해 준비해야하는 부분들) (프로젝트할때에는 정의어를 많이 만듬)
-- CREATE 생성, ALTER 변경, DROP 제거

-- DML(데이터 조작어) (프로젝트할때에는 정의어를 많이 만듬)
-- INSERT, UPDATE, DELETE, SELECT ...

-- TCL(트렌젝션 제어어)
-- COMMIT, ROLLBACK, SAVEPOINT

-- 반 클래스 CLASSTBL
-- 반코드 CLSCODE CHAR(1) 기본키 ex) A,B,C...
-- 반이름 CLSNAME VARCHAR2(20)
-- CLSREGDATE DATE
CREATE TABLE CLASSTBL(
    CLSCODE CHAR(1), -- 고정길이 중요중요중요
    CLSNAME VARCHAR2(20),
    REGDATE TIMESTAMP DEFAULT CURRENT_DATE,
    CONSTRAINT PK_CLASSTBL_CODE PRIMARY KEY(CLSCODE)
);
-- 추가
INSERT INTO CLASSTBL(CLSCODE, CLSNAME) VALUES('A', '601호');
INSERT INTO CLASSTBL(CLSCODE, CLSNAME) VALUES('B', '602호');
INSERT INTO CLASSTBL(CLSCODE, CLSNAME) VALUES('C', '603호');
COMMIT;
SELECT C.* FROM CLASSTBL C;


-- 학생테이블 STUDENTTBL
-- STDNO 학번 숫자 기본키
-- STDNAME 이름
-- STDCLASS 반 CHAR(1) <= 고정길이 <= 외래키 위의 CLASSTBL의 CLSCODE와 일치
-- STDSCOREKOR 국어점수
-- STDSCOREENG 영어점수
-- STDSCOREMATH 수학점수
-- STDREGDATE 등록일
CREATE TABLE STUDENTTBL(
    STDNO NUMBER,
    STDNAME VARCHAR2(30),
    STDCLASS CHAR(1), 
    STDSCOREKOR NUMBER, 
    STDSCOREENG NUMBER, 
    STDSCOREMATH NUMBER, 
    STDREGDATE TIMESTAMP DEFAULT CURRENT_DATE,
    CONSTRAINT PK_STUDENT_NO PRIMARY KEY(STDNO),
    CONSTRAINT FK_CLASS_CODE FOREIGN KEY (STDCLASS) REFERENCES CLASSTBL(CLSCODE)
);
DROP TABLE STUDENTTBL CASCADE CONSTRAINTS;

SELECT S.* FROM STUDENTTBL S;
-- 학생 5명 추가하기
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREENG, STDSCOREMATH) VALUES('1006', '이름6', 'A', 40, 50, 10);
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREENG, STDSCOREMATH) VALUES('1007', '이름7', 'B', 43, 30, 90);
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREENG, STDSCOREMATH) VALUES('1008', '이름8', 'C', 60, 30, 60);
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREENG, STDSCOREMATH) VALUES('1009', '이름9', 'A', 55, 35, 20);
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREENG, STDSCOREMATH) VALUES('1010', '이름10', 'C', 85, 10, 15);
COMMIT;

-- 문제1) 학생번호가 1001, 1005, 1007번의 국어 점수를 98점으로 변경
UPDATE STUDENTTBL SET STDSCOREKOR=98 WHERE STDNO=1001;
UPDATE STUDENTTBL SET STDSCOREKOR=98 WHERE STDNO=1005;
UPDATE STUDENTTBL SET STDSCOREKOR=98 WHERE STDNO=1007;

-- 문제2) 국어점수가 30미만을 68점으로 변경
UPDATE STUDENTTBL SET STDSCOREKOR=68 WHERE STDSCOREKOR<30;

-- 문제3) 학번이 1007인 학생을 삭제
DELETE FROM STUDENTTBL WHERE STDNO=1007;

-- 조회
SELECT S.STDNO, S.STDNAME, S.* FROM STUDENTTBL S; -- 넘버, 이름 + 전체
SELECT S.* FROM STUDENTTBL S; -- 전체
SELECT S.STDNO, S.STDNAME FROM STUDENTTBL S; -- 넘버, 이름
-- SUM, MAX, MIN, AVG <= ORACLE 내장함수
-- CURRENT_DATE, TO_CHAR <= 함수
-- 국어 점수 합계
SELECT 
    SUM(S.STDSCOREKOR) TOT,
    AVG(S.STDSCOREKOR) AVG
FROM 
    STUDENTTBL S
WHERE
    STDCLASS='A';

------------------ TO_CHAR
--SELECT S.*, TO_CHAR(S.STDREGDATE, 'YYYY-MM-DD HH24:MI:SS')
SELECT S.*, TO_CHAR(S.STDREGDATE, 'YYYY-MM-DD HH24') REGDATE1
FROM
    STUDENTTBL S;

SELECT S.STDNO, S.STDNAME, S.STDCLASS, S.STDSCOREKOR, S.STDSCOREENG, S.STDSCOREMATH, TO_CHAR(S.STDREGDATE, 'YYYY-MM-DD') REGDATE1
FROM
    STUDENTTBL S;

SELECT STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREENG, STDSCOREMATH, TO_CHAR(STDREGDATE, 'YYYY-MM-DD') STDREGDATE FROM (SELECT S.*, ROW_NUMBER() OVER (ORDER BY STDNO DESC) ROWN FROM STUDENTTBL S) WHERE ROWN BETWEEN 1 AND 5;

    
-- 문제1) B반의 국어, 영어, 수학 점수의 합 구하기
SELECT SUM(S.STDSCOREKOR) KORTOT, SUM(S.STDSCOREENG) ENGTOT, SUM(S.STDSCOREMATH) MATHTOT FROM STUDENTTBL S WHERE STDCLASS='B';

-- 문제2) 국어점수가 10점 이상인 학생을 학번 순으로 내림차순 정렬
SELECT S.* FROM STUDENTTBL S WHERE STDSCOREKOR >= 10 ORDER BY STDNO DESC;

-- 문제3) 학생별 합계 평균 구하기(학생 숫자 만큼) 그룹그룹그룹그룹그룹그룹그룹그룹그룹그룹그룹
-- ROUND(숫자, 자리수)
SELECT S.STDNO, (S.STDSCOREKOR+S.STDSCOREENG+S.STDSCOREMATH) SUM1 FROM STUDENTTBL S;
SELECT S.STDNO, ROUND((S.STDSCOREKOR+S.STDSCOREENG+S.STDSCOREMATH)/3, 2) AVG1 FROM STUDENTTBL S;
SELECT S.STDNO, S.STDNAME, (S.STDSCOREKOR+S.STDSCOREENG+S.STDSCOREMATH) SUM1, ROUND((S.STDSCOREKOR+S.STDSCOREENG+S.STDSCOREMATH)/3, 2) AVG1 FROM STUDENTTBL S;

SELECT S.* FROM STUDENTTBL S;

SELECT S.* FROM STUDENTTBL S GROUP BY STDCLASS; -- 불가능 입력 출력이 다음
SELECT S.STDCLASS FROM STUDENTTBL S GROUP BY STDCLASS; -- 이건 그대로 들고오라해서 됨 개수가 맞기 때문에
-- 반별합
SELECT S.STDCLASS, SUM(S.STDSCOREKOR) 반별_국어점수_합, COUNT(*) FROM STUDENTTBL S GROUP BY STDCLASS;
SELECT S.STDCLASS, SUM(S.STDSCOREKOR) CLASS_KOR_SUM, SUM(S.STDSCOREENG) CLASS_ENG_SUM, SUM(S.STDSCOREMATH) CLASS_MATH_SUM FROM STUDENTTBL S GROUP BY STDCLASS;

-- 국어점수가 50점 이상인 것만 반별 수학점수합을 구하라
SELECT S.STDCLASS, SUM(S.STDSCOREKOR) 반별국어50점이상만합 FROM STUDENTTBL S WHERE S.STDSCOREKOR>=50 GROUP BY STDCLASS;

-- 반별 국어점수 합을 구해서 합이 300 이상인 것만 조회
SELECT S.STDCLASS, SUM(S.STDSCOREKOR) 반별국어50점이상만합 FROM STUDENTTBL S GROUP BY STDCLASS HAVING SUM(S.STDSCOREKOR) >= 270;


------------------------------------

SELECT B.* FROM BOARDTBL B;

SELECT TO_CHAR(CURRENT_DATE, 'YYYY--MM-DD HH24') FROM DUAL;
SELECT SUBSTR('2022-09-17 14', 12, 2) FROM DUAL;
-- 위에 두개 합치기(중요중요중요중요중요중요중요중요중요중요중요)
SELECT SUBSTR(TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24'), 12, 2) FROM DUAL;

-- 문제1) "날짜"를 기준으로 조회수의 합과 개수
SELECT
    TO_CHAR(B.REGDATE, 'YYYY-MM-DD'),
    SUM(B.HIT),
    COUNT(*)
FROM 
    BOARDTBL B
GROUP BY 
    TO_CHAR(B.REGDATE, 'YYYY-MM-DD');

-- 문제2) "시"를 기준으로 조회수의 평균과 개수
SELECT
    SUBSTR(TO_CHAR(B.REGDATE, 'YYYY-MM-DD HH24'), 12, 2),
    SUM(B.HIT),
    COUNT(*)
FROM 
    BOARDTBL B
GROUP BY 
    SUBSTR(TO_CHAR(B.REGDATE, 'YYYY-MM-DD HH24'), 12, 2);

SELECT B.* FROM BOARDTBL B;