매료매료의 개발 이야기

1. 분석하고자하는 요소를 "마우스 우클릭 > 검사" or F12(단축키)
　- html 코드는 물론이고 각 요소의 css 정보도 확인 가능

2. 페이지 상의 아무 여백 "마우스 우클릭 > 페이지소스보기"
　- 현재 웹페이지의 모든 html 코드 확인 가능

3. 개발자도구 메뉴중 "Toggle device toolbar" 선택
　- 현재 화면은 물론, 다양한 디바이스에서 웹페이지가 구현되는 모습을 확인 가능

Elements -> 태그를 열람하고, 소스 수정하고 실시간보기가 가능한 영역
Resources -> 웹사이트에 사용된 다양한 소스들을 보거나 다운받을 수 있는 영역, 어플리케이션
Network -> 이미지나 리소스를 가져오는데 걸리는 시간, 순서, 어떤 요청과 응답이 이루어지는지 보여주는 영역

                리프레쉬 누르면 웹브라우저와 웹서버가 주고 받는 데이터들을 체크 가능(css, 디자인 관련 작업시 유용)

                (네임, 스태테스, 타입 등의 탭에서 우클릭시 더 많은 탭 있음)

                - headers : general, response headers, request headers

Sources -> 웹 페이지 안에 삽입되어있는 자바스크립트를 디버깅하기 위한 영역

Timeline -> 크롬에서 작동하는 웹페이지의 성능(퍼포먼스)을 측정하기 위한 영역

Profiles -> html, css, js 등의 상태를 측정 할 수 있는 영역

Audits -> 웹페이지의 현재 상태를 체크해서 성능에 악영향을 줄 수 있는 부분에 대해 조언해주는 영역
Console -> 자바스크립트 명령을 실시간으로 입력해 볼 수 있는 영역

▼ 이클립스에는 debug perspective 가 있습니다. Run/Debug 를 하게 되면 자동으로 Debug perspective 로 이동하게 됩니다. 이곳에서 디버깅을 하게 되는 것이죠. 디버깅 perspective 를 추가하기 위해 Open Perspective 를 클릭해 보시면 볼수 있습니다.  

▼ 디버깅은 가장 핵심은 브레이크 포인트 입니다. 그러니까 조사하고 싶은 시점에 진행을 멈추고 각 객체에 들어가 있는 값들을 확인하는 것이죠. 에디터에서 의심되는 소스의 라인을 더블클릭하시면 조그마한 점이 찍힙니다. 이것이 브레이크 포인트 입니다. 프로세스가 단계별로 진행되다고 이곳에 오게 되면 멈추게 되겠죠.

▼ 실제로 브레이크 포인트에서 멈추는지 확인해 보겠습니다. F11 단축키를 누르거나 Run 메뉴 > Debug 를 클릭합니다. 그럼 바로 디버깅 모드로 컴파일이 진행됩니다.

▼ 컴파일이 되고 프로그램이 실행되면 브레이크 포인트가 있는 라인에서 아래 그림과 같이 멈추게 되는 것이죠. 이 소스를 작성한 본인은 브레이크 포인트를 찍은 목적이 있습니다. 자신의 생각대로 출력값이 안 나오거나 에러가 나서 원인이 무엇인지 파악하기 위해서 겠죠. 이렇게 원인을 파악하기 위해 단계를 진행하는 방법과 각 변수에 값이 무엇인지 확인하는 방법에 대해 알아 보겠습니다.

▼ 디버깅을 하게 되면 위에서 언급했던 대로 Debug Perspective 화면으로 전환됩니다. Debug Perspective 상단에는 디버깅을 진행할수 있는 아이콘들이 나타납니다. 이 아이콘들의 용도는 다음과 같습니다. 여기에서 아주 유용한 기능중 하나가 Drop to Frame 입니다. F6 을 눌러 단계별로 진행하다가 보고 싶은 변수 값을 놓치는 경우가 있죠. 그때 Drop to Frame 을 누르게 되면 해당 함수의 처음으로 이동하게 되므로 다시 확인해 볼수 있습니다.

1) Skip All Breakpoints : 모든 브레이크 포인트 건너뜀

2) Resume(F8키) : 다음 브레이크포인트까지 진행함

3) Suspend : 쓰레드를 일시 정지하며 현재 수행문에 지정한 것과 같음

4) Terminate : 쓰레드 종료

6) Step Into(F5키) : 한단계 진행하는데 다음 라인이 함수 안이면 함수 안으로 들어감.

7) Step Over(F6키) : 함수 호출을 지나치고 현재 위치에서 한 단계 진행

8) Step Return(F7키) : 현재 함수 끝까지 바로 가서 리턴한 후 함수 호출부로 되돌아 감

9) Drop to Frame : 선택한 스택 프레임의 첫 행으로 이동. 처음부터 다시 하고자 할 때

10) Use Step Filters(Shift+F5) : 스텝 필터링

▼ 이렇게 디버깅 진행 메뉴들은 상단 아이콘이 아닌 메뉴에서도 볼수 있습니다. Run 메뉴에 보시면 Debug Perspective 화면에서 볼수 있던 모든 디버깅 메뉴들이 있습니다.

▼ 이제 단계별로 진행하는 방법을 알았으니 변수들에 들어 있는 값을 확인해서 문제점을 찾아 봐야 겠죠. Step Into 나 Step Over 를 눌러 단계별로 진행하면서 의심되는 변수가 있다면 마우스로 가져갑니다. 그럼 팝업창이 뜨면서 해당 변수에 값들을 볼수 있습니다.

▼ 현재 프로그램에서 쓰이고 있는 모든 변수들을 확인하고 싶다면 상단에 있는 Variables 창으로 갑니다. 마우스로 확인하는 방법은 하나만 확인이 가능하지만 Variables 창에서는 모든 변수들을 실시간으로 확인할수 있습니다. 

▼ [Variables] 바로 옆에 있는 [Breakpoints] 탭은 현재 프로젝트에 설정되어 있는 모든 브레이크 포인트 정보를 볼수 있습니다. 해당 브레이크 포인트를 스킵하고 싶다면 체크해제 하시면 됩니다. 그리고 항목을 더블클릭하시면 브레이크 포인트가 설정된 포인트로 바로 이동할수 있습니다.

▼ 마지막 탭인 Expressions 은 내가 주로 보고 싶은 변수들을 추가해서 관찰할수 있습니다. Variables 는 변수들이 전부 모여 있기 때문에 일일이 찾아서 보기에는 불편합니다. 그래서 필요한 것만 추가해서 관찰할수 있는 Expressions 을 많이 이용하게 됩니다.

▼ Expressions 창에 추가하고 싶다면 변수를 선택하고 오른 마우스를 클릭합니다. 그리고 단축메뉴중 [Watch] 클릭하시면 바로 추가 됩니다.

출처: https://mainia.tistory.com/2259 [녹두장군 - 상상을 현실로]

/* 0731 실습1 */
-- 1.고객별로 주문한 제품의 수와 주문한 제품 중 //  최고항목가격(판매가격)을 추출하시오
-- 결과: 고객번호, 고객이름, 주문제품 수, 주문제품 중 최고항목가격
SELECT C.cust_id, C.cust_name,
    (SELECT SUM(OI.quantity)
    FROM Orderitems OI, Orders O
    WHERE OI.order_num = O.order_num
    AND O.cust_id = C.cust_id
    ) order_quantity,
        (SELECT MAX(item_price)
        FROM Orderitems OII, Orders OO
        WHERE OII.order_num = OO.order_num
        AND OO.cust_id = C.cust_id
        GROUP BY OO.cust_id
        ) max_price
FROM CUSTOMERS C
GROUP BY C.cust_id, C.cust_name;

-- 집합개념으로 풀이
SELECT m.cust_id, m.cust_name, n.quantity, n.max_price
FROM   customers m,
       (SELECT b.cust_id, SUM(a.quantity) quantity, MAX(a.item_price) max_price
        FROM   orderitems a, orders b
        WHERE  a.order_num = b.order_num
        GROUP BY b.cust_id
       ) n
WHERE  m.cust_id = n.cust_id    
;

-- 2.주문된 제품 중에 // 제품의 공급업체가 있는 도시가 ‘Dollsville’이고 // 주문된 총 제품의 수가 300을 넘는 제품명을 추출하시오
SELECT TRIM(prod_name)
FROM Products P
WHERE vend_id IN(
    SELECT vend_id
    FROM Vendors
    WHERE vend_city = 'Dollsville' 
    GROUP BY vend_id
    )
AND EXISTS(
    SELECT *
    FROM Orderitems
    WHERE prod_id = P.prod_id
    HAVING SUM(quantity) >=300
    )
;

-- 집합개념으로 풀이
SELECT m.prod_name
FROM   products m, vendors n
WHERE  m.vend_id = n.vend_id
AND    prod_id IN (
         SELECT prod_id
         FROM   orderitems a
         WHERE  prod_id = m.prod_id
         GROUP BY prod_id
         HAVING SUM(quantity) > 300
       )
