프로그래머스 SQL 고득점 Kit

Hanbi·2022년 9월 26일

SQL

목록 보기

2/2

SELECT > 평균 일일 대여 요금 구하기

SELECT ROUND(AVG(DAILY_FEE)) AS 'AVERAGE_FEE'
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE='SUV'
GROUP BY CAR_TYPE;

반올림 : ROUND(숫자, 자릿수)

SELECT > 3월에 태어난 여성 회원 목록 출력하기

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

WHERE 월 조건 : MONTH(날짜)=3
✨WHERE 조건 여러 개 AND로 연결

⚠️DATE_FORMAT으로 날짜 출력 형식 맞추기

SELECT > 모든 레코드 조회하기

SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

정렬 : ORDER BY

SELECT > 재구매가 일어난 상품과 회원 리스트 구하기

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*)>=2
ORDER BY USER_ID, PRODUCT_ID DESC;

GROUP BY 2개 이상
ex) 나누는 기준이 년, 월이면
20년 1월, 20년 2월, 20년 3월 이런 식으로 그룹화 됨

SELECT > 오프라인/온라인 판매 데이터 통합하기

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS 'SALES_DATE', PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'

UNION ALL

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS 'SALES DATE', PRODUCT_ID, NULL AS 'USER_ID', SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'

ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

데이터 합치기 : UNION(중복제외), UNION ALL(중복포함)
⚠️합치려는 데이터 column을 동일하게 맞춰야 함 !

SELECT > 어린 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
order by ANIMAL_ID

WHERE '같지 않다' 조건 : C랑 똑같이 !=

SELECT > 여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC	-- 이름순으로 정렬, 이름 같으면 날짜 역순으로 정렬하라는 의미

ORDER BY에 차례로 나열

SELECT > 상위 n개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

✨LIMIT

SELECT > 업그레이드 된 아이템 구하기

SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO
WHERE ITEM_ID IN (SELECT T.ITEM_ID
                FROM ITEM_INFO I, ITEM_TREE T
                WHERE RARITY = 'RARE' AND PARENT_ITEM_ID = I.ITEM_ID)
ORDER BY ITEM_ID DESC;

WHERE절 서브쿼리 다중행은 IN 단일행은 =

SELECT > 조건에 맞는 개발자 찾기

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & (SELECT SUM(CODE)
                   FROM SKILLCODES
                   WHERE NAME IN ('Python', 'C#'))
ORDER BY ID;

✨비트연산 & : 특정값을 포함하는 경우 찾을 때 //각 비트가 전부 1일 때만 1 반환
ex) 축구=1, 농구=2, 독서=4
취미가 축구 or 독서일 경우를 찾으면
5 & 1 => 1
5 & 2 => 0
4 & 4 => 4

SUM, MAX, MIN > 동물 수 구하기

SELECT COUNT(*) AS 'count'
FROM ANIMAL_INS

SELECT에서 COUNT 집계함수 사용

SUM, MAX, MIN > 중복 제거하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS

중복제거 : column명 앞에 DISTINCT
집계함수에서 null 값은 no count

GROUP BY > 식품분류별 가장 비싼 식품의 정보 조회하기

SELECT A.AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(SALES*PRICE) AS 'TOTAL_SALES'
FROM BOOK_SALES BS LEFT JOIN BOOK B ON BS.BOOK_ID=B.BOOK_ID
                    LEFT JOIN AUTHOR A ON B.AUTHOR_ID=A.AUTHOR_ID
WHERE SALES_DATE LIKE '2022-01%'
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC;

다중테이블 JOIN : 어렵게 생각할 필요없이 규칙 맞춰서 연달아 JOIN 해주면 됨

GROUP BY > 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

SELECT MONTH(START_DATE) AS 'MONTH', CAR_ID, COUNT(*) AS 'RECORDS'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE MONTH(START_DATE) IN (8, 9, 10)
                GROUP BY CAR_ID
                HAVING COUNT(*) >= 5)
            AND MONTH(START_DATE) IN (8, 9, 10)
GROUP BY MONTH(START_DATE), CAR_ID
HAVING COUNT(*) > 0
ORDER BY MONTH(START_DATE), CAR_ID DESC;

대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요.

