[SQL] 프로그래머스 코딩테스트 : Lv1. (3)

 

Lv1. 특정 옵션이 포함된 자동차 리스트 구하기

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

SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC

❗WHERE 절에 LIKE 사용하기

 

Lv1. 잔챙이 잡은 수 구하기

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

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE LENGTH IS NULL

❗COUNT() 사용해서 개수 세기

 

Lv1. 잡은 물고리 중 가장 큰 물고기의 길이 구하기

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

SELECT CONCAT(MAX(LENGTH),'cm') AS MAX_LENGTH
FROM FISH_INFO

❗MAX() 사용해서 최대값 찾기
❗CONCAT() 사용해서 문자열과 붙이기 : CONCAT(붙일 것 1, 붙일 것 2)

 

Lv1. 최댓값 구하기

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

SELECT MAX(DATETIME) AS '시간'
FROM ANIMAL_INS

❗MAX() 사용해서 최대값 찾기

 

Lv1. Python 개발자 찾기

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

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS
WHERE SKILL_1 LIKE '%Python%'
    OR SKILL_2 LIKE '%Python%'
        OR SKILL_3 LIKE '%Python%'
ORDER BY ID ASC

❗WHERE 절에 OR 을 사용해서 조건 달기

 

Lv1. 한 해에 잡은 물고기 수 구하기

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

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE TIME BETWEEN '2021/01/01' AND '2021/12/31'

❗COUNT() 사용해서 개수 세기
❗BETWEEN 사용해서 조건 달기

 

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

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

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) >=29 THEN '장기 대여'
        ELSE '단기 대여'
    END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09-%'
ORDER BY HISTORY_ID DESC

❗DATE_FORMAT() 사용해서 포맷 맞추기
❗CASE WHEN 사용해서 조건 맞추기

• 1일차부터 시작이라 총 30일이라면 29일

❗WHERE 절 LIKE 사용해서 조건 달기

 

Lv1. 잡은 물고기의 평균 길이 구하기

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

SELECT ROUND(AVG(IFNULL(LENGTH, 10)),2) AS AVERAGE_LENGTH
FROM FISH_INFO

❗ROUND() 사용해서 소수점 포맷 맞추기
❗AVG() 사용해서 평균 계산하기
❗IFNULL() 사용해서 비어있으면 10으로 대체하기

 

WITH T1 AS (
SELECT ID, FISH_TYPE
    , CASE 
        WHEN LENGTH IS NULL THEN 10
        ELSE LENGTH
    END AS LENGTH
FROM FISH_INFO
)

SELECT ROUND(AVG(LENGTH),2) AS AVERAGE_LENGTH
FROM T1

❗WITH 사용해서 데이터 테이블 만들기

• CASE WHEN 으로 NULL인 곳에 10 넣기

❗ROUND(), AVG() 사용해서 조건에 맞춰 출력

 

Lv1. 가장 큰 물고기 10마리 구하기

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

SELECT ID, LENGTH
FROM FISH_INFO
ORDER BY LENGTH DESC, ID ASC
LIMIT 10

❗ORDER BY 길이 기준 내림차순 정렬하기
❗LIMIT 으로 개수 제한하기

 

Lv1. 조건에 부합하는 중고거래 댓글 조회하기

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

SELECT UGB.TITLE, UGB.BOARD_ID, UGR.REPLY_ID
    , UGR.WRITER_ID, UGR.CONTENTS
    , DATE_FORMAT(UGR.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD AS UGB
    JOIN USED_GOODS_REPLY AS UGR ON UGB.BOARD_ID = UGR.BOARD_ID
WHERE UGB.CREATED_DATE LIKE '%2022-10-%'
ORDER BY UGR.CREATED_DATE ASC, UGB.TITLE ASC

❗DATE_FORMAT() 사용해서 포맷 맞추기
❗JOIN 사용해서 데이터테이블 결합하기

• LEFT, RIGHT JOIN은 비어있는 행도 결합되어서 JOIN만 사용

❗WHERE 절 LIKE 사용해서 조건 달기

 

Lv1. 특정 형질을 가지는 대장균 찾기

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

-- 성공
SELECT COUNT(*) AS COUNT
FROM ECOLI_DATA
WHERE GENOTYPE & 2 = 0 
    AND GENOTYPE & 5 != 0

❗비트 연산자 & 활용하기

비트 연산자 : 비트 단위로 논리 연산을 할 때 사용하는 연산자

• 두 개의 비트가 모두 1 일때 1 출력

BIN(GENOTYPE) : 숫자를 2진수로 바꾼 것

BIN(2) : 10 = 0010 으로, 비트 연산을 눈으로 확인하기 위해 출력

BIN(GENOTYPE & 2) : 2번째 자리에 1이 있는 것들을 배제하기 위해 사용

BIN(GENOTYPE & 4) : 100 = 0100으로, 비트 연산 시 3번째 자리에 1이 있는 것을 확인 및 출력

BIN(GENOTYPE & 1) : 1 = 0001으로, 비트 연산 시 1번째 자리에 1이 있는 것을 확인 및 출력

BIN(GENOTYPE & 5) : 0101으로, 비트 연산 시 1번째 자리와 3번째 자리에 1이 있는 것을 확인 및 출력

결론적으로 GENOTYPE & 4의 특성 GENOTYPE & 1의 특성을 반영하는 GENOTYPE & 5로 사용

+ 입력된 데이터가 16까지일 때만 유효할 듯하다.

+ 어차피 2진수라 16까지라 유효한 것 같기도 하다.

 

---

-- 실패
WITH T1 AS (
    SELECT * 
        , LPAD(CONV(GENOTYPE,10,2),4,0) AS BIT
    FROM ECOLI_DATA
)

SELECT COUNT(*) AS COUNT
FROM T1
WHERE SUBSTR(BIT,3,1) = 0
    AND (SUBSTR(BIT,4,1) = 1 OR SUBSTR(BIT,2,1) = 1)

❗CONV() 사용해서 2진수로 바꾸기
❗LPAD() 사용해서 빈 곳에 0 넣어서 4자리 2진수로 바꾸기
❗SUBSTR() 사용해서 자리수 추출

 

-- 실패
WITH T1 AS (
    SELECT * 
        , LPAD(CONV(GENOTYPE,10,2),4,0) AS BIT
    FROM ECOLI_DATA
)

SELECT COUNT(*) AS COUNT
FROM T1
WHERE BIT LIKE '__0_'
    AND (BIT LIKE '_1__' OR BIT LIKE '___1')

❗LIKE 사용해서 조건에 맞는 것 추출
-> LPAD가 문제라 생각

• 4자리 숫자가 아닐 수도 있다.

 

-- 실패
WITH T1 AS (
    SELECT * 
        , REVERSE(CONV(GENOTYPE,10,2)) AS BIT
    FROM ECOLI_DATA
)

SELECT COUNT(*) AS COUNT
FROM T1
WHERE BIT LIKE '_0__'
    AND (BIT LIKE '__1_' OR BIT LIKE '___1')

❗LPAD() -> REVERSE()

• 문자열을 뒤집어서 앞 숫자 기준으로 한다
• 1101 -> 1011
• LPAD()의 문제점 해결?
• 그래도 실패... 왜지?