AND    n.vend_city = 'Dollsville'        
;

-- 3.고객별로 주문한 상품 중에 //  가장 비싼 제품명을 추출하시오
-- 결과: 고객번호, 고객이름, 제품번호, 제품이름, 항목가격(판매가격)

-- 수정완료
SELECT C.cust_id, TRIM(C.cust_name), TRIM(P.prod_id), TRIM(P.prod_name), OI.item_price
FROM Customers C, Products P, Orders O, Orderitems OI
WHERE P.prod_id = OI.prod_id
AND OI.order_num = O.order_num
AND O.cust_id = C.cust_id
AND OI.item_price IN(
            (SELECT MAX(item_price)
            FROM Orderitems OII, Orders OO
            WHERE OII.order_num = OO.order_num
            AND OO.cust_id = C.cust_id
            GROUP BY C.cust_id
            )
    )
;

-- 집합개념으로 풀이
SELECT m.cust_id, m.cust_name, o.prod_id, o.prod_name, l.item_price
FROM   customers m, orders n, orderitems l, products o,
       (SELECT b.cust_id, MAX(a.item_price) max_price
        FROM   orderitems a, orders b
        WHERE  a.order_num = b.order_num
        GROUP BY b.cust_id
       ) t
WHERE  m.cust_id = n.cust_id
AND    n.order_num = l.order_num
AND    l.prod_id = o.prod_id
AND    m.cust_id = t.cust_id
AND    l.item_price = t.max_price
;

주요내용 : 데이터 삭제, 데이터 입력, 데이터 업데이트, CASE WHEN

/* 0730 실습1 */
-- 1. 주문번호가 20005, 20007인 주문된 제품중에 // 총 주문수량이 100개가 넘은 제품의 제품이름
SELECT TRIM(prod_name)
FROM Products
WHERE prod_id IN(
    SELECT prod_id
    FROM Orderitems
    WHERE order_num IN (20005, 20007)
    GROUP BY prod_id
    HAVING SUM(quantity) >100
    )
;

-- 2. DDL01 나 BRS01 인 제조업체에서 만든 제품 중 // 가격이 10불이상 인 제품의 총 판매수량
-- 결과: 제품명, 총판매수량
SELECT prod_id, TRIM(prod_name),
    (
    SELECT SUM(quantity)
    FROM Orderitems
    WHERE prod_id = a.prod_id
    AND item_price >= 10
    ) sum_quantity
FROM Products a
WHERE vend_id IN ('DDL01', 'BRS01')
AND EXISTS ( --exist는 1개만 존재해도 작동함. exist나 join 둘다 풀수있는 방법이면 exists로 해라.(효율이 더 좋음)
    SELECT * -- exist는 row 만 상관,
    FROM Orderitems
    WHERE prod_id = a.prod_id
    AND item_price >=10
    )
;

-- 3. 다음의 조건에 따라 고객의 고객번호, 고객이름, 고객주소를 추출하시오
-- 조건: 주문한 총 상품의 개수가 2개이상인 고객
SELECT cust_id, cust_name, cust_address
FROM Customers k
WHERE EXISTS(
    SELECT *
    FROM Orderitems OI, Orders O
    WHERE OI.order_num = O.order_num
    AND O.cust_id = k.cust_id
    GROUP BY OI.prod_id
    HAVING COUNT(*) >= 2
    )
;

-- 4. 현재 주문된 상품을 공급한 공급업체의 국가별 공급업체수
-- 결과: 국가,총공급업체수, 주문된 상품을 공급한 공급업체 수
SELECT
    vend_country, vend_cnt,
    (SELECT COUNT(*)
     FROM   orderitems aa, products bb, vendors cc 
     WHERE  aa.prod_id = bb.prod_id
     AND    bb.vend_id = cc.vend_id
     AND    cc.vend_country = t.vend_country
    ) order_vend_cnt
FROM
(
    SELECT d.vend_country, COUNT(d.vend_country) as vend_cnt
    FROM   orderitems a, orders b, products c, vendors d
    WHERE  a.order_num = b.order_num
    AND    a.prod_id = c.prod_id
    AND    c.vend_id = d.vend_id
    GROUP BY d.vend_country
) t;

/* CASE WHEN */
/*  형식
SELECT CASE WHEN 조건1 THEN 참일때 결과
            WHEN 조건2 THEN 참일때 결과
            ELSE 조건모두 불만족 시 결과
        END
    FROM   ..
--  조건절 사용*/

-- 우편번호로 지역 Type을 구분
SELECT vend_id,
    CASE WHEN SUBSTR(vend_zip, 1, 2) ='44' THEN 'A'
         WHEN SUBSTR(vend_zip, 1, 2) ='99' THEN 'B'
         ELSE 'C'
    END AS resion_type
FROM Vendors;

-- 기본제품가격보다 주문 시 낮은 단가로 주문되는 상품 중에 가격을 5$를 기준으로 고가/저가/일치로 구분
SELECT order_num, prod_id,
    CASE WHEN item_price > 5 THEN '고가'
         WHEN item_price < 5 THEN '저가'
         ELSE '일치'
    END AS price_std
FROM Orderitems a
WHERE item_price >= (
            SELECT MIN(prod_price)
            FROM Products
            WHERE prod_id = a.prod_id
            )
;

--  그룹함수와 조건절 함께 사용
-- 공급업체의 주별 수를 컬럼으로 분리하여 추출
SELECT SUM(CASE WHEN vend_state = 'MI' THEN 1 END) MI_CNT,
       SUM(CASE WHEN vend_state = 'OH' THEN 1 END) OH_CNT,
       SUM(CASE WHEN vend_state = 'CA' THEN 1 END) CA_CNT,
       SUM(CASE WHEN vend_state = 'NY' THEN 1 END) NY_CNT
FROM Vendors;

/* 실습 */
-- 1. 주문의 주문번호, 고객번호, 주문시기를 추출하시오
-- 주문시기: 일이 1~15일 사이면 ‘상일’, 16~31일 사이면 ‘하일’
SELECT order_num, cust_id, 
    CASE WHEN TO_CHAR(order_date, 'DD') <= 15 THEN '상일'
    ELSE '하일'
    END AS "주문시기"
FROM Orders;

SELECT order_num, cust_id,
         CASE WHEN TO_CHAR(order_date,'DD') >= '01' AND TO_CHAR(order_date,'DD') <= '15' THEN '상일'
              WHEN TO_CHAR(order_date,'DD') >= '16' AND TO_CHAR(order_date,'DD') <= '31' THEN '하일'
         END order_period
FROM orders;

-- 2. 주문에서 주문번호와 주문가능날자를 추출하시오
-- 주문가능날자: 주문날자가 1~15일이면 현재날자, 16~31일이면 다음달 1일
SELECT order_num, 
    CASE WHEN TO_CHAR(order_date, 'DD') <= 15 THEN '현재날짜'
    ELSE '다음달 1일'
    END AS "주문가능날짜"
FROM Orders;

SELECT  order_num,
          CASE WHEN TO_CHAR(order_date,'DD') >= '01' AND TO_CHAR(order_date,'DD') <= '15' THEN TO_CHAR(SYSDATE,'YYYY-MM-DD')
               WHEN TO_CHAR(order_date,'DD') >= '16' AND TO_CHAR(order_date,'DD') <= '31' THEN TO_CHAR(SYSDATE+1, 'YYYY-MM-DD')
          END orderCanDt
FROM orders;

-- 3. 주문에서 주문번호와 주문가능날자(YYYY-MM-DD)를 추출하시오
-- 주문가능날자: 일자의 10자리가 0이면 1일, 1이면 10일, 2이면 20일, 30이면 30일
SELECT order_num, 
    CASE WHEN SUBSTR(TO_CHAR(order_date, 'DD'), 2) = 0 THEN '1일'
         WHEN SUBSTR(TO_CHAR(order_date, 'DD'), 2) = 1 THEN '10일'
         WHEN SUBSTR(TO_CHAR(order_date, 'DD'), 2) = 2 THEN '20일'
         ELSE '30일'
    END AS "주문가능날짜"
FROM Orders;

SELECT order_num,
          TO_CHAR(
              CASE WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '0' THEN TO_DATE(TO_CHAR(order_date,'YYYYMM')||'01')
                   WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '1' THEN TO_DATE(TO_CHAR(order_date,'YYYYMM')||'10')
                   WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '2' THEN TO_DATE(TO_CHAR(order_date,'YYYYMM')||'20')
                   WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '3' THEN TO_DATE(TO_CHAR(order_date,'YYYYMM')||'30')
              END, 'YYYY-MM-DD') orderCanDt     
FROM orders;

/* 데이터 입력*/
--  형식
-- INSERT INTO 테이블명 VALUES(전체컬럼값…)
-- INSERT INTO 테이블명(컬럼명..) VALUES(컬럼값…): 컬럼명과 컬럼값의 개수는 일치해야 한다

-- Customers에 모든 컬럼에 대해 Data 추가
INSERT INTO Customers
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

-- Customers에 특정 컬럼에 대해 Data 추가
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000017', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL);


