[Programmers] SQL 문제들

https://school.programmers.co.kr/learn/courses/30/lessons/164670

프로그래머스

코드

# 중고 거래 게시물을 3건 이상 등록한 사용자
# 회원 ID 기준으로 내림차순 정렬

SELECT USER_ID, NICKNAME, CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS 전체주소,
        CONCAT(LEFT(TLNO, 3), '-', MID(TLNO, 4, 4), '-', RIGHT(TLNO, 4)) AS 전화번호
FROM USED_GOODS_BOARD AS UGB
JOIN USED_GOODS_USER AS UGU
ON UGU.USER_ID = UGB.WRITER_ID
GROUP BY WRITER_ID
HAVING COUNT(BOARD_ID) >= 3
ORDER BY WRITER_ID DESC

복습할 것

mysql 전화번호 하이폰 넣기

CONCAT(LEFT(TLNO, 3), '-', MID(TLNO, 4, 4), '-', RIGHT(TLNO, 4))

 

 

https://school.programmers.co.kr/learn/courses/30/lessons/59413 

프로그래머스

코드 전체 복습하기 ⭐️

# ANIMAL_OUTS
# 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요.
# 결과 시간대 순으로 정렬


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

실행 결과

• SET: 사용자 정의 변수로, SET @변수이름 = 대입값; 으로 변수 지정 (위 문제에서는 변수 HOUR를 -1으로 지정하고 HOUR을 -1부터 +1씩 증가시킨다는 뜻(0~23))

 

https://school.programmers.co.kr/learn/courses/30/lessons/131116#qna

프로그래머스

코드

# 가격 젤 비싼 식품 조회
# '과자', '국', '김치', '식용유' 만 추력
# 식품 가격 기준으로 내림차순 정렬

# 오답
# SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
# FROM FOOD_PRODUCT
# WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
# GROUP BY CATEGORY
# ORDER BY MAX_PRICE DESC

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY IN ('과자', '국', '김치', '식용유') GROUP BY CATEGORY)
ORDER BY MAX_PRICE DESC

저번부터 헷갈렸던 문제이다.

오답 코드처럼 하면, 가격만 최댓값이 출력될 뿐 다른 열은 해당 가격에 대응되는 값이 아니다.

 

따라서, 아래처럼 카테고리, 가격을 함께 조건절에 넣어줘야 한다.

여기선 카테고리로 그룹화 했을 때 각 카테고리엔 최댓값이 하나라 저렇게 쓸 수 있다.

카테고리, 가격이 정해지면 나머지는 정해지기 때문에.

 

 

https://school.programmers.co.kr/learn/courses/30/lessons/131534

프로그래머스

문제

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

 

풀이 ⭐️다시 풀어보⭐️

# 2021년에 가입한 전체 회원들 중 상품 구매한 회원수와 상품 구매한 회원의 비율
# 년, 월별로 출력
# 상품 구매한 회원의 비율 소수점 두번째 자리에서 반올림, 전체 결과는 년을 기준으로 오름차순 정렬, 월 기준으로 오름차순

# 오답 풀이
# SELECT YEAR(JOINED) AS YEAR, MONTH(JOINED) AS MONTH, COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS, ROUND(COUNT(DISTINCT OS.USER_ID) / COUNT(UI.USER_ID), 1) AS PURCHASED_RATIO
# FROM USER_INFO AS UI, ONLINE_SALE AS OS
# WHERE YEAR(UI.JOINED)=2021 AND YEAR(UI.JOINED) = YEAR(OS.SALES_DATE) AND MONTH(UI.JOINED) = MONTH(OS.SALES_DATE)
# GROUP BY YEAR, MONTH
# ORDER BY YEAR, MONTH  


SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS, ROUND(COUNT(DISTINCT OS.USER_ID) / (SELECT COUNT(USER_ID) FROM USER_INFO WHERE YEAR(JOINED) = 2021), 1) AS PURCHASED_RATIO
FROM ONLINE_SALE AS OS
WHERE USER_ID IN (SELECT USER_ID FROM USER_INFO WHERE YEAR(JOINED)=2021)
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

 

