데이터분석 > 3주차 > 파트 01 > 0826

다양한 JOINS

JOIN : 두 개 이상의 테이블을 특정 key를 기준으로 결합하는 것

테이블을 쪼개는 이유 : 각 테이블을 관리하기 쉽게 하고 보기 쉽게 하려고

테이블에 저장된 데이터에 변경사항이 생길 수 있기 때문이다.

 

INNER JOIN : 두 개의 테이블에서 일치하는 행만 결합

연결된 열에서 값이 일치하는 행만 포함됨

두 테이블 간 교집합 반환

SELECT products.*, managers.name as manager_name
FROM products INNER JOIN managers on products.category = managers.managing

INNER 앞에는 기준할 테이블임

그 뒤에는 교집합 구할 대상이 되는 테이블, ON 뒤에는 어떤 컬럼을 기준으로 작성할지를 나타냄

products 테이블에 managers 테이블을 결합한다, 근데 products 테이블에서 category 컬럼의 값과, mangers 테이블의 maniging 컬럼의 값이 같은 것을 products 테이블에 결합한다

SELECT clicks.*, 1 as ordered
FROM clicks INNER JOIN orders
ON clicks.user_name = orders.user_name
AND clicks.product_id = orders.product_id
AND clicks.date = orders.date

SELECT clicks.*, 1 as ordered, name
FROM clicks INNER JOIN orders
ON clicks.user_name = orders.user_name
AND clicks.product_id = orders.product_id
AND clicks.date = orders.date
INNER JOIN products on clicks.product_id = products.product_id

 

LEFT JOIN

왼쪽 테이블의 모든 행을 가져오고 오른쪽 테이블에서 일치하는 행을 가져와 결합함

오른쪽 테이블에 일치하지 않는 행은 null 값으로 처리됨

FROM clicks LEFT JOIN orders

왼쪽 테이블의 모든 행을 남기고, 오른쪽 행에서는 일치하는 행만 남김

왼쪽 테이블의 모든 행을 포함하므로 누락하는 데이터 없음

 

RIGHT JOIN

SELECT clicks.*, odr_index, name
FROM clicks LEFT JOIN orders
ON clicks.user_name = orders.user_name
AND clicks.product_id = orders.product_id
AND clicks.date = orders.date
INNER JOIN products on clicks.product_id = products.product_id

JOIN의 결과물은 테이블 -> 필터링 가능

SELECT clicks.*, odr_index, name
FROM clicks LEFT JOIN orders
ON clicks.user_name = orders.user_name
AND clicks.product_id = orders.product_id
AND clicks.date = orders.date
INNER JOIN products on clicks.product_id = products.product_id
WHERE products.category='디지털'
AND odr_index is NULL

 

RIGHT JOIN : 오른쪽 테이블의 모든 행을 가져오고, 왼쪽 테이블에서 일치하는 행을 가져와 결합함

가져온 값 중 키값이 일치하지 않으면 null 값임

오른쪽 테이블의 모든 행을 포함함

그러니까 RIGHT JOIN, LEFT JOIN 양 옆에 테이블 명을 써야 하는데, RIGHT는 오른쪽 테이블의 행을 모두 가져오고, LEFT JOIN은 왼쪽 테이블의 행을 모두 가져옴

>> 행 수가 늘어나는 경우가 있음

 

FULL OUTER JOIN : 두 테이블 간의 모든 행을 가져오며 일치하고 일치하지 않는 행 모두를 반환함

두 테이블의 데이터 모두 포함

일치하지 않는 값은 NULL!

MYSQL은 지원하지 않아서 LEFT JOIN, RIGHT JOIN을 UNION으로 결합해야함

inner, left, right join 결과값을 위아래로 붙인 것으로 알 수 있음 > 중복 행은 제거

 

CROSS JOIN = Cartesian product : 두 테이블 간 가능한 모든 조합 반환시 사용

두 테이블 간의 모든 조합을 생성한 뒤 집계를 해야하는 경우에 사용

상품 클릭시간과 구매시간 차이의 평균을 구하고 싶을 때 같은 것으로 한정하는 것이 아니라 모든 상품에 대해 하면 이 방법을 쓰면 됨

>> 모든 경우의 수를 다 구함

FULL OUTER JOIN보다 연산량이 많음

SELECT products.*, 
products_B.product_id as B_product_id, 
products_B.category as B_category,
products_B.name as B_name,
products_B.price as B_price
FROM products  CROSS JOIN products_B

products가 4개 행, products_B가 3개 행이니 총 12개 행이 나옴

상품에 대한 거리를 구해서 가까운 순으로 정리를 하는 경우 등에 쓰임

 

Alias = 별칭

테이블에 별칭을 붙여서 쿼리를 더 가독성 있게 만들 수 있음

FROM clicks c LEFT JOIN orders o

clicks 테이블은 c, orders 테이블은 o로 호칭을 붙임

Alias를 쓰면 테이블 이름을 다 쓰지 않아도 되고 같은 테이블끼리 결합할 때 구분 가능

 

SELF JOIN : 하나의 테이블 자기자신과 결합시키는 join

기존의 join을 활용해 자신과 결합하는 join임!

동일한 테이블 내에 있는 데이터 비교, 연결 시 사용

Alias를 필수로 사용해야함 > 테이블과 컬럼 명이 다 겹치기 때문임

 

필터링

join 결과물은 테이블임! > 기존 테이블에 대해 사용했던 구문들 그대로 사용 가능

결과물을 만들기 전에 필터링 하는 것이 좋음

 

정리(1)

• INNER JOIN : 왼쪽집합과 오른쪽 집합의 교집합

# INNER JOIN
SELECT *
FROM l INNER JOIN r
on l.key = r.key

# LEFT JOIN
SELECT *
FROM l LEFT JOIN r
on l.key = r.key

# LEFT JOIN
SELECT *
FROM l LEFT JOIN r
on l.key = r.key
WHERE r.key is NULL

• LEFT JOIN : 왼쪽 집합에 해당하는 것은 모두 포함하고 오른쪽 집합에서 왼쪽 집합과 키를 기준으로 같은 값 포함
• LEFT JOIN을 하는데 오른쪽 집합과 겹치는 부분을 제거하는 것 => 차집합

# RIGHT JOIN
SELECT *
FROM l RIGHT JOIN r
on l.key = r.key

# RIGHT JOIN
SELECT *
FROM l RIGHT JOIN r
on l.key = r.key
WHERE l.key is NULL

# FULL OUTER JOIN
SELECT *
FROM l LEFT JOIN r
on l.key = r.key
UNION
SELECT *
FROM l RIGHT JOIN r
on l.key = r.key

• RIGHT JOIN : 오른쪽 집합에 해당하는 것만 포함
• 오른쪽 집합에 해당하는 것은 모두 포함하되 왼쪽 집합과 겹치는 것 제외
• FULL OUTER JOIN : 합집합

# FULL OUTER JOIN
SELECT *
FROM l LEFT JOIN r
on l.key = r.key
UNION
SELECT *
FROM l RIGHT JOIN r
on l.key = r.key
WHERE l.key is NULL
OR r.key is NULL

• 합집합 - 교집합 : FULL OUTER JOIN 수행한 뒤 키가 둘 중 하나라도 NULL인 것만 포함

UNION

두 개 이상의 select 문을 결합하여 하나의 집합으로 생성

select한 결과물들을 위아래로 합침

중복된 행 제거, 각 select 문의 결과 집합에 해당하는 열의 수와 데이터 타입이 일치해야 함

SELECT *
FROM products
UNION
SELECT *
FROM products_B

UNION 대상의 컬럼 수가 모두 일치해야 함

 

중복을 제거해주는 기준은 select에 포함된 컬럼들 전체가 대상임

컬럼 전체가 같아야 중복 제거가 됨

UNION ALL : 중복 제거 안함

SELECT o.*, clk_index
FROM clicks c LEFT JOIN orders o
on c.user_name = o.user_name
and c.product_id = o.product_id
and c.date = o.date
WHERe c.user_name = '영희'
UNION
SELECT o.*, clk_index
FROM clicks c RIGHT JOIN orders o
on c.user_name = o.user_name
and c.product_id = o.product_id
and c.date = o.date
WHERe c.user_name = '영희'

WHERE 문에서 어떤 테이블의 컬럼인지 명시를 안해주면, 두 테이블 모두 user_name이라는 컬럼을 갖기 때문이 오류가 남

WITH

CTE(Common Table Expression)라고도 부르며 임시 결과 집합을 생성하여 복잡한 쿼리를 쉽게 작성할 수 있도록 돕는 기능을 한다.

복잡한 쿼리에서 하위 쿼리를 사용해 같은 결과를 여러 번 계산해야 하는 경우를 줄여준다.

같은 쿼리 블록을 여러 번 사용할 수 있도록 함

 

COALESCE => null 값 처리를 함

COALESCE(ship_cnt, 0) -> ship_cnt가 null인 경우 0으로 처리

WITH odr_cnt as (
 
  SELECT c.customer_id, count(distinct order_id) as odr_cnt, sum(amount) as total_purchase
  FROM Customers c INNER JOIN Orders o on c.customer_id = o.customer_id
  GROUP BY 1
  ORDER BY 2 DESC
),

ship_cnt as(
  SELECT c.customer_id, count(distinct shipping_id) as ship_cnt
  FROM Customers c INNER JOIN Shippings s on c.customer_id = s.customer
  WHERE status = 'Pending'
  GROUP BY 1
  ORDER BY 2 DESC
)

