▶ 한줄평 : 종합문제가 처음에는 어려웠는데 몇번 반복해서 하다보니 조금은 익숙해 진 것 같고 오늘부터 본격적으로 자바공부를 시작하게 된다. 민경태 강사님께서 여러모로 실무에 도움이 많이 되는 조언들을 해주셔서 감사하다.
<오전>연습문제_종합2
<오후> SQL 활용 평가
1. 다음 설명을 읽고 적절한 테이블을 생성하시오.
-- 테이블 생성할 땐 기본키나 외래키를 별도로 설정하지 마시오.(5,6번 문제가 기본키, 외래키 설정 문제입니다.)
-> NUMBER(11 BYTE) X
2.1부터 1씩 증가하는 값을 생성하는 BOOK_SEQ 시퀀스를 생성하시오.
-- 아래 데이터를 BOOK_T 테이블에 INSERT하시오. 책번호는 BOOK_SEQ 시퀀스를 이용하시오.
-> ★INSERT하고 COMMIT꼭 하기!
★ 신물이 날 정도로 연습하면 1~2달 후에 안까먹음
3. 1000부터 1씩 증가하는 값을 생성하는 CUST_SEQ 시퀀스를 생성하시오.
-- 아래 데이터를 CUSTOMER_T 테이블에 INSERT하시오. 회원번호는 CUST_SEQ 시퀀스를 이용하시오.
-> 안적어도 알아서 사용된다(디폴트값)
-> 꼭 적어야 하는것 : START WITH 1000, ORDER
NULL값 채울때 NULL이라고 채우기(문자열이 아니라서 ""로 묶으면 안됨)
★ 실제 서비스 구현시) INSERT 하나만 작성해 놓고 요청시에만 반복적으로 돌림
-> 쿼리문자체가 ?쳐짐(한문장 만들어지고 물음표 3개)
-> 물음표를 따로 채워지는 문장 '자바'에 있음
-> 물음표 자리에 값 전달하기
4. 1부터 1씩 증가하는 ORDER_SEQ 시퀀스를 생성하시오.
-- 아래 데이터를 ORDER_T 테이블에 INSERT하시오. '주문일자6자리-시퀀스' 형식으로 주문번호를 만드시오.
-- TIP.
-- 1) ORDER_SEQ 시퀀스를 이용해서 주문번호를 INSERT한다.
-- 2) 기존 주문번호를 주문일-주문번호 형식의 주문번호로 UPDATE한다.
-> 12345678910 넣어놓고 수정
<MAXVALUE 지정하는 법 : MAXVALUE + 값>
->시퀀스 이름이 꼭 이렇게 생겨야만 하는건 아님(수업시간규칙, ORDER_SEQ)
TO_DATE('년/월/일' - 오라클 기본)
-> 기본 패턴이 아니라면 뒤에 알려줘야함(YY/MM/DD 생략가능)
날짜를 6짜리를 / 없이 가져온다 -> TO_CHAR
->날짜를 연월일- 7자리로 나타낼것!
-> 전체대상없이 WHERE없이
5.BOOK_T, CUSTOMER_T, ORDER_T 테이블의 BOOK_ID, CUST_ID, ORDER_ID 칼럼에 기본키를 추가하시오.
-- 기본키 제약조건의 이름은 PK_BOOK, PK_CUSTOMER, PK_ORDER으로 지정하시오.
6. ORDER_T 테이블의 CUST_ID, BOOK_ID 칼럼에 각각 CUSTOMER_T 테이블과 BOOK_T 테이블을 참조할 외래키를 추가하시오.
-- 외래키 제약조건의 이름은 FK_CUSTOMER_ORDER, FK_BOOK_ORDER으로 지정하시오.
-- CUST_ID나 BOOK_ID가 삭제되는 경우 이를 참조하는 ORDER_T 테이블의 정보는 NULL로 처리하시오.
7.책이름에 '올림픽'이 포함된 책 정보를 조회하시오.
-> 검색어가 ?자리에 검색(해당 정보를 포함한 모든 정보를 검색가능)
8. 가격이 가장 비싼 책을 조회하시오.(풀이1)
+ RANK가 1인 책 조회하시오. 가격의 순위 구하고 순위가 1인것 가져오기(풀이2)
-> 실행순서 안맞음
인라인뷰 : 실행순서 1번인 FROM절에 실행순서 배치하는 것
RANK : 35000이 2개 있으면 1등이 2개 있게 됨
9.'20/07/05'부터 '20/07/09' 사이에 주문된 도서 정보를 조회하시오.
- 테이블 두개 사용(다중테이블 조건방법: 조인, 서브쿼리)
-> O.BOOK_ID 아무거나 써도 상관없음
1: M관계 - 하나의 책을 여러번 주문할 수 있다
주문 안된 것은 관심없음 -> INNER JOIN
-> TO-DATE 처리가 이미 되어있음
- ORDER 뒤에 TO_DATE 필요없는 이유?
-> 이렇게 할 필요 없음
-> 이미 INSERT 시) TO_DATE처리해서 넣어음
-> 과거주문내역이 먼저 나오도록
10.주문한 이력이 없는 고객의 이름을 조회하시오.- 서브쿼리가 더 쉬움
주문테이블에 고객테이블에 없다
-> 주문번호가 없는 사람을 조회하겠음
11. '20/07/04'부터 '20/07/07' 사이에 주문 받은 도서를 제외하고 나머지 모든 주문 정보를 조회하시오.
- 테이블 3개 사용(조인)
★제외 개념 : NOT
총구매액 : 가격 * 수량
12. 가장 최근에 구매한 고객의 이름, 책이름, 주문일자를 조회하시오.
★ 빅테이터 쓸때 가장 많이 사용되는 것 : SQL(기본 정제)
★ 주문일자 가장 최근 : MAX
주문일자 가장 옛날 : MIN
BOOK_T INNER JOIN ORDER_T O INNER JOIN CUSTOMER_T C
(책과 고객이 같이 만나는 경우는 없으니까)
CUSTOMER_T C INNER JOIN ORDER_T O INNER JOIN BOOK_T B
(반대로 해도 상관없음, 가운데 ORDER_T 만 오면됨)보통은 ON B.BOOK_ID(PK:기본키) = O.BOOK_ID(FK:외래키) 관계인데 아닌 경우도 있음 13. 주문된 적이 없는 책의 주문번호, 책번호, 책이름을 조회하시오.
- 조인버전으로 접근해야함
주문된 적이 없다 : 책에는 있고 주문에는 없는것
OUTER 조인
14. 모든 서적 중에서 가장 비싼 서적을 구매한 고객이름, 책이름, 가격을 조회하시오.
가장 비싼 서적을 구매한 고객이 없다면 고객 이름은 NULL로 처리하시오.
ORDER테이블에 없더라도 BOOK테이블에 있으면 조회하시오- BOOK_t 항상 포함하는 외부 테이블로
-> 구매 정보 상관없이 모든 정보가 들어가있음 (안팔린책 포함)
-> 내부조회(INENR JOIN) : 양쪽 다 있는 것 조회
-> 책 안산 박세리 없음
-> 35000원짜리가 없음
-> INNER JOIN 안됨
15. '김연아'가 구매한 도서수를 조회하시오.
COUNT(GROUP BY) - 도서수
구매할때마다 주문번호 나옴
-> 주문횟수를 센다
-> 주문할때마다 부여받는 PK값(ORDER_ID)
GROUPING 안해도 이름 걸러낼 수 있음 -> 미리하는 조건 "WHERE절"
GROUP BY 에 명시되어 있어야 함
-> 구매한 횟수 : 2번
-> 이름만 김연아인 모든 사람들이 한사람이 됨
(고객이름은 중복이 많을 수 있음)
-> 이렇게 하면 안되고 , 고객번호도 GROUPING 대상으로 잡기
(고객 번호와 이름이 같은 사람)
+ 추가
어떤 고객이 무슨 책을 샀다
-> 같은 책번호는 하나로 모으는 작업
-> "DISTNICT"
16. 출판사별로 판매된 책의 갯수를 조회하시오.
-> 외부조인(삼성당 , 0)
-> 출판사는 없어도 조회가 되어야 하기때문에(출판사가 포함된 T 외부조인으로)
-> BOOK 왼쪽에 두는 이유 : 성능상
17. '박지성'이 구매한 도서를 발간한 출판사(PUBLISHER) 개수를 조회하시오.
-> 고객은 이름만으로 지으면 안됨
출판사가 중복되어 있을 수 있다 - DISTINCT 포함
18. 모든 구매 고객의 이름과 총구매액(PRICE * AMOUNT)을 조회하시오. 구매 이력이 있는 고객만 조회하시오.
19. 모든 구매 고객의 이름과 총구매액(PRICE * AMOUNT)과 구매횟수를 조회하시오. 구매 이력이 없는 고객은 총구매액과 구매횟수를 0으로 조회하고, 고객번호 오름차순으로 정렬하시오.
20. 총구매액이 2~3위인 고객의 이름와 총구매액을 조회하시오.
★ 서비스 구현시) 많이 씀 - 목록 구하기 (이정도 수준으로 나옴 , RANK 대신 다른것)
-초기화
사용자 만들거나 제거할 수 있는 것 - SYS 가지고 있음
<종합문제 실수한것>
1. 숫자타입 - NUMBER(가로에 쓰기)
2. 시퀀스 1부터 1씩 증가하는~ 만들기
DROP SEQUENXE BOOK_SEQ;
CREATE SEQUENCE BOOK_SEQ
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
CAHCE 20
ORDER;3. 주문번호 UPDATE
UPDATE 