[SQL] SQL 고득점 Kit - String, Date

---

루시와 엘라 찾기

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

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

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS
WHERE NAME LIKE "%el%"
AND ANIMAL_TYPE = "Dog"
ORDER BY NAME ASC;

중성화 여부 파악하기

• CASE

SELECT ANIMAL_ID, NAME,
    CASE WHEN SEX_UPON_INTAKE IN ("Neutered Male", "Spayed Female") THEN "O"
    Else "X"
    END AS "중성화"
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

https://wikidocs.net/31639

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

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A
JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY A.DATETIME - B.DATETIME ASC
LIMIT 2;

DATETIME에서 DATE로 형 변환

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A
JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY A.DATETIME - B.DATETIME ASC
LIMIT 2;

우유와 요거트가 담긴 장바군

SELECT DISTINCT A.CART_ID
FROM (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = "Milk") A
INNER JOIN (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = "Yogurt") B
ON A.CART_ID = B.CART_ID
ORDER BY A.CART_ID;

해비 유저가 소유한 장소

SELECT DISTINCT A.ID AS ID, A.NAME, A.HOST_ID
FROM (SELECT * FROM PLACES) A
INNER JOIN (SELECT * FROM PLACES) B
ON A.HOST_ID = B.HOST_ID
WHERE A.ID != B.ID AND A.HOST_ID = B.HOST_ID
ORDER BY A.ID;

SELECT *
FROM PLACES A
WHERE EXISTS (SELECT * 
              FROM PLACES B
              WHERE A.HOST_ID = B.HOST_ID
              GROUP BY HOST_ID
              HAVING COUNT(*) > 1
             )
ORDER BY ID;

정리

• MySQL은 LIKE로 검사시 대소문자 구분 x

  BINARY(NAME) LIKE ‘%el%’ 이라고 쓰면 구분

• ORDER BY 에 DATETIME

• DATETIME to DATE : date_format(열, ‘%y-%m-%d’) date_format() 소문자로 써야함

  ‘%Y-%m-%d’ : 18-01-22 ‘%Y-%M-%D’ : 2018-January-22nd

참고

출처: 프로그래머스 SQL 고득점 Kit