사용예)HR계정의 테이블을 이용하여 모든 부서별 인원수 및 평균근무년수,평균급여를 조회하시오
--모든: 외부조인, 부서별: GROUP BY
Alias는 부서코드,부서명,인원수,평균근무년수,평균급여
SELECT B.DEPARTMENT_ID AS 부서코드, --부서코드는 부서테이블에 더 많기 때문
B.DEPARTMENT_NAME AS 부서명,
COUNT(A.EMPLOYEE_ID) AS 인원수,
NVL(ROUND(AVG(EXTRACT(YEAR FROM SYSDATE)
-EXTRACT(YEAR FROM A.HIRE_DATE))),0) AS 평균근무년수,
NVL(ROUND(AVG(A.SALARY)),0)평균급여
FROM EMP A
FULL OUTER JOIN DEPT B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID)
--양쪽이 모두 부족(FULL OUTER JOIN) => NULL값을 가지는 DEPARTMENT_ID가 EMP테이블에 있기때문
GROUP BY B.DEPARTMENT_ID,B.DEPARTMENT_NAME
ORDER BY 1;전자결재시스템 -> 지금은 사원테이블에 상사의 ID를 적어놓게 되어있음(결재를 위해)
사용예)2005년 6월 모든 회원별 구매현황을 조회하시오
--일반조건일 때 ANSI조인 써야 함. OR 서브쿼리
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(A.MEM_ID=B.CART_MEMBER AND B.CART_NO LIKE '200506%')
-- 구매가 2005년6월 발생했기 때문
LEFT OUTER JOIN PROD C ON(B.CART_PROD=C.PROD_ID) -- 회원에 대한 정보가 왼쪽이 많기 때문
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)컬럼--------------------------------- 컬럼명 데이터타입 --------------------------------- 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_GOODS_ASIA( COUNTRY VARCHAR2(10), SEQ_NO NUMBER(4), GOODS VARCHAR2(80)); INSERT INTO EXP_GOODS_ASIA VALUES('한국',1,'원유제외 석유류'); INSERT INTO EXP_GOODS_ASIA VALUES('한국',2,'자동차'); INSERT INTO EXP_GOODS_ASIA VALUES('한국',3,'전자집적회로'); INSERT INTO EXP_GOODS_ASIA VALUES('한국',4,'선박'); INSERT INTO EXP_GOODS_ASIA VALUES('한국',5,'LCD'); INSERT INTO EXP_GOODS_ASIA VALUES('한국',6,'자동차부품'); INSERT INTO EXP_GOODS_ASIA VALUES('한국',7,'휴대전화'); INSERT INTO EXP_GOODS_ASIA VALUES('한국',8,'환식탄화수소'); INSERT INTO EXP_GOODS_ASIA VALUES('한국',9,'무선송신기 디스플레이 부품'); INSERT INTO EXP_GOODS_ASIA VALUES('한국',10,'철 또는 비철합금강'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',1,'자동차'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',2,'자동차부품'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',3,'전자집적회로'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',4,'선박'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',5,'반도체 웨이퍼'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',6,'화물차'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',7,'원유제외 석유류'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',8,'건설기계'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',9,'다이오드, 트렌지스터'); INSERT INTO EXP_GOODS_ASIA VALUES('일본',10,'기계류'); SELECT * FROM EXP_GOODS_ASIA;
1)UNION
- 합집합의 결과를 반환
- 중복 배제
사용예)한국과 일본의 수출품목을 조회하시오
(한국의 수출품 순위)
SELECT GOODS AS 수출품
FROM EXP_GOODS_ASIA
WHERE COUNTRY='한국'
ORDER BY SEQ_NO;
(일본의 수출품 순위)
SELECT GOODS AS 수출품
FROM EXP_GOODS_ASIA
WHERE COUNTRY='일본'
ORDER BY SEQ_NO;
(결합)
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY='한국'
UNION
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY='일본';UNION, UNION ALL, INTERSECT, -
A∪B = 17 -> 중복제외
A∩B = ->중복허용
순수하게 B에 속해있는 집합 : B-A
사용예)2005년 6월과 7월에 판매된 상품별 판매현황을 조회하시오
SELECT A.CART_PROD AS 상품코드,
B.PROD_NAME AS 상품명,
SUM(A.CART_QTY) AS 수량,
SUM(A.CART_QTY*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' --LIKE 연산자 사용불가
GROUP BY A.CART_PROD, B.PROD_NAME
ORDER BY 1;
(집합연산자 사용 : 2005년 6월과 2005년 7월의 조회를 별도의 SQL문으로 작성)
--(2005년 6월 판매현황)
SELECT 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'; --LIKE 연산자 사용불가
-- GROUP BY A.CART_PROD, B.PROD_NAME
UNION
--(2005년 7월 판매현황)
SELECT DISTINCT A.CART_PROD AS 상품코드,
B.PROD_NAME AS 상품명
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID --GROUP BY절이 사용되면 값이 달라져서 쓸 수 없음
AND SUBSTR(A.CART_NO,1,6) = '200507'; --LIKE 연산자 사용불가
2)UNION ALL
- 합집합의 결과를 반환
- 중복 허용
사용예)한국과 일본의 수출품목을 중복을 허용해 조회하시오
(한국의 수출품 순위)
SELECT GOODS AS 수출품
FROM EXP_GOODS_ASIA
WHERE COUNTRY='한국'
ORDER BY SEQ_NO;
(일본의 수출품 순위)
SELECT GOODS AS 수출품
FROM EXP_GOODS_ASIA
WHERE COUNTRY='일본'
ORDER BY SEQ_NO;
(결합)
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY='한국'
UNION ALL
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY='일본'
ORDER BY 1;
사용예)2005년 6월과 7월에 판매된 상품별 판매현황을 조회하시오
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' --LIKE 연산자 사용불가
-- GROUP BY A.CART_PROD, B.PROD_NAME
UNION ALL
--(2005년 7월 판매현황)
SELECT DISTINCT (A.CART_PROD) AS 상품코드,
B.PROD_NAME AS 상품명
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID --GROUP BY절이 사용되면 값이 달라져서 쓸 수 없음
AND SUBSTR(A.CART_NO,1,6) = '200507'
ORDER BY 1;
3)INTERSECT
- 교집합의 결과를 반환
사용예)한국과 일본의 수출상품 중 같은 품목을 조회하시오
(집합연산자 사용하지 않는 경우 : 서브쿼리)
SELECT A.GOODS AS 품목
FROM (SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY='한국')A,
(SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY='일본')B
WHERE A.GOODS=B.GOODS;
(집합연산자 사용)
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY='한국'
INTERSECT
SELECT GOODS
FROM EXP_GOODS_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('20050101') AND TO_DATE('20050131')
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('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_GOODS_ASIA
WHERE COUNTRY='한국'
AND GOODS NOT IN(SELECT GOODS -- 포함되지 않은
FROM EXP_GOODS_ASIA
WHERE COUNTRY='일본');
(집합연산자 사용) --한국에는 있고 일본에는 없는 수출품목을 구할 때
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY='한국' --A집합에만 포함되어있는 원소를 구할 때
MINUS
SELECT GOODS
FROM EXP_GOODS_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%')서브쿼리
- SQL문장안에 보조로 사용되는 또다른 SQL문
- 최종결과를 출력하기 위해 사용되는 계산의 중간값 등을 반환하기 위한 쿼리
- 알려지지 않은 조건에 근거한 값들을 검색하기 위한 SQL문에 사용
- 서브쿼리는 반드시 '( )'안에 기술해야함(단, INSERT INTO 문은 제외)
- 서브쿼리는 SELECT절(일반 서브쿼리), FROM절(in_line 서브쿼리), --in_line서브쿼리는 독립실행이 가능해야함
WHERE절에(중첩 서브쿼리) 사용가능 - 메인쿼리에 사용된 테이블과 서브쿼리에 사용된 테이블 사이의 조인 여부에 따라
연관성 없는 서브쿼리(조인조건이 없는 경우), 연관성있는 서브쿼리(조인으로 연결된)로 구분
메인쿼리를 구하기 위해 사용하는 중간값
알려지지 않은 조건으로 비교판단할 때 사용됨
1)연관성 없는 서브쿼리
. 메인쿼리와 서브쿼리 사이에 조인이 발생되지 않는 서브쿼리
사용예)사원테이블에서 전체 사원의 평균 급여보다 많은 급여를 받는 사원 정보출력
SELECT EMPLOYEE_ID,
EMP_NAME,
SALARY
FROM HR.EMP
WHERE SALARY>=(SELECT AVG(SALARY)
FROM HR.EMP); 