[SQL 고득점 Kit] String, Date

 

📍 [루시와 엘라 찾기]

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

 

📍 [이름에 el이 들어가는 동물 찾기]

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS
WHERE NAME LIKE "%EL%" AND ANIMAL_TYPE='DOG' 
ORDER BY NAME

 

 

📍 [중성화 여부 파악하기]

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 ;

 

 

📍 [오랜기간 보호한 동물(2)]

SELECT INS.ANIMAL_ID, INS.NAME 
FROM ANIMAL_INS INS JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID 
ORDER BY DATEDIFF(INS.DATETIME,OUTS.DATETIME) ASC LIMIT 2

 

 

📍 [DATETIME에서 DATE로 형변환]

SELECT ANIMAL_ID, NAME, date_FORMAT(DATETIME, '%Y-%m-%d')
FROM ANIMAL_INS ORDER BY ANIMAL_ID;