[DB] 데이터베이스 - self Join, outer Join, union, exists

문제1

- 회원아이디 b001회원의 마일리지값보다 큰, 
- 회원정보 조회하기
- 조회컬럼 : 회원아이디, 이름
- 2가지 이상의 방법 적용

 

-- 답1 (가상테이블 적용)
SELECT A.mem_id, A.mem_name, A.mem_mileage
FROM (SELECT mem_id ,mem_mileage, mem_name
             FROM member) A,
            (SELECT mem_id ,mem_mileage
             FROM member
             WHERE mem_id = 'b001') B
WHERE  A.mem_mileage > B.mem_mileage;

-- 답2 (서브쿼리적용)
SELECT mem_id, mem_name
FROM member
WHERE mem_mileage > (SELECT mem_mileage FROM member WHERE mem_id = 'b001' );

-- 답3 (가상테이블 적용 - 간단 버전)
SELECT mem_id, mem_name
FROM member,
           ( SELECT mem_mileage FROM member WHERE mem_id = 'b001'  ) MEM
WHERE member. mem_mileage > MEM.mem_mileage;

※ 가상테이블A 설계(회원정보 조회)
SELECT mem_id ,mem_mileage
FROM member
WHERE mem_id = 'b001';

※가상테이블B 설계(b001회원의 마일리지)
SELECT mem_id ,mem_mileage
FROM member
WHERE mem_id = 'b001';

문제1

---

< Self Join >

 Self Join으로 처리하기 
- 셀프 조인 시 : 조건은 한쪽 M2테이블에 적용
- 조회 시 : M1테이블에서 조회

SELECT M1.mem_id, M1.mem_name
FROM member M1, member M2
WHERE M2.mem_id = 'b001'
                AND M1.mem_mileage > M2.mem_mileage;

 

 

문제2-1

- 회원별 구매금액의 총액을 조회
- 2005년 5월에 구매한 내역입니다.
- 조회컬럼 : 회원이름, 구매금액총액
- 일반방식
- 표준방식

 

-- 일반방식
SELECT mem_name, SUM(prod_sale*cart_qty)
FROM member, cart, prod
WHERE mem_id = cart_member
              AND cart_prod = prod_id
              AND SUBSTRING(cart_no, 1, 6) LIKE '200505%'
GROUP BY cart_member;

-- 표준방식
SELECT mem_name, SUM(prod_sale*cart_qty)
FROM member
           INNER JOIN cart
                       ON(mem_id = cart_member
                              AND SUBSTRING(cart_no, 1, 6) LIKE '200505%')
           INNER JOIN prod
                       ON(cart_prod = prod_id)
GROUP BY cart_member;

문제2-1

 

문제2-2

- 위 문제에 이어서
- 회원 전체에 대해서 위 조건으로 조회
- 총 회원은 28명 입니다. ( 확인용 코드 : SELECT COUNT(*) FROM member; )
   ∵ 구매이력 없는 사람까지도 다 나오게 조회하기

 

-- left join (내 코드)
SELECT mem_name, nvl(SUM(prod_sale*cart_qty), 0) AS total
FROM member 
           LEFT JOIN cart
                       ON(mem_id = cart_member)
           LEFT JOIN prod
                       ON(cart_prod = prod_id)
GROUP BY mem_id, mem_name;

-- left outer join (교수님 코드)
SELECT mem_name, SUM(nvl(prod_sale*cart_qty, 0)) AS total
FROM member 
                LEFT OUTER JOIN cart
                          ON(mem_id = cart_member
                                AND cart_no LIKE '200505%')
                LEFT OUTER JOIN prod
                          ON(cart_prod = prod_id)
GROUP BY mem_id, mem_name
ORDER BY total DESC;

 ※ Outer Join 시 절대 where절 사용하지 않기!!( 조건에 맞는 것들을 걸러버려서  null 데이터가 안들어감)
     - left join = left outer join
     - 근데 다른 데이터베이스에서도 사용하려면 left outer join을 써야함
     - 이걸 쓰면 다른 빈 값은 자동으로 null로 저장됨
     - null값을 바꾸는 nvl() 해주기!!