SELECT oc.customer_id, odr_cnt, total_purchase, COALESCE(ship_cnt, 0) as shipping_cnt
FROM odr_cnt oc LEFT JOIN ship_cnt sc on oc.customer_id = sc.customer_id

 

Subquery : 다른 쿼리 내부에 포함된 쿼리로, 더 큰 쿼리의 일부로 사용됨

SELECT user_name, AVG(price) as avg_price
FROM orders o INNER JOIN products p on o.product_id = p.product_id
GROUP BY 1
ORDER BY 2 DESC

 

• SELECT 구문에서 서브쿼리 사용

SELECT user_name, AVG(price) as avg_price, 
(SELECT AVG(price) FROM orders o INNER JOIN products p on o.product_id = p.product_id) as total_avg_price
FROM orders o INNER JOIN products p on o.product_id = p.product_id
GROUP BY 1
ORDER BY 2 DESC

• FROM 구문에서 서브쿼리 사용 > 필터링을 미리 해놓는 기능

SELECT c.*
FROM (SELECT name FROM managers WHERE managing in ('스포츠', '주방용품')) a
INNER JOIN clicks c on a.name = c.user_name

 

ALL, ANY, SOME, EXISTS 연산자는 서브쿼리와 함께 사용함

EXISTS => 서브쿼리 실행 결과가 있으면 TRUE, 없으면 FALSE 반환

FALSE는 서브쿼리 결과가 없다는 것이므로 전체 쿼리 결과도 출력되지 않음

• WHERE 구문에서 서브쿼리 사용

타임스탬프 함수

시간을 다루는 데이터 타입

• STRING : 'yyyy-mm-dd', 'yyyy-mm-dd HH:MM:SS'
• DATE : yyyy-mm-dd
• DATETIME : YYYY-MM-DD HH:MM:SS - 시간 정보를 초 단위까지 저장할 때 사용함
• TIMESTAMP : YYYY-MM-DD HH:MM:SS UTC : 저장 가능한 시간 범위가 DATETIME 보다 제한적임

 

• NOW() : UTC 기준으로 현재 시간을 가져오는 함수로 쿼리 시작 시간 가져옴
  • CURRENT_TIMESTAMP()
  • CURTIME() : 현재 시간 반환
  • CURRENT_DATE() = CURDATE() : yyyy-mm-dd 형식으로 반환
• SYSDATE() : 함수가 호출된 시간을 반환
• YEAR() : 날짜에서 연도 추출/MONTH() : 날짜에서 월 추출/ DAY()
• HOUR() / MINUTE() / SECOND()
• WEEKDAY()
• MONTHNAME() / DAYNAME()

# 현재 시간 함수
SELECT NOW();

SELECT NOW(), SYSDATE(), SLEEP(2),NOW(), SYSDATE();

SELECT WEEKDAY(NOW()) # 0~6 사이를 반환하는데 NOW에 따라 오늘에 해당하는 값 반환

SELECT user_name, DATEDIFF(NOW(), STR_TO_DATE(date, '%Y%m%d')) as dist
FROM orders

• STR_TO_DATE : 문자열 타입 -> 날짜 타입으로 변경
• DATE_FORMAT : 지정된 형식으로 날짜 출력
  • %Y : 연도(2024)
  • %y : 연도(24)
  • %m : 월(11)
  • %d : 일(20)
  • %H : 시(05), 24시간 형태
  • %T : hh:mm:ss
  • %s : 초

SELECT STR_TO_DATE(date, '%Y%m%d') as formatted
FROM orders

# str 형태를 DATE 형태로
# 20240825 -> 2024-08-25
# %Y-%m-%d 처럼 format이 안맞으면 null 값

• ADDDATE() : 특정 간격만큼 시간을 더함
  • DATE_ADD()
• SUBDATE() : 특정 간격만큼 시간을 뺌
  • DATE_SUB()
• CONVERT_TZ() : 타임존 변경하여 출력
• DATEDIFF() : 두 날짜 간 차이를 반환
• TIMEDIFF() : 두 시간 간의 차이를 반환
• TIME_TO_SEC() : 시간을 초 단위로 반환

SELECT ADDDATE('2023-11-01', 3) as added

2023년 11월 1일 기준으로 3일을 더한다는 것

DATE_ADD 함수도 같은 기능임

SELECT ADDDATE('2023-11-01', INTERVAL 3 SECOND) as added

3초 더해짐. SECOND-> HOUR, MINUTE로 바꿔도 됨

SELECT ADDDATE('2023-11-01', INTERVAL -1 SECOND) as added

 

SELECT CONVERT_TZ(NOW(), '+00:00', '+09:00')

입력 : 시간, from time zone, to time zone

from time zone을 어떤 time zone으로 바꿀지 결정하는 것임

KTC = UTC + 9H

 

SELECT DATEDIFF('2023-11-04', '2023-11-01') as diff

출력 값 : 3

SELECT TIMEDIFF('2023-11-04 11:05:05', '2023-11-01 11:00:00') as diff

출력값 : 72:05:05

시간 차이를 시 단위로 변환한 것

SELECT TIME_TO_SEC(TIMEDIFF('2023-11-04 11:05:05', '2023-11-01 11:00:00')) as diff

시간 차이를 초 단위로 변환한 것

 

타입 변환

SQL 쿼리문에서는 적재된 데이터를 타입 변한 뒤 가져올 수 있음

예 : 정수 타입으로 저장된 데이터를 문자열 데이터로 가져온 뒤 다른 테이블과 결합 가능

 

• 필요성 
  • 데이터 타입 불일치로 인한 연산/비교 오류를 피하기 위함
  • 다양한 데이터 소스 간의 호환성을 유지하기 위함
• CAST
• CONVERT
• 사용 문법 상의 차이만 있고 기능은 같음

SELECT CAST('20231014' AS SIGNED INTEGER) as int_date
# 문자열 > 정수형

SELECT CAST(date AS SIGNED INTEGER) as int_date
FROM orders
# date라는 변수의 자료형을 정수형으로 변환

덧셈도 가능해짐

SELECT CAST(date AS SIGNED INTEGER) + 3 as int_date
FROM orders

# 이렇게 해도 날짜를 더할 수 있으나 32일 등이 나올 수 있으므로 DATE_ADD()가 좋음

 

SELECT CONVERT ('20231101' SIGNED INTEGER) as int_date
# 문자열 > 정수형

SELECT CONVERT (20231101, CHAR) as int_date
# 정수형 > 문자열

SELECT CONVERT (20231101, CHAR(4)) as int_date
# 실제 글자 수보다 작게 입력하면 뒤에 것은 잘림

SELECT CONCAT(CONVERT(price,  CHAR), ' ON SALE') as sale_price
FROM products

# price를 문자열로 바꾸고 ON SALE과 합침

SELECT *
FROM orders
UNION
SELECT *
FROM orders_v2

이걸 정확하게 사용하면 아래와 같음

원래는 타입 수가 맞게 변환해주고 하는게 맞음

SELECT odr_index, product_id, user_name, date
FROM orders
UNION
SELECT odr_index, product_id, user_name, CAST(date as CHAR) as date
FROM orders_v2

조건절

IF 

SELECT price,
IF(price >=10000, '고가', '저가') AS '가격구분'
FROM product

 

IFNULL : 값이 NULL 일 때 지정한 값으로 채우는 것

SELECT user_name, IFNULL(date, 20231104)
as date
FROM oreders_v2
# date 컬럼에서 null 값이 있으면 20231104라는 값으로 채우라는 것이다.

SELECT name,
IF(managing in ('스포츠', '디지털'), '3층', '2층') as floor,
IF(off like '%토%', '토요일 휴무', '토요일 근무') as sat_off
FROM managers
# managing 컬럼 값이 스포츠나 디지털에 포함되면 3층, 아니면 2층
# off 컬럼 값의 토라는 문자가 있으면 토요일 휴무, 없으면 토요일 근무

SELECT name, odr_index
FROM managers m leff join orders o on m.name = user_name
group by 1

# managers 테이블 모두 포함, m.name=o.user_name인 경우만 추가
# name으로 그룹화

SELECT name, IFNULL(odr_index, -1)
FROM managers m leff join orders o on m.name = user_name
group by 1

 

고가 상품/저가 상품 클릭 수를 유저 별로 계산

SELECT user_name, price_class, count(1) as cnt
FROM clicks c 
INNER JOIN (SELECT product_id, IF(price > 5000, '고가', '저가') as price_class
            FROM products) p
            ON c.product_id = p.product_id
group by 1, 2
order by 1, 2

# clicks, products 테이블을 조인하는데 price_class로 구분해서  products 테이블에서 행을 가져옴
# user_name, price_class 기준으로 묶은 뒤 클릭 수를 셈

SELECT product_id,
IF(price >= 5000 AND price <= 10000, '중간', '양끝') as price_class
FROM products_B
# 가격 5000원 이상 10000원이하일때 중간 반환, 아니면 양끝 반환

SELECT product_id,
IF(price > 10000, '고가', IF(price>4000, '중가', '저가')) as price_class
FROM products_B

# price가 만원 이상이면 고가, 만원 이상이 아니고 4000원 초과면 중가, 4000원 이하면 저가

CASE WHEN

IF문과 비슷하지만 쿼리 작성 방식만 조금 다름

중첩하지 않아도 세 개 이상의 조건문 가능