처음 from 절에 두 테이블을 두고 그룹바이를 했는데, 그렇게 하면 안된다.

판매 테이블에는 애초에 가입한 정보가 없기 때문에 where절로 서브쿼리를 저렇게 만들어서 가입한 연도가 2021인 아이디만 추리거나

조인을 하거나 하는 등 처리를 해줘야 한다.

sales_date랑 joined는 아예 다른 칼럼인데 왜 저렇게 해줬지 ???

생각을 하고 풀자 ! 

---

 기타함수 

• 절댓값 : ABS
• 올림 / 버림 / 반올림 : CEIL, FLOOR, ROUND
• 제곱 : POW

SELECT ABS(-100) ...
SELECT CEIL(10, 1) ... -- 소수점 첫째자리에서 올림
SELECT FLOOR(10, 1) ... -- 소수점 첫째자리에서 버림
SELECT ROUND(10, 1) ... -- 소수점 첫째자리에서 반올림
SELECT POW(10, 2) ... -- 첫 번째 숫자를 두 번째 숫자만큼 제곱

---

GROUP BY

가격대 별 상품 개수 구하기

# 만원 단위의 가격대 별로 상품 개수를 출력
# 가격대 오름차순 정렬

select (case when price < 10000 then 0
        else truncate(price, -4)
        end) as price_group, count(product_id) as products
from product 
group by price_group
order by price_group

배운것

1. 

• TRUNCATE(column, 숫자): 숫자를 버릴 자릿수 아래로 버림 (숫자가 음수일 경우, 정수에 뒤에서부터 지정된 자릿수까지 0으로 처리)

2. 

case when ~ then ~ else ~ end 문도 기억해두자.

CASE   
	WHEN 조건1 THEN 결과값1 
	WHEN 조건2 THEN 결과값2 
	ELSE 결과값3 
END

진료과별 총 예약 횟수 출력하기

 

-- 코드를 입력하세요
SELECT mcdp_cd as 진료과코드, count(apnt_no) as '5월예약건수'
from appointment
where date_format(apnt_ymd, '%Y-%m') = '2022-05'
group by mcdp_cd
order by 2, 1

date_format 기억

order by 할 때 되도록이면 숫자로 써주기. 문자열로 쓰니 틀렸다고 나왔다.

 

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

프로그래머스

SELECT CAR_TYPE, COUNT(CAR_ID) as CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP '시트'
# WHERE OPTIONS LIKE '%시트%'
# WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
GROUP BY 1
ORDER BY 1

• REGEXP '가|나|다|라': 컬럼에 '가' 또는 '나' 또는 '다' 를 포함한 레코드 모두 출력
  (= where column like '%가%' or column like '%나%' or column like '%다%')
• REGEXP '가': 컬럼에 '가'를 포함한 모든 레코드 출력
  (= where column like '%가%')

 

즐겨찾기가 가장 많은 식당 정보 출력하기 ⭐️ Rank() over (partition by 그룹핑 column order by 정렬) 복습

프로그래머스

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (SELECT *, RANK () OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RK 
    FROM REST_INFO) AS R
WHERE R.RK = 1
ORDER BY 1 DESC

• RANK() OVER (PARTITION BY 그룹핑 column ORDER BY 정렬 column): 순위 구할 때 사용하는 함수로, PARTITION BY는 특정 컬럼 기준으로 그룹핑한다는 의미, ORDER BY는 특정 컬럼 기준으로 정렬한다는 의미

 

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

프로그래머스

여기도 Rank 함수 사용 가능

(기존 풀이)

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY IN ('과자', '국', '김치', '식용유') GROUP BY CATEGORY)
ORDER BY MAX_PRICE DESC

rank() over (partition by ~ order by ~) 사용

select category, price as max_price, product_name
from (select *, rank() over (partition by category order by price desc) as rk from food_product) as fp
where fp.rk = 1 and category in ('과자', '국', '김치', '식용유')
order by max_price desc

 

자동차 대여 기록에서 장기/단기 대여 구분하기

프로그래머스

 

