SQL

64번 - 66번

whateveryouwish 2024. 8. 8. 15:37

64번

문제 설명
PLACES 테이블은 공간 임대 서비스에 등록된 공간의 정보를 담은 테이블입니다.
PLACES 테이블의 구조는 다음과 같으며 ID, NAME, HOST_ID는 각각
공간의 아이디, 이름, 공간을 소유한 유저의 아이디를 나타냅니다. ID는 기본키입니다.

문제
이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다.
헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.

 

group by & having 절의 사용과 서브쿼리, count()를 활용할 수 있어야 한다.

 

select id, name, host_id
from places
where host_id in (
    SELECT host_id
    from places
    group by host_id
    having count(*) >= 2
)
order by id

 

아래는 where 절을 사용한 방법

 

SELECT p.id, p.name, p.host_id
FROM places p
JOIN (
    SELECT host_id
    FROM places
    GROUP BY host_id
    WHERE COUNT(*) >= 2
) AS subquery ON p.host_id = subquery.host_id
ORDER BY p.id;

 

65번

 

 

문제 설명
CART_PRODUCTS 테이블은 장바구니에 담긴 상품 정보를 담은 테이블입니다.
CART_PRODUCTS 테이블의 구조는 ID, CART_ID, NAME, PRICE는 각각
테이블의 아이디, 장바구니의 아이디, 상품 종류, 가격을 나타냅니다.


데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다.
우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요.
이때 결과는 장바구니의 아이디 순으로 나와야 합니다.

 

select cart_id
from cart_products
where name in ('milk', 'yogurt')
group by cart_id
having count(distinct name) = 2
order by cart_id

 

SQL 쿼리의 각 부분에 대한 자세한 설명입니다.

 
SELECT cart_id
  • SELECT: 조회할 열을 지정합니다. 여기서는 cart_id를 선택하고 있습니다. 즉, 결과로 장바구니의 아이디를 반환합니다.
 
FROM cart_products
  • FROM: 데이터를 가져올 테이블을 지정합니다. 이 경우 cart_products 테이블에서 데이터를 조회합니다.
 
WHERE name IN ('Milk', 'Yogurt')
  • WHERE: 조건을 설정하여 필터링합니다. 여기서는 name 열이 'Milk' 또는 'Yogurt'인 행만 선택합니다. 즉, 우유 또는 요거트가 포함된 장바구니 항목을 찾습니다.
 
GROUP BY cart_id
  • GROUP BY: 데이터 집합을 cart_id를 기준으로 그룹화합니다. 이렇게 하면 같은 장바구니에 속하는 상품들이 하나의 그룹으로 묶입니다.
 
HAVING COUNT(DISTINCT name) = 2
  • HAVING: GROUP BY로 그룹화된 데이터에 대해 조건을 설정합니다. COUNT(DISTINCT name)은 각 그룹(장바구니)의 고유한 상품 이름의 수를 세는 것입니다. 여기서 = 2는 'Milk'와 'Yogurt', 두 가지 상품이 모두 있어야 함을 의미합니다. 즉, 해당 장바구니에 두 종류의 상품이 모두 포함된 경우만 선택됩니다.
 
ORDER BY cart_id;
  • ORDER BY: 결과를 정렬하는 부분입니다. 여기서는 cart_id를 기준으로 오름차순으로 정렬합니다. 결과적으로 장바구니 아이디가 작은 순서부터 큰 순서로 정렬된 결과를 얻습니다.

 

# Distinct가 들어가야 하는 이유

 

DISTINCT 키워드는 SQL에서 중복된 값을 제거하고 고유한 값을 선택할 때 사용됩니다. 여기서 DISTINCT가 필요한 이유를 자세히 설명하겠습니다.

쿼리 맥락