-- 초기값을 포함하여 테이블 생성
CREATE TABLE custnew AS
SELECT * FROM Customers;

DELETE FROM custnew;

-- SELECT 쿼리의 결과로 데이터 입력
-- 각 컬럼 간 데이터 타입이 일치해야 한다
INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country
FROM  customers;

/*실습*/
-- 1. 고객 테이블에 다음의 정보를 추가하시오
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000007', '홍길동', '서울시 서초구 강남대로 459', 'Seoul', '51243', 'KOR', 'Park', 'aaa@naver.com');

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000008', '박길동', '서울시 서초구 강남대로 459', 'Seoul', '51472', 'KOR', 'Kim', 'bbb@daum.net');

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000009', '박홍준', '서울시 서초구 강남대로 459', 'Seoul', '53782', 'KOR', 'Kim', 'ccc@google.co.kr');

--2. 제품 테이블에 다음의 정보를 추가하시오
INSERT INTO Products
VALUES('KRO1', 'BRS01', '뽀로로인형', '10', 'TV프로그램으로 유명한 뽀로로를 인형으로 만듦');

INSERT INTO Products
VALUES('KRO2', 'BRE02', '곰인형', '15', '요즘 유행하는 곰모양의 인형');

INSERT INTO Products
VALUES('KR03', 'DLL01', '태권V로보트', '100', '향수를 자극하는 어른을 위한 태권V로보트');

-- 3. 주문 테이블에 다음의 정보를 추가하시오
INSERT INTO Orders
VALUES('20010', '2019-02-01', '1000000006');

INSERT INTO Orders
VALUES('20011', '2019-05-04', '1000000007');

-- 4. 주문상품 테이블에 다음의 정보를 추가하시오
INSERT INTO Orderitems
VALUES('20010', '1', 'KR01', '2', '9');

INSERT INTO Orderitems
VALUES('20011', '1', 'KR02', '2', '16');

INSERT INTO Orderitems
VALUES('20011', '2', 'KR03', '3', '95');

/*데이터 업데이트*/
--  형식 - 하위쿼리를 사용할 수도 있다
-- 값에 쿼리를 넣을 수도 있다.
UPDATE 테이블명
SET    컬럼명 = 값, 컬럼값 = 값
WHERE  컬럼명 = 값;

-- ID가 1000000005인 고객의 전자메일주소를 변경
UPDATE Customers
SET cust_email = 'kim@naver.com',
    cust_contact = 'Sam Roberts'
WHERE  cust_id = '1000000005';

-- 1. 고객테이블에서 고객번호가 ‘1000000006’인 고객의 담당자를 ‘Jin’으로 변경하시오
UPDATE CUSTOMERS
SET cust_contact = 'Jin'
WHERE cust_id = '1000000006';

-- 2. 주문테이블에서 고객국가가 ‘KOR’인 고객이 주문한 항목수량에 +1씩을 더하시오
UPDATE Orderitems
SET quantity = quantity + 1
WHERE Order_num IN(
    SELECT order_num
    FROM Customers C, Orders O
    WHERE O.cust_id = C.cust_id
    AND C.cust_country = 'KOR'
    )
;

-- 3. 주문날자가 2019년도에 발생한 주문 중에 주문상품의 항목가격이 90$를 넘는 항목가격에 대해 10%씩을 DC하여 변경하시오
UPDATE ORDERITEMS
SET item_price = item_price * 0.9
WHERE order_num IN(
    SELECT order_num
    FROM Orders
    WHERE TO_CHAR(order_date, 'YYYY') = '2019'
    )
AND item_price >= 90;

-- 4. 주문상품중에 (제품의 제품가격보다 주문상품의 항목가격이 비싼) 항목가격을 제품의 제품가격으로 변경하시오
UPDATE ORDERITEMS k
SET item_price = (
    SELECT prod_price
    FROM Products
    WHERE prod_id = k.prod_id
    )
WHERE order_num IN (
    SELECT DISTINCT order_num
    FROM Orderitems OI, Products P
    WHERE OI.item_price > P.prod_price
    )
;

/* 데이터 삭제*/
--  형식 - 하위쿼리를 사용할 수도 있다
-- 삭제하기 전에 select으로 확인해보고 할것.
-- 부모부터 삭제할수 없음. 자식 테이블에서 우선 not exists 를 이용해서 확인후 부모 테이블에서 삭제해야함.
DELETE FROM 테이블명
WHERE  컬럼명 = 값;

-- ID가 1000000008인 고객삭제
DELETE FROM Customers
WHERE cust_id = ‘1000000008’;

-- 실습
-- 1. 제품번호가 KR03인 제품을 제품테이블에서 삭제하고 주문상품테이블에서도 삭제하시오
DELETE FROM Products
WHERE prod_id = 'KR03';

DELETE FROM Orderitems
WHERE prod_id = 'KR03';

SELECT *
FROM Products
WHERE prod_id = 'KR03';

-- 2. 공급업체가 ‘BRE02’인 공급업체가 생산한 제품중 ‘곰인형’ 제품을 주문한 주문정보를 삭제하시오
DELETE FROM Orderitems
WHERE prod_id IN (
    SELECT prod_id 
    FROM Products
    WHERE vend_id = 'BRE02'
    AND prod_name LIKE '%곰인형%'
    )
;

DELETE FROM Orders a
WHERE NOT EXISTS (
    SELECT order_num
    FROM Orderitems
    WHERE order_num = a.order_num
    )
;

-- 3. 고객 ‘1000000006’가 주문한 주문정보를 삭제하시오
DELETE FROM Orderitems
WHERE order_num IN(
    SELECT order_num
    FROM Orders
    WHERE cust_id = '1000000006'
    )
;

DELETE FROM Orders a
WHERE NOT EXISTS (
    SELECT order_num
    FROM Orderitems
    WHERE order_num = a.order_num
    )
;

주요내용 : 테이블 조인(OUTER, UNION)

/* 0729 실습1 SQL */
-- 1. 고객에서 고객번호, 고객이름, 고객주소, '주문한 총 금액', '주문한 제품중에 가장비싼금액의 상품명' 을 추출하시오-
-- '주문한 총 금액' : 주문상품에서 주문한 상품의 항목가격의 SUM
-- '주문한 제품중에 가장비싼 금액의 상품명' : 주문상품에서 해당고객이 주문한 상품중 가장비싼 상품의 상품명
SELECT cust_id, cust_name, cust_address, 
    (SELECT SUM(item_price)
    FROM Orderitems a, orders b
    WHERE a.order_num = b.order_num
    AND b.cust_id = k.cust_id
    )order_tot_price,
    (SELECT MAX(prod_name)
    FROM Orderitems a, orders b, products c
    WHERE a.order_num = b.order_num
    AND b.cust_id = k.cust_id
    AND a.item_price = (
        SELECT MAX(item_price)
        FROM Orderitems aa, orders bb
        WHERE aa.order_num = bb.order_num
        AND bb.cust_id = k.cust_id
        )
    ) max_prod_name
FROM CUSTOMERS k;

-- 2. 상품을주문한 고객중에 고객국가, 고객주별 주문수를 추출하시오 // orderitem이 메인테이블. 결과가 동일한게 메인테이블
-- 결과: 고객국가,고객주, 주문수

SELECT cust_country, cust_state, count(DISTINCT a.order_num) order_num
FROM Orderitems a, orders b, customers c
WHERE a.order_num = b.order_num
AND b.cust_id = c.cust_id
GROUP BY cust_country, cust_state;

-- 3. 주문상품에서 고객주별 가장비싼 제품의 가격을 추출하시오
-- 결과: 고객주, 비싼 제품의 가격

SELECT cust_state, MAX(a.item_price)
FROM Orderitems a, orders b, customers c
WHERE a.order_num = b.order_num
AND b.cust_id = c.cust_id
GROUP BY c.cust_state;

/* 테이블 조인*/
-- OUTER 조인
-- 실습
-- 1. 제품과 주문항목을 조인하여 다음의 결과를 추출하시오 결과: 제품번호, 제품이름, 주문번호, 항목수량, 항목가격 조건: 모든 제품이 조회되어야 한다
SELECT P.prod_id, P.prod_name, OI.order_num, OI.quantity, OI.item_price
FROM Products P, Orderitems OI
WHERE P.prod_id (+) = OI.prod_id; --  Main이 되는 테이블에 (+) 표시

-- 2. 제품을 주문한 고객 수와 주문 안 한 고객 수를 추출하시오 결과: 주문여부, 고객 수
SELECT gubn, COUNT(gubn)
FROM
(
    SELECT NVL((SELECT MAX('Y')
            FROM   orders
            WHERE  cust_id = a.cust_id
           ), 'N') gubn
    FROM   customers a
)
GROUP BY gubn;