문제 2-2

 

---

< Outer Join >

Outer Join
- 특정 테이블 전체에 대한 집계를 하고자 할 때 사용
- 통계 낼 때 많이 사용

Outer Join 종류

 Left Outer Join : 테이블 순서 중 왼쪽에 위치한 테이블 전체 (가장 많이 사용)
 Right Outer Join : 오른쪽에 위치한 테이블 전체
 Full Outer Join : 왼쪽 + 오른쪽 전체 (Oracle DB에서만 사용가능)

 

Outer Join 개념
- Outer Join은 표준방식으로만 사용해야하며, 일반조건은 무조건 On() 내부에 작성해야함
- Where절 사용하시면 Outer 전체 개념이 적용 안된다
- Inner Join을 만족해야 함(Inner Join 개념을 그대로 적용)
- Left or Right 기준으로, 같으면 같은 조건대로 조회 , 다르면 Null 조회

 

 

문제3

- 상품분류 전체에 대한 상품종류가 몇개씩 있는지 집계하기

- 아래 그림에서 prod_lgu끼리 묶고 그 상품 종류 갯수 집계

 

SELECT lprod_gu, lprod_nm, COUNT(prod_lgu) AS cnt 
FROM lprod
            LEFT OUTER JOIN prod
                                     ON(lprod_gu = prod_lgu)
GROUP BY lprod_gu
ORDER BY cnt desc;

 ※ outer join 할때 더 큰 범위가 왼쪽으로 기준되어야함(그래야 null값 생김)

문제3

 

문제4-1

- 2005 년도 월별 판매 현황 검색하기
- 조회 컬럼 : 판매월, 월별 총판매수량, 월별 총판매금액

 

SELECT SUBSTRING(cart_no, 5,2) AS mm,
               SUM(cart_qty) AS total_qty,
               SUM(cart_qty*prod_sale) AS total
FROM cart
            LEFT OUTER JOIN prod
                                      ON(cart_prod = prod_id
                                            AND SUBSTRING(cart_no, 1, 4) = '2005')
GROUP BY SUBSTRING(cart_no, 5, 2);

※ group by에 substring 가능!!

문제 4-1

문제4-2

- 위 문제에 이어서..
- 1월부터 12월까지 없어도 모두 표현하고자 한다면 어떻게?
- 조회 컬럼 : 판매월, 월별 총판매수량, 월별 총판매금액

 

< 내 코드 >

-- MONTH 테이블 생성 (month만 )
CREATE TABLE MONTH(
month INT(10),
total_qty_sub INT(10),
total_sub INT(10)
);

-- 테이블에 값 입력( 뛰어쓰기 조심!!)
INSERT INTO MONTH (month, total_qty_sub, total_sub) VALUES (01, NULL, NULL),
  (02, NULL, NULL),
  (03, NULL, NULL),
  (04, NULL, NULL),
  (05, NULL, NULL),
  (06, NULL, NULL),
  (07, NULL, NULL),
  (08, NULL, NULL),
  (09, NULL, NULL),
  (10, NULL, NULL),
  (11, NULL, NULL),
  (12, NULL, NULL);

-- 테이블에 값 입력( 뛰어쓰기 조심!!)
SELECT A.month, nvl(B.total_qty, 0), nvl(B.total, 0)
FROM MONTH A
               LEFT OUTER JOIN (SELECT SUBSTRING(cart_no, 5,2) AS mm,
                                                SUM(cart_qty) AS total_qty,
                                                SUM(cart_qty*prod_sale) AS total
                                                FROM cart
                                                LEFT OUTER JOIN prod
                                                                           ON(cart_prod = prod_id
                                                                                 AND SUBSTRING(cart_no, 1, 4) = '2005')
                                                GROUP BY SUBSTRING(cart_no, 5, 2)) B
                                        ON(B.mm = A.month)
ORDER BY A.month ;