⚠️문제를 잘 읽자... 서브쿼리 잘 짜놓고 대여 종료일을 기준으로 풀어서 틀림 ;

GROUP BY > 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

SELECT CAR_ID, IF(SUM(IF('2022-10-16' BETWEEN START_DATE AND END_DATE, 1, 0))>0, '대여중', '대여 가능') AS 'AVAILABILITY'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

날짜 범위 조건 : ✨날짜 BETWEEN 시작범위 AND 종료범위
동일한 자동차에 대여 기록이 여러 개 있는 경우, 전부 대여하고 있지 않을 때만 대여 가능이므로 IF절 중첩해야 함
IF(SUM(IF('2022-10-16' BETWEEN START_DATE AND END_DATE), 1, 0)>0, '대여중', '대여 가능')

GROUP BY > 고양이와 개는 몇 마리 있을까

SELECT ANIMAL_TYPE, COUNT(*) AS 'count'
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

GROUP BY : 특정 column을 기준으로 그룹화시킴
순서 : GROUP BY ➡️HAVING ➡️ORDER BY

GROUP BY > 동명 동물 수 찾기

SELECT NAME, COUNT(*) AS 'COUNT'
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*) >= 2
ORDER BY NAME

동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요.

이때 결과는 이름이 없는 동물은 집계에서 제외하며,
결과는 이름 순으로 조회해주세요.

⚠️테스트 결과값이 맞게 나왔어도 문제 조건 전부 처리해줘야 함

HAVING : 집계함수로 조건비교 할 때 (WHERE은 불가능)
NULL값 제외 : WHERE COLUMN명 IS NOT NULL

GROUP BY > 입양 시각 구하기(1)

SELECT HOUR(DATETIME) AS 'HOUR', COUNT(*) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY HOUR
ORDER BY HOUR

날짜 데이터에서 일부를 추출해야 하는데 방법을 몰라서 솔루션 찾음

✨HOUR 함수 : DATETIME에서 hour 추출
WHERE에서는 Alias 사용 불가능 : HOUR 대신 HOUR(DATETIME)로 조건 비교

GROUP BY > 년, 월, 성별 별 상품 구매 회원 수 구하기

SELECT YEAR(SALES_DATE) AS 'YEAR', MONTH(SALES_DATE) AS 'MONTH', GENDER, COUNT(DISTINCT INFO.USER_ID) AS 'USERS'
FROM USER_INFO INFO JOIN ONLINE_SALE SALE ON INFO.USER_ID=SALE.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;

년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요.
➡️GROUP BY YEAR, MONTH, GENDER
➡️COUNT(USER_ID)

동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.
➡️중복 제거 : COUNT( DISTINCT USER_ID)

⚠️잘 보면 문제에 답이 있다.
⚠️JOIN 할 때 뒤에 ON A.ID=B.ID 까먹지 말기

GROUP BY > 입양 시각 구하기(2)

count가 0인 값도 테이블에 포함해야 하는데 방법을 몰라서 솔루션 찾음

데이터베이스에 없는 시간까지 포함해서 0부터 23시까지 만들어줘야 함 ➡️0부터 23까지의 테이블 생성

#step 1
SET @HOUR := -1;
SELECT (@HOUR := @HOUR+1) AS HOUR
FROM ANIMAL_OUTS
WHERE @HOUR < 23	-- 23 미만일 때까지 대입되어 HOUR 22일 때 멈추고, 22는 23이 됨

✨SET 함수 사용
변수 지정 : @변수명
대입 : :=

count한 테이블 생성

#step 2
SET @HOUR := -1;
SELECT (@HOUR := @HOUR+1) AS HOUR, (SELECT COUNT(*)
                                   FROM ANIMAL_OUTS
                                   WHERE HOUR(DATETIME) = @HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23

HOUR 변수와 시간이 같을 때, 카운트 : WHERE HOUR(DATETIME) = @HOUR

GROUP BY > 가격대 별 상품 개수 구하기

SELECT TRUNCATE(PRICE, -4) AS 'PRICE_GROUP', COUNT(PRODUCT_ID) AS 'PRODUCTS'
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

가격대별로 GROUP BY를 해줘야 하는데 가격대는 TRUNCATE(PRICE, -4)로 표현 가능
✨TRUNCATE(숫자, 버릴 자릿 수)
ex) TRUNCATE(1234.56789, 1) // 1234.5
ex) TRUNCATE(1234.56789, 4) // 1234.5678
ex) TRUNCATE(1234.56789, -1) // 1230
ex) TRUNCATE(1234.56789, -2) // 1200

