프로그래머스 | SQL 스터디 1 (IFNULL, USING)

프로그래머스에 있는 SQL 코딩테스트의 문제들을 각 챕터별로 1개씩 풀고 각자 파트를 나눠 문제에 대한 풀이를 하고 더 나은 쿼리를 짜는 방법을 공부하기 위해 SQL 스터디를 시작하게 되었습니다. 24.03.26일 부터 스터디 시작~!

SELECT

3월에 태어난 여성 회원 목록 출력하기(Lv2)

문제

MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.

풀이

SELECT로 ID, 이름, 성별, 생년월일을 조회

여기서 DATE_OF_BRITH는 DATE_FORMAT으로 형식 변경해주기!

WHERE문에서 요구하는 전화번호가 NULL인 경우는 출력대상에서 제외 와 3월인 여성 회원을 입력

ORDER BY에서 회원ID를 기준으로 오름차순 정렬 위해 MEMBER_ID 입력

SELECT  MEMBER_ID, MEMBER_NAME, 
	GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') DATE_OF_BIRTH 
FROM MEMBER_PROFILE 
WHERE TLNO IS NOT NULL 
	AND MONTH(DATE_OF_BIRTH) = '3' 
	AND GENDER = 'W'
ORDER BY MEMBER_ID;

SUM, MAX, MIN

가장 비싼 상품 구하기(Lv1)

문제

PRODUCT 테이블에서 판매 중인 상품 중 가장 높은 판매가를 출력하는 SQL문을 작성해주세요. 이때 컬럼명은 MAX_PRICE로 지정해주세요.

풀이

SELECT로 가장 높은 판매가를 조회 ( MAX(PRICE) )

여기서 AS를 이용하여 MAX_PRICE로 칼럼명 변경

SELECT MAX(PRICE) MAX_PRICE
FROM PRODUCT;

GROUP BY

조건에 맞는 사용자와 총 거래금액 조회하기(Lv3)

문제 설명

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

USED_GOODS_USER 테이블 : USER_ID, NICKNAME, CITY, STREET_ADDRESS1, STREET_ADDRESS2, TLNO는 각각 회원 ID, 닉네임, 시, 도로명 주소, 상세 주소, 전화번호를 를 의미합니다.

문제

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.

풀이

우선, USED_GOODS_BOARD는 B로 USED_GOODS_USER는 U로 지칭

SELECT에서 U.USER_ID, U.NICKNAME, SUM(B.PRICE) TOTAL_SALES 입력

두개의 테이블을 모두 사용해야하기 때문에 JOIN 구문으로 두 테이블의 공통 칼럼임 U.USER_ID와 B.WRITER_ID로 연결

WHERE에서 완료된 중고 거래를 구하기 위해 STATUS = 'DONE' 입력

GROUP BY에서 사람마다의 정보를 얻고자 하기에 U.USER_ID별로 그룹화

HAVING에서 GROUP BY의 조건인 총금액이 70만 원 이상인 사람이므로 SUM(B.PRICE) >= 700000 입력

ORDER BY에서 총거래금액을 기준으로 오름차순 정렬하기 위해 TOTAL_SALES입

SELECT U.USER_ID, U.NICKNAME, SUM(B.PRICE) TOTAL_SALES
FROM USED_GOODS_BOARD B
JOIN USED_GOODS_USER U ON U.USER_ID = B.WRITER_ID
WHERE STATUS = 'DONE'
GROUP BY U.USER_ID
HAVING SUM(B.PRICE) >= 700000
ORDER BY TOTAL_SALES;

IS NULL

경기도에 위치한 식품창고 목록 출력하기(Lv1)

문제

FOOD_WAREHOUSE 테이블에서 경기도에 위치한 창고의 ID, 이름, 주소, 냉동시설 여부를 조회하는 SQL문을 작성해주세요. 이때 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 주시고 결과는 창고 ID를 기준으로 오름차순 정렬해주세요.

풀이

SELECT에서 창고의 ID, 이름, 주소, 냉동시설 여부를 조회

# IFNULL 사용버전
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N')
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID;

IFNULL 함수

IFNULL 함수는 일부 데이터베이스 시스템에서 지원하는 함수로, 주로 MySQL에서 사용됩니다. 이 함수는 주어진 열이 null인지 확인하고, 만약 null이면 지정한 대체값을 반환합니다.

WHERE에서 주소가 경기도인 것을 조회하기 위해 (WHERE ADDRESS LIKE '경기도%')

ORDER BY에서 창고 ID 기준으로 오름차순 정렬

# IFNULL 사용버전 
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, 
	ADDRESS, IFNULL(FREEZER_YN, 'N') 
FROM FOOD_WAREHOUSE 
WHERE ADDRESS LIKE '경기도%' 
ORDER BY WAREHOUSE_ID;

CASE WHEN을 통해 FREEZER_YN이 NULL이면 N을 출력하고, 아니면 그대로 출력하게 설정

CASE WHEN FREEZER_YN IS NULL THEN 'N'

ELSE FREEZER_YN

END

# CASE WHEN 사용버전 
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, 
	CASE 
    	WHEN FREEZER_YN IS NULL THEN 'N' 
        ELSE FREEZER_YN 
    END 
FROM FOOD_WAREHOUSE 
WHERE ADDRESS LIKE '경기도%' 
ORDER BY WAREHOUSE_ID;

JOIN

주문량이 많은 아이스크림들 조회하기(Lv4)