※ 나는 새로운 테이블을 만들고 그 테이블을 우리가 했던 4-1에서 했던 코드를 가상테이블(서브쿼리사용)로 left outer join 해주었다. on 조건에 해당 월별을 같다 두었다. 

 

<교수님>

-- 테이블 생성(월만 작성해도 됨)
CREATE TABLE date_mm(
mm CHAR(2) NOT NULL );

-- 테이블에 컬럼 값 넣기
INSERT INTO date_mm (mm) VALUES ('01'),
  ('02'),
  ('03'),
  ('04'),
  ('05'),
  ('06'),
  ('07'),
  ('08'),
  ('09'),
  ('10'),
  ('11'),
  ('12');

-- 코드 합치기
SELECT date_mm.mm,
               SUM(nvl(cart_qty, 0)) AS total_qty,
               SUM(nvl(cart_qty*prod_sale, 0)) AS total
FROM date_mm
                  LEFT OUTER JOIN cart
                                            ON(date_mm.mm = SUBSTRING(cart_no, 5, 2))
                  LEFT OUTER JOIN prod
                                            ON(cart_prod = prod_id
                                                   AND SUBSTRING(cart_no, 1, 4) = '2005')
GROUP BY date_mm.mm;

※ date_mm 기준으로 left outer join 두번 하기(cart와 prod), 데이터 값 쓸때는 테이블 이름 붙여주기  
※ "GROUP BY SUBSTRING(cart_no, 5, 2)"는 조인시키면서 on으로 연결지었기 때문에 그거랑 같은 값인 date_mm.mm을 넣어 줘야함 

date_mm테이블 생성

 

문제 4-2

 

< UNION 사용 >

SELECT
    months.month,
    NVL(SUM(A.cart_qty), 0) AS total_sales,
    NVL(SUM(A.cart_qty*B.prod_sale),0) AS total
FROM (
   -- 월을 나타내는 문자열을 생성하는 서브쿼리, 가상의 테이블(months)을 생성,
   -- 가상 컬럼(month)에 월을 나타내는 문자열이 들어감
    SELECT '200501' AS month
    UNION SELECT '200502'
    UNION SELECT '200503'
    UNION SELECT '200504'
    UNION SELECT '200505'
    UNION SELECT '200506'
    UNION SELECT '200507'
    UNION SELECT '200508'
    UNION SELECT '200509'
    UNION SELECT '200510'
    UNION SELECT '200511'
    UNION SELECT '200512'
) AS months
       LEFT JOIN cart A ON (SUBSTRING(A.cart_no, 1, 6) = months.month)
       LEFT JOIN prod B ON (A.cart_prod = B.prod_id)
GROUP BY months.month
ORDER BY months.month;

※ 'months' 서브쿼리를 사용하여 각 월에 대한 정보를 생성하고,
    왼쪽 조인을 통해 'cart' 및 'prod' 테이블과 결합하여 각 월에 대한 판매 통계를 가져옴.

 

---

< 자연스러운 조인 >

자연스러운 조인
- 컬럼명이 같은 경우

SELECT *
FROM member A, member B
WHERE A.mem_id = B.mem_id;

값에 의한 연결(조인이라고 칭하지는 않음)
- 가장 바람직한 경우 : PK,FK

SELECT *
FROM cart, lprod
WHERE SUBSTRING(cart_prod, 1, 4) = lprod_gu;

 

 

 

 

문제5

- 2005년도에 대한 전체 거래처별 총매출금액 조회
- 조회 컬럼 : 거래처코드, 거래처명, 총매출금액
- 정렬 : 총매출금액 내림차순

< 전체 거래처 수 조회>

SELECT COUNT(*) FROM buyer;

전체 거래처수

 

< 문제5 답>

SELECT buyer_id, buyer_name, SUM(nvl(cart_qty * prod_sale, 0)) AS total
FROM buyer
LEFT OUTER JOIN prod
ON(buyer_id = prod_buyer)
LEFT OUTER JOIN cart
ON(prod_id = cart_prod
and cart_no LIKE '2005%')
GROUP BY buyer_id, buyer_name
ORDER BY total DESC;

