SELECT - FROM만으로 해결되는 너무 간단한 문제를 제외하고 작성하고자 한다.
중복 제거하기
테이블에는 동물의 이름이 주어진다.
중복되지 않는 동물의 이름 수를 구하여라.
이때 이름이 null인 경우는 포함하지 않는다.
select count(distinct NAME) as COUNT
from ANIMAL_INS- 굉장히 간단한 문제인데,
count에서 중복을 허용하지 않을 때distinct를 쓴다는 것은 알았지만, 정확히 어디에 적어야 하는 지를 까먹었었다.
가격이 제일 비싼 식품의 정보 출력하기
테이블에서 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격이 주어진다.
가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 얻어라.
select *
from FOOD_PRODUCT
where PRICE = (select max(PRICE) from FOOD_PRODUCT)- 먼저 가장 비싼 식품의 가격을 얻은 후, 이에 해당하는 행의 모든 정보를 출력했다.
- 서브쿼리를 사용하지 않고 한 번에 수행하고 싶었지만, 해보니까
where절에서는max를 사용할 수가 없어서 불가능했다. - 혹시 더 효율적인 방법이 있을지 GTP-4o에 물어봤는데, 똑같이 풀었다.
물고기 종류 별 대어 찾기
FISH_INFO 테이블에서는 잡은 물고기의 ID, 물고기의 종류(숫자), 잡은 물고기의 길이(cm), 물고기를 잡은 날짜가 주어진다.
FISH_NAME_INFO 테이블에서는 물고기의 종류(숫자), 물고기의 이름(문자)이 주어진다.
물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력해라.
결과는 물고기의 ID에 대해 오름차순으로 정렬하여 나타낸다.
나의 풀이
select ID, FISH_NAME, LENGTH
from FISH_INFO join FISH_NAME_INFO using(FISH_TYPE)
where (FISH_TYPE, LENGTH) in (
select FISH_TYPE, max(LENGTH) as LENGTH
from FISH_INFO
group by FISH_TYPE
)
order by ID- 먼저 서브쿼리로 각 생선 종류의 최대 길이를 구한 다음, 이러한 정보를 가진 행만 선택하도록 했다.
- FISH_NAME_INFO과 조인하는 부분은 단순히 물고기 이름을 얻는 목적이다.
- 원래는 서브쿼리를 사용하지 않으려고 했지만, 그러면 최대 길이 생선의 ID를 동시에 얻을 방법이 없다는 문제가 있다.
- 이 문제에서는
(FISH_TYPE, LENGTH)를 비교할 때=가 아니라in을 사용하는 것이 맞는데, 서브쿼리에서 여러 생선으로 리스트가 주어지기 때문이다.
GPT-4o
WITH MaxLengthFish AS (
SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LENGTH
FROM FISH_INFO
GROUP BY FISH_TYPE
)
SELECT FI.ID, FNI.FISH_NAME, FI.LENGTH
FROM FISH_INFO FI
JOIN MaxLengthFish MLF ON FI.FISH_TYPE = MLF.FISH_TYPE AND FI.LENGTH = MLF.MAX_LENGTH
JOIN FISH_NAME_INFO FNI ON FI.FISH_TYPE = FNI.FISH_TYPE
ORDER BY FI.ID;- 처음에
MaxLengthFish라는 CTE를 선언해서 코드가 길어보이지만, 최대 길이 생선을 구한 후 조인으로 정보를 합친다는 점에서 기본적인 아이디어는 동일하다.
잡은 물고기 중 가장 큰 물고기의 길이 구하기
테이블에는 물고기의 ID, 길이가 주어진다.
물고기 중 가장 큰 물고기의 길이를 'cm' 를 붙여 출력해라.
select concat(max(LENGTH), 'cm') as MAX_LENGTH
from FISH_INFO- 결과에 다른 문자열을 더해주기 위해서는
concat함수를 사용하여 이어 붙이면 된다. max(length)는 숫자형이지만,concat을 사용하면 알아서 문자열로 변경하고 합쳐준다.- 처음에는
+연산자로도 가능하지 않나 생각했는데, 안 되는 것 같다.
연도별 대장균 크기의 편차 구하기
테이블에는 개체의 ID, 크기, 분화된 날짜가 주어진다.
분화된 연도, 연도별 크기 편차, ID를 출력하라.
이때 연도별 크기 편차는 해당 연도의 가장 큰 개체의 크기에서 현 개체의 크기를 빼는 방식으로 얻는다.
결과는 연도의 오름차순, 같은 연도에서는 크기의 편차의 오름차순으로 정렬하여 나타낸다.
select YEAR, MAX_SIZE - SIZE_OF_COLONY as YEAR_DEV, ID
from ECOLI_DATA A join (
select year(DIFFERENTIATION_DATE) as YEAR, max(SIZE_OF_COLONY) as MAX_SIZE
from ECOLI_DATA
group by year(DIFFERENTIATION_DATE)) as B on (year(A.DIFFERENTIATION_DATE)=B.YEAR)
order by YEAR asc, YEAR_DEV asc- 먼저 각 년도의 최대 크기를 구한 뒤, 이를 join해서 각 개체에 정보를 연결해주고, 필요한 값을 계산하여 얻는 방식으로 해결했다.
group by와join on조건에year()함수를 사용한 값도 들어올 수 있다는 것이 인상적이다.- GPT-4o에게 물어보니 똑같은 방식으로 해결했다.