-- 3. 고객별로 주문한 상품 중에 가장 높은 금액을 가지는 상품을 추출하시오 결과: 고객번호, 고객이름, 제품번호, 제품이름, 항목가격
-- 답맞는지 모르겠음??
SELECT C.cust_id, C.cust_name, P.prod_id, P.prod_name, OI.item_price
FROM Customers C, Products P, Orderitems OI
WHERE P.prod_id = OI.PROD_ID
AND OI.item_price = (
    SELECT MAX(item_price)
    FROM Orderitems
    WHERE OI.prod_id = P.prod_id
-- GROUP BY (OI.cust_id)
    )
;

-- 4. 공급업체별로 생산하는 제품의 수와 최고제품가격을 추출하시오 결과: 공급업체번호, 공급업체이름, 제품 수, 최고제품가격 조건: 8$이상의 제품을 생산하는 공급업체

-- 5. 주문 중에 고객이 사는 도시가 ‘Detroit’이고 주문한 총 제품의 수가 500을 넘는 고객을 추출하시오 결과: 고객번호, 고객이름, 고객도시

/*쿼리의 결합 - UNION*/
-- 실습
-- 3. 다음의 조건에 따라 공급업체의 공급업체번호, 공급업체명, 공급업체주소를 추출하시오
-- 조건: 주문된 상품이 2건이상이거나 제조하는 제품이 2개이상인 공급업체
SELECT vend_id, vend_name, vend_address
FROM VENDORS V
WHERE EXISTS(SELECT P.vend_id
             FROM ORDERITEMS OI, PRODUCTS P
             WHERE OI.prod_id = P.prod_id
             AND P.vend_id = V.vend_id
             GROUP BY P.vend_id
             HAVING COUNT(*) >= 2
UNION
         SELECT vend_id
         FROM   PRODUCTS P
         WHERE  vend_id = V.vend_id
         GROUP BY vend_id
         HAVING COUNT(*) >= 2);

-- 4. 다음의 조건에 따라 제품의 제품번호, 제품이름, 제품가격수준, 제품설명을 추출하시오
-- 제품가격수준: 제품가격이 3$대이면 ‘적당’ 11%대이면 ‘비쌈’ 나머지는 ‘보통’
-- 조건: 공급업체의 우편번호 앞의 2자리가 ‘44’, ‘45’, ’99’ 인 공급업체에서 만든 만들고 주문된 날자가 5월이 아닌 제품
SELECT P.prod_id, P.prod_name, DECODE(TRUNC(P.prod_price),3, '적당', 11, '비쌈', '보통') as price_level, P.prod_desc
FROM Products P, Vendors V
WHERE P.vend_id = V.vend_id
AND SUBSTR(V.vend_zip, 1,2) IN ('44', '45', '99')
UNION
SELECT P.prod_id, P.prod_name, DECODE(TRUNC(P.prod_price),3, '적당', 11, '비쌈', '보통') as price_level, P.prod_desc
FROM Products P
WHERE EXISTS(
    SELECT *
    FROM Orders O, Orderitems OI
    WHERE O.order_num = OI.order_num
    AND OI.prod_id = P.prod_id
    AND TO_CHAR(O.order_date, 'MM') = '05'
    )
; 

주요내용 : 집계함수, 데이터 그룹화, 하위 쿼리, 테이블 조인

/*0726과제*/
/*1. 상품주문에서 주문번호, 제품번호, 제품제조국가위치를 추출하시오
  - 제품제조국가위치: 제품번호의 마지막 2번째 1자리의 값이 0이면 ‘국내’,0이아니면 ‘해외’
  -총가격(항목수량*항목가격)이 500$이상인 주문*/
SELECT order_num, PROD_ID, DECODE(SUBSTR(prod_id, LENGTH(TRIM(prod_id))-1,1), '0', '국내', '해외')
FROM Orderitems
WHERE (quantity*item_price) >= 500;

/*2. 주문에서 주문번호와 주문가능날자(YYYY-MM-DD)를 추출하시오
    - 주문가능날자: 일자의 10자리가 0이면 1일,1이면 10일, 20이면 20일,30이면 30일 */
SELECT order_num, DECODE(SUBSTR(TO_CHAR(order_date,'DD'),1,1), '0', '1일', '1', '10일', '2', '20일', '3', '30일')
FROM   orders;

// 강사님 답
SELECT order_num,
          TO_CHAR(
              CASE WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '0' THEN
                             TO_DATE(TO_CHAR(order_date,'YYYYMM')||'01')
                   WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '1' THEN
                             TO_DATE(TO_CHAR(order_date,'YYYYMM')||'10')
                   WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '2' THEN
                             TO_DATE(TO_CHAR(order_date,'YYYYMM')||'20')
                   WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '3' THEN
                             TO_DATE(TO_CHAR(order_date,'YYYYMM')||'30')
              END, 'YYYY-MM-DD') orderCanDt     
   FROM   orders
   ;
//나의 답
SELECT order_num,
    DECODE(SUBSTR(TO_CHAR(order_date,'DD'),1,1),
    '0', TO_DATE(TO_CHAR(order_date,'YYYYMM')||'01'),
    '1', TO_DATE(TO_CHAR(order_date,'YYYYMM')||'10'),
    '2', TO_DATE(TO_CHAR(order_date,'YYYYMM')||'20'),
    '3', TO_DATE(TO_CHAR(order_date,'YYYYMM')||'30')
    ) as Order_available
FROM   orders;

/*3. 제품이름과 제품설명에 ‘king’이라는 단어가 들어가고 제품가격의 소수점 1자리가 9가 아닌 제품번호를 추출하시오*/
SELECT prod_id, prod_price
FROM Products
WHERE  prod_desc LIKE '%king%'
AND    SUBSTR(TRUNC(prod_price,1), LENGTH(TRUNC(prod_price,1)), 1) <> '9';

/*집계함수*/
-- AVG() : NULL값이 있는 경우 계산에서 제외된다.
-- Products 테이블에 있는 모든 제품의 가격평균
SELECT AVG(prod_price) AS avg_price
FROM Products;

-- DLL01의 공급업체에 대한 제품 평균
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

-- COUNT()
-- COUNT(*) : 테이블의 모든 행의 갯수, NULL값 포함
-- COUNT(컬럼) : 컬럼의 NULL값을 제외한 행의 개수
-- Customers 테이블에 있는 모든 고객의 수
SELECT COUNT(*) as num_cust
FROM Customers;

--cust_email 열에 값이 있는 고객의 수만 계산
SELECT COUNT(cust_email) as num_cust
FROM Customers;

--MAX() 지정한 열에서 가장 큰값을 반환. 
--TEXT도 사용가능, NULL값은 무시된다.
--Products 테이블에서 가격이 가장 비싼 제품의 가격
SELECT MAX(prod_price) AS max_price
FROM PRODUCTS;

-- MIN(): 지정한 열에서 가장 낮은 값을 반환
-- TEXT도 사용가능, NULL값은 무시된다
-- Products 테이블에서 가격이 가장 저렴한 제품의 가격
SELECT MIN(prod_price) AS min_price
FROM Products;

-- SUM(): 지정한 열에서 모든 값을 더한 합계
-- NULL값은 무시된다
-- 모든 주문한 물품의 수량의 합계 
SELECT SUM(quantity) AS items_ordered
FROM Orderitems;

-- 각 주문에 대한 총 금액을 반환
-- 연산과정에 null값이 존재하면 결과값도 null값이 됨.(굉장히 많이 발생하는 오류임★★★★★★★)
SELECT SUM(item_price * quantity) AS total_price
FROM Orderitems
WHERE order_num = 20005;

-- 고유값의 집계: SUM, MAX, MIN에서 사용
-- 가격이 같은 물품이 있는 경우 한번만 계산에 포함 
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

-- 집계함수 결합
SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM Products;

-- 1. 고객주문에서 주문날자가 가장 최근인 날자와 가장 최초인 날자를 추출하시오
SELECT TO_CHAR(MIN(order_date), 'YYYY-MM-DD'),  TO_CHAR(MAX(order_date), 'YYYY-MM-DD')
FROM Orders;

-- 2. 주문상품에서 제품번호가 BN으로 시작하는 각 주문의 총금액(항목수량*항목가격)이 가장큰 금액을 추출하시오
SELECT MAX(quantity*item_price)
FROM Orderitems
WHERE prod_id LIKE 'BN%';

-- 3. 주문상품에서 제품번호가 BR01와 BR03인 제품의 주문된 항목수량의 평균을 추출하시오
SELECT ROUND(AVG(quantity), 2)
FROM Orderitems
WHERE prod_id IN ('BR01', 'BR03');

