역시 코테는 문제를 풀어봐야 배우는 점이 많다.
너무 쉬운 문제나, 직전 문제의 아이디어를 알면 바로 해결할 수 있는 문제는 작성하지 않았다.
적절한 접근인지 의문이 드는 문제들(특히 대장균 시리즈)은 Perplexity(GPT-4o)에게 물어본 쿼리도 함께 공부해서 확인해보았다.
(이전 문제들은 올리신 분들이 많아서 추후 추가 예정)
평균 일일 대여 요금 구하기
테이블에는 각 자동차의 ID, 종류, 대여 요금이 주어진다.
자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 구하여라.
이때, 일일 대여 요금은 소수 첫번째 자리에서 반올림한다.
select round(avg(DAILY_FEE), 0) as AVERAGE_FEE
from CAR_RENTAL_COMPANY_CAR
where CAR_TYPE = 'SUV'
group by CAR_TYPE- 평균을 구할 때는
avg()함수를 사용하면 되고, 반올림을 할 때는round(반올림할 수, 나타낼 소수점 자릿수)를 사용하면 된다.
흉부외과 또는 일반외과 의사 목록 출력하기
테이블에는 의사의 이름, ID, 고용일자, 진료과코드 등이 주어진다.
진료과가 흉부외과(CS)거나 일반외과(GS)인 의사의 이름, ID, 진료과, 고용일자를 구하여라.
결과는 고용일자의 내림차순, 같다면 이름의 오름차순으로 정렬하여 나타낸다.
select DR_NAME, DR_ID, MCDP_CD, date_format(HIRE_YMD, "%Y-%m-%d") as HIRE_YMD
from DOCTOR
where MCDP_CD = 'CS' or MCDP_CD = 'GS'
order by HIRE_YMD desc, DR_NAME ascdate_format()함수를 사용하여 DATE또는 DATETIME 자료형의 출력 형태를 변화시킬 수 있다.- 테이블의 고용 일자의 자료형이 DATE라서 형태를 변환하지 않아도 될 줄 알았지만, select를 하면 신기하게도 DATETIME 형태로 나타난다.
- 출력 형태를 설정하는 대신
date()함수를 사용해보기도 했지만, 신기하게도 DATETIME으로 나타난다.... 그냥date_format()을 사용해야겠다.
서울에 위치한 식당 목록 출력하기
REST_INFO 테이블은 식당 ID, 이름, 음식 종류, 즐겨찾기수, 주소 등을 나타낸다.
REST_REVIEW 테이블은 리뷰 ID, 식당 ID, 리뷰 점수 등을 나타낸다.
서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 얻어라.
이때 리뷰 평균 점수는 소수점 세번째 자리에서 반올림한다.
결과는 평균 점수의 내림차순, 같다면 즐겨찾기수의 내림차순으로 정렬한다.
select REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS, round(avg(REVIEW_SCORE), 2) as SCORE
from REST_INFO join REST_REVIEW using(REST_ID)
where ADDRESS like "서울%"
group by REST_ID
order by SCORE desc, FAVORITES desc- 문자열을 비교하는
like에 와일드카드인%를 사용해서 서울에 위치한 식당을 얻고, 식당 ID로 그룹화하여 리뷰 평균 점수를 얻었다.
12세 이하인 여자 환자 목록 출력하기
테이블은 환자 번호, 이름, 성별코드, 나이, 전화번호가 주어진다.
12세 이하인 여자환자의 환자 이름, 번호, 성별코드, 나이, 전화번호를 나타내라.
이때 전화번호가 없는 경우에는 'NONE'으로 출력한다.
결과는 나이의 내림차순, 같다면 환자이름의 오른차순으로 정렬한다.
select PT_NAME, PT_NO, GEND_CD, AGE, ifnull(TLNO, 'NONE') as TLNO
from PATIENT
where AGE <= 12 and GEND_CD = 'W'
order by AGE desc, PT_NAME ascifnull(열, 출력할 값)을 사용하면 해당 열의 값이 null일 때 특정 값으로 나타낼 수 있다.
재구매가 일어난 상품과 회원 리스트 구하기
테이블에는 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일이 주어진다.
동일한 회원이 동일한 상품을 재구매한 적이 있는 경우에 대해 회원 ID와 상품 ID를 구하여라.
결과는 회원 ID의 오름차순, 같다면 상품 ID의 내림차순으로 정렬한다.
select USER_ID, PRODUCT_ID
from ONLINE_SALE
group by USER_ID, PRODUCT_ID
having count(*) > 1
order by USER_ID asc, PRODUCT_ID desc- 먼저 회원 ID와 상품 ID로 그룹화를 한다. 이때, 각 그룹의 크기가 1 초과인 경우만 선택하도록 하여 재구매가 일어난 그룹만 나타나도록 한다.
상위 n개 레코드
테이블에는 동물 ID, 이름, 보호 시작일이 주어진다.
동물 보호소에 가장 먼저 들어온 동물의 이름을 얻어라.
select NAME
from ANIMAL_INS
order by DATETIME
limit 1limit을 이용해서 상위 n개의 레코드만 보는 설정이 가능하다.
대장균들의 자식의 수 구하기
테이블에는 각 ID의 부모 ID가 주어진다.
이때, 각 ID의 자식 개수를 구하여라.
출력은 ID의 오름차순으로 나타낸다.
나의 풀이
select ID, IFNULL(CHILD_COUNT, 0) as CHILD_COUNT
from ECOLI_DATA natural left outer join (
select PARENT_ID as ID, count(PARENT_ID) as CHILD_COUNT
from ECOLI_DATA
group by PARENT_ID
having PARENT_ID is not null
) as A
order by ID- 서브쿼리에서 각 ID의 자식 개수를 구했다. 다만 이렇게 끝내면 자식이 0인 ID가 나타나지 않는 문제가 있다.
- 그래서 해당 결과를 원래의 테이블과
left outer join을 수행해 전체 ID에 자식 개수를 연결해주고, 자식이 0인 경우에는 정보가 없어서 null로 되어 있을 테니ifnull을 활용해 0으로 설정해준다.
GPT-4o
SELECT ID, COUNT(CHILDREN.PARENT_ID) AS CHILD_COUNT
FROM ECOLI_DATA
LEFT JOIN ECOLI_DATA AS CHILDREN ON ECOLI_DATA.ID = CHILDREN.PARENT_ID
GROUP BY ECOLI_DATA.ID
ORDER BY ECOLI_DATA.ID ASC;left join을 수행하는 접근법은 동일했다. (left join과 left outer join은 동일하다)- 다만 나는 조인 이전에 자식 수를 세서 제공한 반면, 이 풀이는 조인으로 각 ID에 자식을 모두 연결한 뒤에 그 개수를 세는 방식이라는 점이 다르다.
대장균의 크기에 따라 분류하기 1
테이블에는 ID와 개체의 크기가 주어진다.
이때 개체의 크기가 100 이하인 경우 'LOW', 100 초과 1000 이하인 경우 'MEDIUM', 1000 초과인 경우 'HIGH'로 변경하여 ID와 개체의 크기를 출력하라.
결과는 ID의 오름차순으로 정렬하여 나타낸다.
나의 풀이
(select ID, 'LOW' as SIZE
from ECOLI_DATA
where SIZE_OF_COLONY <= 100)
union
(select ID, 'MEDIUM' as SIZE
from ECOLI_DATA
where SIZE_OF_COLONY > 100 and SIZE_OF_COLONY <= 1000)
union
(select ID, 'HIGH' as SIZE
from ECOLI_DATA
where SIZE_OF_COLONY > 1000)
order by ID- 컬럼을 새로 추가하며 각 조건에 부합하는 값을 지정했다.
- 이후 모두 합친 뒤에 정렬을 수행했다.
GPT-4o
SELECT ID,
CASE
WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
WHEN SIZE_OF_COLONY > 100 AND SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
ELSE 'HIGH'
END AS SIZE
FROM ECOLI_DATA
ORDER BY ID ASC;- 컬럼 추가 시 값의 조건을
CASE문을 활용해 나타내었다.
특정 형질을 가지는 대장균 찾기
테이블에는 각 개체의 ID와 형질이 주어진다.
2번 형질을 보유하지 않으면서 1번이나 3번 형질을 보유하는(1번과 3번 모두 보유한 경우도 포함) 개체 수를 구하여라.
다만 각 개체의 형질은 2진수의 형태로 주어진다.
나의 풀이
select count(ID) as COUNT
from ECOLI_DATA
where GENOTYPE & 2 = 0 and (GENOTYPE & 1 = 1 or GENOTYPE & 4 = 4)- 기본적으로 비트 연산인
&(and)를 사용했다. - 먼저 2번 형질을 포함하지 않으면서 1번 또는 3번을 포함한 경우를 선택했다.
- 이때
GENOTYPE & 4 = 4이 부분에서 실수했는데, 비트 단위 연산이기는 하지만 각 자리 숫자에 모두 연산한 결과를 얻기 떄문에 1이 아니라 4와 같다고 설정해야 한다.
GPT-4o
SELECT COUNT(*) AS COUNT
FROM ECOLI_DATA
WHERE (GENOTYPE & 2) = 0 -- 2번 형질을 보유하지 않음
AND ((GENOTYPE & 1) != 0 OR (GENOTYPE & 4) != 0); -- 1번 또는 3번 형질을 보유- 기본적으로 동일한 아이디어이다. 이처럼
COUNT(*)를 사용하는 것이 더 적절한 것 같긴 하다. AS COUNT는 작성해주지 않아서 직접 추가했다.
부모의 형질을 모두 가지는 대장균 찾기
테이블에는 개체의 ID와 형질, 그리고 부모 개체의 ID가 주어진다.
부모의 형질을 모두 보유한 개체의 ID와 형질, 그리고 부모 개체의 형질을 구하여라.
결과는 ID의 오름차순으로 정렬하여 나타내라.
다만 각 개체의 형질은 2진수의 형태로 주어진다.
나의 풀이
select A.ID, A.GENOTYPE GENOTYPE, B.GENOTYPE PARENT_GENOTYPE
from ECOLI_DATA A left join ECOLI_DATA B on(A.PARENT_ID = B.ID)
where A.GENOTYPE & B.GENOTYPE = B.GENOTYPE
order by A.ID- 먼저
left join을 수행해 각 개체에 부모 정보를 결합했다. - 이후 부모 형질을 모두 갖는 개체를 선택하기 위해 개체의 형질과 부모 형질 간의 비트 연산
&를 사용했다. 부모의 형질을 모두 갖고 있다면&를 했을 때 부모 형질이 그대로 나타나야 한다.
GPT-4o
SELECT CHILD.ID, CHILD.GENOTYPE, PARENT.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA AS CHILD
JOIN ECOLI_DATA AS PARENT ON CHILD.PARENT_ID = PARENT.ID
WHERE (CHILD.GENOTYPE | PARENT.GENOTYPE) = CHILD.GENOTYPE
ORDER BY CHILD.ID ASC;left join을 수행해 자식 정보와 부모 정보를 결합한다는 아이디어는 동일하다.- 다만 형질 비교에
|(or)을 사용했다는 차이가 있다. 이러한 방식도 가능한 이유는, 자식이 부모 형질을 가지고 있지 않으면 or을 수행했을 때 자식 형질이 그대로 얻어지지 않기 때문이다.
대장균의 크기에 따라 분류하기 2
테이블에는 개체의 ID와 크기가 주어진다.
개체의 크기를 내림차순으로 정렬했을 때 상위 25%까지를 'CRITICAL', 50%까지를 'HIGH', 75%까지를 'MEDIUM', 나머지를 'LOW'라고 분류할 수 있다.
이런 상황에서 개체의 ID와 분류된 이름을 얻어라. 결과는 개체의 ID에 대해 오름차순으로 정렬하여 나타낸다.
이때 총 데이터의 수는 4의 배수이며, 같은 크기 개체가 다른 이름으로 분류되는 경우는 없다.
나의 풀이
select A.ID,
case
when A.SIZE_RANK <= A.COUNT * 0.25 then 'CRITICAL'
when A.SIZE_RANK > A.COUNT * 0.25 and A.SIZE_RANK <= A.COUNT * 0.50 then 'HIGH'
when A.SIZE_RANK > A.COUNT * 0.50 and A.SIZE_RANK <= A.COUNT * 0.75 then 'MEDIUM'
else 'LOW'
end as COLONY_NAME
from (select ID, row_number() over(order by SIZE_OF_COLONY desc) as SIZE_RANK, count(*) over() as COUNT
from ECOLI_DATA) as A
order by A.ID- 기본적인 아이디어는 크기 순으로 정렬하여 크기 순위를 얻고, 이를 전체 개수와 비교해서 얻어지는 퍼센트에 따라 값을 설정하는 것이다.
- 처음에는 각 행의 순위를 얻는 방법을 몰랐는데,
row_number()라는 함수가 있었다. 뒤의over내에 정렬 방법을 설정해서 그에 대응하는 순위를 얻었다. - 유의할 점은,
group by없이 집계 함수count(*)를 다른 열과 함께 선택하면 에러가 발생한다는 것이다. 뒤에over()을 꼭 붙여주어야 에러가 발생하지 않는다. - 사실 가장 먼저 떠오른 방법은 퍼센트 단위를 얻는 것이었는데, 방법이 생각이 안 났다. 알고 보니
percent_rank()라는 함수가 있었으며, 적용 방식이row_number()와 유사하다. 이를 활용한 예시는 아래와 같다.
select A.ID,
case
when A.PERCENT <= 0.25 then 'CRITICAL'
when A.PERCENT > 0.25 and A.PERCENT <= 0.50 then 'HIGH'
when A.PERCENT > 0.50 and A.PERCENT <= 0.75 then 'MEDIUM'
else 'LOW'
end as COLONY_NAME
from (select ID, percent_rank() over(order by SIZE_OF_COLONY desc) as PERCENT, count(*) over() as COUNT
from ECOLI_DATA) as A
order by A.IDcase문에서 위아래 구간을 모두 표현했지만, 사실 순서대로 실행되기 때문에 이전 조건에서 처리될 조건은 적지 않아도 생각한 대로 동작하기는 한다.
GPT-4o
WITH RankedColony AS (
SELECT ID, SIZE_OF_COLONY,
NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS Quartile
FROM ECOLI_DATA
)
SELECT ID,
CASE Quartile
WHEN 1 THEN 'CRITICAL'
WHEN 2 THEN 'HIGH'
WHEN 3 THEN 'MEDIUM'
WHEN 4 THEN 'LOW'
END AS COLONY_NAME
FROM RankedColony
ORDER BY ID ASC;- 이는 같은 크기로 그룹을 나누는
NTILE()함수를 사용한 접근법이다. 전체적인 아이디어와 함수 활용 방법은 유사하다.
특정 세대의 대장균 찾기
테이블에는 개체의 ID, 부모의 ID가 주어진다.
3세대 개체의 ID를 얻어라.
결과는 ID의 오름차순으로 정렬해서 나타내어라.
이때 최초 개체의 부모 ID는 NULL이다.
나의 풀이
select A.ID
from (ECOLI_DATA A join ECOLI_DATA B on(A.PARENT_ID = B.ID))
join ECOLI_DATA C on(B.PARENT_ID = C.ID)
where C.PARENT_ID is null
order by A.ID- 3세대 개체라는 것은 부모의 부모가 1세대 개체라는 뜻이며, 1세대 개체의 부모 ID는 NULL 값을 가진다.
- 따라서 각 개체의 부모의 부모를 찾아서 연결한 뒤, 부모의 부모의 부모 ID 값이 NULL인 경우를 찾았다.
- 다만 부모의 부모를 찾는 과정에서 1세대 개체와 2세대 개체에 대해 조심히 접근해야 하는데, 여기서는 오히려
outer join대신 그냥join을 사용해서 이로 인한 문제를 회피했다.
GPT-4o
SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IN (
SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IN (
SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL
)
)
ORDER BY ID ASC;- 이것도 아이디어는 동일한데, 1세대 개체를 찾고, 2세대 개체를 찾고, 마지막으로 3세대 개체를 찾는다는 순서로 수행된다.
- 이 방식은
join대신in을 사용하기 때문에 나의 접근보다 더 효율적일 것 같다.
멸종위기의 대장균 찾기
테이블에는 개체의 ID, 부모 ID가 주어진다.
각 세대에 대해 그 세대 번호와 세대 별 자식이 없는 개체의 수를 구하여라.
결과는 세대에 대해 오름차순으로 나타낸다.
이때, 모든 세대에는 자식이 없는 개체가 적어도 1개체 존재한다.
나의 풀이
with recursive RECURSION as(
select ID, PARENT_ID, 1 as GENERATION
from ECOLI_DATA
where PARENT_ID is null
union all
select A.ID, A.PARENT_ID, REC.GENERATION+1 as GENERATION
from ECOLI_DATA A join RECURSION REC on(A.PARENT_ID = REC.ID)
where A.PARENT_ID = REC.ID
)
select count(*) as COUNT, GENERATION
from RECURSION
where ID not in (select PARENT_ID from ECOLI_DATA where PARENT_ID is not null)
group by GENERATION
order by GENERATION- 기본적인 아이디어는 부모 ID가 null인 1세대부터 재귀로 돌면서 각 개체의 세대를 기록하고, 자식이 없는 개체 수를 얻기 위해 부모 ID에 해당하지 않는 ID의 개수를 얻는 것이다.
- 처음에는 재귀를 수행하는 방법을 몰랐는데,
with recursion을 사용하면 되는 것이었다. - 그 내부에서는 먼저 초기 상태를 설정해주고, 이를 재귀적으로 사용하는 부분과
union해서 결과를 얻는 것이다. - 특이한 점은 자식이 없는 개체를 찾을 때
PARENT_ID is not null이라는 조건이 필요하다는 것이었다. 이는 null과not in을 수행하면 전체가 부정이 되어버리는 상황이 발생하기 때문이었다.
GPT-4o
WITH RECURSIVE RecursiveGenerations AS (
SELECT ID, PARENT_ID, 1 AS GENERATION
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL
UNION ALL
SELECT e.ID, e.PARENT_ID, rg.GENERATION + 1
FROM ECOLI_DATA e
JOIN RecursiveGenerations rg ON e.PARENT_ID = rg.ID
),
LeafNodes AS (
SELECT ID
FROM ECOLI_DATA
WHERE ID NOT IN (SELECT DISTINCT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL)
)
SELECT COUNT(rg.ID) AS COUNT, rg.GENERATION
FROM RecursiveGenerations rg
JOIN LeafNodes ln ON rg.ID = ln.ID
GROUP BY rg.GENERATION
ORDER BY rg.GENERATION ASC;- 완전히 동일한 아이디어이다. 다만 자식이 없는 개체를
LeafNodes라는 이름의 CTE(Common Table Expression)로 따로 얻었다는 차이점이 있다. - 사실 처음에는
with recursive가 아니라 그냥with로 적혀있어서 에러가 발생했는데, 해당 부분을 수정해서 작동하도록 했다.
