1.없어진 기록 찾기
SELECT ANIMAL_ID,NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (
SELECT ANIMAL_ID
FROM ANIMAL_INS
)
ORDER BY ANIMAL_ID ASC;
2.우유와 요거트가 담긴 장바구니
SELECT A.CART_ID
FROM (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Yogurt') AS A,
(SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Milk') AS B
WHERE A.CART_ID = B.CART_ID
3.있었는데요 없었습니다
SELECT ANIMAL_INS.ANIMAL_ID,ANIMAL_INS.NAME
FROM ANIMAL_INS,ANIMAL_OUTS
WHERE ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID AND ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME
ORDER BY ANIMAL_INS.DATETIME ASC;
4.입양 시각 구하기(1)
SELECT HOUR(DATETIME) AS 'HOUR', COUNT(*) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 20
GROUP BY HOUR
ORDER BY HOUR;
5.입양 시각 구하기(2)
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
ORDER BY HOUR;
6.이름에 el이 들어가는 동물 찾기
SELECT ANIMAL_ID,NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' AND NAME LIKE '%EL%'
ORDER BY NAME ASC;
7.보호소에서 중성화한 동물
SELECT A.ANIMAL_ID,A.ANIMAL_TYPE,A.NAME
FROM ANIMAL_INS AS A,ANIMAL_OUTS AS B
WHERE A.SEX_UPON_INTAKE LIKE 'Intact%'
AND A.ANIMAL_ID = B.ANIMAL_ID
AND (B.SEX_UPON_OUTCOME LIKE 'Neutered%' OR B.SEX_UPON_OUTCOME LIKE 'Spayed%')
ORDER BY A.ANIMAL_ID;
8.동물 수 구하기
SELECT COUNT(*) AS 'COUNT'
FROM ANIMAL_INS;
9.NULL 처리하기
SELECT ANIMAL_TYPE,
CASE WHEN NAME IS NULL THEN 'No name' ELSE NAME END AS 'NAME',
SEX_UPON_INTAKE
FROM ANIMAL_INS;
10.오랜 기간 보호한 동물(1)
SELECT NAME,DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (
SELECT ANIMAL_ID
FROM ANIMAL_OUTS
)
ORDER BY DATETIME
LIMIT 3