/*중요★★★데이터 그룹화★★★*/
 -- 그룹만들기
 -- 중첩된 그룹이 있을 경우 데이터는 마지막 지정된 그룹을 기준으로 요약된다
 -- GROUP BY로 그룹화 되면 해당 컬럼은 SELECT절에 나타나야 한다
 -- NULL값되 그룹으로 분류된다 
 -- 공급업체별 상품 수 
 SELECT vend_id, COUNT(*) AS num_prods
 FROM Products
 GROUP BY vend_id;
 
 -- 필터링 그룹: 그룹을 만든다음 그 그룹결과에서 그룹함수를 통해 필터링을 수행
 -- WHERE: 그룹화 하기 전에 필터링 수행
 -- HAVING: 그룹화 한 후에 필터링 수행
 SELECT cust_id, COUNT(*) AS orders
 FROM Orders
 GROUP BY cust_id
 HAVING COUNT(*) >=2;
 
 -- 가격이 4이상인 제품을 두 개 이상 가진 공급업체를 추출 
 SELECT vend_id, COUNT(*) AS num_prods
 FROM Products
 WHERE prod_price >=4
 GROUP BY vend_id
 HAVING COUNT(*) >=2;
 
 -- ORDER BY와 함께 사용
 SELECT order_num, COUNT(*) AS items
 FROM Orderitems
 GROUP BY order_num
 HAVING COUNT(*) >=3
 ORDER BY items, order_num;
 
/*SELECT 절의 순서
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY */

-- 주문된 제품의 종류수 
SELECT COUNT(DISTINCT prod_id)
FROM Orderitems;

-- 주문된 제품별 총 주문수량
SELECT prod_id, SUM(quantity)
FROM Orderitems
GROUP BY prod_id;

-- 주문된 제품의 총 주문수량이 100개나 넘은 제품의 제품번호
SELECT prod_id, SUM(quantity)
FROM Orderitems
GROUP BY prod_id
HAVING SUM(quantity) > 100;

-- 주문번호가 20005, 20007인 주문된 제품중에 총 주문수량이 100개가 넘은 제품의 제품번호
SELECT prod_id, SUM(quantity)
FROM Orderitems
WHERE order_num IN (20005, 20007)
GROUP BY prod_id
HAVING SUM(quantity) > 100;

-- 1. 주문제품의 주문번호와 주문번호별 주문제품의 수를 추출하시오 / 결과: 주문번호, 주문제품의수
SELECT order_num, COUNT(prod_id)
FROM Orderitems
GROUP BY order_num;

-- 2. 주문에서 주문날자별 주문한 고객의 수를 추출하시오 / 결과: 주문날자(YYYY-MM-DD), 고객의 수
SELECT TO_CHAR(order_date, 'YYYY-MM-DD'), COUNT(DISTINCT cust_id)
FROM Orders
GROUP by order_date;

-- 3. 제품에서 공급업체번호별 제품의 수를 추출하시오 / 결과: 공급업체번호, 제품의수
-- 제품일수이기 때문에 * 쓰면 안됨. ->널값도 카운트됨.
SELECT vend_id, COUNT(prod_id)
FROM Products
GROUP by vend_id;

-- 4. 고객 중에 우편번호가 4로 시작되는 고객의 수를 추출하시오  /결과: 고객의 수
SELECT COUNT(cust_id)
FROM Customers
WHERE cust_zip LIKE '4%';

-- 5. 고객 중에 이메일 주소가 없는 고객의 수를 추출하시오 / 결과: 고객의 수
SELECT COUNT(cust_id)
FROM Customers
WHERE cust_email IS NULL;

-- 6. 제품중에 공급업체별 제품가격의 평균을 추출하시오 / 결과: 공급업체번호, 평균가격
SELECT vend_id, AVG(prod_price)
FROM Products
GROUP BY vend_id;

-- 7. 고객에서 주별 고객의 수를 추출하시오 / 결과: 고객주, 고객의수
SELECT cust_state, COUNT(cust_id)
FROM Customers
GROUP BY cust_state;

-- 8. 제품에서 공급업체가 BRS01, DLL01인 제품중에 가장비싼 제품의 가격이 5$이상인 제품의 제품번호를 추출하시오 
-- 결과: 공급업체번호, 제품번호, 제품가격(5$이상인 가장비싼 제품의 가격)
SELECT vend_id
FROM Products
WHERE vend_id IN ('BRS01', 'DLL01')
group by vend_id
having max (prod_price) >=5;

-- 9. 주문에서 1월중 주문한 주문중에 가장늦게 주문한 고객번호를 추출 / 결과: 주문번호, 주문일자(YYYY-MM-DD), 고객번호
SELECT order_num, MIN(TO_CHAR(order_date, 'YYYY-MM-DD')), cust_id
FROM Orders
WHERE TO_CHAR(order_date, 'MM') = '01' group by order_num, cust_id; 

-- 10. 주문에서 1월중 주문한 주문중에 고객별 가장늦게 주문한 주문일자를 추출 / 결과: 고객번호, 주문일자(YYYY-MM-DD)
SELECT cust_id, MIN(TO_CHAR(order_date, 'YYYY-MM-DD'))
FROM Orders
WHERE TO_CHAR(order_date, 'MM') = '01' group by cust_id; 

/*하위쿼리 사용*/
-- IN절안에 하위쿼리 사용하기
-- 하위쿼리를 먼저 실행하여 결과를 추출하고 그 결과를 바탕으로 메인 쿼리를 실행한다
-- 상품번호가 RGAN01인 상품을 주문한 고객ID추출 
SELECT cust_id
FROM Orders
WHERE order_num IN (
    SELECT order_num
    FROM Orderitems
    WHERE prod_id = 'RGAN01');
    
-- 하위쿼리 안에 하위쿼리 사용
-- 상품번호가 RGAN01인 상품을 주문한 고객의 이름과 연락처 추출 
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (
    SELECT cust_id
    FROM Orders
    WHERE order_num IN (
        SELECT order_num
        FROM Orderitems
        WHERE prod_id = 'RGAN01'));
        
-- 하위쿼리를 계산필드로 사용
-- 중복된 컬럼명을 구분하기 위해 테이블명으로 구분
SELECT cust_name, cust_state,
    (SELECT COUNT(*)
    FROM Orders
    WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
            
-- FROM절에 SQL 사용하기
SELECT order_num, order_item, type
FROM
(SELECT order_num, order_item, SUBSTR(prod_id, 1, 2), type
FROM Orderitems) A
WHERE type = 'BR';

-- EXISTS, NOT EXISTS
-- EXITTS : 주문한게 1건이라도 있는것을 추출하라.
SELECT cust_id, cust_name, cust_address
FROM customers a
WHERE EXISTS(
    SELECT *
    FROM orders
    WHERE cust_id = a.cust_id
    );
    
-- 1. 제품의 고유ID와 제품이름, 제품가격, 제품설명을 추출하시오 조건: 공급업체의 국가가 ‘USA’인 공급업체의 제품
SELECT prod_id, prod_name, prod_price, prod_desc
FROM Products a
WHERE vend_id IN(
    SELECT vend_id
    FROM Vendors
    WHERE vend_country = 'USA'
    );

-- 2. 고객의 고객번호, 고객이름, 고객주소, 고객메일주소를 추출하시오 조건: 고객의 메일주소가 있고 주문을 한 건이라도 한 고객
SELECT cust_id, cust_name, cust_address, cust_email
FROM Customers a
WHERE EXISTS (
    SELECT *
    FROM Orders
    WHERE cust_id = a.cust_id
    )
AND cust_email IS NOT NULL; -- AND와 WHERE 위치가 바뀌어도 됨.

-- 3. 공급업체의 공급업체번호, 공급업체이름, 공급업체주소, 공급업체별 제품 수를 추출하시오 조건: 공급업체별 제품수가 2개 이상인 공급업체
SELECT vend_id, vend_name, vend_address,
    (SELECT COUNT(*)
    FROM Products
    WHERE vend_id = a.vend_id) prod_su
FROM Vendors a
WHERE EXISTS(
       SELECT *
       FROM Products
       WHERE vend_id = a.vend_id
       GROUP BY vend_id
       HAVING COUNT(prod_id) >=2);           

-- 4. 주문제품 중에 제품별로 항목가격이 가장 낮은 제품번호를 추출하시오 결과: 제품번호, 항목가격(제품별 가장 낮은 항목가격)
SELECT prod_id, MIN(item_price)
FROM Orderitems
GROUP BY prod_id;

/*테이블 조인*/
-- 1:1로 할지 , 1:多로 할지 사전에 결정해야함. 조인과 조인이 만나면 결과집합의 모수가 변하면 안됨.
-- ★★★Main table 판단(결과값 개수만큼 나오는 테이블이 main) -> 가상의 table
-- 모든 key join
-- 조인: 테이블 간에 동일한 컬럼들을 연결시켜서 통합된 정보를 추출하는 방법
-- 조인을 많이 할 수록 성능을 떨어진다
-- 상품에 대한 공급업체 정보를 함께 추출
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products --메인 테이블 : Products
WHERE Vendors.vend_id = Products.vend_id;

-- 조인 시에 조건절에서 테이블간의 관계를 설정하지 안으면 cartesian product(집합의 개수 곱하기 결과.)
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;

-- ANSI SQL - 내부 조인 (ANSI도 많이 쓰이므로 연습해놔야함.)
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products -- 이너조인 : 두 데이터가 같은 결과만 조인
    ON Vendors.vend_id = Products.vend_id;
    
-- 여러 테이블 조인
-- 상품에 대한 공급업체 정보와 주문수량까지 추출
SELECT vend_name, prod_name, prod_price, quantity
FROM Orderitems, Vendors, Products -- 메인테이블 : Orderitems
WHERE Products.vend_id = Vendors.vend_id
AND Orderitems.prod_id = Products.prod_id
AND order_num = '20007';

-- 상품번호가 RGAN01인 주문자이름과 연락처 추출 
SELECT cust_name, cust_contact
FROM Customers, Orders, Orderitems
WHERE Customers.cust_id = Orders.cust_id
AND Orderitems.order_num = Orders.order_num
AND prod_id = 'RGAN01';

--  테이블 별칭사용
-- 상품번호가 RGAN01인 주문자이름과 연락처 추출 
SELECT cust_name, cust_contact
FROM Customers C, Orders O, Orderitems OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

--  동일 테이블 조인
-- Jim Jones라는 사람이 일하는 회사의 모든 고객 담당자의 메일주소 추출 
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                FROM Customers
                WHERE cust_contact = 'Jim Jones');
                
