--1) 상품테이블에서 상품코드, 상품명, 분류명을 조회.
-- 가. 기본형식
-- 나. ANSI형식
SELECT A.PROD_ID 상품코드,
A.PROD_NAME 상품명,
B.LPROD_NM 분류명
FROM PROD A
INNER JOIN LPROD B ON (A.PROD_LGU=B.LPROD_GU);
--2) 상품테이블에서 상품코드, 상품명, 분류명, 거래처 명을 조회
--가. 테이블명 사용
-- 나. Alias 사용
-- 다. ANSI형식
SELECT PROD_ID 상품코드,
PROD_NAME 상품명,
B.LPROD_NM 분류명,
C.BUYER_NAME 거래처명
FROM PROD A
INNER JOIN LPROD B ON (A.PROD_LGU=B.LPROD_GU)
INNER JOIN BUYER C ON (A.PROD_LGU=C.BUYER_LGU);
--3) 상품테이블에서 거래처가 '삼성전자' 인 자료의 상품코드, 상품명, 거래처 명을 조회
SELECT A.PROD_ID 상품코드,
A.PROD_NAME 상품명,
B.BUYER_NAME 거래처명
FROM PROD A, BUYER B
WHERE B.BUYER_NAME = '삼성전자';
--4) 상품테이블에서 거래처가 '삼성전자' 인 자료의 상품코드, 상품명, 거래처 명을 조회
SELECT A.PROD_ID 상품코드,
A.PROD_NAME 상품명,
B.BUYER_NAME 거래처명
FROM PROD A, BUYER B
WHERE B.BUYER_NAME = '삼성전자';
--5) 상품테이블에서 상품코드, 상품명, 분류명, 거래처명, 거래처주소를 조회.
-- - 판매가격이 10만원 이하 이고
-- - 거래처 주소가 부산인 경우만 조회
SELECT A.PROD_ID 상품코드,
A.PROD_NAME 상품명,
C.LPROD_NM 분류명,
B.BUYER_NAME 거래처명,
B.BUYER_ADD1 || ' ' || B.BUYER_ADD2 주소
FROM PROD A
INNER JOIN BUYER B ON (A.PROD_LGU=B.BUYER_LGU AND SUBSTR(B.BUYER_ADD1,1,2) = '부산')
INNER JOIN LPROD C ON (A.PROD_LGU=C.LPROD_GU AND A.PROD_PRICE <=100000);
--6) 상품 분류가 전자제품(P102 )인 상품의 상품코드, 상품명, 분류명, 거래처 명을 조회.
SELECT PROD_ID 상품코드,
PROD_NAME 상품명,
B.LPROD_NM 상품명,
C.BUYER_NAME 거래처명
FROM PROD A
INNER JOIN LPROD B ON (A.PROD_LGU = B.LPROD_GU AND UPPER(A.PROD_LGU)='P102')
INNER JOIN BUYER C ON (A.PROD_LGU = C.BUYER_LGU);
--7) 상품입고테이블(BUYPROD)의 2005년도 1월의 거래처별(거래처코드, 거래처명) 매입금액을 검색( 매입금액 = 매입수량 * 매입단가 )
-- ( Alias는 거래처코드, 거래처명, 매입금액 )
-- 가. EQUAL JOIN 사용
SELECT A.BUYER_ID 거래처코드,
A.BUYER_NAME 거래처명,
SUM(C.BUY_QTY*C.BUY_COST) 매입금액
FROM BUYER A, PROD B, BUYPROD C
WHERE A.BUYER_LGU = B.PROD_LGU
AND B.PROD_ID= C.BUY_PROD
AND C.BUY_DATE BETWEEN '20050101' AND '20050131'
GROUP BY A.BUYER_ID, A.BUYER_NAME;
-- 나) INNER JOIN 사용
SELECT A.BUYER_ID 거래처코드,
A.BUYER_NAME 거래처명,
SUM(C.BUY_QTY*C.BUY_COST) 매입금액
FROM BUYER A
INNER JOIN PROD B ON (A.BUYER_LGU = B.PROD_LGU)
INNER JOIN BUYPROD C ON (B.PROD_ID= C.BUY_PROD AND C.BUY_DATE BETWEEN '20050101' AND '20050131')
GROUP BY A.BUYER_ID, A.BUYER_NAME
;
--8) 장바구니테이블의 2005년도 5월의 회원별 구매금액을 검색( 구매금액 = 구매수량 * 판매가 ) ( Alias는 회원ID, 회원명, 구매금액 )
-- (Equi Join 방식과 Inner Join 방식 중 선택)
SELECT A.MEM_ID 회원ID,
A.MEM_NAME 회원명,
B.CART_QTY*C.PROD_PRICE 구매금액
FROM MEMBER A
INNER JOIN CART B ON (A.MEM_ID = B.CART_MEMBER AND SUBSTR(CART_NO,1,6) LIKE '200505%')
INNER JOIN PROD C ON (B.CART_PROD = C.PROD_ID);
--9) OUTER JOIN
--10) 전체 분류의 상품자료 수 를 검색 조회( Alias는 분류코드, 분류명, 상품자료수 )
-- 가. 분류테이블 조회
SELECT *
FROM LPROD;
-- 나. EQUAL JOIN
SELECT LPROD_GU 분류코드,
LPROD_NM 분류명,
COUNT(B.PROD_ID) 상품자료수
FROM LPROD A, PROD B
WHERE A.LPROD_GU = B.PROD_LGU
GROUP BY LPROD_GU , LPROD_NM;
-- 다. OUTER JOIN
SELECT LPROD_GU 분류코드,
LPROD_NM 분류명,
COUNT(B.PROD_ID) 상품자료수
FROM LPROD A
RIGHT OUTER JOIN PROD B ON(A.LPROD_GU = B.PROD_LGU)
GROUP BY LPROD_GU , LPROD_NM;
-- 라. ANSI OUTER JOIN
SELECT LPROD_GU 분류코드,
LPROD_NM 분류명,
COUNT(B.PROD_ID) 상품자료수
FROM LPROD A
INNER JOIN PROD B ON (A.LPROD_GU = B.PROD_LGU)
GROUP BY LPROD_GU , LPROD_NM;
--11) 전체상품의 2005년 1월 입고수량을 검색 조회( Alias는 상품코드, 상품명, 입고수량 )
SELECT A.PROD_ID 상품코드,
A.PROD_NAME 상품명,
SUM(B.BUY_QTY) 입고수량
FROM PROD A, BUYPROD B
WHERE A.PROD_ID = B.BUY_PROD
GROUP BY A.PROD_ID, A.PROD_NAME;
--12) 전체 상품의 2005년도 5월 5일의 입고.출고현황 조회(상품코드, 상품명, 입고수량의 합, 판매수량의 합 )
--- 입고 확인(Equi Join)
SELECT A.PROD_ID 상품코드,
A.PROD_NAME 상품명,
SUM(B.BUY_QTY) 입고수량의합,
SUM(C.CART_QTY) 판매수량의합
FROM PROD A,BUYPROD B,CART C
WHERE A.PROD_ID = B.BUY_PROD
AND B.BUY_DATE = '20050505'
AND B.BUY_PROD=C.CART_PROD
GROUP BY A.PROD_ID ,A.PROD_NAME;
--- 판매 확인(Inner Join)
SELECT A.PROD_ID 상품코드,
A.PROD_NAME 상품명,
SUM(B.BUY_QTY) 입고수량의합,
SUM(C.CART_QTY) 판매수량의합
FROM PROD A
INNER JOIN BUYPROD B ON (A.PROD_ID = B.BUY_PROD AND B.BUY_DATE = '20050505')
INNER JOIN CART C ON (B.BUY_PROD=C.CART_PROD)
GROUP BY A.PROD_ID ,A.PROD_NAME;
--13) 거래처의 2005년도 1월의 거래처별 일자별 매입현황 조회 ( 거래처명, 매입일자, 매입금액 )
SELECT A.BUYER_NAME 거래처명,
B.BUY_DATE 매입일자,
SUM(B.BUY_COST*B.BUY_QTY) 매입금액
FROM BUYER A, BUYPROD B, PROD C
WHERE A.BUYER_ID=C.PROD_BUYER
AND B.BUY_PROD=C.PROD_ID
AND B.BUY_DATE BETWEEN '20050101' AND '20050131'
GROUP BY A.BUYER_NAME, B.BUY_DATE;
--14) 2005년도 월별 판매 현황을 검색
-- (Alias는 판매월, 판매수량, 판매금액(판매수량*상품테이블의 판매가))
SELECT SUBSTR(A.CART_NO,5,2) 판매월,
SUM(A.CART_QTY) 판매수량,
SUM(A.CART_QTY*B.PROD_PRICE) 판매금액
FROM CART A, PROD B
WHERE SUBSTR(A.CART_NO,1,4)='2005'
AND A.CART_PROD=B.PROD_ID
GROUP BY SUBSTR(A.CART_NO,5,2);
--15) 상품분류가 컴퓨터제품('P101')인 상품의 2005년도 일자별 판매 조회
-- ( 판매일, 판매금액(5,000,000초과의 경우만), 판매수량 )
SELECT TO_DATE(SUBSTR(A.CART_NO,1,8)) 판매일,
SUM(A.CART_QTY*B.PROD_PRICE) 판매금액,
SUM(A.CART_QTY) 판매수량
FROM CART A, PROD B
WHERE UPPER(B.PROD_LGU)='P101'
AND SUBSTR(A.CART_NO,1,4)='2005'
GROUP BY TO_DATE(SUBSTR(A.CART_NO,1,8))
HAVING SUM(A.CART_QTY*B.PROD_PRICE)>5000000;
--============================================
--1) 상품Table에서 판매가가 상품평균판매가 보다 큰 상품을 검색
--(Alias는 상품명,판매가,평균판매가)
SELECT PROD_NAME 상품명,
PROD_PRICE 판매가,
B.PRODAVG 평균판매가
FROM PROD A, (SELECT ROUND(AVG(PROD_PRICE)) PRODAVG,
PROD_LGU LGU
FROM PROD GROUP BY PROD_LGU) B
WHERE PROD_PRICE>B.PRODAVG AND B.LGU = A.PROD_LGU;
--2) 장바구니Table에서 회원별 최고의 구매수량을 가진 자료의 회원, 주문번호, 상품, 수량에 대해 모두 검색
--(Alias는 회원, 주문번호, 상품, 수량)
SELECT D.MEM_NAME 회원,
A.CART_NO 주문번호,
C.PROD_NAME 상품,
B.M 수량
FROM CART A , (SELECT MAX(CART_QTY) M, CART_MEMBER CM FROM CART GROUP BY CART_MEMBER) B, PROD C, MEMBER D
WHERE A.CART_MEMBER = B.CM
AND A.CART_PROD = C.PROD_ID
AND A.CART_MEMBER=D.MEM_ID; --잘모르겟
--3) 모든 거래처의 2005년도 거래처별 매입금액 합계를 조회
-- - From절의 A는 거래처테이블의 자료 중 거래처코드와 거래처명
-- - B는 거래처별 매입 총액
-- - 거래처코드로 JOIN 해당거래처의 매입 총액 READ
-- - BUYER(거래처)테이블과 BUYPROD(입고상품)테이블은 참조관계가 아니므로 OUTER JOIN 안됨
SELECT A.BI 거래처,
A.BN 거래처명,
B.BCO 매입금액합계
FROM (SELECT BUYER_ID BI, BUYER_NAME BN FROM BUYER) A,
(SELECT SUM(A.BUY_QTY*A.BUY_COST) BCO, B.BUYER_ID BBI
FROM BUYPROD A, BUYER B, PROD C
WHERE B.BUYER_ID = C.PROD_BUYER AND A.BUY_PROD = C.PROD_ID AND EXTRACT(YEAR FROM A.BUY_DATE)='2005'
GROUP BY BUYER_ID) B
WHERE A.BI = B.BBI;
--4) 모든 거래처의 2005년도 거래처별 매출금액합계를 검색하시오 ?
-- (Alias는 거래처코드, 거래처명, 매출금액합계 거래처명 순)
-- (cart 테이블 이용, 매출금액은 prod_sale * cart_qty)
SELECT 거래처코드,
거래처명,
매출금액합계,
거래처명
FROM BUYER
--5) 상품Table에서 판매가가 상품평균판매가 보다 큰 상품을 검색
--(Alias는 상품명,판매가)
--6) 회원Table에서 회원주소 지역이 거래처주소 지역 중 하나이면 선택 검색
--(Alias는 회원명,지역)
--7) 직업이 '공무원'인 사람들의 마일리지를 검색하여 최소한 그들 중 어느 한사람보다는 마일리지가 큰 사람들을 출력. 단, 직업이 '공무원'인 사람은 제외하고 검색 (Alias는 회원명, 직업, 마일리지)
--8) 직업이 '공무원'인 사람들의 마일리지를 검색하여 최소한 그들 보다는 마일리지가 큰 사람들을 출력. 단, 직업이 '공무원'인 사람은 제외하고 검색
-- (Alias는 회원명, 직업, 마일리지)