[day_05] DB / 09. 기본 SQL 6 - Sub Query 서브쿼리, GROUP BY, HAVING

SubQuery : 서브쿼리

:  SQL 내의 SQL

 

실습해보자 !

 

-- 실습 환경 구성 --

1. PRODUCT TABLE 생성

CREATE TABLE PRODUCT (
        ID		VARCHAR2(100)	PRIMARY KEY,
	NAME	VARCHAR2(100)	NOT NULL,
	MAKER	VARCHAR2(100)	NOT NULL,
   	PRICE		NUMBER		DEFAULT 0)

2. product sequence 만들기

CREATE SEQUENCE PRODUCT_SEQ;

3. 정보 INSERT

INSERT INTO PRODUCT(ID, NAME, MAKER, PRICE) VALUES(PRODUCT_SEQ.NEXTVAL, '바나나우유', '빙그레', 1200);
INSERT INTO PRODUCT(ID, NAME, MAKER, PRICE) VALUES(PRODUCT_SEQ.NEXTVAL, '마이쮸', '크라운', 2000);
INSERT INTO PRODUCT(ID, NAME, MAKER, PRICE) VALUES(PRODUCT_SEQ.NEXTVAL, '핫식스', '롯데', 1000);
INSERT INTO PRODUCT(ID, NAME, MAKER, PRICE) VALUES(PRODUCT_SEQ.NEXTVAL, '클라우드', '롯데', 2200);
INSERT INTO PRODUCT(ID, NAME, MAKER, PRICE) VALUES(PRODUCT_SEQ.NEXTVAL, '참이슬', '진로', 1300);

 

-- 실습 예제 --

문제 1. 저장된 상품 정보 중 가장 비싼 상품 가격의 상품명 조회하기

 

방법 1. 하나 하나씩 구해보기

1) 상품 최고가격을 조회

SELECT	MAX(PRICE)
FROM	PRODUCT;

2) 조회한 상품 최고 가격을 이용해 상품명을 검색

SELECT	NAME
FROM	PRODUCT
WHERE	PRICE = 2200;

 

방법 2. 서브쿼리를 이용해서 한번에 구하기

SELECT	NAME
FROM	PRODUCT
WHERE	PRICE = ( SELECT	MAX(PRICE)
		  	     FROM   PRODUCT);

 

문제 2. 서브쿼리를 이용해서 최저가 상품의 NAME, MAKER, PRICE를 조회

SELECT	NAME, MAKER, PRICE
FROM	PRODUCT
WHERE	PRICE = ( SELECT	MIN(PRICE)
			     FROM  PRODUCT);	

 

문제 3. 전체 상품의 평균 가격보다 높은 가격의 상품 중 가장 낮은 가격의 상품의 NAME, MAKER, PRICE를 조회

SELECT	NAME, MAKER, PRICE
FROM 	PRODUCT
WHERE	PRICE = ( SELECT 	MIN(PRICE)
	  		     FROM   	PRODUCT
	  		     WHERE	        PRICE > ( SELECT	AVG(PRICE)
							     FROM   PRODUCT  )
									   	         )

 

---

GROUP BY 

: 테이블에서 특정 컬럼을 기준으로 그룹화하여 검색할 때 사용

: 그룹 함수와 함께 사용 ( 그룹함수 - MAX, MIN, AVG, COUNT )

 

HAVING

: GROUP BY 와 함께 사용하는 조건절 (그룹에 대한 조건을 지정)

 

사용 예 ) 상품 테이블에서 제조사별 상품 수, 평균 가격, 부서 별 사원 수, 평균 월급 등..

---

예제 1) 

-- 제조사 별 상품 수를 조회

SELECT		MAKER, COUNT(*)
FROM		PRODUCT
GROUP BY		MAKER;

 

-- 제조사 별 상품 수를 조회 + COUNT가 1을 넘는 경우 조건 추가

SELECT		MAKER, COUNT(*)
FROM		PRODUCT
GROUP BY	MAKER
HAVING  	COUNT(*) > 1;

 

-- 제조사 별 상품 수를 조회 + 정렬 

: 별칭을 주어 ORDER BY 한다. (ORDER BY절에서는 별칭을 넣어줘도 OK)

SELECT		MAKER, COUNT(*) AS 상품수
FROM		PRODUCT
GROUP BY		MAKER
ORDER BY 		상품수 DESC

 

예제 2)

제조사(MAKER) 별 상품 평균가를 조회하되, 평균가 내림차순으로 정렬하여 MAKER와 평균가를 조회

SELECT		MAKER, AVG(PRICE) AS 평균가
FROM		PRODUCT
GROUP BY		MAKER
ORDER BY 		평균가 ASC

 

예제 3)

상품 평균가 1500을 초과하는 상품들만 조회하되, MAKER, MAKER 별 상품 수, 평균가를 조회한다. ( + 평균가 오름차순 )

SELECT		MAKER, COUNT(*) AS 상품수, AVG(PRICE) AS 평균가
FROM		PRODUCT
GROUP BY		MAKER
HAVING		AVG(PRICE) > 1500
ORDER BY		평균가