-- 같은 쿼리를 조인으로 추출
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers c1, Customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

--  조인 시 전체 컬럼 조회 처리
SELECT C.*, O.order_num, O.order_date, OI.prod_id
FROM Customers C, Orders O, Orderitems OI -- Orderitems를 기준으로 관계를 맺음.
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

-- 1. 주문과 고객정보를 조인하여 다음의 결과를 추출하시오 결과: 주문번호, 주문날자, 고객번호, 고객이름, 고객주소, 고객시, 고객메일주소 조건: 고객별로 마지막 주문날자의 주문
SELECT O.order_num, O.order_date, O.cust_id, C.cust_name, C.cust_address, C.cust_city, C.cust_email
FROM Orders O, Customers C -- 메인테이블 : Orders
WHERE O.cust_id = C.cust_id
AND O.order_date = (
    SELECT MAX(order_date)
    FROM orders
    WHERE O.cust_id = C.cust_id
--    GROUP BY (O.cust_id) 그룹바이로 해도 됨.
    )
;

-- 정렬기준 추가
--SELECT * FROM table_name ORDER BY 3, 1 DESC;
--3번째 열을 기준으로 오름차순으로 정렬한 상태에서 1번째 열을 기준으로 내림차순으로 정렬하는 쿼리 입니다.

SELECT prod_id, prod_name, prod_price
FROM   Products
ORDER BY 3, 1 DESC;

-- 2. 공급업체와 제품을 조인하여 다음의 결과를 추출하시오 결과: 공급업체번호, 공급업체이름, 공급업체주소, 제품번호, 제품이름, 제품가격, 제품설명 조건: 공급업체별로 평균제품가격 이상인 제품
SELECT V.vend_id, V.vend_name, V.vend_address, P.prod_id, P.prod_name, P.prod_price, P.prod_desc
FROM Vendors V, Products P
WHERE V.vend_id = P.vend_id
AND P.prod_price >= (
    SELECT AVG(prod_price)
    FROM Products
    WHERE vend_id = V.vend_id
    )
;

-- 3. 제품, 주문, 주문제품을 조인하여 다음의 결과를 추출하시오
-- 결과: 주문번호, 주문일자, 제품번호, 제품이름, 제품가격, 항목가격 조건: 제품가격과 항목가격이 다른 제품
SELECT O.order_num, TO_CHAR(O.order_date, 'YYYY-MM-DD') order_date, P.prod_id, P.prod_name, P.prod_price, OI.item_price
FROM Orders O, Products P, Orderitems OI
WHERE OI.order_num = O.order_num
AND OI.prod_id = P.prod_id
AND P.prod_price <> OI.item_price;

-- 4. 공급업체, 제품, 주문제품을 조인하여 다음의 결과를 추출하시오
-- 결과: 공급업체번호, 공급업체이름, 공급업체주소, 주문번호, 제품번호, 제품이름
SELECT V.vend_id, V.vend_name, V.vend_address, OI.order_num, P.prod_id, P.prod_name
FROM Vendors V, Orderitems OI, Products P, Orders O
WHERE OI.order_num = O.order_num
AND OI.prod_id = P.prod_id
AND P.vend_id = V.vend_id;

package Week03.day0726;

import java.sql.Date;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.LinkedList;

public class practice0726 {

	public static void main(String[] args) {
		
//		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
//		
//		String s1 = "2019-07-26 13:00:45";
		
		Food fd1 = new Noodle(1, "팟타이", 1500000);
		Food fd2 = new FriedRice(2, "XO게살볶음밥", 1000);
		Food fd3 = new FriedRice(3, "김치볶음밥", 300000);
		
		AutoFood af = new AutoFood(1, Date.valueOf("2019-07-26"));
		
		af.orderFood(fd1);
		af.orderFood(fd2);
		af.orderFood(fd3);
		
		System.out.println("====주문된  음식====");
		af.printOrderFood();
		
		af.serviceFood(fd1);
		
		System.out.println("====제공된  음식====");
		af.printServiceFood();
		
		af.printTotalCount();
		
	}

}

class AutoFood{
	private int orderFoodNo; //주문번호
	private Date orderDt; // 주문시간 - 분까지 넣어라.
	private LinkedList foods = new LinkedList(); // 주문음식
	private ArrayList serviceFoods = new ArrayList(); //제공된 음식
	private static int fdcnt;
	
	public AutoFood(int orderFoodNo, Date orderDt) {
		this.orderFoodNo = orderFoodNo;
		this.orderDt = orderDt;
	}

	public void orderFood(Food f) { // 음식을 주문한다
//         주문 시 조리시간을 기준으로 정렬한다(조리시간이 짧은음식을 상위로)
//         주문된 음식유형(클래스)별로 총 주문 수량을 Count한다 ex. 국수몇개, 볶음밥 몇개(클래스별로)
//		     수량 자식클래스에 static 공통변수 만들어서 카운트 되도록 하고 출력만 하면 됨.
		this.foods.add(f);
		Collections.sort(foods);
	}
	
	public void printOrderFood() { // 주문된 음식을 출력한다(주문시간이 짧은 걸 기준으로?)
		
//		LinkedList<Food> list = new LinkedList<Food>();
//		list.addAll(foods);
//		Collections.sort(list);
//	    for(Food f : list) {
//	    	System.out.println(f.toString());
//	    }
	    
	    Iterator it = foods.iterator();
	    while(it.hasNext()){
	    	System.out.println(it.next());
	    }
		
	}
	
	public void serviceFood(Food f) { // 주문음식에서 삭제하고 제공된 음식으로 이동시킨다
		foods.remove(f);
		serviceFoods.add(f);
	}
	
	public void printServiceFood() { // 제공된 음식을 출력한다
		
		LinkedList<Food> list = new LinkedList<Food>();
		list.addAll(serviceFoods);
		Collections.sort(list);
	    for(Food f : list) {
	    	System.out.println(f.toString());
	    }
		
	}
	
	public void printTotalCount() { // 음식유형(클래스)별로 총 주문 수량을 출력한다
		System.out.println("====음식별 총 주문 수량====");
		System.out.println("누들 : " + Noodle.getFdcnt() + " 개");
		System.out.println("볶음밥 : " + FriedRice.getFdcnt() + " 개");
	}
	
}

abstract class Food implements Comparable<Food>{ // 추상클래스로 만들어라.
	private int foodNo; //음식번호
	private String foodNm; // 음식이름
	private int time; // 조리시간
	private static int fdcnt;
	
	public Food(int foodNo, String foodNm) {
		this.foodNo = foodNo;
		this.foodNm = foodNm;
	}

	public int getFoodNo() {
		return foodNo;
	}

	public void setFoodNo(int foodNo) {
		this.foodNo = foodNo;
	}

	public String getFoodNm() {
		return foodNm;
	}

	public void setFoodNm(String foodNm) {
		this.foodNm = foodNm;
	}

	public int getTime() {
		return time;
	}

	public void setTime(int time) {
		this.time = time;
	}
	
}

class Noodle extends Food{
	static int fdcnt; // 주문수량 카운트
	private int time;
	
	public int getTime() {
		return time;
	}

	public void setTime(int time) {
		this.time = time;
	}

	public Noodle(int foodNo, String foodNm, int time) {
		super(foodNo, foodNm);
		this.time = time;
		fdcnt++; // 생성자에 카운트를 넣음으로써 자동카운트 가능.
	}

	public static int getFdcnt() {
		return fdcnt;
	}

	public static void setFdcnt(int fdcnt) {
		Noodle.fdcnt = fdcnt;
	}
	
	@Override
	public int compareTo(Food f) {
		// 주문 시 조리시간을 기준으로 정렬한다(조리시간이 짧은음식을 상위로)
		if(this.time > f.getTime()) {
			return 1;
		} else if (this.time < f.getTime()) {
			return -1;
		}
		return 0;
	}
	
	public String toString() { // int foodNo, String foodNm, int time
		return "음식번호 : " + this.getFoodNo() + "번 / 음식이름 : " + this.getFoodNm() + " / 조리시간: " + this.getTime() + " 분";
	}
}