# 맞왜틀?
select history_id, car_id, date_format(start_date, '%Y-%m-%d') as start_date, date_format(end_date, '%Y-%m-%d') as end_date, (case when datediff(end_date, start_date) >= 30 then '장기 대여' else '단기 대여' end) as rent_type
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where date_format(start_date, '%Y-%m') = '2022-09'
order by history_id desc

# 정답
select history_id, car_id, date_format(start_date, '%Y-%m-%d') as start_date, date_format(end_date, '%Y-%m-%d') as end_date, (case when datediff(end_date, start_date)+1 >= 30 then '장기 대여' else '단기 대여' end) as rent_type
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where date_format(start_date, '%Y-%m') = '2022-09'
order by history_id desc

Level1문제지만 모르는게 있었다.

• DATEDIFF(날짜1, 날짜2): 날짜 차이 계산하는 함수

이 함수를 쓸 때 여기선 10/2~10/5 이라면 총 2, 3, 4, 5 이렇게 4일을 기대하는데 저 함수는 3을 반환한다.

따라서 +1을 해줘야 기대하는 값을 얻을 수 있다. 주의!

 

 

카테고리 별 상품 개수 구하기

프로그래머스

문제

PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

-- 코드를 입력하세요
SELECT substr(product_code, 1, 2) as category, count(product_id) as products
from product
group by category
order by category

• SUBSTR(문자열, 시작 위치값): 시작 위치값 숫자 (5라면 5번째) 문자열부터 읽으라는 뜻
• SUBSTR(원본 문자열,시작 위치값,가져올 길이값): 시작 위치값 숫자 (5라면 5번째) 문자열부터 읽고, 가져올 길이값 (3이라면 3글자를) 가져오라는 뜻

시작 위치는 1부터 !

 

조건별로 분류하여 주문상태 출력하기

프로그래머스

 

select order_id, product_id, date_format(out_date, '%Y-%m-%d') as out_date,
    (case when out_date is null then '출고미정'
        when date_format(out_date, '%Y-%m-%d') <= '2022-05-01' then '출고완료'
        else '출고대기' end)as '출고여부'
from food_order
order by order_id

date_format(out_date, '%Y-%m-%d') <= '2022-05-01'

날짜 비교

 

 

 

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

# 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해 대여 기록 별로 대여 금액

select history_id, (case when rent_days >= 90 then floor(daily_fee*rent_days*(85/100))
                   when rent_days >= 30 then floor(daily_fee*rent_days*(92/100))
                    when rent_days >= 7 then floor(daily_fee*rent_days*(95/100))
                   else daily_fee*rent_days end )as fee
from CAR_RENTAL_COMPANY_CAR as crcc
join (select car_id, history_id, sum(datediff(end_date, start_date)+1) as rent_days from CAR_RENTAL_COMPANY_RENTAL_HISTORY group by history_id) as ccrh
on crcc.car_id = ccrh.car_id
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN as crcdp
on crcc.car_type = crcdp.car_type
where crcc.car_type = '트럭'
group by history_id
order by fee desc, history_id desc

 

 

12세 이하인 여자 환자 목록 출력하기

프로그래머스

select pt_name, pt_no, gend_cd, age, ifnull(tlno, 'NONE') as tlno
from patient
where gend_cd = 'W' and age <= 12
order by age desc, pt_name


# 혹은 

select pt_name, pt_no, gend_cd, age, COALESCE(tlno, 'NONE') as tlno
from patient
where gend_cd = 'W' and age <= 12
order by age desc, pt_name

• COALESCE(A, B, C): A가 NULL이 아니면 A 리턴, B가 NULL이 아니면 B 리턴, 둘 다 아닐 경우 C 리턴
• IFNULL(column, 값): 해당 컬럼이 null 이라면 특정 값 반환

 

 

 

---

Solve SQL

Solve SQL Code Challenges

해커랭크 SQL 문제 정리

 

Weather Observation Station 3

Weather Observation Station 3 | HackerRank

select distinct city
from station
where id % 2 = 0

• 연산자 산술 연산자 %: 나머지 연산으로, 왼쪽의 값을 오른쪽 값으로 나눈 나머지를 반환

 

