[SQL] Independent subquery

independent subquery

연습문제: 전체 영화 중 평점이 평균 평점보다 높은 영화 리스트 찾기

SELECT
	COUNT(*)
FROM
	movies
WHERE 1=1
	AND rating > (SELECT AVG(rating) FROM movies); -- 결과값 일정 --> 데이터베이스 최적화 엔진인 query planner가 한번만 수행시킴

CTE: Common Table Expression

• 똑같은 명령을 실행하는 공통 쿼리를 재사용
• CTE는 select문 이전에 정의되어야 함
• CTE, subquery 사용할 때는 ()를 꼭 넣어줘야 함

연습문제: 전체 영화 중 평점이 평균 평점보다 높으면서 수익도 평균 수익보다 높은 영화 리스트 찾기

WITH avg_revenue_cte AS (
	SELECT
    	AVG(revenue) AS avg_revenue
    FROM
    	movies
), avg_rating_cte AS (
	SELECT
    	AVG(rating) AS avg_rating
    FROM
    	moives
)
SELECT
	title,
    director,
    revenue,
    ROUND((SELECT avg_revenue FROM avg_revenue_cte), 0) AS avg_revenue,
    rating,
    ROUND((SELECT avg_rating FROM avg_rating_cte), 0) AS avg_rating,
FROM
	movies
WHERE 1=1
	AND rating > (SELECT avg_revenue FROM avg_revenue_cte)
    AND revenue > (SELECT avg_rating FROM avg_rating_cte)

연습문제: 같은 해에 개봉된 영화의 평균 평점보다 높은 평점을 가진 영화 찾기

WITH movie_avg_per_year AS (
	SELECT
    	AVG(inner_movies.rating)
    FROM movies AS inner_movies
    WHERE 1=1
    	AND inner_movies.release_date = main_movies.release_date
) -- CTE 내부 subquery가 하위 컬럼을 참조하는 건 sqlite만 가능
SELECT
	title,
    director,
    rating,
    release_date,
    ROUND((SELECT * FROM movie_avg_per_year), 2) AS year_average
FROM
	movies AS main_movies
WHERE 1=1
	AND main_movies.release_date > 2020
    AND main_movies.rating > (SELECT * FROM movie_avg_per_year); -- subquery 안에 외부 쿼리를 참조