[PostgresDB-2] 19. 서브쿼리실습 (SUBQUERY)

참고 : https://cloudstudying.kr/lectures/514

(테이블 생성 및 데이터 입력은 아래 posting 참조)
https://midnightcoding.tistory.com/64

[PostgresDB-2] 10. Relationship (별다방 카페 실습)

DB 구조도

 

DB 데이터

 

SELECT절 서브쿼리 

1. 음료(beverages)의 이름 및 가격과 함께, 가장 비싼 음료와의 가격 차이를 조사하려 한다. 주어진 두 쿼리를 조합하여 이를 구하시오. (SELECT 절 서브쿼리를 사용할 것)

SELECT
	name	AS "음료명",
	price	AS "가격",
	price - (
		-- 가장 비싼 음료의 가격 (단일값 결과 생성)
		SELECT
			MAX(price)
		FROM
			beverages
	) AS "최대 가격과의 차이"
		
FROM
	beverages
;

빨간영역 : Sub query, 노란영역 : main query

 

FROM 절 서브쿼리

2. 주문내역(order_details)로부터 주문 별 음료 개수의 평균을 조회하려 한다. 주어진 쿼리를 조합하여 이를 구하시오. (FROM 절 서브쿼리를 사용할 것)

-- 주문 별 음료 개수
SELECT
	order_id,
	SUM(count)
FROM
	order_details
GROUP BY
	order_id
;
-- 위 쿼리를 FROM절의 SUB_QUERY로 가져가
-- WHY? aggregate function은 중첩(nested)해서 사용 안됨

SELECT
	AVG(sum) AS "주문별 음료 개수 평균"
FROM
 (
	SELECT
		order_id,
		SUM(count)
	FROM
		order_details
	GROUP BY
		order_id
 ) AS "주문 별 음료 개수 집계" --FROM절의 SUB query는 별칭 필수!!
;

 

** 두번째 SELECT 절에 AVG() 안에  sum 은 

-- 주문 별 음료 개수
SELECT
	order_id,
	SUM(count)
FROM
	order_details
GROUP BY
	order_id
;

위에 sub query를 실행했을때, colum의 이름이다.

** 그리고 위에 코맨트 해놨듯이, FROM절의 Sub query는 별칭을 꼭 써줘야한다.

JOIN 절 서브쿼리

3. 주문내역(order_details)로부터 음료 별 주문 개수를 조회하려 한다. 주어진 두 쿼리를 조합하여 이를 구하시오. (JOIN 절 서브쿼리를 활용할 것)

-- 먼저 음료 id별 주문 개수
SELECT
	beverage_id,
	SUM(count)	AS total_count
FROM
	order_details
GROUP BY
	beverage_id
;

SELECT
	beverages.name					AS "음료명",
	COALESCE(total_count, 0)		AS "주문 개수"
FROM
	beverages
LEFT JOIN
 (
	-- 음료 id별 주문 개수
	SELECT
		beverage_id,
		SUM(count)	AS total_count
	FROM
		order_details
	GROUP BY
		beverage_id
 ) AS b_counts ON
 b_counts.beverage_id = beverages.id
;

 

** 두번째 SELECT절에서

	COALESCE(total_count, 0)		AS "주문 개수"

위와 같은 처음보는 함수가 등장하였다.

내용은 total_count 의 값이 null 인경우에, 0으로 출력되도록 해주는 함수이다.

 

WHERE 절 서브쿼리

4. 평균 가격보다 비싼 음료(beverages)를 다음과 같이 조회하려 한다. 주어진 두 쿼리를 조합하여 이를 구하시오. (WHERE 절 서브쿼리를 사용할 것)

-- 먼저 beverages table에서 음료의 평균가격을 구해보자
SELECT
	AVG(price)
FROM
	beverages


SELECT
	beverages.name	AS	"음료명",
	beverages.price AS "가격"
FROM
	beverages
WHERE
 beverages.price > (
	 -- 음료의 평균가격
	SELECT
		AVG(price)
	FROM
		beverages
 )
;

출처 : 홍팍의 'SQL 데이터 분석, 활용!'