끝날 때 END AS 컬럼명 사용해야 함

범위를 가지고 조건 설정 시 유용

 

SELECT price,
CASE WHEN price > 10000 THEN '고가'
WHEN (price <= 10000 AND price > 4000) THEN '중가'
ELSE '저가'
END AS price_class
FROM products_B

 

switch처럼 case when을 사용하는 방법

특정 컬럼 값을 기준으로 그룹을 구분할 수 있는 경우에 적절한 방법

특정 값을 가지고 조건 설정 시 유용

SELECT date,
CASE date
WHEN '20231014' THEN '첫째날'
WHEN '20231015' THEN '둘째날'
ELSE '기타'
END AS date_group
FROM clicks

SELECT *
FROM products
WHERE ( CASE WHEN category = '키즈' THEN 1
       WHEN name like '%어린이%' THEN 1
       WHEN name like '%보호대%' THEN 2
       WHEN category = '디지털' THEN 2
       ELSE 3
       END
) = 1

= 2로 바꾸면 보호대가 들어가거나 category가 디지털인 경우를 뽑아냄

SELECT *
FROM products
ORDER BY (
  CASE
  WHEN category = '디지털' THEN 1
  WHEN category = '주방용품' THEN 2
  WHEN name like "%보호대%" THEN 3
  ELSE 4
  END
  )
  
  # 카테고리가 디지털인 경우 1
  # 카테고리가 주방용품인 경우 2
  # 이름에 보호대가 들어가면 3
 # 아니면 4 순서로 뽑아낼 수 있음

맨 뒤에 DESC 붙이면 역순으로 정렬됨

 

SELECT category, name, date, price,
CASE date WHEN '20231014' THEN
		  CASE WHEN price > 5000 THEN '첫째날-고가'
          ELSE '첫째날-저가'
          END
     ELSE
     	   CASE WHEN price > 5000 THEN'둘째날-고가'
           ELSE '둘째날-저가'
           END

END as date_price_class

FROM clicks c INNER JOIN products p
	ON c.product_id = p.product_id

date를 기준으로 분기를 함 

date가 20231014면 "첫째날"을 앞에 붙이고 아니면 "둘째날"을 앞에 붙임

date가 20231014이고 price가 5000 초과면 첫째날-고가, 아니면 첫째날 -저가

둘째날도 마찬가지

그 외 유용한 함수

RANK() : 특정 컬럼 기준으로 등수를 매기는 함수

dense_rank() : 동점인 랭크가 있을 때 랭크 숫자를 건너뛰지 않고 빽빽하게 채우는 함수

percent_rank() : 몇 퍼센트의 다른 값들이 지금 값보다 작은지를 비율로 나타내는 함수

0.75면 75%의 값이 지금 이 값보다 작다는 뜻!

select age,
rank() over (order by age) as asc_rank,
rank() over (order by age desc) as desc_rank,
dense_rank() over (order by age) as dense_rank,
percent_rank() over (order by age) as percent_rank
from Customers

SELECT date, name, price, percent_rank() over (partition by date order by price) as date_p_rank
FROM clicks c INNER JOIN products_B p on c.product_id = p.product_id
ORDER BY 1, 3 DESC

 

lead() : 파티션 내에서 다음으로 오는 값을 찾는 함수

SELECT clk_index, user_name, date,
LEAD(clk_index, 1) OVER (partition by user_name, date order by clk_index) next_click
FROM clicks
ORDER by 3, 1

LEAD 함수가 가져올 컬럼은 clk_index이고 1이 들어가므로 바로 다음 값을 가져옴

user_name과 date를 기준으로 partition을 나눔 > 사람별로 날짜 별로 파티셔닝

순서는 clk_index 순으로 나누고 LEAD 함수의 결과물을 next_click으로 반환

 

lag() : lead와 비슷하지만 반대임

lead는 파티션 내에서 다음으로 오는 값을 찾는 것이라면, lag는 이전 값이 무엇이었는지 찾는 것

 

기준을 잡아 랭크를 나누고 다음 값과 이전 값을 찾는 것

WINDOW 함수

 

문제 설명

다음은 어느 대학교의 학생 정보를 담은 PCSQL_STUDENTS 테이블입니다.
PCSQL_STUDENTS 테이블은 아래와 같은 구조로 되어있으며, ID, NAME, DEPARTMENT_ID, TUITION, TOEIC, GRADUATED는 각각 학생의 학번, 성명, 소속 학부의 ID, 등록금(단위: 만원), 토익 점수, 졸업 여부를 나타냅니다.

COLUMN NAMETYPENULLABLE

ID	INTEGER	FALSE
NAME	VARCHAR(255)	FALSE
DEPARTMENT_ID	INTEGER	FALSE
TUITION	INTEGER	FALSE
TOEIC	INTEGER	TRUE
GRADUATED	BOOLEAN	FALSE

---

문제

PCSQL_STUDENTS 테이블에서 학생의 학번, 이름, 소속 학부의 ID, 소속 학부의 이름을 조회해주세요. 소속 학부의 이름은 아래 정보를 참고해 작성해주세요. 이때 소속 학부의 이름은 department_name이라는 이름으로 지정해주시고, 전체 결과는 학생의 학번 순으로 나와야합니다.

• 1번 소속 학부의 이름은 English Language and Literature 입니다.
• 2번 소속 학부의 이름은 Computer Science 입니다.
• 3번 소속 학부의 이름은 Department of History 입니다.

---

예시

예를 들어 PCSQL_STUDENTS 테이블이 다음과 같다면

IDNAMEDEPARTMENT_IDTUITIONTOEICGRADUATED

20400003	박서준	1	300	960	FALSE
20400001	이서연	2	500	740	FALSE
20410001	김서윤	2	500	200	FALSE
20410002	이서연	2	500		FALSE
20400002	정도윤	3	480	900	FALSE
20410003	조하윤	3	480	770	FALSE
20400005	강시우	3	480		FALSE
20400004	박예준	1	300	800	TRUE
20410004	김서현	1	300		TRUE
20410005	최지우	3	480	300	TRUE

SQL을 실행하면 다음과 같이 출력되어야 합니다.

IDNAMEDEPARTMENT_IDDEPARTMENT_NAME

20400001	이서연	2	Computer Science
20400002	정도윤	3	Department of History
20400003	박서준	1	English Language and Literature
20400004	박예준	1	English Language and Literature
20400005	강시우	3	Department of History
20410001	김서윤	2	Computer Science
20410002	이서연	2	Computer Science
20410003	조하윤	3	Department of History
20410004	김서현	1	English Language and Literature
20410005	최지우	3	Department of History

SELECT ID, NAME, DEPARTMENT_ID,
CASE DEPARTMENT_ID
WHEN 1 THEN 'English Language and Literature'
WHEN 2 THEN 'Computer Science'
WHEN 3 THEN 'Department of History'
END AS DEPARTMENT_NAME
FROM PCSQL_STUDENTS

• CASE WHEN - 비어 있는 값 0으로 바꿔주기

darkliht

MySQL 

문제 설명

다음은 어느 대학교의 학생 정보를 담은 PCSQL_STUDENTS 테이블입니다.
PCSQL_STUDENTS 테이블은 아래와 같은 구조로 되어있으며, ID, NAME, DEPARTMENT_ID, TUITION, TOEIC, GRADUATED는 각각 학생의 학번, 성명, 소속 학부의 ID, 등록금(단위: 만원), 토익 점수, 졸업 여부를 나타냅니다.

COLUMN NAMETYPENULLABLE

ID	INTEGER	FALSE
NAME	VARCHAR(255)	FALSE
DEPARTMENT_ID	INTEGER	FALSE
TUITION	INTEGER	FALSE
TOEIC	INTEGER	TRUE
GRADUATED	BOOLEAN	FALSE

---

문제

PCSQL_STUDENTS 테이블에서 학생의 학번, 이름, 토익 점수를 조회해주세요. 토익 점수가 없는 사람의 토익 점수는 0으로 표시하고, 전체 결과는 학생의 학번 순으로 나와야합니다.

---

예시

예를 들어 PCSQL_STUDENTS 테이블이 다음과 같다면

IDNAMEDEPARTMENT_IDTUITIONTOEICGRADUATED

20400003	박서준	1	300	960	FALSE
20400001	이서연	2	500	740	FALSE
20410001	김서윤	2	500	200	FALSE
20410002	이서연	2	500		FALSE
20400002	정도윤	3	480	900	FALSE
20410003	조하윤	3	480	770	FALSE
20400005	강시우	3	480		FALSE
20400004	박예준	1	300	800	TRUE
20410004	김서현	1	300		TRUE
20410005	최지우	3	480	300	TRUE

SQL을 실행하면 다음과 같이 출력되어야 합니다.

IDNAMETOEIC

20400001	이서연	740
20400002	정도윤	900
20400003	박서준	960
20400004	박예준	800
20400005	강시우	0
20410001	김서윤	200
20410002	이서연	0
20410003	조하윤	770
20410004	김서현	0
20410005	최지우	300

SELECT ID, NAME,
CASE WHEN TOEIC IS NULL THEN 0
ELSE TOEIC
END as TOEIC
FROM PCSQL_STUDENTS
ORDER BY 1

문제 설명

다음은 어느 쇼핑몰의 유저 정보를 담은 USERS 테이블입니다. USERS 테이블은 아래와 같은 구조로 되어있으며, USER_ID, USER_NAME, EMAIL, JOINED_AT은 각각 유저의 id, 유저의 이름, 이메일, 가입 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