class FriedRice extends Food{
	static int fdcnt; // 주문수량 카운트
	private int time;

	public FriedRice(int foodNo, String foodNm, int time) {
		super(foodNo, foodNm);
		this.time = time;
		fdcnt++;
		// TODO Auto-generated constructor stub
	}
	
	public static int getFdcnt() {
		return fdcnt;
	}

	public static void setFdcnt(int fdcnt) {
		FriedRice.fdcnt = fdcnt;
	}
	
	public int getTime() {
		return time;
	}

	public void setTime(int time) {
		this.time = time;
	}
	
	@Override
	public int compareTo(Food f) {
		// 주문 시 조리시간을 기준으로 정렬한다(조리시간이 짧은음식을 상위로)
		if(this.time > f.getTime()) {
			return 1;
		} else if (this.time < f.getTime()) {
			return -1;
		}
		return 0;
	}
	
	public String toString() { // int foodNo, String foodNm, int time
		return "음식번호 : " + this.getFoodNo() + "번 / 음식이름 : " + this.getFoodNm() + " / 조리시간: " + this.getTime() + " 분";
	}
}

############### 반복문(Loops and iteration) ##################

반복 조건이 true면 실행하고, false일때 멈춘다.

// document.write는 자바스크립트를 이용해서 웹페이지에 텍스트를 출력한다.
// 이것은 웹브라우저에서만 동작할 것이다. node.js 콘솔과 같은 환경에서 실습을 한다면
// console.log와 같은 메소드를 대신 사용한다.

while(true){
    document.write("coding everybody <br />");
}

for(var i = 0; i < 10; i++){
          document.write("coding everybody"+i+"<br />");
}

- while과 for의 차이점
while문은 [ ①초기화 ②반복조건 ③반복이 될 때마다 실행되는 코드 ] 의 기입장소가 서로 멀어질 수 있는데,
for문 같은 경우, 
for( ① ; ② ; ③ ) { ~ } 이런 형식으로, while문의 반복조건을 넣는곳에 전부 기입할 수 있다.
그러므로, 더 보기 편하고 오류도 줄일 수 있다.

- 반복문을 실행 도중에 제어하고 싶을때
break 반복문 도중에 만나면 완전히 정지시키고 빠져나간다
continue 반복문 도중에 만나면 다시 반복문을 재 실행 시킨다(변수의 초기화는 하지 않음).

// break 사용
for(var i = 0; i < 10; i++){
    if(i === 5) {
        break;
    }
    document.write('coding everybody'+i+'<br />');
}

// continue 사용
for(var i = 0; i < 10; i++){
    if(i === 5) {
        continue;
    }
    document.write('coding everybody'+i+'<br />');
}

- 반복문 안에 또 다시 반복문(조건문)이 들어갈수 있다(반대로 조건문 안에 반복문 ok).

// 0부터 9까지 변수 i에 순차적으로 값을 할당        
for(var i = 0; i < 10; i++){
    // 0부터 9까지의 변수를 j의 값에 순차적으로 할당
    for(var j = 0; j < 10; j++){    
        // i와 j의 값을 더한 후에 출력
        // String은 숫자인 i와 j의 데이터 타입을 문자로 형태를 변환하는 명령이다. 
        // String()을 제거하고 실행해보면 의미가 좀 더 분명하게 드러날 것이다.
        document.write(String(i)+String(j)+'<br />');
    }
}

* ("hello"+i) 이렇게 문자열 hello 와 숫자 i를 결합시킬때, javascript는 숫자를 문자열로 만든다.
** i++ i 를 리턴하고 값을 더한다 | ++i i 의 값을 더하고 리턴한다. /// 밑 코드 참조 

var x = 3;
y = x++; // y = 3, x = 4

var a = 2;
b = ++a; // a = 3, b = 3

package Week03.day0725;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Objects;

public class practice0725 {

	public static void main(String[] args) throws ParseException {
		
		SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
		
		Product p1 = new Product(3424, "갤럭시S10");
		Product p2 = new Product(3331, "아이폰XR");
		Product p3 = new Product(5552, "아이폰X");
		Product p4 = new Product(1111, "아이폰S7");
		
		HashMap products = new HashMap();
		
//		products.put(004, "갤럭시S10");
//		products.put(001, "아이폰XR");
//		products.put(002, "아이폰X");
//		products.put(003, "아이폰S7");
		
		Order or = new Order(123, f.parse("2019-07-01"), products);
		
		or.addProduct(1, p1);
		or.addProduct(2, p2);
		or.addProduct(3, p3);
		or.addProduct(4, p4);
		
		or.delProduct(123);
		
		Product pr = new Product(9997, "아이패드");
		
		or.chgProduct(1, pr);
		
		or.printKeyBySort(products);
		
		Compare1 c = new Compare1();
		
		or.printValueBySort(c);
		
		
		//comparable로 구현
//		System.out.println("====printValueBySort(by comparable)====");
//		ArrayList<Product> list = new ArrayList<Product>();
//	    list.addAll(products.values());
//		Collections.sort(list);
//	    for(Product p : list) {
//	    	System.out.println(p.toString());
//	    }

	}
}

class Order{
	private int orderNo;
	private Date orderDt;
	private HashMap products = new HashMap(); // 주문상품
	
	public int getOrderNo() {
		return orderNo;
	}

	public void setOrderNo(int orderNo) {
		this.orderNo = orderNo;
	}

	public Order(int orderNo, Date orderDt, HashMap products) {
		this.orderNo = orderNo;
		this.orderDt = orderDt;
		this.products = products;
	}

	public void addProduct(int ordProdNo, Product p) { // 주문상품을 추가한다
		this.orderNo = orderNo;
		this.products.put(ordProdNo, p);
	}
	
	public void delProduct(int ordProdNo) { // 주문상품을 삭제한다
		products.remove(ordProdNo);
	}
	
	public void chgProduct(int orProdNo, Product p) { //  주문상품을 변경한다
		products.replace(orProdNo, p);
	}

	public void printKeyBySort(HashMap products) { // 주문상품을 키를 기준으로 정렬하여 출력한다
		
		System.out.println("====printKeyBySort====");
		HashMap<Integer, Product> hs = new HashMap<Integer, Product>(products);
		Iterator<Integer> it = products.keySet().iterator();   //키값 오름차순 정렬(기본)
		while(it.hasNext()) {
		  Integer key = it.next();
		  System.out.println("key 값 : " + (key) +", 제품명 : " + hs.get(key).getProductNm() + ", 제품번호 : " + hs.get(key).getProductNo());
		 }
	}
	
	public void printValueBySort(Comparator <Product> c) {
		//주문상품을 Value를 기준으로 정렬하여 출력한다                             
		//Value는 제품번호를 기준으로 정렬한다
		
		System.out.println("====printValueBySort====");
		ArrayList<Product> list = new ArrayList<Product>();
	    list.addAll(this.products.values()); // 값을 리턴. comparable로 (내부)
	    Collections.sort(list, c);
	    for(Product p : list) {
	    	System.out.println(p.toString());
	    }
	}
}

class Compare1 implements Comparator<Product> {
	@Override
	public int compare(Product o1, Product o2) {
		if(o1.getProductNo() > o2.getProductNo()){
			return 1;
		} else if (o1.getProductNo() < o2.getProductNo()) {
			return -1;
		} else {
			return 0;
		}
    }
}
	    
	    // Comparator - 편법 키값을 정렬해서 다시 value를 정렬하는 방법.
//	    list.addAll(products.keySet()); // 키값을 리턴. comparator로 (외부)
//	    Collections.sort(list, new Comparator() { 
//	    	@Override
//            public int compare(Object o1,Object o2) {
//                Object v1 = products.get(o1);
//                Object v2 = products.get(o2);
//                return ((Comparable<Object>) v2).compareTo(v1);
//            }
//        });
//	    
//	    Collections.reverse(list); // 주석시 오름차순
//        return;

class Product{
	private int productNo; // 제품번호
	private String productNm; // 제품이름
	
	public Product(int productNo, String productNm) {
		this.productNo = productNo;
		this.productNm = productNm;
	}

	public int getProductNo() {
		return productNo;
	}

	public void setProductNo(int productNo) {
		this.productNo = productNo;
	}

	public String getProductNm() {
		return productNm;
	}

	public void setProductNm(String productNm) {
		this.productNm = productNm;
	}
	
	public int hashCode() {
		return Objects.hash(this.productNm, this.productNo);
	}
	
	public boolean equals(Object obj) {
		if(obj instanceof Product) {
			Product tmp = (Product)obj;
			return this.productNo== tmp.productNo && this.productNm.equals(tmp.productNm);
		}
		return false;
	}
	
	public String toString() {
		return "제품번호 : " + this.getProductNo() + "/" + "제품이름 : " + this.getProductNm();
	}
	
	//comparable로 구현 (Product클래스에 implements Comparable 붙어야함)
//	public int compareTo(Object s) {
//		if(this.productNo < ((Product)s).getProductNo()) {
//			return -1;
//		} else if (this.productNo > ((Product)s).getProductNo()) {
//			return 1;
//		}
//		return 0;
//	}
}

