사용예) hr계정의 테이블을 이용하여 모든(OUTER JOIN) 부서별(GRUOP BY)
인원 수(COUNT(해당 키본키 값)) 및 평균근무년수(AVG), 평균급여(AVG)를 조회하시오
--해당 부서 코드에는 27개 사원정보에는 12개 사용됨 하지만 부서테이블에는 NULL값이 없다.
--양쪽 다 부족한 형태라 한쪽이 적다 많다. 할수 없음
Alias는 부서코드, 부서명, 인원수, 평균근무년수, 평균급여이다.
SELECT B.DEPARTMENT_ID AS 부서코드,
B.DEPARTMENT_NAME AS 부서명,
COUNT(A.EMPLOYEE_ID) AS 인원수,
ROUND(AVG(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM A.HIRE_DATE))) AS 평균근무년수,
NVL(ROUND(AVG(A.SALARY)),0) AS 평균급여
FROM HR.EMP A
FULL OUTER JOIN DEPT B ON(A.DEPARTMENT_ID = B.DEPARTMENT_ID)
--A는 60개의 NULL ,B는 1개 NULL
GROUP BY B.DEPARTMENT_ID, B.DEPARTMENT_NAME
ORDER BY 1;
사용예) 2005년 6월 모든(OUTER JOIN) 회원별 구매현황을 조회하시오
--ANSI사용이 안전
Alias는 회원번호, 회원명, 구매수량(합계), 구매금액(합계)이다.
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
NVL(SUM(B.CART_QTY),0) AS 구매수량,
NVL(SUM(B.CART_QTY*C.PROD_PRICE),0) AS 구매금액
FROM MEMBER A -- 회원에 대한 정보는 회원 테이블이 가장 정확하고 많음
LEFT OUTER JOIN CART B ON(B.CART_MEMBER = A.MEM_ID AND B.CART_NO LIKE '200506%')
--A는 B보다 정보가 많으니까 LEFT
--구매 일자는 카트에 관한 내용이므로 CART가 쓰임
LEFT OUTER JOIN PROD C ON(B.CART_PROD=C.PROD_ID)
--A,B는 C보다 정보가 많으니까 LEFT
GROUP BY A.MEM_ID, A.MEM_NAME
ORDER BY 1;
집합연산자
- SELECT 문에 의한 결과를 하나의 집합(SET)이라고 함
- 이 집합을 대상으로한 연산을 담당하는 연산자
- UNION, UNION ALL, INTERSECT, MINUS 가 제공됨
- 집합연산자에 사용되는 모든 SELECT문의 컬럼의 갯수와 타입 및 순서가 일치해야함
- CLOB, BLOB, BFILE타입의 컬럼은 연산에 참여 불가능
- ORDER BY 절은 맨 마지막 SELECT문에만 사용 가능
- 출력의 기준이 되는 컬럼은 첫 번째 SELECT문이다. **예제 테이블 생성
1)테이블명 : EXP_GOODS_ASIA
2)컬럼
1)UNION---------------------------------------- 컬럼명 데이터타입 ---------------------------------------- COUNTRY VARCHAR2(10) SEQ_NO NUMBER(4) GOODS VARCHAR2(80) ---------------------------------------- 3)자료 ---------------------------------------- COUNTRY SEQ_NO GOODS ---------------------------------------- 한국 1 원유제외 석유류 "" 2 자동차 "" 3 전자집적회로 "" 4 선박 "" 5 LCD "" 6 자동차부품 "" 7 휴대전화 "" 8 환식탄화수소 "" 9 무선송신기 디스플레이 부품 "" 10 철 또는 비철합금강 일본 1 자동차 "" 2 자동차 부품 "" 3 전자집적회로 "" 4 선박 "" 5 반도체 웨이퍼 "" 6 화물차 "" 7 원유제외 석유류 "" 8 건설기계 "" 9 다이오드, 트렌지스터 "" 10 기계류 ----------------------------------------- CREATE TABLE EXP_GOOD_ASIA( COUNTRY VARCHAR2(10), SEQ_NO NUMBER(4), GOODS VARCHAR2(80)); INSERT INTO EXP_GOOD_ASIA VALUES('한국',1,'원유제외 석유류'); INSERT INTO EXP_GOOD_ASIA VALUES('한국',2,'자동차'); INSERT INTO EXP_GOOD_ASIA VALUES('한국',3,'전자집적회로'); INSERT INTO EXP_GOOD_ASIA VALUES('한국',4,'선박'); INSERT INTO EXP_GOOD_ASIA VALUES('한국',5,'LCD'); INSERT INTO EXP_GOOD_ASIA VALUES('한국',6,'자동차부품'); INSERT INTO EXP_GOOD_ASIA VALUES('한국',7,'휴대전화'); INSERT INTO EXP_GOOD_ASIA VALUES('한국',8,'환식탄화수소'); INSERT INTO EXP_GOOD_ASIA VALUES('한국',9,'무선송신기 디스플레이 부품'); INSERT INTO EXP_GOOD_ASIA VALUES('한국',10,'철 또는 비철합금강'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',1,'자동차'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',2,'자동차 부품'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',3,'전자집적회로'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',4,'선박'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',5,'반도체 웨이퍼'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',6,'화물차'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',7,'원유제외 석유류'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',8,'건설기계'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',9,'다이오드, 트렌지스터'); INSERT INTO EXP_GOOD_ASIA VALUES('일본',10,'기계류'); SELECT * FROM EXP_GOOD_ASIA;
- 합집합의 결과를 반환
- 중복 배제
-- SUM(A.CART_QTY) AS 수량,사용예) 한국과 일본의 수출품목을 중복을 허용하지 않고 조회하시오 (한국의 수출품 순위) SELECT GOODS AS 수출품 FROM EXP_GOOD_ASIA WHERE COUNTRY = '한국' ORDER BY SEQ_NO; (일본의 수출품 순위) SELECT GOODS AS 수출품 FROM EXP_GOOD_ASIA WHERE COUNTRY = '일본' ORDER BY SEQ_NO; (결합) SELECT GOODS FROM EXP_GOOD_ASIA WHERE COUNTRY = '한국' UNION -- 합집합(중복 제외하고 전체 출력) SELECT GOODS FROM EXP_GOOD_ASIA WHERE COUNTRY = '일본' 사용예) 2005년 6월과 7월에 판매된 상품별 판매현황을 조회하시오 --카트 테이블에서는 상품명이 없으므로 PROD에서 가져오는 것 SELECT A.CART_PROD AS 상품코드, B.PROD_NAME AS 상품명, SUM(A.CART_QTY) AS 수량, SUM(A.CART_QTY*B.PROD_PRICE)AS 금액 FROM CART A, PROD B WHERE A.CART_PROD = B.PROD_ID AND SUBSTR(A.CART_NO,1,6) BETWEEN '200506' AND '200507' GROUP BY A.CART_PROD, B.PROD_NAME ORDER BY 1; (집합연산자 사용: 2005년 4월과 2005년 7월의 조회를 별도의 SQL문으로 작석) (2005년 6월 판매현황)-- 15개 SELECT DISTINCT (A.CART_PROD) AS 상품코드, B.PROD_NAME AS 상품명
-- SUM(A.CART_QTYB.PROD_PRICE)AS 금액
FROM CART A, PROD B
WHERE A.CART_PROD = B.PROD_ID
AND SUBSTR(A.CART_NO,1,6)='200506'
-- GROUP BY A.CART_PROD, B.PROD_NAME
UNION --90개
-- (2005년 7월 판매현황) --33개
SELECT DISTINCT (A.CART_PROD) AS 상품코드,
B.PROD_NAME AS 상품명
-- SUM(A.CART_QTY) AS 수량, --상품코드와 상품명이 같더라도 금액이 다르면 다르게 취급되므로 사용하면 안됨
-- SUM(A.CART_QTYB.PROD_PRICE)AS 금액
FROM CART A, PROD B
WHERE A.CART_PROD = B.PROD_ID
AND SUBSTR(A.CART_NO,1,6)='200507'
-- GROUP BY A.CART_PROD, B.PROD_NAME;
2)UNION ALL
- 합집합의 결과를 반환
- 중복 허용
사용예) 한국과 일본의 수출품목을 중복을 허용하여 조회하시오
(한국의 수출품 순위)
SELECT GOODS AS 수출품
FROM EXP_GOOD_ASIA
WHERE COUNTRY = '한국'
ORDER BY SEQ_NO;
(일본의 수출품 순위)
SELECT GOODS AS 수출품
FROM EXP_GOOD_ASIA
WHERE COUNTRY = '일본'
ORDER BY SEQ_NO;
(결합)
SELECT GOODS
FROM EXP_GOOD_ASIA
WHERE COUNTRY = '한국'
UNION ALL
SELECT GOODS
FROM EXP_GOOD_ASIA
WHERE COUNTRY = '일본'
ORDER BY 1;
사용예) 2005년 6월과 7월에 판매된 상품판매현황을 조회하시오
SELECT A.CART_PROD AS 상품코드,
B.PROD_NAME AS 상품명,
SUM(A.CART_QTY) AS 수량,
SUM(A.CART_QTY*B.PROD_PRICE)AS 금액
FROM CART A, PROD B
WHERE A.CART_PROD = B.PROD_ID
AND SUBSTR(A.CART_NO,1,6) BETWEEN '200506' AND '200507'
GROUP BY A.CART_PROD, B.PROD_NAME
ORDER BY 1;
(집합연산자 사용: 2005년 4월과 2005년 7월의 조회를 별도의 SQL문으로 작석)
(2005년 6월 판매현황)--20개
SELECT DISTINCT(A.CART_PROD) AS 상품코드,
B.PROD_NAME AS 상품명
FROM CART A, PROD B
WHERE A.CART_PROD = B.PROD_ID
AND SUBSTR(A.CART_NO,1,6)='200506'
UNION ALL--35개
-- (2005년 7월 판매현황)--15개
SELECT DISTINCT(A.CART_PROD) AS 상품코드,
B.PROD_NAME AS 상품명
FROM CART A, PROD B
WHERE A.CART_PROD = B.PROD_ID
AND SUBSTR(A.CART_NO,1,6)='200507'
ORDER BY 1;3)INTERSECT
- 교집합의 결과를 반환
사용예) 한국과 일본의 수출상품 중 같은 품목을 조회하시오
(집합연산자를 사용하지 않는 경우: 서브 쿼리)
SELECT A.GOODS AS 품목
FROM (SELECT GOODS
FROM EXP_GOOD_ASIA
WHERE COUNTRY='한국')A,
(SELECT GOODS
FROM EXP_GOOD_ASIA
WHERE COUNTRY='일본')B
WHERE A.GOODS = B.GOODS;--테이블 두개가 사용되어 JOIN
(집합연산자 사용)
SELECT GOODS
FROM EXP_GOOD_ASIA
WHERE COUNTRY='한국'
INTERSECT
SELECT GOODS
FROM EXP_GOOD_ASIA
WHERE COUNTRY='일본'
사용예) 2005년 1월, 3월, 6월 3개월 모두 매입된 상품정보를 조회하시오
Alias는 상품코드, 상품명
SELECT DISTINCT A.BUY_PROD,
B.PROD_NAME
FROM BUYPROD A, PROD B
WHERE A.BUY_PROD = B.PROD_ID
AND BUY_DATE BETWEEN TO_DATE('20050301') AND TO_DATE('20050331')
INTERSECT
SELECT DISTINCT A.BUY_PROD,
B.PROD_NAME
FROM BUYPROD A, PROD B
WHERE A.BUY_PROD = B.PROD_ID
AND BUY_DATE BETWEEN TO_DATE('20050601') AND TO_DATE('20050630')
ORDER BY 1;
4)MINUS
-차집합의 결과 반환
사용예) 한국과 일본 두 나라의 수출 품목 중 한국만 수출하는 품목을 조회하시오
(집합연산자를 사용하지 않는 경우 - 서브 쿼리 사용)
SELECT GOODS
FROM EXP_GOOD_ASIA
WHERE COUNTRY = '한국'
AND GOODS NOT IN(SELECT GOODS --포함되어지지 않는 제품 출력
FROM EXP_GOOD_ASIA
WHERE COUNTRY='일본'); --서브쿼리 사용
(집합연산자를 사용하는 경우)
SELECT GOODS
FROM EXP_GOOD_ASIA
WHERE COUNTRY = '한국'
MINUS --한국에서 일본을 뺀 값 출력
SELECT GOODS
FROM EXP_GOOD_ASIA
WHERE COUNTRY = '일본';
사용예)2005년 5월과 7월 판매된 상품 중 5월에만 판매된 상품을 조회하시오
SELECT DISTINCT A.CART_PROD,
B.PROD_NAME
FROM CART A, PROD B
WHERE A.CART_NO LIKE '200505%'
AND A.CART_PROD = B.PROD_ID
MINUS
SELECT DISTINCT A.CART_PROD,
B.PROD_NAME
FROM CART A, PROD B
WHERE A.CART_NO LIKE '200507%'
AND A.CART_PROD = B.PROD_ID
(집합연산자를 사용하지 않는 경우 - 서브 쿼리 사용)
SELECT DISTINCT A.CART_PROD,
B.PROD_NAME
FROM CART A, PROD B
WHERE A.CART_NO LIKE '200505%'
AND A.CART_PROD=B.PROD_ID
AND NOT EXISTS (SELECT 1
FROM CART C
WHERE A.CART_PROD = C.CART_PROD
AND C.CART_NO LIKE '200507%');
신입 개발자 입니다!!!