USER_ID	INTEGER	FALSE
USER_NAME	VARCHAR	FALSE
EMAIL	VARCHAR	FALSE
JOINED_AT	DATETIME	FALSE

---

문제

users 테이블의 가입 시점은 UTC+00을 기준으로 표시된 것으로, 이는 대한민국 시간보다 9시간 빠릅니다. 유저의 id, 가입 시점, 가입 시점을 대한민국 시간에 맞춰 표시한 값을 조회하는 SQL문을 작성해 주세요. 이때 세 번째 컬럼의 이름은 joined_at_kst로 지정하고, 결과는 유저의 id 순으로 나와야 합니다.

---

예제

예를 들어 USERS 테이블이 다음과 같다면

user_iduser_nameemailjoined_at

2	Axel Mckee	axel@pcsql.com	2024-03-01 08:23:59
3	Bryce Velez	bryce@pscql.com	2024-03-01 08:30:00
4	Zavier Owen	zavior@pcsql.com	2024-03-02 21:12:00
6	Karlie Andrew	karlie@pscql.com	2024-03-13 23:41:17

SQL을 실행하면 다음과 같이 출력되어야 합니다.

user_idjoined_atjoinedatkst

2	2024-03-01 08:23:59	2024-03-01 17:23:59
3	2024-03-01 08:30:00	2024-03-01 17:30:00
4	2024-03-02 21:12:00	2024-03-03 06:12:00
6	2024-03-13 23:41:17	2024-03-14 08:41:17

SELECT USER_ID, JOINED_AT, DATE_ADD(JOINED_AT, INTERVAL 9 HOUR) as joined_at_kst
FROM USERS
ORDER BY USER_ID

문제 설명

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, ORDERED_AT 은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

---

문제

주문 테이블에서 각 주문의 id, 주문 시점, 주문 시점에서 날짜만 표시한 것, 주문 시점에서 시각만을 표시한 것을 조회하는 SQL문을 작성해 주세요. 필드명은 각각 order_id, ordered_at, ordered_date, ordered_time으로 지정하고, 결과는 주문의 id 순으로 정렬해 주세요.

---

예를 들어 ORDERS 테이블이 다음과 같다면

예제order_iduser_idtotal_amountordered_at

1	2	48.87	2024-03-01 13:12:29
2	3	48.87	2024-03-02 18:08:40
3	2	20.979	2024-03-02 21:48:00
4	3	19.99	2024-03-14 00:01:23

SQL을 실행하면 다음과 같이 출력되어야 합니다.

order_idordered_atordered_dateordered_time

1	2024-03-01 13:12:29	2024-03-01	13:12:29
2	2024-03-02 18:08:40	2024-03-02	18:08:40
3	2024-03-02 21:48:00	2024-03-02	21:48:00
4	2024-03-14 00:01:23	2024-03-14	00:01:23

SELECT order_id, ordered_at, 
DATE_FORMAT(ordered_at, '%Y-%m-%d') as ordered_date, 
DATE_FORMAT(ordered_at, "%T") as ordered_time
FROM ORDERS

문제 설명

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, ORDERED_AT 은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

---

문제

주문 테이블에서 각 주문의 id, 주문 시점의 년, 월, 일, 시, 분, 초를 별도의 컬럼으로 조회하는 SQL문을 작성해 주세요. 필드명은 각각 order_id, val1, val2, val3, val4, val5, val6으로 지정하고, 결과는 주문의 id 순으로 정렬해 주세요.

---

예제

예를 들어 ORDERS 테이블이 다음과 같다면

order_iduser_idtotal_amountordered_at

1	2	48.87	2024-03-01 13:12:29
2	3	48.87	2024-03-02 18:08:40
3	2	20.979	2024-03-02 21:48:00
4	3	19.99	2024-03-14 00:01:23

SQL을 실행하면 다음과 같이 출력되어야 합니다.

order_idval1val2val3val4val5val6

1	2024	3	1	13	12	29
2	2024	3	2	18	8	40
3	2024	3	2	21	48	0
4	2024	3	14	0	1	23

SELECT 
    order_id, 
    YEAR(ordered_at) AS val1,
    MONTH(ordered_at) AS val2,
    DAY(ordered_at) AS val3,
    HOUR(ordered_at) AS val4,   -- 시 앞의 0 제거
    MINUTE(ordered_at) AS val5, -- 분 앞의 0 제거
    SECOND(ordered_at) AS val6  -- 초 앞의 0 제거
FROM 
    ORDERS
ORDER BY 
    order_id;

문제 설명

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, ORDERED_AT 은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

---

문제

주문 일과 일별 주문 건수를 조회하는 SQL문을 작성해 주세요. 결과는 일자 순으로 정렬해야 하며, 컬럼 명은 val1, val2로 지정해야 합니다. 주문이 없는 일은 표시하지 않습니다.

---

예제

예를 들어 ORDERS 테이블이 다음과 같다면

order_iduser_idtotal_amountordered_at

1	2	48.87	2024-03-01 13:12:29
2	3	48.87	2024-03-02 18:08:40
3	2	20.979	2024-03-02 21:48:00
4	3	19.99	2024-03-14 00:01:23

• 3월 1일에는 주문이 한 건입니다.
• 3월 2일에는 주문이 두 건입니다.
• 3월 14일에는 주문이 한 건입니다.

따라서 SQL을 실행하면 다음과 같이 출력되어야 합니다.

val1val2

1	1
2	2
14	1

SELECT DAY(ordered_at) as val1,
count(*) as val2
 
FROM ORDERS

GROUP BY val1

문제 설명

다음은 어느 쇼핑몰의 유저 정보를 담은 USERS 테이블입니다. USERS 테이블은 아래와 같은 구조로 되어있으며, USER_ID, USER_NAME, EMAIL, JOINED_AT은 각각 유저의 id, 유저의 이름, 이메일, 가입 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

