📌 루시와 엘라 찾기

🔥 in

🗨 문제

여러 이름들을 주고, 이름들을 포함하는 데이터 찾기

📍 내 답

SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE FROM ANIMAL_INS
WHERE NAME = "Lucy" OR
NAME = "Ella" OR
NAME = "Pickle" OR
NAME = "Rogan" OR
NAME = "Sabrina" OR
NAME = "Mitty"
ORDER BY ANIMAL_ID

📍 다른 답

SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE FROM ANIMAL_INS
WHERE NAME in ("Lucy","Ella","Pickle","Rogan","Sabrina","Mitty")
ORDER BY ANIMAL_ID

📌 이름에 EL이 들어가는 Dog

🔥 Wildcard

https://www.w3schools.com/sql/sql_wildcards.asp

http://www.tcpschool.com/mysql/mysql_operator_patternMatching

📍 답

SELECT ANIMAL_ID,NAME FROM ANIMAL_INS
WHERE name LIKE "%EL%" AND animal_type = "Dog"
ORDER BY NAME

📌 중성화 여부 파악하기

🔥 CASE - WHEN - THEN - ELSE - END

📍 답

SELECT ANIMAL_ID,NAME,
    CASE
    WHEN SEX_UPON_INTAKE LIKE "%Neutered Male%" OR SEX_UPON_INTAKE LIKE "%Spayed Female"
    THEN "O"
    ELSE "X"
    END 
    AS "중성화"
FROM ANIMAL_INS

📌 오랜 기간 보호한 동물 (2)

🗨 문제

📍 같은 Date type 끼리는 연산이 가능하다.

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

📌 DATETIME에서 DATE로 형변환

🔥 DATE_FORMAT(paramter,'%Y-%m-%d')

https://murra.tistory.com/157

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