쿼리에서 HAVING COUNT(DISTINCT name) = 2를 사용한 이유는 다음과 같습니다:

  1. 중복 상품 처리:
    • 만약 하나의 장바구니(cart_id)에 우유(Milk)와 요거트(Yogurt)가 여러 번 담겨 있다면, name 필드에서 'Milk'와 'Yogurt'가 각각 여러 번 나타날 수 있습니다. 예를 들어, 한 장바구니에 "Milk", "Milk", "Yogurt"가 있다고 가정할 때, COUNT(name)를 사용하면 3이 결과로 나오게 됩니다.
  2. 고유한 상품 수 세기:
    • DISTINCT를 사용하면 중복을 제거하여 고유한 상품의 수를 셉니다. 위 예시의 경우 COUNT(DISTINCT name)를 사용하면 'Milk'와 'Yogurt'가 각각 1회씩만 카운트되므로 결과는 2가 됩니다. 이는 우리가 원하는 결과입니다. 즉, 장바구니에 'Milk'와 'Yogurt'가 동시에 존재하는지를 확인하는 데에 적합합니다.
  3. 조건 만족 확인:
    • HAVING COUNT(DISTINCT name) = 2는 장바구니에 'Milk'와 'Yogurt' 두 종류의 상품이 모두 포함되었는지를 확인하는 조건입니다. 만약 DISTINCT 없이 COUNT(name)을 사용하면, 중복된 상품이 있을 경우 조건이 충족되지 않을 수 있습니다. 따라서 정확한 결과를 얻기 위해서 DISTINCT를 사용하는 것이 중요합니다.

요약

  • DISTINCT는 중복된 값을 제거하여 고유한 값만을 세기 위해 사용됩니다.
  • 이 쿼리에서는 두 가지 상품이 모두 존재하는지를 확인하기 위해 중복을 고려해야 하므로 DISTINCT를 사용합니다.
  • 이를 통해 정확하게 장바구니에 'Milk'와 'Yogurt'가 모두 포함된 경우만을 필터링할 수 있습니다.

 

66번

 

문제 설명

중고거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과
중고거래 게시판 첨부파일 정보를 담은 USED_GOODS_FILE 테이블입니다.

USED_GOODS_BOARD 테이블은
BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS은
게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.


USED_GOODS_FILE 테이블은
FILE_ID, FILE_EXT, FILE_NAME, BOARD_ID는
각각 파일 ID, 파일 확장자, 파일 이름, 게시글 ID를 의미합니다.


문제
USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서
조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요.
첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요.
기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고,
파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요.
조회수가 가장 높은 게시물은 하나만 존재합니다.

 

 

SELECT concat('/home/grep/src/', b.board_id, '/', file_id, file_name, file_ext) as file_path
from used_goods_file f join used_goods_board b on f.board_id = b.board_id
where views in (
        select max(views)
        from used_goods_board
        )
order by file_id desc

 

 

쿼리 설명

  1. SELECT:
    • CONCAT('/home/grep/src/', b.board_id, '/', f.file_id, f.file_name, f.file_ext) AS file_path:
      • 기본 파일 경로와 게시글 ID, 파일 ID, 파일 이름, 파일 확장자를 결합하여 최종 파일 경로를 생성합니다. 이 결과를 file_path라는 별칭으로 지정합니다.
  2. FROM:
    • used_goods_file f:
      • 첨부파일 정보를 담고 있는 USED_GOODS_FILE 테이블을 f라는 별칭으로 사용합니다.
  3. JOIN:
    • JOIN used_goods_board b ON f.board_id = b.board_id:
      • USED_GOODS_BOARD 테이블을 b라는 별칭으로 사용하여 board_id를 기준으로 두 테이블을 조인합니다. 이를 통해 게시물에 대한 정보를 첨부파일과 연결합니다.
  4. WHERE:
    • WHERE views = (SELECT Max(views) FROM used_goods_board):
      • 조회수가 가장 높은 게시물만 선택하기 위해 views가 서브쿼리에서 반환한 최대 조회수와 일치하는 경우를 필터링합니다. 서브쿼리는 USED_GOODS_BOARD 테이블에서 최대 조회수를 찾습니다.
  5. ORDER BY:
    • ORDER BY f.file_id DESC:
      • 최종 결과를 파일 ID 기준으로 내림차순 정렬합니다. 즉, 가장 큰 파일 ID가 먼저 나타납니다.

실행 결과

이 쿼리를 실행하면 조회수가 가장 높은 게시물에 대한 모든 첨부파일의 경로가 /home/grep/src/B0001/IMG_000001photo1.jpg와 같은 형식으로 출력됩니다. 각 파일 경로는 게시글 ID에 따라 구분되며, 모든 파일이 포함됩니다.