USER_ID	INTEGER	FALSE
USER_NAME	VARCHAR	FALSE
EMAIL	VARCHAR	FALSE
JOINED_AT	DATETIME	FALSE

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, `ORDERED_AT은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

---

문제

VIP 조건이 아래와 같을 때, VIP 조건을 만족하는 유저의 id와 이름을 뽑는 SQL 문을 작성해 주세요. 이때 결과는 user_id 순으로 나열해 주세요.

vip 조건:

• 주문을 3회 이상했다.
• 또는 총액의 합이 300 이상이다.

---

예제

예를 들어 USERS 테이블과 ORDERS 테이블이 다음과 같다면

USER 테이블:

user_iduser_nameemailjoined_at

2	Axel Mckee	axel@pcsql.com	2024-03-01 08:23:59
3	Bryce Velez	bryce@pscql.com	2024-03-01 08:30:00
4	Zavier Owen	zavior@pcsql.com	2024-03-02 21:12:00
6	Karlie Andrew	karlie@pscql.com	2024-03-13 23:41:17
9	Jaeden Malone	jaeden@pcsql.com	2024-03-14 01:02:48
24	Elle Bowers	elie@pcsql.com	2024-03-17 21:20:01

ORDERS 테이블:

order_iduser_idtotal_amountordered_at

1	2	209.27	2024-03-01 13:12:29
2	3	64.87	2024-03-02 18:08:40
3	2	29.97	2024-03-02 21:48:00
4	3	19.98	2024-03-14 00:01:23
5	6	300	2024-03-14 00:10:41
6	2	19.98	2024-03-17 00:12:29
10	24	359.9	2024-03-17 19:14:29
11	9	57.76	2024-03-17 21:22:41

• USER_ID가 2인 유저는 3번 주문을 했으므로 VIP입니다.
• USER_ID가 3인 유저는 2번 주문을 했으며, 총액의 합은 84.85 이므로 VIP가 아닙니다.
• USER_ID가 6인 유저는 총액의 합이 300.00이므로 VIP입니다.
• USER_ID가 9인 유저는 1번 주문을 했으며, 총액의 합이 57.76이므로 VIP가 아닙니다.
• USER_ID가 24인 유저는 총액의 합이 359.9이므로 VIP입니다.

따라서 SQL을 실행하면 다음과 같이 출력되어야 합니다.

user_iduser_name

2	Axel Mckee
6	Karlie Andrew
24	Elle Bowers

SELECT u.user_id, u.user_name
FROM USERS u INNER JOIN ORDERS o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name

HAVING SUM(o.total_amount) >= 300
OR COUNT(order_id) >= 3

문제 설명

다음은 카테고리의 정보를 담은 CATEGORIES 테이블입니다. CATEGORIES 테이블은 아래와 같은 구조로 되어있으며, CATEGORY_ID, CATEGORY_NAME, MANAGER_ID는 각각 카테고리의 id, 카테고리 명, 카테고리를 담당하는 매니저의 id를 나타냅니다.

COLUMN NAMETYPENULLABLE

CATEGORY_ID	INTEGER	FALSE
CATEGORY_NAME	VARCHAR	FALSE
MANAGER_ID	INTEGER	TRUE

다음은 또 다른 카테고리의 정보를 담은 NEW_CATEGORIES 테이블입니다. NEW_CATEGORIES 테이블은 아래와 같은 구조로 되어있으며, CATEGORY_ID, CATEGORY_NAME, MANAGER_ID는 각각 카테고리의 id, 카테고리 명, 카테고리를 담당하는 매니저의 id를 나타냅니다.

COLUMN NAMETYPENULLABLE

CATEGORY_ID	INTEGER	FALSE
CATEGORY_NAME	VARCHAR	FALSE
MANAGER_ID	INTEGER	TRUE

다음은 또 다른 카테고리의 정보를 담은 NEW_NEW_CATEGORIES 테이블입니다. NEW_NEW_CATEGORIES 테이블은 아래와 같은 구조로 되어있으며, CATEGORY_ID, CATEGORY_NAME은 각각 카테고리의 id, 카테고리 명을 나타냅니다.

COLUMN NAMETYPENULLABLE

CATEGORY_ID	INTEGER	FALSE
CATEGORY_NAME	VARCHAR	FALSE

---

문제

세 테이블을 합치면 어떤 모습이 되는지 조회하는 SQL문을 작성해 주세요. 이때 MANAGER_ID 없는 테이블은 해당 항목을 NULL로 두면 됩니다. 같은 id를 가진 레코드가 주어지는 경우는 없으며, 결과는 id 순으로 나와야 합니다.

---

예제

CATEGORIES 테이블:

category_idcategory_namemanager_id

1	Sports	4
2	Accessories	5
3	Crafts

NEW_CATEGORIES 테이블:

category_idcategory_namemanager_id

4	Toys	3
5	Smart Home
6	Elenctronics	5

NEW_NEW_CATEGORIES 테이블:

category_idcategory_name

10	Pets
11	Baby

SQL을 실행하면 다음과 같이 출력되어야 합니다.

category_idcategory_namemanager_id

1	Sports	4
2	Accessories	5
3	Crafts
4	Toys	3
5	Smart Home
6	Elenctronics	5
10	Pets
11	Baby

SELECT *
FROM CATEGORIES

UNION ALL

SELECT *
FROM NEW_CATEGORIES

UNION ALL

SELECT category_id, category_name, NULL as manager_id
FROM NEW_NEW_CATEGORIES

문제 설명

다음은 어느 쇼핑몰의 주문 기록 상세 내역을 담은 ORDER_DETAILS 테이블입니다. ORDER_DETAILS 테이블은 아래와 같은 구조로 되어있으며, ORDER_DETAIL_ID, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE_PER_UNIT은 각각 주문 상세의 id, 주문의 id, 제품의 id, 주문 수량, 주문 당시의 단가를 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDERDETAILID	INTEGER	FALSE
ORDER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
QUANTITY	INTEGER	FALSE
PRICEPERUNIT	NUMERIC	FALSE

다음은 어느 쇼핑몰의 상품 정보를 담은 PRODUCTS 테이블입니다. PRODUCTS 테이블은 아래와 같은 구조로 되어있으며, PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, UNIT_PRICE, STOCK은 각각 상품의 id, 상품명, 상품이 속한 카테고리의 id, 단가, 재고량을 나타냅니다.

COLUMN NAMETYPENULLABLE

PRODUCT_ID	INTEGER	FALSE
PRODUCT_NAME	VARCHAR	FALSE
CATEGORY_ID	INTEGER	FALSE
UNIT_PRICE	NUMERIC	FALSE
STOCK	INTEGER	FALSE

다음은 카테고리의 정보를 담은 CATEGORIES 테이블입니다. CATEGORIES 테이블은 아래와 같은 구조로 되어있으며, CATEGORY_ID, CATEGORY_NAME, MANAGER_ID는 각각 카테고리의 id, 카테고리 명, 카테고리를 담당하는 매니저의 id를 나타냅니다.

COLUMN NAMETYPENULLABLE

CATEGORY_ID	INTEGER	FALSE
CATEGORY_NAME	VARCHAR	FALSE
MANAGER_ID	INTEGER	TRUE

---

문제

모든 주문 상세에 대해 주문 상세의 id, 주문 id, 상품의 이름, 상품 카테고리의 이름을 조회하는 SQL문을 작성하세요. 이때 결과는 주문 상세의 id 순으로 나와야 합니다.

---

예제

예를 들어 각 테이블이 다음과 같다면

ORDER_DETAILS 테이블:

orderdetailidorder_idproduct_idquantitypriceperunit

1	1	1	6	28.88
2	1	3	1	35.99
3	2	3	1	35.99
4	2	1	1	28.88
5	3	4	1	9.99
6	3	4	2	9.99
7	4	4	2	9.99
8	5	3	10	30
9	6	4	2	9.99
10	10	3	10	35.99
11	11	1	2	28.88

PRODUCTS 테이블:

product_idproduct_namecategory_idunit_pricestock

1	Workout Shorts	1	28.88	22
2	3.5mm Silver Tibe	2	19.99	50
3	ABC Yoga Pants	1	35.99	0
4	Embroidery Floss	3	9.99	10000
5	Painting Storage	3	2.37	9
6	Black Flats	1	23.15	10

CATEGORIES 테이블:

category_idcategory_namemanager_id

1	Sports	4
2	Accessories	5
3	Crafts

SQL을 실행하면 다음과 같이 출력되어야 합니다.

order_detail_idorder_idproduct_namecategory_name

1	1	Workout Shorts	Sports
2	1	ABC Yoga Pants	Sports
3	2	ABC Yoga Pants	Sports
4	2	Workout Shorts	Sports
5	3	Embroidery Floss	Crafts
6	3	Embroidery Floss	Crafts
7	4	Embroidery Floss	Crafts
8	5	ABC Yoga Pants	Sports
9	6	Embroidery Floss	Crafts
10	10	ABC Yoga Pants	Sports
11	11	Workout Shorts	Sports

SELECT o.ORDER_DETAIL_ID, o.order_id, p.product_name, c.category_name
FROM ORDER_DETAILS o INNER JOIN PRODUCTS p ON o.product_id = p.product_id
INNER JOIN CATEGORIES c ON p.category_id = c.category_id

ORDER BY o.ORDER_DETAIL_ID

문제 설명

다음은 어느 쇼핑몰의 상품 정보를 담은 PRODUCTS 테이블입니다. PRODUCTS 테이블은 아래와 같은 구조로 되어있으며, PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, UNIT_PRICE, STOCK은 각각 상품의 id, 상품명, 상품이 속한 카테고리의 id, 단가, 재고량을 나타냅니다.

COLUMN NAMETYPENULLABLE

PRODUCT_ID	INTEGER	FALSE
PRODUCT_NAME	VARCHAR	FALSE
CATEGORY_ID	INTEGER	FALSE
UNIT_PRICE	NUMERIC	FALSE
STOCK	INTEGER	FALSE

다음은 어느 쇼핑몰의 주문 기록 상세 내역을 담은 ORDER_DETAILS 테이블입니다. ORDER_DETAILS 테이블은 아래와 같은 구조로 되어있으며, ORDER_DETAIL_ID, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE_PER_UNIT은 각각 주문 상세의 id, 주문의 id, 제품의 id, 주문 수량, 주문 당시의 단가를 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDERDETAILID	INTEGER	FALSE
ORDER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
QUANTITY	INTEGER	FALSE
PRICEPERUNIT	NUMERIC	FALSE

---

문제

인기가 없는 상품 현황을 파악하려 합니다. 재고가 10개 이상 남아있으나 주문된 이력이 없는 상품의 id, 이름, 재고량을 조회하는 SQL문을 작성해 주세요. 이때 결과는 상품의 id 순으로 나와야 합니다.

---

예제

예를 들어 PRODUCTS 테이블과 ORDER_DETAILS 테이블이 다음과 같다면

PRODUCTS 테이블:

product_idproduct_namecategory_idunit_pricestock

1	Workout Shorts	1	28.88	22
2	3.5mm Silver Tibe	2	19.99	50
3	ABC Yoga Pants	1	35.99	0
4	Embroidery Floss	3	9.99	10000
5	Painting Storage	3	2.37	9
6	Black Flats	1	23.15	10

ORDER_DETAILS 테이블:

orderdetailidorder_idproduct_idquantitypriceperunit

1	1	1	6	28.88
2	1	3	1	35.99
3	2	3	1	35.99
4	2	1	1	28.88
5	3	4	1	9.99
6	3	4	2	9.99
7	4	4	2	9.99
8	5	3	10	30
9	6	4	2	9.99
10	10	3	10	35.99
11	11	1	2	28.88

• PRODUCT_ID가 1인 상품은 주문 이력이 있으므로, 결과에 포함하지 않습니다.
• PRODUCT_ID가 2인 상품은 주문된 적이 없고 재고가 10 이상이므로 결과에 포함합니다.
• PRODUCT_ID가 3인 상품은 주문 이력이 있으므로, 결과에 포함하지 않습니다.
• PRODUCT_ID가 4인 상품은 주문 이력이 있으므로, 결과에 포함하지 않습니다.
• PRODUCT_ID가 5인 상품은 주문된 적이 없으나 재고가 10 미만이므로 결과에 포함하지 않습니다.
• PRODUCT_ID가 6인 상품은 주문된 적이 없고 재고가 10 이상이므로 결과에 포함합니다.

따라서 SQL을 실행하면 다음과 같이 출력되어야 합니다.

product_idproduct_namestock

2	3.5mm Silver Tibe	50
6	Black Flats	10

SELECT p.PRODUCT_ID, p.product_name, p.stock
FROM PRODUCTS p LEFT JOIN ORDER_DETAILS o ON p.PRODUCT_ID = o.product_id
WHERE p.stock >= 10
AND o.PRODUCT_ID is null
ORDER BY p.PRODUCT_ID

문제 설명

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, `ORDERED_AT은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

