level1
부터 level4
까지의 문제 중 헷갈렸던 문제와 다시 기억하면 좋을 것 같은 문제들을 정리했다.
ANIMAL_INS
테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.ANIMAL_OUTS
테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.level2
/ ANIMAL_INS 테이블 사용
동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.
SELECT name, COUNT(name) FROM animal_ins GROUP BY 1 HAVING COUNT(name) > 1 ORDER BY 1;
level2
/ ANIMAL_OUTS 테이블 사용
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
GROUP BY
절과 같이 작성해야하고 SUM, AVG, MAX, MIN, COUNT
와 같은 집계함수
가 와야함
- 내 풀이
-- 시간만 짤라내는 HOUR() 함수 사용 SELECT HOUR(datetime) AS hour, COUNT(*) FROM animal_outs WHERE HOUR(datetime) > 8 AND HOUR(datetime) < 20 GROUP BY 1 ORDER BY 1;
- 강사님 풀이
SELECT HOUR(datetime) AS HOUR, COUNT(*) FROM animal_outs WHERE HOUR(datetime) >=9 AND HOUR(datetime) < 20 GROUP BY 1 ORDER BY 1;
level2
/ ANIMAL_INS 테이블
입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.
SELECT animal_type, IFNULL(name, 'No name'), sex_upon_intake FROM animal_ins ORDER BY animal_id;
level2
/ ANIMAL_INS 테이블 사용
ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜1를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다.
SELECT animal_id, name, DATE_FORMAT(datetime, '%Y-%m-%d') FROM animal_ins;
level2
/ ANIMAL_INS 테이블 사용
보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 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 AS '중성화' FROM animal_ins ORDER BY animal_id;
- 강사님 풀이
-- WHEN 뒤에 조건이 2개 들어갈 때는 OR나 AND로 사용 -- 혹시 모를 오작동 방지를 위해 조건은 ()로 묶는다 -- END 뒤에는 적절한 칼럼명을 준다(필수 X) SELECT animal_id , name , CASE (WHEN sex_upon_intake LIKE 'Neutered%') OR (sex_upon_intake LIKE 'Spayed%') THEN 'O' ELSE 'X' END FROM animal_ins ORDER BY animal_id;
JOIN
문제인데 왜 나는 자꾸 서브쿼리
로 푸는가...
level3
/ ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블 모두 사용
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
FROM animal_ins WHERE animal_id NOT IN (SELECT animal_id FROM animal_outs) ORDER BY datetime LIMIT 3;
level3
/ ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블 모두 사용
SELECT animal_id, name FROM animal_outs WHERE animal_id NOT IN (SELECT animal_id FROM animal_ins) ORDER BY animal_id;
level3
/ PLACES 테이블 사용
이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.
1) 이렇게 풀었는데 오답이 나왔다
- 왜 오답일까 생각하다가 내 코드가 찾은 답과
SELECT * FROM palces
로 전체 테이블을 확인한 뒤 눈으로 헤비 유저의 정보 수가 다르다는 것을 알았다.- 내가 쓴 코드는 그냥 헤비 유저를 찾는 코드였던 것이다!!
- 그래서 이 코드를 WHERE 조건절이 되는 코드를 다시 짰다!
SELECT * FROM places GROUP BY host_id HAVING COUNT(host_id) > 1 -- 이 부분이 조건이 되어야함!!!! 이건 그냥 헤비 유저를 추출하는 것뿐임!! ORDER BY id;
2) 통과한 풀이
- 1)의 코드를 조건절에 넣고 풀었더니 통과되었다!
SELECT * FROM places WHERE host_id IN (SELECT host_id FROM places GROUP BY host_id HAVING COUNT(host_id) > 1) ORDER BY id;
- 강사님 풀이
-- IN 사용 SELECT * FROM places WHERE host_id IN (SELECT host_id FROM places GROUP BY 1 HAVING COUNT(id) >= 2) -- COUNT는 행을 세주는 건데, 이미 host_id로 그룹바이 했기 때문에 여기에서는 아무 거나 넣어줘도 된다! ORDER BY id; -- EXISTS 사용 FROM places p1 WHERE EXISTS (SELECT * FROM places p2 WHERE p1.host_id = p2.host_id -- EXISTS는 WHERE 조건을 넣어줘야 한다 : p1의 테이블의 값이 p2 테이블에 있는지 찾아지도록 해야하기 때문 GROUP BY host_id HAVING COUNT(id) >= 2) ORDER BY id;
level4
/ ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블 모두 사용
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.
OR
로 연결한, 중성화한 동물들의 조건에 ()
를 안 했더니 통과가 안 됐다. 아마 괄호에 있는 조건 둘 중 하나만 통과해도 되기 때문인데, 괄호를 안 하면 하나라도 통과하지 않으면 안되기 때문인 듯하다.AND
랑 OR
를 쓸 때는 ()
로 어디까지가 범위인지 지정해주는 게 좋다! 컴퓨터는 위에서 하나씩 읽는다!SELECT o.animal_id, o.animal_type, o.name FROM animal_outs o JOIN animal_ins i ON o.animal_id = i.animal_id WHERE i.sex_upon_intake LIKE 'Intact%' AND (o.sex_upon_outcome LIKE 'Spayed%' OR o.sex_upon_outcome LIKE 'Neutered%') ORDER BY 1;
- 강사님 풀이1
SELECT I.animal_id, I.animal_type, I.name FROM animal_ins I, animal_outs O WHERE I.animal_id = O.animal_id -- JOIN AND I.sex_upon_intake LIKE 'Intact%' AND O.sex_upon_outcome NOT LIKE 'Intact%' ORDER BY I.animal_id;
- 강사님 풀이2
SELECT I.animal_id, I.animal_type, I.name FROM animal_ins I, animal_outs O WHERE I.animal_id = O.animal_id AND I.sex_upon_intake != O.sex_upon_outcome; -- 처음과 다른 상태를 찾는 거기 때문에 이렇게 해줄 수도 있다.
level4
/ CART_PRODUCTS 테이블 사용
데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.
SELECT cart_id FROM cart_products WHERE name = 'Yogurt' AND cart_id IN (SELECT cart_id FROM cart_products WHERE name = 'Milk') ORDER BY 1;
- 강사님 풀이
SELECT cart_id FROM cart_products WHERE name IN ('Milk', 'Yogurt') -- 일단 밀크와 요거트를 뽑고 GROUP BY cart_id -- 밀크, 밀크도 있으니까 cart_id로 GROUP BY를 해줬다 HAVING COUNT(DISTINCT name) = 2; -- 중복 제거 DISTINCT name으로 한 장바구니(cart_id)에 '밀크', '밀크' 또는 '요거트', '요거트'를 제외시킨다! -- = 2는 2개 나오는 것들만 뽑는다 : `밀크`, `요거트`를 뽑는다
- 강사님 풀이2
-- GROUP_CONCAT() 사용 -- GROUP_CONCAT()을 사용하면 GROUP BY의 cart_id를 기준으로 name을 CONCAT 시킨다 = 마치 하나의 장바구니에 있는 것처럼! SELECT * FROM (SELECT cart_id, GROUP_CONCAT(name) names FROM cart_products GROUP BY cart_id) A;
- GROUP_CONCAT() 적용한 모습
- 강사님 풀이2 최종
SELECT cart_id FROM (SELECT car_id, GROUP_CONCAT(name) names FROM cart_products GROUP BY cart_id) A WHERE names LIKE '%Milk%' AND names LIKE '%Yogurt%';
level4
/ ANIMAL_OUTS 테이블 사용
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
- 강사님 풀이1
-- RECURSIVE는 재귀함수로, While문과 비슷한 역할을 한다 -- 0부터 23까지 들어간 타임라인 -- 즉, 아무것도 없을 때 0이 들어가있는 base_h 테이블을 만들었다 WITH RECURSIVE base_h AS (SELECT 0 hour UNION ALL SELECT hour + 1 FROM base_h WHERE hour < 23) -- hour에서 1씩 증가하기 때문에 22에서 1증가하면 23임으로 < 23! SELECT hour, COUNT(animal_id) FROM base_h LEFT JOIN animal_outs O ON hour = HOUR(O.datetime) GROUP BY hour ORDER BY hour;
- 강사님 풀이2
-- 새로운 변수를 만들어 값을 담을 때 사용하는 SET -- @를 쓰면 쓰고 싶을 때 이 변수를 계속 쓸 수 있다 SET @HOUR = -1; -- +1을 해줘야 하기 때문에 -1부터 시작 SELECT (@HOUR := @HOUR + 1) AS hour, -- ':=' 반복적으로 구동이 된다는 의미 (SELECT COUNT(animal_id) FROM animal_outs WHERE HOUR(datetime) = @HOUR) AS cnt FROM animal_outs WHERE @HOUR < 23;