IS NULL > NULL 처리하기

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS

✨IFNULL 함수 : column 값이 null일 때, 다른 값을 반환하도록
SELECT IFNULL(column명, '대체할 값')

IS NULL > 업그레이드 할 수 없는 아이템 구하기

SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO
WHERE ITEM_ID NOT IN (SELECT PARENT_ITEM_ID
                     FROM ITEM_TREE
                     WHERE PARENT_ITEM_ID IS NOT NULL)
ORDER BY ITEM_ID DESC;

WHERE절 NOT IN 조건 : NULL값이 포함되는 경우, 값이 출력되지 않음 ➡️조회 칼럼에 IS NOT NULL 조건 줘야 함

JOIN > 없어진 기록 찾기

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS LEFT OUTER JOIN ANIMAL_INS INS ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID

JOIN : TABLE_A A JOIN TABLE_B B ON A.KEY 값=B.KEY 값
- JOIN : key값이 같은 데이터만 가져옴
- LEFT OUTER JOIN (좌측 정보 손실 방지) : 좌측 테이블 값 전부 나타내고, key값 다르면 null로 표시
- RIGHT OUTER JOIN (우측 정보 손실 방지) : 우측 테이블 값 전부 나타내고, key값 다르면 null로 표시
- FULL OUTER JOIN (양쪽 전부 정보 손실 방지) : 양쪽 테이블 값 전부 나타내고, key값 다르면 null로 표시
OUTS에 있고 INS에 없는 데이터를 찾아야 함 ➡️결과에 OUTS 데이터가 무조건 존재 ➡️LEFT OUTER JOIN 사용

JOIN > 보호소에서 중성화한 동물

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.SEX_UPON_INTAKE LIKE 'Intact %' AND (OUTS.SEX_UPON_OUTCOME LIKE 'Spayed %' OR OUTS.SEX_UPON_OUTCOME LIKE 'Neutered %')
ORDER BY INS.ANIMAL_ID

WHERE에서 sub_string과 match 되는지 확인 : ✨like

String, Date > 자동차 대여 기록 별 대여 금액 구하기

WITH RENT_INFO AS
    (SELECT HISTORY_ID, DATEDIFF(END_DATE, START_DATE)+1 AS 'RENT_DAYS', DAILY_FEE, CAR_TYPE,
        CASE
            WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 7 THEN '7일 이상'
            ELSE ''
        END AS 'DURATION_TYPE'
     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID=C.CAR_ID
     WHERE CAR_TYPE='트럭')


SELECT RENT_INFO.HISTORY_ID, ROUND(RENT_INFO.DAILY_FEE * RENT_INFO.RENT_DAYS * (100-IFNULL(PLAN.DISCOUNT_RATE, 0))/100) AS 'FEE'
FROM RENT_INFO LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN ON PLAN.DURATION_TYPE=RENT_INFO.DURATION_TYPE AND PLAN.CAR_TYPE=RENT_INFO.CAR_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC;

1번 자동차 대여 기간이 90일 이상 일 때는 15% 할인 / 30일 : 10% / 7일 : 5% 중에 해당하는 경우로 가격을 구해야하는데 테이블 조인하니까 각각의 자동차에 3가지 데이터가 나왔다.
➡️임시 테이블 만들어서 '90일 이상' 이 부분을 join 조건으로 만들어줘야 함

⚠️
5000원에 5% 할인된 금액 : 5000 * 0.05 = 5000 * (100 - DISCOUNT_RATE / 100)
➡️NULL값을 100으로 나누면 안되니까 IFNULL함수로 처리해줘야 함

with절로 가상 테이블 만들기 (만든 테이블 쿼리문에서 사용 가능)

WITH 가상테이블명 AS
    (SELECT
    FROM
    WHERE)

case 조건문 : 형식 주의

SELECT NAME,
    CASE
        WHEN point > 1000 THEN '상'
        WHEN point > 500 THEN '중'
        ELSE '하'
    END AS 'LEVEL'
FROM TABLE