Weather Observation Station 5

Weather Observation Station 5 | HackerRank

 

Weather Observation Station 6

Weather Observation Station 6 | HackerRank

select distinct city
from station
where left(city, 1) in ('a', 'e', 'i', 'o', 'u')


select distinct city
from station
where regexp_like(city, '^[aeiou]')


select distinct city
from station
where city regexp '^[aeiou]'

• LEFT(문자열, 길이): 문자열을 받아서 왼쪽부터 원하는 길이만큼 자르는 함수
• REGEXP_LIKE(문자열, 정규식 패턴): 정규식을 비교하여 일치할 경우 추출해주는 함수
• REGEXP: 정규 표현식(Regular Expression)을 활용해 기본 연산자보다 더 복잡한 문자열 조건을 걸어 데이터 검색 가능
  • |(수직선): |로 구분된 문자에 해당하는 문자열 찾기 (ex> "하이|안녕" -> '하이' 또는 '안녕'에 해당하는 문자열 찾기)
  • [] : 안에 나열된 패턴에 해당하는 문자열 찾기 (ex> "[abc]d" -> 'ad' 또는 'bd' 또는 'cd'인 문자열 찾기)
  • ^ : 시작하는 문자열을 찾기 (ex> "^폰" -> '폰'으로 시작하는 문자열 찾기)
  • $ : 끝나는 문자열을 찾기 (ex> "고기$"-> '고기'로 끝나는 문자열을 찾음)

 

Weather Observation Station 7

Weather Observation Station 7 | HackerRank

select distinct city
from station
where right(city, 1) in ('a', 'e', 'i', 'o', 'u')

• RIGHT(문자열, 길이): 문자열을 받아서 오른쪽부터 원하는 길이만큼 자르는 함수

 

Weather Observation Station 11

Weather Observation Station 11 | HackerRank

select distinct city
from station
where left(city, 1) not in ('a', 'e', 'i', 'o', 'u') or right(city, 1) not in ('a', 'e', 'i', 'o', 'u')


select distinct city
from station
where city regexp '^[^aeou]' or city regexp '[^aeiou]$'

 

• 참고
  • ^x: x로 시작하는 문자열을 찾기
  • [^xy] : not의 의미로, x및 y를 제외한 문자 의미함
  • 위 예시에서 '^[^aeiou]'의 뜻은 aeiou를 제외한 문자로 시작하는 문자열 찾기

 

Type of Triangle

Type of Triangle | HackerRank

SELECT CASE WHEN A + B <= C OR B + C <= A OR A + C <= B THEN 'Not A Triangle'
            WHEN A = B AND A = C THEN 'Equilateral'
            WHEN A = B OR B = C OR A = C THEN 'Isosceles'
            WHEN A <> B OR B <> C THEN 'Scalene'
            END
FROM TRIANGLES;

• 참고: CASE WHEN 구문 사용해 해당 조건에 맞는 값 출력

 

The PADS

The PADS | HackerRank

