사용예) 2005년 4-6월 남여회원별 평균 구매금액을 조회하시오.
--CART매출, MEMBER성별 구분, PROD 구매 금액 결정 조인해야함
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 평균구매금액 -- ROUND소수점 제거: 매출 금액 *
FROM CART A, MEMBER B, PROD C
WHERE SUBSTR(A.CART_NO,1,6) BETWEEN '200502' 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월 월별, 남여회원별 평균 구매금액을 조회하시오. --월에서 분류하고 남녀 회원에서 또 구분한것으로 행은 6개가 나올것(4,5,6월 * 남,여)
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 평균구매금액 -- ROUND소수점 제거
FROM CART A, MEMBER B, PROD C
WHERE SUBSTR(A.CART_NO,1,6) BETWEEN '200502' 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;
숙제) 미국이외에 위치한 각 부서별 평균임금을 계산하고 자신이 속한 부서의 평균급여보다 급여를 더 받는 사원을 조회하시오
Alias는 사원번호, 사원명, 부서명, 급여, 부서평균급여
제출기한 : 10/15
제출 방법: SEM-PC\D:\공유폴더\oracle\homework01로 전송
파일명: 자신의 이름_제출일자.txt(메모장 등을 이용한 문서로 저장)
EX) 홍길동 _20211015.TXT
SELECT 사원번호, 사원명, 부서명, 급여, 부서평균급여
FROM
3) COUNT(*|컬럼명)
-자료의 수(행의 수)를 반환
-'*|컬럼명' : 외부조인을 제외하고 대부분 '*'를 사용
--내부조인: 조인조건이 만족하는 것만 반환 / 외부조인: 많은 쪽을 기준으로 조인
외부 조인인 경우 반드시 '컬럼명'을 사용해야 함
사용예) 각 부서별 사원수를 조회하시오
SELECT DEPARTMENT_ID AS 부서코드,
COUNT(*) AS 사원수1,
COUNT(PHONE_NUMBER)AS 사원수2
FROM EMP
GROUP BY DEPARTMENT_ID
ORDER BY 1;
사용예)모든 부서별 사원수를 조회하시오 --사원EMP,부서, 상품- 상품코드, 분류- 분류테이블
-- 외부조인이고 그룹바에 이고 사원수를 구하는 것이라 COUNT사용
SELECT B.DEPARTMENT_ID AS 부서코드,--A테이블로 하면 널값나옴
B.DEPARTMENT_NAME AS 부서명,
COUNT(JOB_ID) AS 사원수
FROM EMP A, DEPT B
WHERE A.DEPARTMENT_ID(+)= B.DEPARTMENT_ID --(+):작은쪽에 붙혀둔것
GROUP BY B.DEPARTMENT_ID,B.DEPARTMENT_NAME
ORDER BY 1;
사용예) 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 년도, --EXTRACT 날짜 함수 추출 문자
COUNT(*) AS 인원
FROM EMP
GROUP BY EXTRACT (YEAR FROM HIRE_DATE)
ORDER BY 1;
사용예) 2005년도 제품별 매출건수, 매출수량합계 및 매출금액합계를 조회하되 매출건수가 5회 이상인 제품만 조회하시오 --BUY는 매입 / CSRT는 매출
SELECT B.CART_PROD AS 제품코드,
-- 집계함수가 없는 함수들은 거의 GROUP BY에 와야한다
A.PROD_NAME AS 제품명,
--제품 코드가 같으면 제품도 같다. 하지만 여기서 두개를 출력해야해서 형식상 쓴것(1개와 같음)
COUNT(*) AS 매출건수,
--열(세로)의 전부이며 몇개 있는지 세는것 / 외부조인: * 대신 컬럼명 사용
SUM(B.CART_QTY) AS 매출수량합계,
SUM(B.CART_QTY * A.PROD_PRICE) AS 매출금액합계
FROM PROD A, CART B --CART는 매출정보
WHERE B.CART_NO LIKE '2005%'
AND A.PROD_ID = B.CART_PROD
GROUP BY B.CART_PROD, A.PROD_NAME
HAVING COUNT(*) >= 5
ORDER BY 1;
4)MAX(col), MIN(col)
-컬럼 col의 값 중 최대값(MAX), 최소값(MIN)을 반환
사용예) 각 부서별 최대급여와 최소급여를 조회하시오
SELECT DEPARTMENT_ID AS 부서코드,
MAX(SALARY) AS DID,
MIN(SALARY) AS MSAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID
ORDER BY 1;
사용예) 각 부서별 최대급여와 최소급여를 받는 사원을 조회하시오
(최대급여 사원정보)
SELECT A.EMPLOYEE_ID AS 사원번호,
A.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 A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME) AS 사원명,
A.DID AS 부서코드,
A.MSAL AS최소급여
FROM (SELECT DEPARTMENT_ID AS DID,
MIN(SALARY) AS MSAL
FROM HR.EMP
GROUP BY DEPARTMENT_ID )A,
HR.EMP B
WHERE A.DID = DEPARTMENT_ID
AND A.MSAL = B.SALARY
ORDER BY 3;
사용예) 급여가 많은 5명의 사원정보 조회
SELECT A.EMP_NAME,
A.SALARY
FROM (SELECT EMP_NAME,
SALARY
FROM HR.EMP
ORDER BY 2 DESC)A
WHERE ROWNUM <= 5 -- ROWNUM은 자르기 WHERE이 먼저되고 정렬이 나중에됨
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가지 --최종 레벨
신입 개발자 입니다!!!