-- [문제]
/*
- 회원아이디 b001 회원 마일리지값보다 큰,
- 회원정보 조회하기
- 조회컬럼 : 회원아이디, 이름
*/
SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE mem_id = 'b001' ;
SELECT A.mem_id, A.mem_name
FROM member A, (SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE mem_id = 'b001') B
WHERE A.mem_mileage > B.mem_mileage;
-- 다른방법
SELECT mem_id, mem_name
FROM member
WHERE mem_mileage > (SELECT mem_mileage
FROM member
WHERE mem_id= 'b001');
/* 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;
-- [문제]
-- 회원별 구매금액의 총액을 조회하려고 합니다.
-- 2005년 5월에 구매한 내역입니다.
-- (일반조건)
SELECT mem_name,
sum(cart_qty * prod_sale) AS total
FROM member, cart, prod
WHERE mem_id = cart_member
AND cart_prod = prod_id
AND cart_no LIKE '200505%'
GROUP BY mem_id, mem_name
ORDER BY total DESC;
-- (표준방식)
SELECT mem_name,
sum(cart_qty * prod_sale) AS total
FROM member
INNER JOIN cart
ON(mem_id = cart_member)
AND cart_no LIKE '200505%'
INNER JOIN prod
ON(cart_prod = prod_id)
GROUP BY mem_id, mem_name
ORDER BY total DESC;
-- 위 문제에 이어서
-- 회원 전체에 대해서 위 조건으로 조회해주세요
-- 총 회원은 28명 입니다.
/* Outer Join
- 특정 테이블 전체에 대한 집계를 하고자 할 때 사용
- Outer Join 종류
: Left Outer Join : 테이블 순서 중 왼쪽에 위치한 ㅔㅌ이블 전체
: Right Outer Join : 오른쪽에 위치한 테이블 전체
: Full Outer Join : 왼쪽 + 오른쪽 전체
: Oracle DB에서만 사용가능
- Outer Join 개념
: Outer Join은 표준방식으로만 사용해야하며,
: 일반조건은 무조건 On() 내부에 작성해야함
(Where절 사용하시면 Outer 전체 개념이 적용 X )
: Inner Join을 만족해야 함(Inner Join 개념을 그대로 적용)
: Left or Right 기준으로, 같으면 같은 조건대로 조회
, 다르면 Nall
*/
SELECT mem_name,
sum(nvl(cart_qty * prod_sale,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;
-- [문제]
-- 상품분류 전체에 대한 상품의 갯수 집계하기...
SELECT lprod_nm, COUNT(nvl(prod_name,0)) AS total
FROM lprod
LEFT OUTER JOIN prod
ON(lprod_gu = prod_lgu)
GROUP BY lprod_nm
ORDER BY lprod_gu ASC;
-- [문제]
/*
2005년도 월별 판매 현황 검색하기
조회컬럼 : 판매월, 월별 총판매수량, 월별 총판매금액
*/
SELECT SUBSTR(cart_no,3,4)AS MONTH,
SUM(cart_qty)AS qty_sale,
SUM(cart_qty * prod_sale)AS month_total
FROM cart
LEFT OUTER JOIN prod
ON(cart_prod = prod_id)
GROUP BY SUBSTR(cart_no,3,4) ;
SELECT SUBSTR(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 SUBSTR(cart_no,1,4) = '2005')
GROUP BY SUBSTR(cart_no,5,2);
-- [문제]
-- 위의 문제에 이어서
-- 1월 ~ 12월 까지 모두 표현하고자 한다면,, 어떻게
-- INSERT INTO 테이블명 (컬럼명1, ...gjdb컬럼명N)
-- VALUES (값1, ... 값M)
SELECT SUBSTR(cart_no,3,4)AS MONTH,
-- if( SUBSTR(cart_no,3,4) != '0501', INSERT INTO cart(cart_member, cart_no, cart_prod_cart_qty)
-- value('','2005010100001','','')
SUM(cart_qty)AS qty_sale,
SUM(cart_qty * prod_sale)AS month_total
FROM cart
LEFT OUTER JOIN prod
ON(cart_prod = prod_id)
GROUP BY SUBSTR(cart_no,3,4) ;
SELECT
MONTHS.month,
COALESCE(SUM(cart.cart_qty), 0) AS qty_sale,
COALESCE(SUM(cart.cart_qty * prod.prod_sale), 0) AS month_total
FROM (
SELECT '01' AS month UNION SELECT '02' UNION SELECT '03' UNION
SELECT '04' UNION SELECT '05' UNION SELECT '06' UNION
SELECT '07' UNION SELECT '08' UNION SELECT '09' UNION
SELECT '10' UNION SELECT '11' UNION SELECT '12'
) AS MONTHS
LEFT JOIN cart
ON (MONTHS.month = SUBSTR(cart.cart_no, 5, 2))
LEFT JOIN prod
ON (cart.cart_prod = prod.prod_id
AND MONTHS.month = SUBSTR(cart.cart_no, 5, 2))
GROUP BY MONTHS.month;
-- 테이블 생성
CREATE TABLE date_mm (
mm CHAR(2) NOT NULL);
-- 컬럼 값 넣기
INSERT INTO date_mm (mm) VALUES('01');
INSERT INTO date_mm (mm) VALUES('02');
INSERT INTO date_mm (mm) VALUES('03');
INSERT INTO date_mm (mm) VALUES('04');
INSERT INTO date_mm (mm) VALUES('05');
INSERT INTO date_mm (mm) VALUES('06');
INSERT INTO date_mm (mm) VALUES('07');
INSERT INTO date_mm (mm) VALUES('08');
INSERT INTO date_mm (mm) VALUES('09');
INSERT INTO date_mm (mm) VALUES('10');
INSERT INTO date_mm (mm) VALUES('11');
INSERT INTO date_mm (mm) VALUES('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 = SUBSTR(cart_no, 5, 2))
LEFT OUTER JOIN prod
ON(cart_prod = prod_id
AND SUBSTR(cart_no,1,4) = '2005')
GROUP BY date_mm.mm;
/* 자연스러운 조인 */
-- 컬럼명이 같은 경우..
SELECT *
FROM member A, member B
WHERE A.mem_id = B.mem_id;
/* 값에 의한 연결(조인이라고 칭하지는 않음) */
SELECT *
FROM cart, lprod
WHERE SUBSTR(cart_prod, 1, 4) = lprod_gu;
-- [문제]
-- 2005년도에 대한 전체 거래처별 총매출금액 조회
-- 조회 컬럼 : 거래처코드, 거래처명, 총매출금액
-- 정렬 : 총매출금액 내림차순
SELECT buyer_id, buyer_name, SUM(nvl(prod_sale * cart_qty, 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;
-- Union
-- : 조회결과와 조회결과를 행 단위로 합치는 기능
-- : 조회결과들간의 컬럼의 갯수는 동일해야함
-- : 조회결과들간의 데이터 타입은 동일해야함
SELECT 'member', mem_id, mem_name
FROM member
UNION
SELECT 'cart', cart_member, cart_prod
FROM cart;
-- [문제]
-- 회원전체에 대한 총구매금액을 조회하기
-- 조회컬럼 : 회원아이디, 회원이름, 총구매금액
-- 마지막 행에는 전체구매금액의
SELECT mem_id, mem_name, SUM(nvl(prod_sale * cart_qty,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(prod_sale * cart_qty,0)) AS total
FROM cart
LEFT OUTER JOIN prod
ON(cart_prod = prod_id)
GROUP BY '',''
ORDER BY total DESC;
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 '','';
-- Union을 사용하게 되면...
-- : 정렬은 제일 마지막에 정의해야함
-- : 컬럼명은 첫번째 Select문의 컬럼이름을 따름
-- Union : 중복 제거
-- Union All : 중복포함(실제 전체)
-- 회원 : 28명, 주문정보 : 135건 = 163건
SELECT 'member', mem_id, mem_name
FROM member
UNION ALL
SELECT 'cart', cart_member, cart_prod
FROM cart;
-- exists(조회)
-- : 조회결과가 1건이라도 있으면 True
-- : 0건이면 False
-- : 서브쿼리를 적용(다중컬럼의 다중행 모두 가능)
SELECT mem_id, mem_name
FROM member
WHERE EXISTS(SELECT *
FROM cart
WHERE cart_member = mem_id) ;
-- [문제]
-- 구매 내역이 없는 회원만 조회해주렴
-- exists 사용
SELECT mem_id, mem_name
FROM member
WHERE Not EXISTS(SELECT *
FROM cart
WHERE cart_member = mem_id) ;
-- [문제]
-- 2005년도 구매내역이 있는 회원에 대한
-- 회원아이디, 회원이름, 마일리지 조회
-- 단, 구매내역이 있는 회원의 총구매금액이
-- 3천만원 이상인 데이터에 대해서만 조회
SELECT A.mem_id, A.mem_name, B.total
FROM member A,
(SELECT mem_id, mem_name, SUM(prod_sale * cart_qty) AS total
FROM member, cart, prod
WHERE mem_id = cart_member
AND cart_prod = prod_id
GROUP BY mem_id, mem_name) B
WHERE A.mem_id = B.mem_id
AND B.total >= 30000000;
SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE EXISTS(
SELECT SUM(cart_qty * prod_sale)
FROM cart, prod
WHERE cart_member = mem_id
AND cart_prod = prod_id
AND cart_no LIKE '2005%'
HAVING SUM(cart_qty * prod_sale) >= 30000000
);
-- 수정하기
-- 회원아이디 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');
-- [문제]
-- 2005년도 구매내역이 있는 회원에 대한
-- 회원아이디, 회원이름, 마일리지 조회
-- 단, 구매내역이 있는 회원의 총구매금액이
-- 3천만원 이상인 데이터에 대해서만 조회
-- 위 문제 조건을 만족하는 회원에 대해서
-- 마일리지 값을 1000점 부여하기
SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE EXISTS(
SELECT SUM(cart_qty * prod_sale)
FROM cart, prod
WHERE cart_member = mem_id
AND cart_prod = prod_id
AND cart_no LIKE '2005%'
HAVING SUM(cart_qty * prod_sale) >= 30000000
);
UPDATE member
SET mem_mileage = mem_mileage + 1000
WHERE EXISTS(
SELECT SUM(cart_qty * prod_sale)
FROM cart, prod
WHERE cart_member = mem_id
AND cart_prod = prod_id
AND cart_no LIKE '2005%'
HAVING SUM(cart_qty * prod_sale) >= 30000000
);