출제되는 SQL 모음

COUNT, GROUP BY

-- 코드를 입력하세요
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

COUNT, GROUP BY, HAVING

-- 코드를 입력하세요
SELECT NAME,COUNT(NAME) FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >=2
ORDER BY NAME

GROUP BY, HAVING

-- 코드를 입력하세요
SELECT HOUR(DATETIME) HOUR, COUNT(DATETIME) FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR>= 9 and HOUR < 20 
ORDER BY HOUR

SET, SUBQUERY

-- 코드를 입력하세요

SET @hour := -1; # 변수 선언

SELECT (@hour := @hour+1) as HOUR, # 새로운 열 생성 0~ 23
(SELECT COUNT(DATETIME) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as COUNT #해당 시간에 맞는 데이터를 쿼리해옴
FROM ANIMAL_OUTS
WHERE @hour < 23

IS NULL

I

-- 코드를 입력하세요
SELECT ANIMAL_ID FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

IS NOT NULL

-- 코드를 입력하세요
SELECT ANIMAL_ID FROM ANIMAL_INS
WHERE NAME IS NOT NULL

IFNULL

-- 코드를 입력하세요
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE FROM ANIMAL_INS

LEFT OUTER JOIN ~ ON ~

-- 코드를 입력하세요
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

LEFT OUTER JOIN ~ ON ~ 2

-- 코드를 입력하세요
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

LEFT OUTER JOIN ~ ON ~

-- 코드를 입력하세요
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
LIMIT 3

LEFT OUTER JOIN ~ ON~ 3

-- 코드를 입력하세요
SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE,INS.NAME FROM ANIMAL_INS INS
LEFT OUTER JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME