사용예)2005년 4-6월 남여회원별 평균 구매금액을 조회하시오.
--구분 : 성별 / 평균 구매금액, 필요테이블 : 3개
SELECT CASE WHEN SUBSTR(B.MEM_REGNO2,1,1) IN(1,3) THEN '남성회원' ELSE '여성회원' END AS 구분,
ROUND(AVG(A.CART_QTY*C.PROD_PRICE)) AS 평균구매금액
FROM CART A, MEMBER B, PROD C
WHERE SUBSTR(A.CART_NO,1,6) BETWEEN '200504' AND '200506'
AND A.CART_MEMBER=B.MEM_ID
AND A.CART_PROD=PROD_ID
GROUP BY CASE WHEN SUBSTR(B.MEM_REGNO2,1,1) IN(1,3) THEN '남성회원' ELSE '여성회원' END;
사용예)2005년 4-6 월별, 평균 구매금액을 조회하시오.
--구분 : 성별 / 평균 구매금액, 필요테이블 : 3개
SELECT SUBSTR(A.CART_NO,5,2) AS 월,
CASE WHEN SUBSTR(B.MEM_REGNO2,1,1) IN(1,3) THEN '남성회원' ELSE '여성회원' END AS 구분,
ROUND(AVG(A.CART_QTY*C.PROD_PRICE)) AS 평균구매금액
FROM CART A, MEMBER B, PROD C
WHERE SUBSTR(A.CART_NO,1,6) BETWEEN '200504' AND '200506'
AND A.CART_MEMBER=B.MEM_ID
AND A.CART_PROD=PROD_ID
GROUP BY SUBSTR(A.CART_NO,5,2),
CASE WHEN SUBSTR(B.MEM_REGNO2,1,1) IN(1,3) THEN '남성회원' ELSE '여성회원' END
ORDER BY 1;
숙제01)미국에 위치한 각 부서별 평균임금을 계산하고 자신이 속한 부서의 평균급여보다 급여를 더 받는 사원을 조회하시오.
Alias는 사원번호,사원명,부서명,급여,부서평균급여
제출기한 : 10월 15일
제출방법 : SEM-PC
파일명 : 자신의 이름_제출일자.txt(메모장 등을 이용한 문서)
3)COUNT(*|컬럼명) --숫자로 반환됨
- 자료의 수(행의 수)를 반환
- '*|컬럼명' : 외부조인을 제외하고 대부분 '*'를 사용
외부 조인인 경우 반드시 '컬럼명'을 사용해야 함
사용예)각 부서별 사원수를 조회하시오
SELECT DEPARTMENT_ID AS 부서코드,
COUNT(*) AS 사원수1,
COUNT(PHONE_NUMBER) AS 사원수2
FROM EMP
GROUP BY DEPARTMENT_ID
ORDER BY 1;
---사원 수 1,2 모두 같은 값 도출사용예)모든 부서별 사원수를 조회하시오 -- 모든이라는 말이 들어가면 : 외부조인(OUTER JOIN)
=> 외부조인에서 COUNT(*)를 사용하면 안됨!!
SELECT B.DEPARTMENT_ID AS 부서코드,
B.DEPARTMENT_NAME AS 부서명,
COUNT(A.JOB_ID) AS 사원수
-- COUNT 안에 A테이블의 컬럼을 쓰면 됨 '*'사용불가 ##이것만 주의하기
FROM EMP A ,DEPT B
WHERE A.DEPARTMENT_ID(+)=B.DEPARTMENT_ID
--EMP의 부서코드 : 12개(NULL 포함) DEPT의 부서코드 : 27개(작은 쪽에 + : 아우터조인)
GROUP BY B.DEPARTMENT_ID,B.DEPARTMENT_NAME
ORDER BY 1;
SELECT DISTINCT DEPARTMENT_ID
FROM DEPT;
사용예)2005년 1-6월 제품별 매입건수를 조회하시오
SELECT BUY_PROD AS 제품코드,
COUNT(*) AS 매입건수
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050630')
GROUP BY BUY_PROD
ORDER BY 1;
사용예)사원테이블에서 년도별 입사인원을 조회하시오
SELECT EXTRACT(YEAR FROM HIRE_DATE) AS 연도,
COUNT(*) AS 입사인원 -- 조인이 없을 때는 * 사용가능
FROM EMP
GROUP BY EXTRACT(YEAR FROM HIRE_DATE)
ORDER BY 1;
GROUP BY 절 다음에는 집계함수를 쓰지 않은 일반컬럼 사용
집계함수를 쓰면 SELECT절에서 쓴 일반컬럼(집계함수를 쓰지 않은)들은 100% GROUP BY절에 와야한다.
COUNT(*) : * 를 쓰면 열(컬럼명)의 도메인(자료 수)이 몇 개 인지 세준다.
서브쿼리가 들어가는 SQL의 특징
1. 지문(요구사항)이 짧다.
2. 쿼리가 길다.
SELECT, FROM, WHERE절에서 서브쿼리가 쓰일 수 있다.
VIEW 창에서 왼쪽에 나타나는 숫자열은 : ROWNUM
사용예)2005년도 제품별 매출건수,매출수량합계 및 매출금액합계를 조회하되 매출건수가 5회 이상인 제품만 조회하시오.
Alias는 제품코드,제품명,매출건수,매출수량합계,매출금액합계
SELECT A.CART_PROD AS 제품코드,
B.PROD_NAME AS 제품명,
COUNT(*) AS 매출건수,
SUM(A.CART_QTY) AS 매출수량합계,
TO_CHAR(SUM(A.CART_QTY*B.PROD_PRICE),'9,999,999') AS 매출금액합계
FROM CART A,PROD B
WHERE A.CART_NO LIKE '2005%'
AND A.CART_PROD=B.PROD_ID
GROUP BY A.CART_PROD,B.PROD_NAME
HAVING COUNT(*)>=5
ORDER BY 1;
4)MAX(col), MIN(col)
- 컬럼 col의 값 중 최대값(MAX), 최소값(MIN)을 반환
사용예)각 부서별 최대급여와 최소급여를 조회하시오 --최대값을 가지는 사원을 구하는 게 아님, 최대값을 구하는 것
SELECT DEPARTMENT_ID AS 부서코드,
MAX(SALARY) AS 최대급여,
MIN(SALARY) AS 최소급여
FROM HR.EMP
GROUP BY DEPARTMENT_ID
ORDER BY 1;
사용예)각 부서별 최대급여와 최소급여를 받는 사람을 조회하시오
(최대급여)
SELECT DEPARTMENT_ID AS DID,
MAX(SALARY) AS MSAL
FROM EMP
GROUP BY DEPARTMENT_ID
ORDER BY 2 DESC;
(최소급여)
SELECT DEPARTMENT_ID AS DID,
MIN(SALARY) AS NSAL
FROM EMP
GROUP BY DEPARTMENT_ID
ORDER BY 2;
(최대급여 사원정보)
SELECT B.EMPLOYEE_ID AS 사원번호,
B.EMP_NAME AS 사원명,
A.DID AS 부서코드,
A.MSAL AS 최대급여
FROM (SELECT DEPARTMENT_ID AS DID, --인라인 서브쿼리
MAX(SALARY) AS MSAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID)A,
HR.EMP B
WHERE A.DID=B.DEPARTMENT_ID
AND A.MSAL=B.SALARY
ORDER BY 3;
(최소급여 사원정보)
SELECT B.EMPLOYEE_ID AS 사원번호,
B.EMP_NAME AS 사원명,
A.DID AS 부서코드,
A.NSAL AS 최대급여
FROM(SELECT DEPARTMENT_ID AS DID,
MIN(SALARY) AS NSAL
FROM EMP
GROUP BY DEPARTMENT_ID)A,
HR.EMP B
WHERE A.DID=B.DEPARTMENT_ID
AND A.NSAL=B.SALARY
ORDER BY 3;
--집계함수는 중첩될 수 없다.
사용예)각 부서별 급여가 많은 5명의 사원정보 조회
--실행순서 : FROM -> WHERE -> SELECT절 순(원하는 형태대로 하려면 서브쿼리필요)
SELECT A.EMP_NAME,
A.SALARY
FROM (SELECT EMP_NAME,
SALARY
FROM HR.EMP
ORDER BY 2 DESC)A
WHERE ROWNUM<=5;
5) ROLLUP(col[,col,...])
- 다양한 종류의 집계를 반환
- GROUP BY 절에서만 사용됨
- 기술된 col의 순서(왼쪽->오른쪽)가 레벨을 결정하고 그 레벨별 집계를 반환
- 사용된 col의 갯수가 n개일 때 n+1 가지의 집계반환
ex) GROUP BY ROLLUP(C1,C2,C3) =>
C1,C2,C3를 기준으로 한 집계 =1
C1,C2를 기준으로 한 집계 =2
C1을 기준으로 한 집계 =3
전체집계 =4가지
