[프로그래머스] SQL 고득점 kit

Jubami·2022년 6월 24일

SQL고득점KIT SQL코딩테스트 프로그래머스

코테연습

목록 보기

9/19

SQL 고득점 KIT

SELECT

모든 레코드 조회하기

oracle로 작성
동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성

SELECT *
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID;

역순 정렬하기

동물 보호소에 들어온 모든 동물의 이름과 보호 시작일을 조회하는 SQL문을 작성

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;

아픈 동물 찾기

동물 보호소에 들어온 동물 중 아픈 동물1의 아이디와 이름을 조
결과는 아이디순 정렬

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION ='Sick'
ORDER BY ANIMAL_ID;

어린 동물 찾기

젊은 동물1의 아이디와 이름을 조회하는 SQL
ID순으로 조회
db index는 1부터 시작

SELECT animal_id, name
from animal_ins
where INTAKE_CONDITION != 'Aged'
order by 1;

동물의 아이디와 이름

모든 동물의 아이디와 이름을 ANIMAL_ID순으로 조회하는 SQL

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

여러 기준으로 정렬하기

모든 동물의 아이디와 이름, 보호 시작일을 이름 순으로 조회하는 SQL

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS 
ORDER BY NAME ASC , DATETIME DESC ;

상위 n개 레코드

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL
-oracle은 rownum 사용

SELECT NAME
FROM (
        select name,datetime
        from animal_ins
        order by datetime asc
    ) 
WHERE ROWNUM <= 1;

mysql : limit을 사용

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME LIMIT 1;

SUM,MAX,MIN

최댓값 구하기

가장 최근에 들어온 동물은 언제 들어왔는지 조회

SELECT MAX(DATETIME) FROM ANIMAL_INS;

최솟값 구하기

가장 먼저 들어온 동물

SELECT min(datetime) from ANIMAL_INS

동물 수 구하기

동물 보호소에 동물이 몇 마리 들어왔는지 조회

SELECT count(ANIMAL_TYPE) as count from ANIMAL_INS;

중복 제거하기

동물의 이름 몇 개인지 조회하는 SQL
이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.

SELECT count(distinct(name)) as count
from animal_ins
where name is not null;

GROUP BY

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

동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

SELECT ANIMAL_TYPE , count(ANIMAL_TYPE) as count
from animal_ins
group by ANIMAL_TYPE
order by ANIMAL_TYPE;

동명 동물 수 찾기

두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회
이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순

SELECT NAME, COUNT(NAME) as COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >=2
ORDER BY NAME;

입양시각구하기

09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
ORACLE
- TO_CHAR(SYSDATE,'HH24'), 시간을 24시 형식으로 표시
- TO_CHAR(SYSDATE,'HH'), --11 (시간을 0 ~ 12 형태로 표시)

SELECT TO_CHAR(DATETIME,'HH24') AS "HOUR", COUNT(*) AS "COUNT"
FROM ANIMAL_OUTS
WHERE TO_CHAR(DATETIME,'HH24') BETWEEN 9 AND 20
GROUP BY TO_CHAR(DATETIME,'HH24')
ORDER BY 1;

MYSQL

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

입양시각 구하기(2)

이 문제를 풀기 위해서는 계층형 쿼리에 대한 이해가 필요.

🔍 계층형 쿼리란?
계층형 쿼리는 테이블에 저장된 데이터를 계층형 구조로 반환하는 쿼리를 말합니다.
START WITH 조건으로 최상위 노드를 설정하고,
CONNECT BY 절로 계층형 구조에서의 연결조건을 설정할 수 있습니다.

0 부터 23까지의 숫자를 나타내주기 위해 LEVEL, CONNECT BY절을 이용

select LEVEL-1 as HOUR from dual CONNECT BY LEVEL <=24;

레벨은 기본 1부터 시작, LEVEL-1, LEVEL <=23 -> 0~23컬럼 반환
0~23시에 데이터가 없더라도 결과 출력 -> 조인으로 해결(LEFT)
Oracle

SELECT HOUR, COUNT(O.DATETIME) AS COUNT
FROM
(
    SELECT LEVEL-1 AS HOUR
    FROM DUAL 
    CONNECT BY LEVEL<=24)A LEFT join ANIMAL_OUTS O
ON A.HOUR = to_char(O.DATETIME,'HH24')
GROUP BY HOUR
ORDER BY HOUR;

Mysql

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