문제 5

---

 

< UNION >

UNION

- 조회결과와 조회결과를 행단위로 합치는 기능
- 조회결과들 간의 컬럼의 갯수는 동일해야 함
- 조회결과들 간의 데이터 타입은 동일해야 함
- 보통은 합칠 때 앞에 합친 테이블 명을 '테이블명'으로 넣어서 조회함
- 정렬은 제일 마지막에 정의해야함
- 컬럼명은 첫번째 select문의 컬럼이름을 따름

UNION : 중복제거
UNION ALL : 중복포함(실제 전체)
MINUS : 차집합
INTERSECT : 교집합
exists(조회) : 조회결과가 1건이라도 있으면 True, 0건이면 False
                         서브쿼리를 적용(다중컬럼의 다중행 모두 가능) 

 

< UNION >

-- 28행
SELECT 'member', mem_id, mem_name
FROM member
-- 합치면 155
UNION
-- 135행
SELECT 'cart', cart_member, cart_prod
FROM cart;

 

< UNION ALL>

-- 28명
SELECT 'member', mem_id, mem_name
FROM member
-- 합치면 163
UNION ALL
-- 135행
SELECT 'cart', cart_member, cart_prod
FROM cart;

 중복 포함(현장에서 많이 사용)

 

< EXISTS >

SELECT mem_id, mem_name
FROM member
WHERE EXISTS(SELECT *
                                FROM cart
                                WHERE cart_member = mem_id);

  값이 존재하면 True 존재하지, 않으면 False

 

 

---

문제 6-1

- 회원전체에 대한 총구매금액을 조회하기
- 조회 컬럼 : 회원아이디, 회원이름, 총구매금액
- 마지막 행에는 전체구매금액 표시

SELECT mem_id, mem_name, SUM(nvl(cart_qty*prod_sale, 0)) AS total
FROM member
LEFT OUTER JOIN cart
ON(mem_id = cart_member)
LEFT OUTER JOIN prod
ON(cart_prod = prod_id)
GROUP BY mem_id, mem_name
UNION
SELECT '', '', SUM(nvl(cart_qty*prod_sale, 0)) AS total
FROM member
LEFT outer JOIN cart
ON(mem_id = cart_member)
LEFT OUTER JOIN prod
ON(cart_prod = prod_id)
GROUP BY '', '';

※ 여기서 컬럼 개수를 맞추기 위해 작성한 ''(빈 문자열)은 오라클에서는 문법상 오류가 나타난다.

※ 따라서 Group BY '', '' 해줘야한다.(빈 문자열이기 때문에 아무 의미 없는 것!!)

문제 6-1

문제 6-2

- 위 결과에서
- 총 구매금액 높은순으로 내림차순하기
- 단, 총구매금액의 총합은 마지막 행에

SELECT A.mem_id, A.mem_name, A.total
FROM (SELECT mem_id, mem_name, SUM(nvl(cart_qty*prod_sale, 0)) AS total
             FROM member
                            LEFT OUTER JOIN cart
                                      ON(mem_id = cart_member)
                            LEFT OUTER JOIN prod
                                      ON(cart_prod = prod_id)
              GROUP BY mem_id, mem_name
              ORDER BY total DESC) A
UNION
SELECT 'mem_id', 'mem_name', SUM(A.total)
FROM (SELECT SUM(nvl(cart_qty*prod_sale, 0)) AS total
             FROM member
                         LEFT OUTER  JOIN cart
                                   ON(mem_id = cart_member)
                         LEFT OUTER JOIN prod
                                   ON(cart_prod = prod_id)
             GROUP BY mem_id, mem_name) A ;

※ 정렬은 맨 마지막에서만 사용할 수 있는데 여기서 문제는 union값을 맨 마지막에 나오게 하기 위해 정렬을 넣기 위해서 위에를 서브쿼리로 묶어어 desc 해주었다.

