SQL 3일차
벌서 3일차 이다 차근차근 문제를 풀어나가면서 실습 활동을 통해 적응하고 추후에는 데이터베이스에 이론에 대해서 공부하고 이해한 내용을 이 곳에 풀고자 한다.
ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.
NAMETYPENULLABLE
| 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 |
1. 동명 동물 수 찾기
동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.
- Raven 이름은 2번 쓰였습니다.
- Lucy 이름은 3번 쓰였습니다
- Shadow 이름은 1번 쓰였습니다.
따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.
NAME COUNT
| Lucy | 3 |
| Raven | 2 |
이 문제에서는 첫 번쨰로 고려해야하는 것은 2번 이상이라고 명시되어있다 따라서 1번은 출력에서 제외해야한다.
또한 '이름이 없는 동물은 집계에서 제외' 또한 null처리를 명시해야한다.
따라서 필자는 처음에
SELECT name , count(name) as 'COUNT' from animal_ins where name is not null group by name having count(name)>=2 order by name; 를 작성했다.
count(name)으로 불러오되 where절에서 이름에대한 null처리를 명시해주고 group by를 통해 이름을 그룹화 시키고 having 조건절을 통하여 2건이상부터 출력시키게 작성하였다.
이를 작성하면서 count명령어는 null처리를 해야하는 것으로 알고있었지만 위에 count(name)으로 작성할 경우 뒤에 있는 where name is not null명령어를 없애도 상관이 없다.
SELECT name , count(name) as 'COUNT' from animal_ins group by name having count(name)>=2 order by name;
다만 count(*)로 작성하였을때는 무조건 where name is not null 를 작성해야 한다.
SELECT name , count(*) as 'COUNT' from animal_ins where name is not null group by name having count(name)>=2 order by name;
2. 입양 시각 구하기
필자는 mysql로 작성하였고 Hour 함수를 이용하였다
SELECT Hour(datetime) as 'HOUR' , Count(*) as 'COUNT' from animal_outs where hour(datetime) >=9 and hour(datetime)<20 group by hour order by hour asc;
우선 Hour(datetime)을 통하여 시간별로 나눠서 출력하고자 하였고 조건절에서 9시부터 20시까지 구분을 조건절로 나누었고 시간별로 그룹화 하여 카운트하는데 사용하였다.
3.NULL 처리하기
이번 문제는 NULL처리를 명시한 문제이다.
필자는 이 문제를 보고 IFNULL로 접근하였다
SELECT animal_type, ifnull(name,'No name') as 'NAME', sex_upon_intake from animal_ins
명령어 풀이 :animal_ins 테이블에서 animal_type, name, sex_upon_intake 값을 출력하는데
name 필드에 값 중 NULL값이 존재할경우 'No name' 으로 대체 출력하라
또한 다른 방법으로는 COALESCE 명령어를 사용할 수 있다.
COALESCE명령어는 NULL값이 아닌 첫번째 값을 출력하는 명령어이다.
select animal_type, COALESCE(name,'No name') as 'NAME',sex_upon_intake from animal_ins;
name값 중에 NULL이 존재하면 그 다음값 No name을 출력하라
4.오랜 기간 보호한 동물(1)
Animal_Outs 테이블
| Name | TYPE | NULLABLE |
| ANIMAL_ID(FK) | VARCHAR(N) | FALSE |
| ANIMAL_TYPE | VARCHAR(N) | FALSE |
| DATETIME | DATETIME | FALSE |
| NAME | VARCHAR(N) | TRUE |
| SEX_UPON_OUTCOME | VARCHAR(N) | FALSE |
이 문제는 오랜 기간 보호한 동물(2)문제와 동일하게 animal_ins테이블 뿐만아니라 위와 같은 테이블(Animal_Outs)이 존재한다.
이 문제에서는 animal_ins 테이블에 있는 값들 중 animal_outs 테이블에 안들어간 데이터들을 뽑아야 한다.
여기서 animal_ins에 있는 값들만 출력되므로 필자는 left join을 사용하였다.
이 문제를 그림을 그려서 표현할 경우 이렇게 표현 가능하다.
SELECT ai.name,ai.datetime from animal_ins as ai left join animal_outs as ao on ai.animal_id=ao.animal_id where ao.animal_id is null order by ai.datetime asc limit 3;
이문제에 핵심은 바로 '입양이 안된 동물들이다' INS테이블에 값은 있지만 OUTS테이블에는 값이 없어야한다. 따라서
LEFT JOIN을 할 경우 입양이 안된 동물은 NULL값이 나오므로 OUTS 값에 is null처리를 함으로써 입양안간 상위 3마리 동물이 출력할 수 있었다.
필자는 Inner join으로만 풀어왔기에 left join 문제는 생소하였다. 이러한 문제를 풀면서 Join 종류에대해서 이론적으로만 알고있었던걸 직접 풀면서 이해가 안될경우 스스로 그림이나 , 표를 통하여 이해하려고 한다. 이 문제를 스스로 해결함으로써 추후 left join뿐만아니라 right join 문제도 응용하고자 한다.