IS NULL

이름이 없는 동물의 아이디

SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID

NULL 처리하기

입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.
oracle : case when ~ else ~ end

select ANIMAL_TYPE, 
    case 
    when name is null then 'No name'
    else name end, 
    SEX_UPON_INTAKE
from ANIMAL_INS
order by animal_id

mysql : ifnull(컬럼,'값'

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

JOIN

없어진 기록 찾기

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록(ANIMAL_OUTS)은 있는데, 보호소에 들어온 기록(ANIMAL_INS)이 없는 동물의 ID와 이름을 ID 순으로 조회하는 sql문 작성.

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;

있었는데요 없었습니다.

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

오랜 기간 보호한 동물(1)

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

SELECT * 
FROM (
    SELECT INS.NAME, INS.DATETIME
    FROM ANIMAL_INS INS LEFT OUTER JOIN ANIMAL_OUTS OUTS
    ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
    WHERE OUTS.ANIMAL_ID IS NULL
    ORDER BY INS.DATETIME
     )
WHERE ROWNUM <=3;

보호소에서 중성화한 동물

보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL문 작성
```
	- LEFT OUTER JOIN은 두가지 방식으로 표현: LEFT OUTER JOIN 구문을 사용하는 것과 (+)를 사용하는 것
```

SELECT b.animal_id, b.animal_type, b.name
FROM animal_ins a, animal_outs b
where b.animal_id = a.animal_id(+)
and b.sex_upon_outcome != a.sex_upon_intake
order by b.animal_id;

위 코드는 아래와 같다. (+)를 사용하면 훨씬 간결해짐. 단 + 사용시 where 조건에 on 조건을 명시하고, 제약조건을 and에서 표현.

select b.animal_id, b.animal_type, b.name
FROM animal_ins a left outer join animal_outs b
on a.animal_id = b.animal_id
where b.sex_upon_outcome != a.sex_upon_intake
order by b.animal_id;

String,Date

루시와 엘라찾기

이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요.

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

이름에 el이 들어가는 동물 찾기

동물 보호소에 들어온 동물 이름 중, 이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 이름 순으로 조회해주세요. 단, 이름의 대소문자는 구분하지 않습니다.

SELECT animal_id, name
FROM animal_ins
where animal_type='Dog'
and (name like '%el%' or name like '%El%')
order by name;

중성화 여부 파악하기

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

SELECT ANIMAL_ID, NAME, 
      CASE
      when SEX_UPON_INTAKE like 'Neutered%' then 'O'
      when SEX_UPON_INTAKE like 'Spayed%' then 'O'
      else 'X' end "중성화"
FROM ANIMAL_INS 
order by ANIMAL_ID;

오랜 기간 보호한 동물(2)

SELECT *
FROM (
    SELECT INS.ANIMAL_ID, INS.NAME
    FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
    WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID
    ORDER BY OUTS.DATETIME - INS.DATETIME DESC
)
WHERE ROWNUM <=2;

DATETIME에서 DATE로 형 변환

2017-04-13 16:29:00(') -> 2017-04-13(yyyy-mm-dd)
to_char을 이용해서 변환

SELECT ANIMAL_ID, NAME, TO_CHAR(DATETIME,'YYYY-MM-DD') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

Jubami

LV.1 아밥퍼

이전 포스트

[python] 백준 단계별로 풀어보기 - 재귀

다음 포스트

[프로그래머스] SQL 고득점 kit

코테연습

SELECT

모든 레코드 조회하기

역순 정렬하기

아픈 동물 찾기

어린 동물 찾기

동물의 아이디와 이름

여러 기준으로 정렬하기

상위 n개 레코드

SUM,MAX,MIN

최댓값 구하기

최솟값 구하기

동물 수 구하기

중복 제거하기

GROUP BY

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

동명 동물 수 찾기

입양시각구하기

입양시각 구하기(2)

IS NULL

이름이 없는 동물의 아이디

NULL 처리하기

JOIN

없어진 기록 찾기

있었는데요 없었습니다.

오랜 기간 보호한 동물(1)

보호소에서 중성화한 동물

String,Date

루시와 엘라찾기

이름에 el이 들어가는 동물 찾기

중성화 여부 파악하기

오랜 기간 보호한 동물(2)

DATETIME에서 DATE로 형 변환

[python] 백준 단계별로 풀어보기 - 재귀

[python] 시간 복잡도와 빅오표기법

0개의 댓글