주요내용 : 텍스트 제어함수, 문자자르기, 숫자 제어함수, 데이터 조작함수

/*-- (빈공백은 나오지 않게 처리한다)  */
/*1. 고객정보에서 고객이름이 ‘The’로 시작되고 고객국가가 ‘USA’인 고객의 주소정보를 다음의 Format으로 추출하시오    
   (빈공백은 나오지 않게 처리한다)    
   Format = 고객주소’ ‘고객시/도’ ‘고객주’ ‘고객우편번호’(‘고객국가’)’
*/

SELECT TRIM(CUST_ADDRESS) || '''' || '''' ||TRIM(cust_city) || '''' || '''' || TRIM(cust_state)|| '''' || '''' || TRIM(cust_zip) || '''' || '(' || '''' || TRIM(cust_country) || '''' || ')' || ''''
FROM CUSTOMERS
WHERE CUST_NAME LIKE 'The%'
AND CUST_country = 'USA';

   
/*2. 주문제품항목에서 주문번호가 ‘20005’, ‘20007’ 인 주문에 대해 주문번호, 제품번호와 주문총금액(항목수량*항목가격) 을 추출하시오*/
SELECT order_num, prod_id, (quantity*item_price)
FROM orderitems
WHERE order_num IN ('20005', '20007');
   
   
/*3. 제품정보에서 공급업체번호가 ‘BRS01’, ‘DLL01’이 아닌 제품이름과 제품가격을 다음의 Format으로 추출하시오
   Format = 제품이름’[’제품가격’]’
*/

SELECT TRIM(prod_name) ||'['||prod_price||']'
FROM Products
WHERE vend_id NOT IN ('BRS01', 'DLL01');

/*텍스트 제어함수*/
/*공급업체이름의 길이*/
SELECT vend_id, LENGTH(TRIM(vend_name))
FROM vendors;

/*공급업체 주소를 소문자 or 대문자로 */
SELECT vend_id, LOWER(vend_address), UPPER(vend_address)
FROM vendors;

/*문자 자르기*/
SELECT vend_id, SUBSTR(vend_address, 1, 3), SUBSTR(vend_address, 4)
FROM vendors;

--문자 대체하기--
SELECT vend_id, REPLACE(vend_address, 'Street', 'Road')
FROM vendors;

--NULL이 아닌 첫번째 값--
SELECT vend_id, vend_state, vend_city, COALESCE(vend_state, vend_city)
FROM vendors
WHERE vend_id = 'FNG01';

-- 공급업체주가 CA이면 1, NY이면 2 모두 아니면 3--
SELECT vend_id, vend_state, DECODE(TRIM(vend_state), 'CA', 'NY', 2, 3)
FROM vendors;

--NULL이면 다른 값으로 
--nvl : 널값이면 디폴트값 리턴
SELECT nvl(cust_email, 'test@naver.com')
FROM customers;

--1. 고객의 주소 중에 소문자로 ‘south’가 들어간 고객정보를 다음의 Format으로 추출하시오(공백제거) - Format: 고객이름’/’고객주소, 소문자로
SELECT RTRIM(cust_name)||'/'||LTRIM(cust_address)
FROM Customers
WHERE LOWER(cust_address) LIKE '%south%';

--2. 주문상품의 주문번호가 ‘20005’, ‘20007’가 아닌 제품유형코드를 추출하시오 - 제품유형코드: 제품번호의 앞의 2자리
SELECT SUBSTR(prod_id, 0, 2)
FROM Orderitems
WHERE order_num NOT IN ('20005', '20007');

--3. 고객의 고객이름과 고객주소정보를 추출하시오 - 고객주소정보: 고객주소중에 Drive는 Car로 변경
SELECT cust_name, REPLACE(cust_address, 'Drive', 'Car')
FROM Customers;

--4. 공급업체의 공급업체번호가 ‘BR’로 시작되는 공급업체이름과 공급업체지역번호를 공급업체이름으로 내림차순으로 추출한다 - 공급업체지역번호: 우편번호의 앞에 2자리
SELECT vend_name, SUBSTR(vend_zip, 0, 2)
FROM Vendors 
WHERE vend_id LIKE 'BR%' ORDER by vend_name DESC;

--5. 공급업체의 공급업체번호가 ‘BRE02’이 아닌 공급업체이름과 공급업체사업구역번호를 추출한다 - 공급업체사업구역번호: 공급업체지역번호가 44이면 A, 99이면 B, 11이면 C 나머지는 D
SELECT vend_name, DECODE(SUBSTR(vend_zip,1,2), '44', 'A', '99', 'B', '11', 'C', 'D')
FROM Vendors
WHERE vend_id <> 'BRE02';

--6. 고객의 고객명과 고객주소정보를 추출한다 - 고객주소정보: 고객메일주소 or 고객주소, 고객메일주소가 있으면 고객메일주소가 우선
SELECT cust_name, COALESCE(cust_email, cust_address)
FROM Customers;

--7. 제품의 제품명에 ‘bear’이 들어있는 모든 제품에 대해 제품이름을 추출하는데 bear를 toy로 변경하여 추출한다
SELECT REPLACE(prod_name, 'bear', 'toy')
FROM Products
WHERE prod_name LIKE '%bear%';

/*숫자제어함수*/
-- 올림, 내림
SELECT prod_id, prod_price, CEIL(prod_price), FLOOR(prod_price)
FROM products;

-- 반올림, 버림
SELECT prod_id, prod_price, ROUND(prod_price,1), TRUNC(prod_price,1)
FROM products;

-- 제곱, 제곱근
SELECT prod_id, prod_price, POWER(prod_price,2), SQRT(prod_price)
FROM products;

-- 나머지
SELECT prod_id, prod_price, MOD(prod_price,2)
FROM products;

--1. 제품의 제품번호가 ‘BR’로 시작되는 상품의 가격을 소수점 첫째자리에서 반올림 하여 제품이름과 함께 추출하시오
SELECT RTRIM(prod_name), ROUND(prod_price, 0)
FROM Products
WHERE prod_id LIKE 'BR%';

--2. 주문상품중에 가격이 10$이상인 주문번호와 상품번호, 항목가격을 추출하시오 - 항목가격: 항목가격은 소수점 버림으로 처리하여 추출
SELECT order_num, prod_id, TRUNC(item_price,0)
FROM Orderitems
WHERE item_price >=10;

/*데이터 조작함수 사용하기*/
-- 현재날짜와 주문날짜 간의 월차이
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), MONTHS_BETWEEN(sysdate, order_date)
FROM orders;

-- 주문날짜에서 10개월을 추가
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), TO_CHAR(ADD_MONTHS(order_date,10), 'YYYY-MM-DD')
FROM orders;

-- 주문한 달의 마지막 날짜
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), TO_CHAR(LAST_DAY(order_date), 'YYYY-MM-DD')
FROM orders;

-- 특정날짜와 주문날짜간의 월차이
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), MONTHS_BETWEEN(TO_DATE('2018-12-05', 'YYYY-MM-DD'), order_date)
FROM orders;

-- 숫자를 문자로
SELECT order_num, quantity, 'A'||TO_CHAR(quantity)
FROM orderitems;

-- 날짜를 문자로
SELECT order_num, TO_CHAR(order_date, 'YYYY-MM-DD'), TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS')
FROM orders;

-- 2004년에 주문된 항목의 주문번호
SELECT order_num
FROM Orders
WHERE TO_NUMBER(TO_CHAR(order_date, 'YYYY'))=2004;

-- BETWEEN으로 구현
SELECT order_num
FROM Orders
WHERE order_date BETWEEN TO_DATE('20040101') AND TO_DATE('20041231');

-- 1. 고객주문에서 주문월일 2월인 모든 주문번호를 주문번호를 내림차순으로 하여 추출한다
SELECT order_num, order_date
FROM Orders
WHERE TO_NUMBER(TO_CHAR(order_date, 'MM'))=2 Order by order_num DESC;

-- 2. 고객주문에서 주문일에 +1일을 한 날자가 해당 주문월의 마지막 날자와 같은 주문번호를 오름차순으로 추출한다
SELECT order_num
FROM Orders
WHERE LAST_DAY(order_date) = (order_date + 1)
Order by order_num ASC;

-- 3. 고객주문에서 현재일과 주문일간의 차이가 185개월을 넘는 주문번호를 추출한다
SELECT order_num, order_date
FROM Orders
WHERE MONTHS_BETWEEN(sysdate, order_date) > 185;

-- 4. 고객주문에서 고객번호가 1로 끝나는 주문의 주문날짜의 월일을 추출하시오 (Format예 : 07-01)
SELECT cust_id, TO_CHAR(order_date, 'MM')||'-'||TO_CHAR(order_date, 'DD') /* TO_CHAR(order_date, 'MM-DD')*/
FROM Orders
WHERE cust_id LIKE '%1'; /* WHERE SUBSTR(cust_id, LENGTH(cust_id),1) = '1'*/