문제 6-2

문제 7

- 구매(주문)내역이 없는 회원만 조회해주세요
- 조회 컬럼 : 회원아이디, 회원이름
- exists 사용

SELECT mem_id, mem_name
FROM member
where NOT EXISTS (SELECT * FROM cart WHERE cart_member = mem_id);

문제7

 

문제 8

- 2005년도 구매내역이 있는 회원에 대한
- 조회 컬럼 : 회원아이디, 회원이름, 마일리지 조회
- 단, 구매내역이 있는 회원의 총구매금액이 3천만원(30000000) 이상인 데이터에 대해서만 조회

-- EXISTS
SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE EXISTS(SELECT sum(prod_sale*cart_qty) 
                             FROM cart,prod
                             WHERE cart_member = mem_id
                                            AND cart_prod = prod_id
                                            AND cart_no LIKE '2005%'
 -- group by 안하고 having 쓸 수 있는 이유 : 한명씩 값을 가져오므로(cart_member = mem_id)
                             HAVING SUM(prod_sale*cart_qty) >= 30000000);

 

-- LEFT OUTER JOIN
SELECT A.mem_id, A.mem_name, A.mem_mileage
FROM (SELECT mem_id, mem_name, mem_mileage, sum(cart_qty*prod_sale) AS total
             FROM member
                         LEFT OUTER JOIN cart
                                   ON(mem_id = cart_member
                                          AND cart_no LIKE '2005%')
                        LEFT OUTER JOIN prod
                                   ON(prod_id = cart_prod)
              GROUP BY mem_id) A
WHERE A.total >= 30000000;

 

문제8

 

---

< UPDATE >

Update( 수정하기 )
- 수정 및 삭제 시
- 기준 컬럼은 ?
- PK값을 기준으로 행을 조건 처리해야 한다.(반드시!!)
- 기존 값에 값을 추가하고 싶다면?
- SET 컬럼명 = 컬럼명 + 추가 값

UPDATE 테이블명 SET 컬럼명 = 바꿀 값
WHERE 조건;

 

문제 9

- 수정하기
- 회원아이디 a001, b001인 회원의
- 마일리지 값을 2로 수정하기

-- 조회하기
SELECT mem_id, mem_mileage
FROM member
WHERE mem_id IN ('a001', 'b001');

-- 수정하기
UPDATE member SET mem_mileage = 2
WHERE mem_id IN ('a001', 'b001');

 

문제 10

- 구매내역이 있는 회원의 마일리지 값을 3으로 일괄 수정처리하기

-- 조회하기
SELECT mem_id, mem_mileage
FROM member
WHERE EXISTS (SELECT cart_member FROM cart WHERE cart_member = mem_id);

-- 수정하기
UPDATE member
SET mem_mileage = 3
WHERE EXISTS (SELECT * FROM cart WHERE cart_member = mem_id);

문제10

문제 11

- 2005년도 구매내역이 있는 회원에 대한
- 회원아이디, 회원이름, 마일리지 조회
- 단, 구매내역이 있는 회원의 총구매금액이 3천만원 이상인 데이터에 대해서만 조회
- 위 문제 조건을 만족하는 회원에 대해서 마일리지 값 1000점 부여하기

-- 조회하기
SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE EXISTS(SELECT SUM(prod_sale*cart_qty)
                             FROM prod, cart
                             WHERE cart_member = mem_id
                                            AND prod_id = cart_prod
                                            AND cart_no LIKE '2005%'
                             HAVING SUM(prod_sale*cart_qty) >= 30000000);  

-- 수정하기 (깂 추가하기)
UPDATE member
SET mem_mileage = mem_mileage + 1000
WHERE EXISTS(SELECT SUM(prod_sale*cart_qty)
 FROM prod, cart
 WHERE cart_member = mem_id
                AND prod_id = cart_prod
               AND cart_no LIKE '2005%'
               HAVING SUM(prod_sale*cart_qty) >= 30000000);

 

 

문제11