開発日記)2022年1月6日

今日の目標
今日は昨日設定した目標通りISNULL JOIN String Dateを解きます
プログラマSQLコードテスト練習
これはIS NULL部とJOIN部、String, Date部の問題です.
IS NULL
無名動物のID
IS NULLを使うと簡単に解けます.

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

名前のついた動物のID
IS NOT NULLを使うと簡単に解けます.

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

処理NULL
IFNULLで解くことができます.

SELECT ANIMAL_TYPE, IFNULL(NAME, "No name"), SEX_UPON_INTAKE
FROM ANIMAL_INS

JOIN
失われたレコードの検索

SELECT B.ANIMAL_ID, B.NAME
FROM ANIMAL_INS A
RIGHT JOIN ANIMAL_OUTS B 
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.ANIMAL_ID IS NULL

いいえ.

SELECT ANIMAL_ID, A.NAME
FROM ANIMAL_INS A
JOIN ANIMAL_OUTS B USING (ANIMAL_ID)
WHERE A.DATETIME > B.DATETIME
ORDER BY A.DATETIME

長期保護動物(1)

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I
LEFT JOIN ANIMAL_OUTS O USING (ANIMAL_ID)
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME
LIMIT 3

中性化した動物を保護する

SELECT ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM ANIMAL_INS I
JOIN ANIMAL_OUTS O USING (ANIMAL_ID)
WHERE I.SEX_UPON_INTAKE != O.SEX_UPON_OUTCOME
ORDER BY ANIMAL_ID

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

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%" OR SEX_UPON_INTAKE LIKE "%Spayed%" THEN "O"
    ELSE "X"
END AS "중성화"
FROM ANIMAL_INS

長期保護動物(2)

SELECT ANIMAL_ID, O.NAME
FROM ANIMAL_INS I
JOIN ANIMAL_OUTS O USING (ANIMAL_ID)
ORDER BY DATEDIFF(I.DATETIME, O.DATETIME)
LIMIT 2

DATETIMEからDATEへの変換
SELECT ANIMAL ID,NAME,DATE FORMAT(DATETIME,"%Y-%m-%d")AS"日付"

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

明日の目標