문제 설명

다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과 7월의 아이스크림 주문 정보를 담은 JULY 테이블입니다. FIRST_HALF 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER는 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 상반기 아이스크림 총주문량을 나타냅니다. FIRST_HALF 테이블의 기본 키는 FLAVOR입니다. FIRST_HALF테이블의 SHIPMENT_ID는 JULY테이블의 SHIPMENT_ID의 외래 키입니다.

JULY 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER 은 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 7월 아이스크림 총주문량을 나타냅니다. JULY 테이블의 기본 키는 SHIPMENT_ID입니다. JULY테이블의 FLAVOR는 FIRST_HALF 테이블의 FLAVOR의 외래 키입니다. 7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다.

문제

7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

풀이

JULY는 J, FIRST_HALF는 F로 지칭

SELECT에서 맛을 조회해야하므로 SELECT J.FLAVOR

두 테이블을 모두 사용해야하므로, 공통 컬럼인 FLAVOR로 JOIN을 한다

맛 컬럼에서 중복되는 데이터 있으므로 GROUP BY를 통해 하나로 묶어줌

구하고 싶은 7월 주문량과 상반기 주문량을 더한 것을 기준으로 내림차순 정렬 → ORDER BY SUM(J.TOTAL_ORDER + F.TOTAL_ORDER) DESC

상위 3개만 조회해야하므로 LIMIT 3

SELECT J.FLAVOR
FROM JULY J
JOIN FIRST_HALF F ON F.FLAVOR = J.FLAVOR
GROUP BY J.FLAVOR
ORDER BY SUM(J.TOTAL_ORDER + F.TOTAL_ORDER) DESC
LIMIT 3;

String, Date

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기(Lv3)

문제 설명

다음은 중고거래 게시판 정보를 담은 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에서 구해야 하는 '기본적인 파일경로는 /home/grep/src/이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성' → SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH

JOIN으로 USED_GOODS_FILE을 연결, BOARD_ID가 겹치기에 USING으로 연결

WHERE에서 VIEWS가 가장 높은 것을 구하려고 하기에 서브쿼리로

WHERE VIEWS = (SELECT MAX(VIEWS)

FROM USED_GOODS_BOARD) 을 작성

ORDER BY에서 FILE_ID를 내림차순으로 정렬

SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD 
JOIN USED_GOODS_FILE USING (BOARD_ID)
WHERE VIEWS = (SELECT MAX(VIEWS)
              FROM USED_GOODS_BOARD)
ORDER BY FILE_ID DESC;

USING :

일반적으로 SQL에서는 ON 절을 사용하여 조인 조건을 명시하는데, 이는 일반적으로 두 테이블 간의 조인 조건이 서로 다른 이름을 가진 열들에 의해 수행될 때 사용됩니다. 그러나 USING을 사용하면 두 테이블 간의 조인 조건이 서로 동일한 이름을 가진 열에 의해 수행됩니다.

일반적으로 사용하는 JOIN ON

SELECT *
FROM table1
JOIN table2 ON table1.id = table2.id;

USING을 사용한 버전

SELECT *
FROM table1
JOIN table2 USING (id);

USING을 사용할 때 주의해야 할 몇 가지 사항이 있습니다

동일한 이름의 열 사용: USING을 사용하여 조인할 때는 두 테이블에서 동일한 이름의 열을 사용해야 합니다. 그렇지 않으면 오류가 발생합니다.
결과에 중복 열 포함: USING을 사용하면 조인 기준으로 사용된 열이 결과에 나타납니다. 예를 들어, 위의 예제에서 id 열이 조인 기준으로 사용되었기 때문에 결과에는 id 열이 한 번만 나타납니다. 이것이 의도한 동작이 아니라면 주의해야 합니다.
JOIN 종류에 따른 사용: USING은 INNER JOIN 또는 NATURAL JOIN과 함께 사용될 때 가장 효과적입니다. OUTER JOIN과 함께 사용하면 예상치 못한 결과가 발생할 수 있습니다.
가독성 저하: USING을 사용하면 조인하는 열이 명시적으로 표시되지 않기 때문에 가독성이 저하될 수 있습니다. 특히 조인에 사용된 열이 여러 개인 경우에는 가독성 문제가 더 커질 수 있습니다.

이러한 점들을 고려하여 USING을 적절하게 사용해야 합니다. 때로는 ON 절을 명시적으로 사용하는 것이 더 안전하고 이해하기 쉬울 수 있습니다.

'SQL > 프로그래머스' 카테고리의 다른 글

프로그래머스 \| SQL 스터디 6 (0)	2024.09.03
프로그래머스 \| SQL 스터디 5 (0)	2024.09.03
프로그래머스 \| SQL 스터디 4 (1)	2024.09.03
프로그래머스 \| SQL 스터디 3 (2)	2024.09.03
프로그래머스 \| SQL 스터디 2 (0)	2024.09.03

SELECT

3월에 태어난 여성 회원 목록 출력하기(Lv2)

SUM, MAX, MIN

가장 비싼 상품 구하기(Lv1)

GROUP BY

조건에 맞는 사용자와 총 거래금액 조회하기(Lv3)

IS NULL

경기도에 위치한 식품창고 목록 출력하기(Lv1)

IFNULL 함수

JOIN

주문량이 많은 아이스크림들 조회하기(Lv4)

String, Date

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기(Lv3)

'SQL > 프로그래머스' 카테고리의 다른 글

티스토리툴바