다음은 어느 쇼핑몰의 주문 기록 상세 내역을 담은 ORDER_DETAILS 테이블입니다. ORDER_DETAILS 테이블은 아래와 같은 구조로 되어있으며, ORDER_DETAIL_ID, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE_PER_UNIT은 각각 주문 상세의 id, 주문의 id, 제품의 id, 주문 수량, 주문 당시의 단가를 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_DETAIL_ID	INTEGER	FALSE
ORDER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
QUANTITY	INTEGER	FALSE
PRICEPERUNIT	NUMERIC	FALSE

다음은 어느 쇼핑몰의 리뷰 내역을 담은 REVIEWS 테이블입니다. REVIEWS 테이블은 아래와 같은 구조로 되어있으며, REVIEW_ID, ORDER_DETAIL_ID, SCORE는 각각 리뷰의 id, 리뷰를 남긴 주문 기록 상세, 리뷰 점수를 나타냅니다.

COLUMN NAMETYPENULLABLE

REVIEW_ID	INTEGER	FALSE
ORDER_DETAIL_ID	INTEGER	FALSE
SCORE	INTEGER	FALSE

---

문제

각 주문별로 리뷰 점수를 구하려고 합니다. 주문의 리뷰 점수는 주문에 딸린 주문 기록 상세의 리뷰 점수의 평균으로 산출합니다. 이때 주문의 id와 리뷰 점수를 조회하는 SQL문을 작성해 주세요. 각 컬럼의 이름은 order_id, score로 지정하고, 결과는 order_id 순으로 나열해 주세요. 리뷰가 없는 주문은 리뷰 점수를 NULL으로 넣어주세요.

---

예제

예를 들어 ORDERS 테이블 ORDER_DETAILS 테이블과 REVIEWS 테이블이 다음과 같다면

ORDERS 테이블:

order_iduser_idtotal_amountordered_at

1	2	209.27	2024-03-01 13:12:29
2	3	64.87	2024-03-02 18:08:40
3	2	29.97	2024-03-02 21:48:00
4	3	19.98	2024-03-14 00:01:23
5	6	300	2024-03-14 00:10:41
6	2	19.98	2024-03-17 00:12:29
10	24	359.9	2024-03-17 19:14:29
11	9	57.76	2024-03-17 21:22:41

ORDER_DETAILS 테이블:

order_detail_idorder_idproduct_idquantitypriceperunit

1	1	1	6	28.88
2	1	3	1	35.99
3	2	3	1	35.99
4	2	1	1	28.88
5	3	4	1	9.99
6	3	4	2	9.99
7	4	4	2	9.99
8	5	3	10	30
9	6	4	2	9.99
10	10	3	10	35.99
11	11	1	2	28.88

REVIEWS 테이블:

review_idorder_detail_idscore

2	1	4
3	2	1
11	3	4
4	4	5
10	6	4
6	7	3
9	8	3
1	9	3
7	11	3

• ORDER_ID 1의 주문 상세에는 REVIEW_ID 3이 1점, REVIEW_ID 11이 4점을 매겼습니다. 따라서 평균 점수는 2.5입니다.
• ORDER_ID 2의 주문 상세에는 REVIEW_ID 4가 5점, REVIEW_ID 7이 3점을 매겼습니다. 따라서 평균 점수는 4입니다.
• ORDER_ID 3의 주문 상세에는 리뷰가 없으므로 평균 점수는 NULL입니다.
• ORDER_ID 4의 주문 상세에는 REVIEW_ID 10이 4점을 매겼습니다. 따라서 평균 점수는 4입니다.

나머지 주문에 대해서도 같은 방식으로 평균 점수를 구하면 SQL을 실행했을 때 다음과 같이 출력되어야 합니다.

order_idscore

1	2.5
2	4.5
3	4
4	3
5	3
6	3
10	NULL
11	3

SELECT o.order_id, AVG(r.score) as score
FROM ORDERS o LEFT JOIN ORDER_DETAILS od ON o.order_id = od.order_id
LEFT JOIN REVIEWS r ON od.order_detail_id = r.order_detail_id
GROUP BY o.order_id
ORDER BY o.order_id

문제 설명

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, `ORDERED_AT은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

---

문제

모든 주문에 대해서 주문을 한 유저의 id, 주문의 id, 주문 시점, 유저가 다음 주문을 한 시점을 조회하는 SQL문을 작성해 주세요. 유저가 다음 주문을 한 시점을 나타내는 컬럼의 이름은 next_ordered_at으로 지정하고, 다음 주문이 없다면 해당 필드는 NULL으로 두면 됩니다. 결과는 유저ID 순으로 정렬해 주세요.

---

예제

ORDERS 테이블이 다음과 같다면

order_iduser_idtotal_amountordered_at

1	2	209.27	2024-03-01 13:12:29
2	3	64.87	2024-03-02 18:08:40
3	2	29.97	2024-03-02 21:48:00
4	3	19.98	2024-03-14 00:01:23
5	6	300	2024-03-14 00:10:41
6	2	19.98	2024-03-17 00:12:29
10	24	359.9	2024-03-17 19:14:29
11	9	57.76	2024-03-17 21:22:41

SQL을 실행하면 다음과 같이 출력되어야 합니다.

user_idorder_idordered_atnext_ordered_at

2	1	2024-03-01 13:12:29	2024-03-02 21:48:00
2	3	2024-03-02 21:48:00	2024-03-17 00:12:29
2	6	2024-03-17 00:12:29
3	2	2024-03-02 18:08:40	2024-03-14 00:01:23
3	4	2024-03-14 00:01:23
6	5	2024-03-14 00:10:41
9	11	2024-03-17 21:22:41
24	10	2024-03-17 19:14:29

SELECT user_id, order_id, ordered_at, 
LEAD(ordered_at, 1) OVER(partition by user_id ORDER BY ordered_at) as next_ordered_at
FROM ORDERS
ORDER BY user_id

문제 설명

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, `ORDERED_AT은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

---

문제

주문의 총액을 기준으로 각 주문의 순위를 확인하려고 합니다. 각 주문의 주문 번호, 총액, 그리고 순위를 조회하는 SQL문을 작성해 주세요. 이때 순위를 의미하는 컬럼의 이름은 val으로 지정해 주세요. 결과는 순위가 낮을수록(순위가 1에 가까울수록) 먼저 조회되어야 하며, 같은 순위를 가진 주문 중에서는 주문 번호가 낮은 레코드가 먼저 조회되어야 합니다. 단, 총액이 가장 높은 주문의 순위가 1위여야 하며, 총액이 같은 주문은 동일한 순위를 부여해 주세요.

---

예제

ORDERS 테이블이 다음과 같다면

order_iduser_idtotal_amountordered_at

1	2	209.27	2024-03-01 13:12:29
2	3	64.87	2024-03-02 18:08:40
3	2	29.97	2024-03-02 21:48:00
4	3	19.98	2024-03-14 00:01:23
5	6	300	2024-03-14 00:10:41
6	2	19.98	2024-03-17 00:12:29
10	24	359.9	2024-03-17 19:14:29
11	9	57.76	2024-03-17 21:22:41

SQL을 실행하면 다음과 같이 출력되어야 합니다.

order_idtotal_amountval

10	359.9	1
5	300	2
1	209.27	3
2	64.87	4
11	57.76	5
3	29.97	6
4	19.98	7
6	19.98	7

SELECT order_id, total_amount, dense_rank() over (order by total_amount desc) as val
FROM ORDERS

ORDER BY total_amount desc, val asc

문제 설명

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, `ORDERED_AT은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

---

문제

유저가 주문한 금액을 기준으로 순위를 매기려고 합니다 합니다. 유저의 ID, 유저의 총 주문액, 순위를 조회하는 SQL문을 작성해 주세요. 이때 총 주문액을 의미하는 컬럼의 이름은 val1, 순위를 의미하는 컬럼의 이름은 val2으로 지정해 주세요. 결과는 유저의 id 순으로 나열해야 합니다. 총 주문액이 같은 유저가 주어지는 경우는 주어지지 않습니다.

---

예제

ORDERS 테이블이 다음과 같다면

order_iduser_idtotal_amountordered_at

1	2	209.27	2024-03-01 13:12:29
2	3	64.87	2024-03-02 18:08:40
3	2	29.97	2024-03-02 21:48:00
4	3	19.98	2024-03-14 00:01:23
5	6	300	2024-03-14 00:10:41
6	2	19.98	2024-03-17 00:12:29
10	24	359.9	2024-03-17 19:14:29
11	9	57.76	2024-03-17 21:22:41

SQL을 실행하면 다음과 같이 출력되어야 합니다.

user_idval1val2

2	259.22	3
3	84.85	4
6	300	2
9	57.76	5
24	359.9	1

SELECT user_id, sum(total_amount) as val1, rank() over(order by sum(total_amount) desc) as val2
FROM ORDERS
GROUP BY user_id
ORDER BY user_id

문제 설명

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, `ORDERED_AT은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

다음은 어느 쇼핑몰의 주문 기록 상세 내역을 담은 ORDER_DETAILS 테이블입니다. ORDER_DETAILS 테이블은 아래와 같은 구조로 되어있으며, ORDER_DETAIL_ID, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE_PER_UNIT은 각각 주문 상세의 id, 주문의 id, 제품의 id, 주문 수량, 주문 당시의 단가를 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDERDETAILID	INTEGER	FALSE
ORDER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
QUANTITY	INTEGER	FALSE
PRICEPERUNIT	NUMERIC	FALSE

