시험 대비 SQL 문제 풀이

zio도미닉·2022년 2월 2일

SQL 문제풀이

목록 보기

7/7

1. WITH RECURESIVE를 이용하여 가상 테이블 만들기

-- 예제 
	WITH RECURSIVE 테이블명 AS(
    	SELECT 초기값 AS 별명1
    	UNION ALL
    	SELECT 별명1 계산식 FROM 테이블명 WHERE 제어문)
        
-- 0~23시간 테이블 필요할때         
	WITH RECURSIVE time AS (
    	SELECT 0 AS h
    	UNION ALL
    	SELECT h+1 FROM TIME WHERE h < 23
	)
	SELECT h AS HOUR, count(DATETIME) 
	FROM time
    	LEFT JOIN animal_outs on h = hour(datetime)
	GROUP BY h
	ORDER BY h;

2. IN과 LIKE 비교

LIKE는 단건 조회할때 주로 사용 - 주로 와일드 카드 %, _를 이용
- 단건 조회
- 다건 조회 시 괄호( )와 OR을 사용하기

	-- 다건 조회 시 OR을 사용
    SELECT ANIMAL_ID,NAME
    FROM ANIMAL_INS 
    WHERE (NAME LIKE '%el%' OR NAME LIKE '%EL%') -- 괄호를 사용해서 묶어주기
    AND ANIMAL_TYPE LIKE 'Dog'
    ORDER BY NAME
    
	-- 사원명이 'E'로 시작하고 5글자로 구성된 사원 출력 
    SELECT first_name 
    From employees 
    WHERE FIRST_NAME LIKE 'E____'

IN은 다건 조회일때 주로 사용
1. IN 안에 전체 인자를 넣어줄수 있음 (단 와일드 카드는 못 넣음)

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

서브쿼리를 이용해서 넣어서 값을 찾을 수 있음.

SELECT ai.ANIMAL_ID,ai.ANIMAL_TYPE,ai.NAME
FROM ANIMAL_INS ai
LEFT JOIN ANIMAL_OUTS ao
ON ai.ANIMAL_ID=ao.ANIMAL_ID
WHERE ai.SEX_UPON_INTAKE LIKE 'Intact %'
# 3. IN은 와일드카드를 사용할 없기 때문에 SELECT 절을 이용해 찾기
# 방법 1. AND ao.SEX_UPON_OUTCOME NOT LIKE 'Intact %'
# 방법 2. IN 안에 서브쿼리를 넣어서 실행
# AND ao.SEX_UPON_OUTCOME IN (SELECT SEX_UPON_OUTCOME FROM ANIMAL_OUTS WHERE SEX_UPON_OUTCOME LIKE 'Spayed%' OR SEX_UPON_OUTCOME LIKE 'Neutered%')
# 방법 3. IN 안에 값을 다 넣어서 비교
# AND AO.SEX_UPON_OUTCOME IN ('Spayed Female','Spayed Male','Neutered Male','Neutered Female')
# 방법 4. OR을 사용하기 
AND (AO.SEX_UPON_OUTCOME LIKE 'Spayed%' OR AO.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY ai.ANIMAL_ID

3. IFNULL

나온 값이 null이면 변경하기

-- NAME 컬럼에 null이 들거가면 No name으로 변경하기
SELECT ANIMAL_TYPE, IFNULL(NAME,'No name') AS NAME
FROM ANIMAL_INS

4. IF 조건문, CASE WHEN 비교

IF 조건문은 O, X 둘 중에 하나 비교할때 사용
CASE WHEN은 여러 조건일때 사용 가능

-- IF (조건문, 참일때 값, 거짓일때 값)
SELECT 
	A.seq,
	IF (A.seq<=3, 'A','B') AS IF_result
FROM TABLE

-- 
CASE 
	WHEN 조건1 THEN '조건1 반환값'
	WHEN 조건2 THEN '조건2 반환값'
	ELSE '충족되는 조건 없을때 반환값'
END
CASE 
	WHEN (seq BETWEEN 1 AND 3) THEN 'A'
	WHEN (seq BETWEEN 1 AND 4) THEN 'B'
	ELSE 'X'
END AS '등수'

풀이

-- 코드를 입력하세요
-- 중성화된 동물 Neutered, Spayed라면 O, 아니라면 X 
SELECT ANIMAL_ID,NAME, 
    -- 방법 1. IF 사용
    # IF((SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%'),'O','X') AS '중성화'
    -- 방법 2. CASE WHEN END 사용 
    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

DATEDIFF

DATEDIFF(날짜부분- 생략가능, 시작, 종료)의 차이를 알려줌
DATEDIFF('day' or 'year, 'month', 'start_date', 'end_date')

-- 코드를 입력하세요
-- 입양을 간 동물 중 OUT 기준, 입양을 갔다는 건 OUT이 NOT NULL 
-- IN을 기준 테이블로 잡고 , OUT이 NOT NULL 인거를 기준으로 삼자
-- 보호 기간이 가장 길었던 동물 두마리의 아이디와 이름 조회 
-- 결과는 보호 기간이 긴 순
SELECT ao.ANIMAL_ID,ao.NAME
FROM ANIMAL_OUTS ao
LEFT JOIN ANIMAL_INS ai
ON ai.ANIMAL_ID=ao.ANIMAL_ID
ORDER BY DATEDIFF(ao.DATETIME,ai.DATETIME) DESC -- 보호 기간이 가장 긴것 찾기 
-- (입양일 - 보호 시작일의 차이)이 내림차순으로 정렬 
LIMIT 2

DATE_FORMAT

날짜를 원하는 형식으로 변경

SELECT ANIMAL_ID,NAME,date_format(DATETIME,'%Y-%m-%d') AS 날짜
FROM ANIMAL_INS

서브쿼리를 이용한 문제 풀이

1. 헤비 유저가 소유한 장소

문제 풀이

서브 쿼리부터 작성
HOST_ID를 기준으로 묶고, 이 카운트가 2개 이상인거 찾기

SELECT HOST_ID
FROM PLACES 
GROUP BY HOST_ID
HAVING COUNT(ID)>=2

IN으로 묶고 맞는거 찾기

SELECT ID,NAME,HOST_ID
FROM PLACES
WHERE HOST_ID
    IN (SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(ID)>=2)

2. 우유와 요거트가 담긴 장바구니

-- 코드를 입력하세요
-- 장바구니의 아이디, 우유와 요거트를 동시에 구입한- 서브 쿼리
SELECT CART_ID
FROM CART_PRODUCTS
WHERE (NAME LIKE 'Milk') -- 우유 구매한 사람
AND (CART_ID IN (SELECT CART_ID
    FROM CART_PRODUCTS 
    WHERE NAME LIKE 'Yogurt')) -- 요거르틀 구매한 사람
GROUP BY CART_ID -- 묶어줌