SELECT CONCAT('There are a total of ', COUNT(*), ' ', LOWER(OCCUPATION), 's.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(*), OCCUPATION;

• CONCAT(문자열1, 문자열2,...): 여러 문자열을 하나로 합칠 때 CONCAT 함수 사용, 파라미터를 두 개 이상 사용 가능
• LOWER(문자열): LOWER 함수는 모든 문자열을 소문자로 통일시켜주는 함수이고, 이와 반대되는 함수는 UPPER 함수 (즉, UPPER 함수는 컬럼의 문자열을 모두 대문자 알파벳으로 변경)

 

Occupations

Occupations | HackerRank

복습 ⭐️

SELECT MIN(CASE WHEN OCCUPATION = 'DOCTOR' THEN NAME END) AS DOCTOR
     , MIN(CASE WHEN OCCUPATION = 'PROFESSOR' THEN NAME END) AS PROFESSOR
     , MIN(CASE WHEN OCCUPATION = 'SINGER' THEN NAME END) AS SINGER
     , MIN(CASE WHEN OCCUPATION = 'ACTOR' THEN NAME END) AS ACTOR
FROM (SELECT NAME, OCCUPATION, RANK() OVER (PARTITION BY OCCUPATION ORDER BY NAME ASC) RK
      FROM OCCUPATIONS) RANKLISTS
GROUP BY RK

• 참고: OCCUPATION을 기준으로 그룹핑해주어 OCCUPATION별 NAME 순으로 RANK 매겨주는 서브 쿼리 작성 후, 그 서브쿼리에서 DOCTOR, PROFESSOR, SINGER, ACTOR 출력 (이때 MIN을 사용하고 RANK를 GROUP BY 해주어야 함, MAX()를 이용해도 동일한 결과 <- 마지막에 GROUP BY를 사용하기 위해 집계 함수 사용)

 

The Blunder

The Blunder | HackerRank

SELECT CEILING(AVG(SALARY) - AVG(REPLACE(SALARY, 0, ''))) 
FROM EMPLOYEES

• CEILING: 해당 숫자보다 크거나 같은 최소 정수 반환 ⭐️
• 참고: 반대로 FLOOR 함수는 해당 숫자보다 작거나 같은 최대 정수 반환 ⭐️
  • 예시: 11.92 숫자에 대해 각각 CEILING은 12, FLOOR는 11를 반환
• REPLACE(Column,'해당문자','바꿀문자'): 어떠한 문자를 다른 문자로 변환하고 싶을 때 사용, 위 문제에서는 SALARY 컬럼에서 0을 ''로 변환(즉, SALARY 컬럼에서 0을 없앤다는 의미)

 

Weather Observation Station 18

Weather Observation Station 18 | HackerRank

SELECT ROUND(ABS(MAX(LAT_N)-MIN(LAT_N)) + ABS(MAX(LONG_W)-MIN(LONG_W)), 4)
FROM STATION

• ABS: 절대값 구할 때 사용하는 함수로, 위 문제에서는 LAT_N의 최대값과 LAT_N의 최솟값의 차이의 절대값과 LONG_W의 최대값과 LAT_N의 최솟값의 차이의 절댓값을 구할 때 사용

 

Weather Observation Station 19

Weather Observation Station 19 | HackerRank

SELECT ROUND(SQRT(POWER(MAX(LAT_N)-MIN(LAT_N), 2) + POWER(MAX(LONG_W)-MIN(LONG_W), 2)), 4)
FROM STATION

• POWER(숫자, 거듭제곱 횟수): 숫자를 제곱 횟수만큼 제곱하고 싶을 때 사용하는 함수
• SQRT: 제곱근을 구할 때 사용하는 함수

 

Weather Observation Station 20

Weather Observation Station 20 | HackerRank

SELECT ROUND(LAT_N, 4)
FROM (SELECT *, PERCENT_RANK () OVER (ORDER BY LAT_N) RK
      FROM STATION) TBL
WHERE RK = 0.5

• PERCENT_RANK: 행의 PERCENTILE RANK(백분위수 순위)를 구하고 싶을 때 사용하는 함수로, 위 문제에서는 MEDIAN값을 구하기 위해 LAT_N 순으로 정렬하고 PERCENTILE RANK를 매기는 서브쿼리 작성, 그 후 그 서브쿼리에서 RANK가 0.5(즉, 중앙에 위치한)인 값을 추출

 

 

참고한 블로그 : https://velog.io/@2_j_life/%ED%95%B4%EC%BB%A4%EB%9E%AD%ED%81%AC-SQL-%EA%B3%B5%EB%B6%80-%EC%A0%95%EB%A6%AC-1

해커랭크 SQL 공부 정리 1

 

감사합니다 🙏🏻

 

더 볼 것 : https://velog.io/@2_j_life/%ED%95%B4%EC%BB%A4%EB%9E%AD%ED%81%AC-SQL-%EA%B3%B5%EB%B6%80-%EC%A0%95%EB%A6%AC-2

해커랭크 SQL 공부 정리 2

https://velog.io/@2_j_life/Codesignal-SQL-%EA%B3%B5%EB%B6%80-%EC%A0%95%EB%A6%AC

Codesignal SQL 공부 정리1