[프로그래머스] SQL 문제

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

SQL 문제를 포함한 알고리즘 문항을 시험보는 곳이 꽤 있는 것 같아서 관련 자료 글을 찾아보다

프로그래머스에 있는 SQL 문제가 있어 문제와 답안을 기록하고 있다.

 

문제 리스트 (추가중)

• 고양이와 개는 몇 마리 있을까
• 동명 동물 수 찾기
• 입양 시각 구하기(1)
• 입양 시각 구하기
• NULL 처리하기
• 이름이 있는 동물의 아이디
• 이름이 없는 동물의 아이디
• 루시와 엘라 찾기
• 오랜 기간 보호한 동물(2) (Lv 3)
• DATETIME에서 DATE로 형 변환 (Lv 2)
• 헤비 유저가 소유한 장소 (Lv 3)
• 있었는데요 없었습니다 (Lv 3)
• 없어진 기록 찾기
• 오랜 기간 보호한 동물(1)
• 보호소에서 중성화한 동물  (Lv 4)

 

 

문제를 풀며 다시 찾아보고 정리하는 개념들

HAVING 절

• WHERE 절에서는 집계함수를 사용 할 수 없다.
• HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
• HAVING절은 GROUP BY절과 함께 사용이 된다.

HAVING 절은 WHERE 절과 비슷하지만 그룹 전체 즉, 그룹을 나타내는 결과 집합의 행에만 적용된다는 점에서 차이가 있습니다. 반면, WHERE 절은 개별 행에 적용됩니다. 쿼리에는 WHERE 절과 HAVING 절이 모두 포함될 수 있습니다. 이 경우 다음을 수행합니다.

• 다이어그램 창에서 테이블이나 테이블 반환 개체의 개별 행에 WHERE 절이 먼저 적용됩니다. WHERE 절의 조건에 맞는 행만 그룹화됩니다.
• 그런 다음 WHERE절로 그룹화된 행에 HAVING 절이 적용됩니다. HAVING 조건에 맞는 그룹만 쿼리 출력에 표시됩니다. 집계 함수나 GROUP BY 절에도 나타나는 열에만 HAVING 절을 적용할 수 있습니다.

 

SELECT문 검색 순서

5. SELECT 칼럼명 [AS ALIAS명]	데이터 값을 출력
1. FROM 테이블명	발췌대상 테이블을 참조
2. WHERE 조건식	조건에 따라 발췌 대상 데이터가 아닌 것은 제거
3. GROUP BY 칼럼이나 표현식	행들을 소그룹화
4. HAVING 그룹 조건식	그룹핑된 값의 조건에 해당하는 데이터만 출력 (WHERE 이후)
6. ORDER BY 칼럼이나 표현식;	데이터 정렬

 

참고 사진

출처 : https://dkkim2318.tistory.com/6

CASE WHEN THEN

CASE WHEN THEN은 아래 상황에서 사용합니다.

1. 조건에 따라서 값을 지정해 줄 때, 어떤 값을 보기 쉽게 바꿔 보여줄 때 
2. 데이터를 범주화할 때 사용한다.

2번의 예로, 점수가 90점이면 A로, 80점이면 B로, 그 이하면 C로 범주화한다면!

SELECT 
CATEGORY =			-- 혹은 AS CATEOGRY로 표현
CASE
WHEN SCORE>=90 AND SCORE>80 THEN A
WHEN SCORE>=80 AND SCORE<90 THEN B
ELSE C
FROM STUDENT;

 

아래 문제 5번에서 CASE WHEN THEN으로 문제를 풀었는데, NULL의 경우 IFNULL 이라는 문법도 있다.

즉, 아래 두 SQL문은 동일한 데이터를 보여준다.

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

SELECT
CASE WHEN NAME ISNULL THEN 'No name' ELSE NAME END AS NAME
FROM ANIMAL_INS;

 

 

---

 

문제 1. 고양이와 개는 몇 마리 있을까 (링크)

코딩테스트 연습 - 고양이와 개는 몇 마리 있을까

내 답안

SELECT ANIMAL_TYPE, count(ANIMAL_TYPE) AS count 
FROM ANIMAL_INS 
GROUP BY ANIMAL_TYPE 
ORDER BY ANIMAL_TYPE;

 

문제 2. 동명 동물 수 찾기 (링크)

내 답안

SELECT NAME, count(NAME) AS COUNT 
FROM ANIMAL_INS 
GROUP BY NAME 
HAVING COUNT>1 
ORDER BY NAME;

 

문제 3. 입양 시각 구하기(1) (링크)

 

내 답안

SELECT HOUR(DATETIME) AS HOUR, count(DATETIME) AS COUNT 
FROM ANIMAL_OUTS 
GROUP BY HOUR 
HAVING HOUR<20 AND HOUR>8 
ORDER BY HOUR ASC;

*주의*

• count() 에는 기존 테이블에 있는 칼럼명을 넣어줘야 한다.

 

문제 4. 입양 시각 구하기 (링크)

내 답안

// 추가 풀이 예정

• ORDER BY 절은 문장의 맨 마지막에 위치한다.
• ORDER BY 절에서는 컬럼명과 ALIAS 명 모두 이용할 수 있다.

 