---

문제

ORDERS 테이블에 있는 사용자가 최초로 주문한 시점의 주문 ID, 첫 주문 상품의 ID와 이 상품의 주문 시점 단가를 확인하는 SQL문을 작성해 주세요. 각 컬럼의 이름은 user_id, order_id, product_id, price_per_unit 으로 지정하고, 결과는 user_id 순으로 나열해 주세요. 사용자가 주문을 하지 않았다면 3번째, 4번째 필드는 NULL으로 둡니다. 첫 주문은 주문 시점(ordered_at)이 가장 빠른 주문을 의미하며, 첫 주문 상품은 주문 시점(ordered_at)이 가장 빠른 주문 중에서 주문 상세의 id가 가장 작은 상품을 의미합니다.

---

예제

예를 들어 ORDERS 테이블과 ORDER_DETAILS 테이블이 다음과 같다면

ORDERS 테이블:

order_iduser_idtotal_amountordered_at

1	2	209.27	2024-03-01 13:12:29
2	3	64.87	2024-03-02 18:08:40
3	2	29.97	2024-03-02 21:48:00
4	3	19.98	2024-03-14 00:01:23
5	6	300	2024-03-14 00:10:41
6	2	19.98	2024-03-17 00:12:29
10	24	359.9	2024-03-17 19:14:29
11	9	57.76	2024-03-17 21:22:41

ORDER_DETAILS 테이블:

orderdetailidorder_idproduct_idquantitypriceperunit

1	1	1	6	28.88
2	1	3	1	35.99
3	2	3	1	35.99
4	2	1	1	28.88
5	3	4	1	9.99
6	3	4	2	9.99
7	4	4	2	9.99
8	5	3	10	30
9	6	4	2	9.99
10	10	3	10	35.99
11	11	1	2	28.88

• USER_ID가 2인 유저의 첫 주문 id는 1이며 처음 주문한 상품의 ID는 1이고 주문 당시 단가는 28.88입니다.
• USER_ID가 3인 유저의 첫 주문 id는 2이며 처음 주문한 상품의 ID는 3이고 주문 당시 단가는 35.99입니다.
• USER_ID가 6인 유저의 첫 주문 id는 5이며 처음 주문한 상품의 ID는 3이고 주문 당시 단가는 35.99입니다.
• USER_ID가 9인 유저의 첫 주문 id는 11이며 처음 주문한 상품의 ID는 1이고 주문 당시 단가는 28.88입니다.
• USER_ID가 24인 유저의 첫 주문 id는 10이며 처음 주문한 상품의 ID는 3이고 주문 당시 단가는 35.99입니다.

따라서 SQL을 실행하면 다음과 같이 출력되어야 합니다.

user_idorder_idproduct_idpriceperunit

2	1	1	28.88
3	2	3	35.99
6	5	3	35.99
9	11	1	28.88
24	10	3	35.99

SELECT 
    o.user_id,
    o.order_id,
    od.product_id,
    od.price_per_unit
FROM 
    ORDERS o
JOIN 
    ORDER_DETAILS od ON o.order_id = od.order_id
WHERE 
    o.ordered_at = (
        SELECT MIN(ordered_at)
        FROM ORDERS
        WHERE user_id = o.user_id
    )
    AND od.order_detail_id = (
        SELECT MIN(od2.order_detail_id)
        FROM ORDER_DETAILS od2
        WHERE od2.order_id = o.order_id
    )
ORDER BY 
    o.user_id;

문제 설명

다음은 어느 쇼핑몰의 상품 정보를 담은 PRODUCTS 테이블입니다. PRODUCTS 테이블은 아래와 같은 구조로 되어있으며, PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, UNIT_PRICE, STOCK은 각각 상품의 id, 상품명, 상품이 속한 카테고리의 id, 단가, 재고량을 나타냅니다.

COLUMN NAMETYPENULLABLE

PRODUCT_ID	INTEGER	FALSE
PRODUCT_NAME	VARCHAR	FALSE
CATEGORY_ID	INTEGER	FALSE
UNIT_PRICE	NUMERIC	FALSE
STOCK	INTEGER	FALSE

다음은 어느 쇼핑몰의 주문 기록 상세 내역을 담은 ORDER_DETAILS 테이블입니다. ORDER_DETAILS 테이블은 아래와 같은 구조로 되어있으며, ORDER_DETAIL_ID, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE_PER_UNIT은 각각 주문 상세의 id, 주문의 id, 제품의 id, 주문 수량, 주문 당시의 단가를 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDERDETAILID	INTEGER	FALSE
ORDER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
QUANTITY	INTEGER	FALSE
PRICEPERUNIT	NUMERIC	FALSE

---

문제

인기가 없는 상품 현황을 파악하려 합니다. 재고가 10개 이상 남아있으나 주문된 이력이 없는 상품의 id, 이름, 재고량을 조회하는 SQL문을 작성해 주세요. 이때 결과는 상품의 id 순으로 나와야 합니다.

---

예제

예를 들어 PRODUCTS 테이블과 ORDER_DETAILS 테이블이 다음과 같다면

PRODUCTS 테이블:

product_idproduct_namecategory_idunit_pricestock

1	Workout Shorts	1	28.88	22
2	3.5mm Silver Tibe	2	19.99	50
3	ABC Yoga Pants	1	35.99	0
4	Embroidery Floss	3	9.99	10000
5	Painting Storage	3	2.37	9
6	Black Flats	1	23.15	10

ORDER_DETAILS 테이블:

orderdetailidorder_idproduct_idquantitypriceperunit

1	1	1	6	28.88
2	1	3	1	35.99
3	2	3	1	35.99
4	2	1	1	28.88
5	3	4	1	9.99
6	3	4	2	9.99
7	4	4	2	9.99
8	5	3	10	30
9	6	4	2	9.99
10	10	3	10	35.99
11	11	1	2	28.88

• PRODUCT_ID가 1인 상품은 주문 이력이 있으므로, 결과에 포함하지 않습니다.
• PRODUCT_ID가 2인 상품은 주문된 적이 없고 재고가 10 이상이므로 결과에 포함합니다.
• PRODUCT_ID가 3인 상품은 주문 이력이 있으므로, 결과에 포함하지 않습니다.
• PRODUCT_ID가 4인 상품은 주문 이력이 있으므로, 결과에 포함하지 않습니다.
• PRODUCT_ID가 5인 상품은 주문된 적이 없으나 재고가 10 미만이므로 결과에 포함하지 않습니다.
• PRODUCT_ID가 6인 상품은 주문된 적이 없고 재고가 10 이상이므로 결과에 포함합니다.

따라서 SQL을 실행하면 다음과 같이 출력되어야 합니다.

product_idproduct_namestock

2	3.5mm Silver Tibe	50
6	Black Flats	10

SELECT p.PRODUCT_ID, p.product_name, p.stock
FROM PRODUCTS p LEFT JOIN ORDER_DETAILS o ON p.PRODUCT_ID = o.product_id
WHERE p.stock >= 10
AND o.PRODUCT_ID is null
ORDER BY p.PRODUCT_ID

문제 설명

📌 이 문제는 앞서 풀어본 [VIP 찾기] 문제와 같습니다.
서브쿼리를 이용한 정답 코드를 드릴테니, 이 코드를 서브쿼리를 쓰지 않고 WITH - AS를 쓰도록 바꿔보세요.

정답 코드:

SELECT 
    user_id,
    user_name
FROM users
WHERE user_id IN (
    SELECT user_id
    FROM orders
    GROUP BY 1
    HAVING 
        SUM(total_amount) >= 300
        OR COUNT(1) >= 3
)
ORDER BY user_id;

---

다음은 어느 쇼핑몰의 유저 정보를 담은 USERS 테이블입니다. USERS 테이블은 아래와 같은 구조로 되어있으며, USER_ID, USER_NAME, EMAIL, JOINED_AT은 각각 유저의 id, 유저의 이름, 이메일, 가입 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

USER_ID	INTEGER	FALSE
USER_NAME	VARCHAR	FALSE
EMAIL	VARCHAR	FALSE
JOINED_AT	DATETIME	FALSE

