1. 경기도에 위치한 식품창고 목록 출력하기
문제
https://school.programmers.co.kr/learn/courses/30/lessons/131114
다음은 식품창고의 정보를 담은 FOOD_WAREHOUSE 테이블입니다. FOOD_WAREHOUSE 테이블은 다음과 같으며 WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, TLNO, FREEZER_YN는 창고 ID, 창고 이름, 창고 주소, 전화번호, 냉동시설 여부를 의미합니다.
| Column name | Type |
|---|---|
| WAREHOUSE_ID | VARCHAR(10) |
| WAREHOUSE_NAME | VARCHAR(20) |
| ADDRESS | VARCHAR(100) |
| TLNO | VARCHAR(20) |
| FREEZER_YN | VARCHAR(1) |
FOOD_WAREHOUSE 테이블에서 경기도에 위치한 창고의 ID, 이름, 주소, 냉동시설 여부를 조회하는 SQL문을 작성해주세요. 이때 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 주시고 결과는 창고 ID를 기준으로 오름차순 정렬해주세요.
구현
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '%경기도%'
ORDER BY WAREHOUSE_ID;2. 이름이 없는 동물의 아이디
문제
https://school.programmers.co.kr/learn/courses/30/lessons/59039
ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ANIMAL_ID | VARCHAR(N) | FALSE |
| ANIMAL_TYPE | VARCHAR(N) | FALSE |
| DATETIME | DATETIME | FALSE |
| INTAKE_CONDITION | VARCHAR(N) | FALSE |
| NAME | VARCHAR(N) | TRUE |
| SEX_UPON_INTAKE | VARCHAR(N) | FALSE |
동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.
구현
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL;3. 이름이 있는 동물의 아이디
문제
https://school.programmers.co.kr/learn/courses/30/lessons/59407
ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ANIMAL_ID | VARCHAR(N) | FALSE |
| ANIMAL_TYPE | VARCHAR(N) | FALSE |
| DATETIME | DATETIME | FALSE |
| INTAKE_CONDITION | VARCHAR(N) | FALSE |
| NAME | VARCHAR(N) | TRUE |
| SEX_UPON_INTAKE | VARCHAR(N) | FALSE |
동물 보호소에 들어온 동물 중, 이름이 있는 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.
구현
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID;4. NULL 처리하기
문제
https://school.programmers.co.kr/learn/courses/30/lessons/59410
ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ANIMAL_ID | VARCHAR(N) | FALSE |
| ANIMAL_TYPE | VARCHAR(N) | FALSE |
| DATETIME | DATETIME | FALSE |
| INTAKE_CONDITION | VARCHAR(N) | FALSE |
| NAME | VARCHAR(N) | TRUE |
| SEX_UPON_INTAKE | VARCHAR(N) | FALSE |
입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.
구현
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS ORDER BY ANIMAL_ID;5. 나이 정보가 없는 회원 수 구하기
문제
https://school.programmers.co.kr/learn/courses/30/lessons/131528
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며, USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.
| Column name | Type |
|---|---|
| USER_ID | INTEGER |
| GENDER | TINYINT(1) |
| AGE | INTEGER |
| JOINED | DATE |
GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다.
USER_INFO 테이블에서 나이 정보가 없는 회원이 몇 명인지 출력하는 SQL문을 작성해주세요. 이때 컬럼명은 USERS로 지정해주세요.
구현
SELECT COUNT(*) AS USERS FROM USER_INFO WHERE AGE IS NULL;6. ROOT 아이템 구하기
문제
https://school.programmers.co.kr/learn/courses/30/lessons/273710
어느 한 게임에서 사용되는 아이템들은 업그레이드가 가능합니다.
'ITEM_A'->'ITEM_B'와 같이 업그레이드가 가능할 때
'ITEM_A'를 'ITEM_B'의 PARENT 아이템,
PARENT 아이템이 없는 아이템을 ROOT 아이템이라고 합니다.
예를 들어 'ITEM_A'->'ITEM_B'->'ITEM_C' 와 같이 업그레이드가 가능한 아이템이 있다면
'ITEM_C'의 PARENT 아이템은 'ITEM_B'
'ITEM_B'의 PARENT 아이템은 'ITEM_A'
ROOT 아이템은 'ITEM_A'가 됩니다.
다음은 해당 게임에서 사용되는 아이템 정보를 담은 ITEM_INFO 테이블과 아이템 관계를 나타낸 ITEM_TREE 테이블입니다. ITEM_INFO 테이블은 다음과 같으며, ITEM_ID, ITEM_NAME, RARITY, PRICE는 각각 아이템 ID, 아이템 명, 아이템의 희귀도, 아이템의 가격을 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ITEM_ID | INTEGER | FALSE |
| ITEM_NAME | VARCHAR(N) | FALSE |
| RARITY | INTEGER | FALSE |
| PRICE | INTEGER | FALSE |
ITEM_TREE 테이블은 다음과 같으며, ITEM_ID, PARENT_ITEM_ID는 각각 아이템 ID, PARENT 아이템의 ID를 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ITEM_ID | INTEGER | FALSE |
| PARENT_ITEM_ID | INTEGER | TRUE |
단, 각 아이템들은 오직 하나의 PARENT 아이템 ID를 가지며, ROOT 아이템의 PARENT 아이템 ID는 NULL 입니다.
ROOT 아이템이 없는 경우는 존재하지 않습니다.
ROOT 아이템을 찾아 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME)을 출력하는 SQL문을 작성해 주세요. 이때, 결과는 아이템 ID를 기준으로 오름차순 정렬해 주세요.
구현
SELECT II.ITEM_ID, II.ITEM_NAME FROM ITEM_INFO II
JOIN ITEM_TREE IT ON II.ITEM_ID = IT.ITEM_ID
WHERE IT.PARENT_ITEM_ID IS NULL
ORDER BY II.ITEM_ID;7. 업그레이드 할 수 없는 아이템 구하기
문제
https://school.programmers.co.kr/learn/courses/30/lessons/273712
어느 한 게임에서 사용되는 아이템들은 업그레이드가 가능합니다.
'ITEM_A'->'ITEM_B'와 같이 업그레이드가 가능할 때
'ITEM_A'를 'ITEM_B'의 PARENT 아이템,
PARENT 아이템이 없는 아이템을 ROOT 아이템이라고 합니다.
예를 들어 'ITEM_A'->'ITEM_B'->'ITEM_C' 와 같이 업그레이드가 가능한 아이템이 있다면
'ITEM_C'의 PARENT 아이템은 'ITEM_B'
'ITEM_B'의 PARENT 아이템은 'ITEM_A'
ROOT 아이템은 'ITEM_A'가 됩니다.
다음은 해당 게임에서 사용되는 아이템 정보를 담은 ITEM_INFO 테이블과 아이템 관계를 나타낸 ITEM_TREE 테이블입니다. ITEM_INFO 테이블은 다음과 같으며, ITEM_ID, ITEM_NAME, RARITY, PRICE는 각각 아이템 ID, 아이템 명, 아이템의 희귀도, 아이템의 가격을 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ITEM_ID | INTEGER | FALSE |
| ITEM_NAME | VARCHAR(N) | FALSE |
| RARITY | INTEGER | FALSE |
| PRICE | INTEGER | FALSE |
ITEM_TREE 테이블은 다음과 같으며, ITEM_ID, PARENT_ITEM_ID는 각각 아이템 ID, PARENT 아이템의 ID를 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ITEM_ID | INTEGER | FALSE |
| PARENT_ITEM_ID | INTEGER | TRUE |
단, 각 아이템들은 오직 하나의 PARENT 아이템 ID를 가지며, ROOT 아이템의 PARENT 아이템 ID는 NULL 입니다.
ROOT 아이템이 없는 경우는 존재하지 않습니다.
더 이상 업그레이드할 수 없는 아이템의 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력하는 SQL 문을 작성해 주세요. 이때 결과는 아이템 ID를 기준으로 내림차순 정렬해 주세요.
구현
SELECT II.ITEM_ID, II.ITEM_NAME, II.RARITY FROM ITEM_INFO II
JOIN ITEM_TREE IT ON II.ITEM_ID = IT.ITEM_ID
WHERE II.ITEM_ID NOT IN (SELECT PARENT_ITEM_ID FROM ITEM_TREE WHERE PARENT_ITEM_ID IS NOT NULL)
ORDER BY II.ITEM_ID DESC;unknown
위 쿼리에서 NOT IN 절의 서브 쿼리의 마지막에 IS NOT NULL을 추가하지 않으면 아무것도 조회가 되지 않는다.
그 이유는 해당 서브 쿼리의 수행 결과에는 null이 포함되어 있는데, SQL에서는 null 값과의 비교, 논리 연산에서 단순히 true/false뿐만 아니라 unknown이라는 값을 반환할 수 있기 때문이다.
그리고 이 unknown이 포함된 AND, OR 연산의 결과는 다음과 같다.
- unknown과의
AND연산 결과
- unknown과의
OR연산 결과
IN 연산자는 파라미터 값들 간의 OR 연산을 수행한다.
NOT IN의 파라미터 값이 value1, value2, value3이라면
value1 OR value2 OR value3이 수행되는 것이다.
위 unknown과의 OR 연산 결과를 보면, TRUE 값과의 OR 연산 시 TRUE가 반환됨을 알 수 있다.
즉, value1의 값이 unknwon이라도 다른 value 중 어느 하나라도 true를 반환하면 전체 expression이 true를 반환하게 된다. 따라서 null 값이 존재하더라도 큰 문제가 없다.
반면, NOT IN 연산자는 파라미터 값들 간의 AND 연산을 수행한다.
NOT IN의 파라미터 값이 value1, value2, value3이라면
!value1 AND !value2 AND !value3이 수행되는 것이다.
위 unknown과의 AND 연산 결과를 보면, TRUE 값과 연산을 수행하더라도 여전히 unknown이 반환됨을 알 수 있다. 즉, 파라미터 값 중 어느 하나라도 null이라면 다른 value에 TRUE 값이 존재하더라도 전체 expression이 unknown을 반환하게 된다.
결론: NOT IN을 활용할 경우 서브쿼리 튜플에 null 값이 포함되면 안 된다.* 따라서 반드시 서브 쿼리 내에 IS NOT NULL 조건을 걸어주어야 한다.
8. 잡은 물고기의 평균 길이 구하기
문제
https://school.programmers.co.kr/learn/courses/30/lessons/293259
낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다. FISH_INFO 테이블의 구조는 다음과 같으며 ID, FISH_TYPE, LENGTH, TIME은 각각 잡은 물고기의 ID, 물고기의 종류(숫자), 잡은 물고기의 길이(cm), 물고기를 잡은 날짜를 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ID | INTEGER | FALSE |
| FISH_TYPE | INTEGER | FALSE |
| LENGTH | FLOAT | TRUE |
| TIME | DATE | FALSE |
단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH 에 NULL 만 있는 경우는 없습니다.
잡은 물고기의 평균 길이를 출력하는 SQL문을 작성해주세요.
평균 길이를 나타내는 컬럼 명은 AVERAGE_LENGTH로 해주세요.
평균 길이는 소수점 3째자리에서 반올림하며, 10cm 이하의 물고기들은 10cm 로 취급하여 평균 길이를 구해주세요.
구현
SELECT ROUND(AVG(IFNULL(LENGTH, 10.0)), 2) AS AVERAGE_LENGTH FROM FISH_INFO;