💁‍♂️ SQL - IS NULL 8 문항

IS NULL

1. 경기도에 위치한 식품창고 목록 출력하기(Level 1)

SELECT warehouse_id,
       warehouse_name,
       address,
       IFNULL(freezer_yn, 'N') AS freezer_yn
FROM FOOD_WAREHOUSE
WHERE address LIKE '경기%'
ORDER BY warehouse_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/131114

2. 이름이 없는 동물의 아이디(Level 1)

SELECT animal_id
FROM ANIMAL_INS
where name IS NULL
ORDER BY animal_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/59039

3. 이름이 있는 동물의 아이디(Level 1)

SELECT animal_id
FROM ANIMAL_INS
WHERE name IS NOT NULL
ORDER BY animal_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/59407

4. NULL 처리하기(Level 2)

SELECT animal_type,
       IFNULL(name, 'No name'),
       sex_upon_intake
FROM ANIMAL_INS
ORDER BY animal_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/59410

5. 나이 정보가 없는 회원 수 구하기(Level 1)

SELECT COUNT(*) AS users
FROM USER_INFO
WHERE age IS NULL;

• https://school.programmers.co.kr/learn/courses/30/lessons/131528

6. ROOT 아이템 구하기(Level 2)

SELECT II.item_id,
       II.item_name
FROM ITEM_TREE IT JOIN ITEM_INFO II 
ON IT.item_id = II.item_id
WHERE IT.parent_item_id IS NULL
ORDER BY item_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/273710

7. 업그레이드 할 수 없는 아이템 구하기(Level 3)

SELECT II.item_id,
       II.item_name,
       II.rarity
FROM ITEM_TREE IT JOIN ITEM_INFO II
ON IT.item_id = II.item_id
WHERE II.item_id NOT IN (
    SELECT DISTINCT parent_item_id
    FROM ITEM_TREE
    WHERE parent_item_id IS NOT NULL
)
ORDER BY II.item_id DESC;

• https://school.programmers.co.kr/learn/courses/30/lessons/273712

8. 잡은 물고기의 평균 길이 구하기(Level 1)

SELECT ROUND(avg(IFNULL(length, 10)), 2) AS average_length
FROM FISH_INFO;

• https://school.programmers.co.kr/learn/courses/30/lessons/293259