다음은 어느 쇼핑몰의 주문 기록을 담은 ORDERS 테이블입니다. ORDERS 테이블은 아래와 같은 구조로 되어있으며, ORDER_ID, USER_ID, TOTAL_AMOUNT, `ORDERED_AT은 각각 주문의 id, 주문을 한 유저의 id, 총액, 주문 시점을 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
TOTAL_AMOUNT	NUMERIC	FALSE
ORDERED_AT	DATETIME	FALSE

---

문제

VIP 조건이 아래와 같을 때, VIP 조건을 만족하는 유저의 id와 이름을 뽑는 SQL 문을 작성해 주세요. 이때 결과는 user_id 순으로 나열해 주세요.

vip 조건:

• 주문을 3회 이상했다.
• 또는 총액의 합이 300 이상이다.

---

예제

예를 들어 USERS 테이블과 ORDERS 테이블이 다음과 같다면

USERS 테이블:

user_iduser_nameemailjoined_at

2	Axel Mckee	axel@pcsql.com	2024-03-01 08:23:59
3	Bryce Velez	bryce@pscql.com	2024-03-01 08:30:00
4	Zavier Owen	zavior@pcsql.com	2024-03-02 21:12:00
6	Karlie Andrew	karlie@pscql.com	2024-03-13 23:41:17
9	Jaeden Malone	jaeden@pcsql.com	2024-03-14 01:02:48
24	Elle Bowers	elie@pcsql.com	2024-03-17 21:20:01

ORDERS 테이블:

order_iduser_idtotal_amountordered_at

1	2	209.27	2024-03-01 13:12:29
2	3	64.87	2024-03-02 18:08:40
3	2	29.97	2024-03-02 21:48:00
4	3	19.98	2024-03-14 00:01:23
5	6	300	2024-03-14 00:10:41
6	2	19.98	2024-03-17 00:12:29
10	24	359.9	2024-03-17 19:14:29
11	9	57.76	2024-03-17 21:22:41

• USER_ID가 2인 유저는 3번 주문을 했으므로 VIP입니다.
• USER_ID가 3인 유저는 2번 주문을 했으며, 총액의 합은 84.85 이므로 VIP가 아닙니다.
• USER_ID가 6인 유저는 총액의 합이 300.00이므로 VIP입니다.
• USER_ID가 9인 유저는 1번 주문을 했으며, 총액의 합이 57.76이므로 VIP가 아닙니다.
• USER_ID가 24인 유저는 총액의 합이 359.9이므로 VIP입니다.

따라서 SQL을 실행하면 다음과 같이 출력되어야 합니다.

user_iduser_name

2	Axel Mckee
6	Karlie Andrew
24	Elle Bowers

WITH EligibleUsers AS (
    SELECT 
        user_id
    FROM 
        orders
    GROUP BY 
        user_id
    HAVING 
        SUM(total_amount) >= 300
        OR COUNT(1) >= 3
)

SELECT 
    u.user_id,
    u.user_name
FROM 
    users u
JOIN 
    EligibleUsers eu ON u.user_id = eu.user_id
ORDER BY 
    u.user_id;

문제 설명

다음은 어느 쇼핑몰의 주문 기록 상세 내역을 담은 ORDER_DETAILS 테이블입니다. ORDER_DETAILS 테이블은 아래와 같은 구조로 되어있으며, ORDER_DETAIL_ID, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE_PER_UNIT은 각각 주문 상세의 id, 주문의 id, 제품의 id, 주문 수량, 주문 당시의 단가를 나타냅니다.

COLUMN NAMETYPENULLABLE

ORDER_DETAIL_ID	INTEGER	FALSE
ORDER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
QUANTITY	INTEGER	FALSE
PRICEPERUNIT	NUMERIC	FALSE

다음은 어느 쇼핑몰의 리뷰 내역을 담은 REVIEWS 테이블입니다. REVIEWS 테이블은 아래와 같은 구조로 되어있으며, REVIEW_ID, ORDER_DETAIL_ID, SCORE는 각각 리뷰의 id, 리뷰를 남긴 주문 기록 상세, 리뷰 점수를 나타냅니다.

COLUMN NAMETYPENULLABLE

REVIEW_ID	INTEGER	FALSE
ORDER_DETAIL_ID	INTEGER	FALSE
SCORE	INTEGER	FALSE

---

문제

각 리뷰별로 리뷰의 정보와 해당 상품의 평균 리뷰 점수를 구하려고 합니다. 리뷰의 id, 리뷰 점수, 리뷰한 상품의 id, 상품의 평균 리뷰 점수를 조회하는 SQL문을 작성해 주세요. 평균 리뷰 점수는 소수점 둘째자리까지 반올림해 나타내고, 각 컬럼의 이름은 review_id, score, product_id, product_score로 지정해주세요. 결과는 review_id 순으로 나열해야합니다.

---

예제

예를 들어 ORDER_DETAILS 테이블과 REVIEWS 테이블이 다음과 같다면

ORDER_DETAILS 테이블:

order_detail_idorder_idproduct_idquantityprice_per_unit

1	1	1	6	28.88
2	1	3	1	35.99
3	2	3	1	35.99
4	2	1	1	28.88
5	3	4	1	9.99
6	3	4	2	9.99
7	4	4	2	9.99
8	5	3	10	30
9	6	4	2	9.99
10	10	3	10	35.99
11	11	1	2	28.88

REVIEWS 테이블:

review_idorder_detail_idscore

2	1	4
3	2	1
11	3	4
4	4	5
10	6	4
6	7	3
9	8	3
1	9	3
7	11	3

• PRODUCT_ID 1에 달린 리뷰의 점수는 4, 5, 3점으로 평균을 소수점 2째자리까지 반올림하면 4입니다.
• PRODUCT_ID 3에 달린 리뷰의 점수는 1, 3, 4점으로 평균을 소수점 2째자리까지 반올림하면 2.67입니다.
• PRODUCT_ID 4에 달린 리뷰의 점수는 3, 3, 4점으로 평균을 소수점 2째자리까지 반올림하면 3.33입니다.

따라서 SQL을 실행하면 다음과 같이 출력되어야 합니다.

review_idscoreproduct_idproduct_score

1	3	4	3.33
2	4	1	4
3	1	3	2.67
4	5	1	4
6	3	4	3.33
7	3	1	4
9	3	3	2.67
10	4	4	3.33
11	4	3	2.67

WITH ProductReviews AS (
    SELECT 
        od.product_id,
        ROUND(AVG(r.score), 2) AS product_score
    FROM 
        ORDER_DETAILS od
    JOIN 
        REVIEWS r ON od.order_detail_id = r.order_detail_id
    GROUP BY 
        od.product_id
)

SELECT 
    r.review_id,
    r.score,
    od.product_id,
    pr.product_score
FROM 
    REVIEWS r
JOIN 
    ORDER_DETAILS od ON r.order_detail_id = od.order_detail_id
JOIN 
    ProductReviews pr ON od.product_id = pr.product_id
ORDER BY 
    r.review_id;

문제 설명

다음은 어느 웹사이트 계정의 이메일 정보를 저장하는 INFOS 테이블입니다. INFOS 테이블은 아래와 같은 구조로 되어있으며, EMAIL은 유저의 이메일을 나타냅니다.

COLUMN NAMETYPENULLABLE

EMAIL

VARCHAR

FALSE

---

문제

이메일의 아이디[1] 가 같은 계정은 동일한 인물이 사용하는 것이라고 가정할 수 있습니다. 예를 들어 이메일 주소 axel1234@gmail.com를 사용하는 사람과 이메일 주소 axel1234@naver.com를 사용하는 사람은 동일 인물일 수 있습니다. INFOS 테이블에서 여러 계정을 사용하는 사람의 수를 구하는 SQL 문을 작성해 주세요. 이때 컬럼 명은 NUM으로 지정해 주세요.

---

예시

예를 들어 INFOS 테이블이 다음과 같다면

email

axel1234@gmail.com

axel1234@naver.com

axel1234@pcsql.com

axel_pxi23@naver.com

axel_pxi23@kakao.com

bryce@pscql.com

zavior_rox@naver.com

karliejpax@qq.com

karliejpax@gmail.com

1.axel1234@gmail.com, axel1234@naver.com, axel1234@pcsql.com계정을 소유한 사람

1. axel_pxi23@naver.com, axel_pxi23@kakao.com 계정을 소유한 사람
2. bryce@pscql.com 계정을 소유한 사람
3. zavior_rox@naver.com 계정을 소유한 사람
4. karlie_j_pax@qq.com, karlie_j_pax@gmail.com 계정을 소유한 사람

이렇게 총 5명의 인물이 사이트를 사용한다고 볼 수 있으며, 이 중 여러 계정을 사용하는 사람의 수는 3명입니다. 따라서 SQL을 실행하면 다음과 같이 출력되어야 합니다.

NUM

3

[1] 이메일 아이디는 이메일 주소에서 '@' 앞의 문자만 추출한 것을 의미합니다.

WITH EmailIds AS (
    -- 이메일 주소에서 아이디 부분 추출
    SELECT 
        SUBSTRING_INDEX(email, '@', 1) AS email_id
    FROM 
        INFOS
)

SELECT 
    COUNT(DISTINCT email_id) AS NUM
FROM 
    (SELECT 
        email_id
    FROM 
        EmailIds
    GROUP BY 
        email_id
    HAVING 
        COUNT(*) > 1) AS multiple_accounts;

쿼리 설명:

1. WITH EmailIds AS (...):
   • EmailIds라는 CTE를 정의합니다.
   • SUBSTRING_INDEX(email, '@', 1) 함수를 사용하여 이메일 주소에서 '@' 앞의 부분(즉, 이메일 아이디)을 추출합니다.
2. 서브쿼리:
   • EmailIds CTE에서 email_id별로 그룹화합니다.
   • HAVING COUNT(*) > 1 조건을 사용하여 동일한 이메일 아이디를 가진 이메일 주소가 2개 이상인 경우만 선택합니다. 이는 여러 계정을 사용하는 사람을 식별하기 위한 것입니다.
3. 메인 쿼리:
   • 서브쿼리에서 DISTINCT email_id의 개수를 세어 NUM으로 반환합니다. 이 값은 여러 계정을 사용하는 사람의 수를 나타냅니다.

결과:

이 쿼리를 실행하면, 여러 계정을 사용하는 사람의 수를 NUM 컬럼으로 출력합니다. 예제 데이터에서 이 쿼리를 실행하면 다음과 같은 결과가 반환될 것입니다:

NUM

3

이 결과는 동일한 이메일 아이디로 여러 계정을 사용하는 사람이 총 3명임을 나타냅니다.