String, Date > 자동차 평균 대여 기간 구하기

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS 'AVERAGE_DURATION'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

날짜 차이 구하기 : ✨DATEDIFF ➡️평균 구하기 : AVG ➡️반올림 : ROUND

String, Date > 조건에 맞는 사용자 정보 조회하기

SELECT USER_ID, NICKNAME, CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS '전체주소', CONCAT(SUBSTRING(TLNO, 1, 3), '-', SUBSTRING(TLNO, 4, 4), '-', SUBSTRING(TLNO, 8, 4)) AS '전화번호'
FROM USED_GOODS_USER
WHERE USER_ID IN (SELECT WRITER_ID
                  FROM USED_GOODS_BOARD
                  GROUP BY WRITER_ID
                  HAVING COUNT(*) >= 3)
ORDER BY USER_ID DESC;

문자열 이어붙이기 : ✨CONCAT(first_name, ' ', last_name)
문자열 자르기 : ✨SUBSTRING(문자열, 시작위치, 길이) // SQL은 index 1부터 시작함
WHERE절 서브쿼리 형태

// 서브쿼리 값 여러개
WHERE A.name IN (SELECT B.name
                          FROM 테이블
                          WHERE 조건)

// 서브쿼리 값 한개
WHERE A.name = (SELECT B.name
                          FROM 테이블
                          WHERE 조건)

String, Date > 루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')

WHERE에서 비교해야 하는 값이 여러 개일 때 : ✨IN ➡️집합 개념

String, Date > 중성화 여부 파악하기

SELECT ANIMAL_ID, NAME, IF(SEX_UPON_INTAKE LIKE 'Intact %', 'X', 'O') AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

조건문 : IF(조건, 참 일때의 값, 거짓일 때의 값) ➡️SELECT에서 사용

String, Date > 오랜 기간 보호한 동물(2)

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS INS JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY OUTS.DATETIME - INS.DATETIME DESC -- 보호 기간이 긴 순서니까 DESC
LIMIT 2

✨두 날짜의 차이 값 바로 ORDER BY에서 사용할 수 있음

String, Date > DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

✨DATE_FORMAT(DATETIME, '%Y-%m-%d') // 2018-01-22

구분기호

년 : %Y(4자리 연도), %y(2자리 연도)
월 : %m
일 : %d

시 : %H(24시간), %h(12시간)
분 : %i
초 : %s

Hanbi

👩🏻‍💻

이전 포스트

프로그래머스 SQL 고득점 Kit

SQL

SELECT > 평균 일일 대여 요금 구하기

SELECT > 3월에 태어난 여성 회원 목록 출력하기

SELECT > 모든 레코드 조회하기

SELECT > 재구매가 일어난 상품과 회원 리스트 구하기

SELECT > 오프라인/온라인 판매 데이터 통합하기

SELECT > 어린 동물 찾기

SELECT > 여러 기준으로 정렬하기

SELECT > 상위 n개 레코드

SELECT > 업그레이드 된 아이템 구하기

SELECT > 조건에 맞는 개발자 찾기

SUM, MAX, MIN > 동물 수 구하기

SUM, MAX, MIN > 중복 제거하기

GROUP BY > 식품분류별 가장 비싼 식품의 정보 조회하기

GROUP BY > 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

GROUP BY > 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

GROUP BY > 고양이와 개는 몇 마리 있을까

GROUP BY > 동명 동물 수 찾기

GROUP BY > 입양 시각 구하기(1)

GROUP BY > 년, 월, 성별 별 상품 구매 회원 수 구하기

GROUP BY > 입양 시각 구하기(2)

GROUP BY > 가격대 별 상품 개수 구하기

IS NULL > NULL 처리하기

IS NULL > 업그레이드 할 수 없는 아이템 구하기

JOIN > 없어진 기록 찾기

JOIN > 보호소에서 중성화한 동물

String, Date > 자동차 대여 기록 별 대여 금액 구하기

String, Date > 자동차 평균 대여 기간 구하기

String, Date > 조건에 맞는 사용자 정보 조회하기

String, Date > 루시와 엘라 찾기

String, Date > 중성화 여부 파악하기

String, Date > 오랜 기간 보호한 동물(2)

String, Date > DATETIME에서 DATE로 형 변환

SQL 정리

0개의 댓글