문제5. NULL 처리하기 (링크)

문제를 풀면서 CASE WHEN THEN을 알게 되었다.  이또한 위에 정리해놓았다.

# SELECT ANIMAL_TYPE, 
# CASE WHEN NAME IS NULL THEN 'No name' ELSE NAME END as NAME,
# SEX_UPON_INTAKE 
# FROM ANIMAL_INS;

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

 

문제6. 이름이 있는 동물의 아이디 (링크)

SELECT ANIMAL_ID 
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID;

 

문제7. 이름이 없는 동물의 아이디 (링크)

 

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID ASC;

 

문제8. 루시와 엘라 찾기 (링크)

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

IN으로 사용하는 게 가장 깔끔할 것 같습니다.

 

문제9. 오랜 기간 보호한 동물(2) (링크)

LIMIT 으로 출력할 데이터 갯수를 제한할 수 있습니다.

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS AS OUTS
JOIN ANIMAL_INS AS INS
ON(INS.ANIMAL_ID = OUTS.ANIMAL_ID)
ORDER BY OUTS.DATETIME - INS.DATETIME DESC  -- 차이가 큰 값먼저 정렬하므로 내림차순!!
LIMIT 2;    -- 데이터 출력 제한 LIMIT

 

문제10.오랜 기간 보호한 동물(2) (링크)

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

 

문제11. DATETIME에서 DATE로 형 변환 (링크)

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

 

 

문제12. 중성화 여부 파악하기

 

CASE WHEN THEN + LIKE 문제로 풀었습니다.

더 간결히는, CASE WHEN THEN + 정규식을 사용하는 REGEXP 문법으로 풀 수 있습니다.

-- CASE WHEN ELSE 와 LIKE 사용
SELECT ANIMAL_ID, NAME,  
CASE WHEN 
SEX_UPON_INTAKE LIKE 'Neutered%' OR
SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
ELSE 'X' 
END
AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

-- REGEXP 정규식 사용

 

문제13. 헤비 유저가 소유한 장소 (링크)

 

-- 셀프 조인
SELECT A.ID, A.NAME, A.HOST_ID
FROM PLACES A
JOIN PLACES B
ON (A.HOST_ID = B.HOST_ID AND A.ID != B.ID)
GROUP BY A.ID       -- 같은 ID로 그룹
ORDER BY A.ID ASC;

--  HAVING으로 조건에 해당하는 ID들을 보여주고 그중 있는 HOST_ID의 데이터를 출력한다.
SELECT *
FROM PLACES
WHERE HOST_ID IN (
    SELECT HOST_ID FROM PLACES
    GROUP BY HOST_ID HAVING count(ID) >=2
)
ORDER BY ID;

 

 

문제14.  있었는데요 없었습니다 (링크)

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS INS
JOIN ANIMAL_OUTS OUTS
ON (INS.ANIMAL_ID = OUTS.ANIMAL_ID)
WHERE OUTS.DATETIME < INS.DATETIME
ORDER BY INS.DATETIME ASC;

 

문제15. 없어진 기록 찾기(링크)

RIGHT OUTER JOIN은 A(좌)와 B(우)가 있을 때 우측 테이블의 모든 데이터는 포함된다.

여기서 WHERE A.칼럼 IS NULL 을 추가하면 B 테이블에만 있는 데이터가 출력된다. (차집합)

-- OUTS에 있는데 INS에 없는..
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS AS INS
RIGHT OUTER JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID ASC;

 

 

문제16. 오랜 기간 보호한 동물(1) (링크)

LEFT JOIN은 좌측의 테이블의 데이터를 모두 출력한다.

여기서 WHERE 절로 우측의 테이블의 데이터가 NULL인 조건을 추가한다면 A에만 있는 데이터(차집합)만 출력한다.

 

 

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS AS INS
LEFT JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID= OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL -- INS 기준이고 외래키로.
ORDER BY INS.DATETIME ASC  
LIMIT 3;

 

 

문제17. 보호소에서 중성화한 동물 (링크)

두 데이터 모두에서, ANIMAL_INS에서는 Intact로 시작하는, ANIMAL_OUTS에서는 Neutered 혹은 Spayed로 시작하는 두가지 조건을 모두 만족하는 데이터를 추출해야하기 때문에 JOIN을 사용해야 한다. 

 

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS AS INS
JOIN ANIMAL_OUTS AS OUTS
ON (INS.ANIMAL_ID = OUTS.ANIMAL_ID)
WHERE INS.SEX_UPON_INTAKE REGEXP 'Intact'
AND
OUTS.SEX_UPON_OUTCOME REGEXP 'Neutered|Spayed'
ORDER BY INS.ANIMAL_ID ASC;

 

 

출처 및 참고 링크

https://geniusjo.tistory.com/entry/ORDER-BY-%EC%A0%88

https://docs.microsoft.com/ko-kr/sql/ssms/visual-db-tools/use-having-and-where-clauses-in-the-same-query-visual-database-tools?view=sql-server-ver15 

https://paris-in-the-rain.tistory.com/100